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

2024年9月26日PostgreSQLその他

月ごとに集計するSQL（date型、timestamp型の列）

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

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

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

２：月ごとの合計値の集計と並び順＋抽出条件を指定する例

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

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

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

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


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


--２．月ごとに集計する例（合計値と並び順、抽出条件を指定する）
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;          


--３．月ごとに集計する例（合計値と並び順、抽出条件、集計後の結果でさらに絞る）
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パターン用意してみましたので。ご参考に。

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

２：月ごとの合計値の集計と並び順＋抽出条件を指定する例

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

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

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


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


--２．月ごとに集計する例（合計値と並び順、抽出条件を指定する）
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;          


--３．月ごとに集計する例（合計値と並び順、抽出条件、集計後の結果でさらに絞る）
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;