月ごとに集計する、年ごとに集計するSQL【PostgreSQL】

2020年12月30日PostgreSQLその他

月ごとに集計するSQL(date型、timestamp型の列)

date型、timestamp型の列で月ごとに集計するSQLです。

 

3パターン用意してみましたので。ご参考に。

1:月ごとの合計値の集計と並び順を指定する例

2:月ごとの合計値の集計と並び順+抽出条件を指定する例

3:月ごとの合計値の集計と並び順+抽出条件+集計後の結果でさらに絞る例

 

※ 次の例はdate型で実行していますが、timestampでもSQLは全く同じです。

年で集計するには、「'YYYYMM'」となっているところを「'YYYY'」にして実行します。

--用意したテーブル
CREATE TABLE precipitation         -- 降水量を保持するテーブル
(
    observation_date1 date,        -- 日付(date型の列)
    amount numeric,                -- 降水量を登録しておく列
    CONSTRAINT pk_precipitation1 PRIMARY KEY (observation_date1)
);


--1.月ごとに集計する例(合計値と並び順を指定)
select 
   to_char(observation_date1,'YYYYMM') as observation_month -- 年月に変換
   , sum(amount)             -- (降水量の)合計値
from precipitation
group by observation_month   -- グループ化
order by observation_month;  -- 年月の順番にする


--2.月ごとに集計する例(合計値と並び順、抽出条件を指定する)
select 
   to_char(observation_date1,'YYYYMM') as observation_month
   , sum(amount)                       
from precipitation
where observation_date1 >= '20190401'  -- 20190401以降を対象にする
group by observation_month           
order by observation_month;          


--3.月ごとに集計する例(合計値と並び順、抽出条件、集計後の結果でさらに絞る)
select 
   to_char(observation_date1,'YYYYMM') as observation_month
   , sum(amount)                     
from precipitation
where observation_date1 > '20190331'
group by observation_month          
having sum(amount) > 200   -- 集計結果(ここでは合計値)で抽出する
order by observation_month;        

 

 

 

月ごとに集計するSQL(文字列型の列)

文字型の列で月ごとに集計するSQLです。

 

3パターン用意してみましたので。ご参考に。

1:月ごとの合計値の集計と並び順を指定する例

2:月ごとの合計値の集計と並び順+抽出条件を指定する例

3:月ごとの合計値の集計と並び順+抽出条件+集計後の結果でさらに絞る例

 

年で集計するには、「'YYYYMM'」となっているところを「'YYYY'」にして実行します。

--用意したテーブル
CREATE TABLE precipitation                    -- 降水量を保持するテーブル
(
    observation_date2 character varying(10),  -- 日付(文字型の列)YYYY/MM/DD形式
    amount numeric,                           -- 降水量を登録しておく列
    CONSTRAINT pk_precipitation1 PRIMARY KEY (observation_date2)
);


--1.月ごとに集計する例(合計値と並び順を指定)
select 
   -- 一度日付型に変換し、年月に変換
   to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
   , sum(amount)             -- (降水量の)合計値
from precipitation
group by observation_month   -- 年月でグループ化
order by observation_month;  -- 年月の順番にする


--2.月ごとに集計する例(合計値と並び順、抽出条件を指定する)
select 
   to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
   , sum(amount)                       
from precipitation
-- 20190401以降を対象にする
where cast(observation_date2 as date) >= cast('20190401' as date)
group by observation_month           
order by observation_month;          


--3.月ごとに集計する例(合計値と並び順、抽出条件、集計後の結果でさらに絞る)
select 
   to_char(cast(observation_date2 as date),'YYYYMM') as observation_month
   , sum(amount)                     
from precipitation
where cast(observation_date2 as date) >= cast('20190401' as date)
group by observation_month          
having sum(amount) > 200  -- 集計結果(ここでは合計値)で抽出する
order by observation_month;