PostgreSQLで集計をとる時にキク技（日毎集計）

PostgreSQL database
2020.05.30

今日は久しぶりに業務に直結することを、自分の覚書も兼ねて記事にしようと思います。今回のテスト環境のバージョンは10.2です。

サンプルで使う表 sales を作成する

例えば売上日・売上金額2列だけのテーブルがあったとします。適当に売上データも作っておきましょう。

CREATE TABLE sales(uriagebi date, amount integer);

INSERT INTO sales(uriagebi, amount) VALUES(‘2020-05-01’, 1000);
INSERT INTO sales(uriagebi, amount) VALUES(‘2020-05-03’, 1000);
INSERT INTO sales(uriagebi, amount) VALUES(‘2020-05-03’, 1000);
INSERT INTO sales(uriagebi, amount) VALUES(‘2020-05-05’, 1000);
INSERT INTO sales(uriagebi, amount) VALUES(‘2020-05-07’, 1000);

テーブルの全列へ定義順にINSERTするときはこれでもOKです。

INSERT INTO sales VALUES(‘2020-05-01’, 1000);

sales表をSELECTしてみる

テーブルはこんな感じになっています。

SELECT uriagebi, amount FROM sales ORDER BY uriagebi;
  uriagebi | amount
———————————+——————————-
2020-05-01 | 1000
2020-05-03 | 1000
2020-05-03 | 1000
2020-05-05 | 1000
2020-05-05 | 1000
(5 rows)

ORDER BYを付けなくても今は上記と同じ状態になると思いますが、ORDER BYを付けない場合SELECTの順序は保証されません。SELECT文にはORDER BYを付けるようにしましょう。では、ここで日毎の集計を取得します。

日毎に売上を集計する

SELECT
 uriagebi,
 SUM(amount)
FROM
 sales
GROUP BY
 uriagebi
ORDER BY
 uriagebi;
  uriagebi | amount
———————————+——————————-
2020-05-01 | 1000
2020-05-03 | 2000
2020-05-05 | 2000
(3 rows)

集計関数（ここではSUM）が付かない列（ここではuriagebi）をGROUP BY句へ入れるのを忘れないようにしましょう。

よくある要望

さて、これでも集計は正しく取れているのですが「売上がゼロの日付も表示したい」「売上がゼロの日は0を出力したい」ということがよくあります。「よくある」と言うよりも、むしろそちらの方が多いかもしれません。このとき例えば作業表を使って連続する日付をINSERTし

UPDATE
 hoge
SET
 amount = (SELECT
            COALESCE(SUM(amount), 0)
           FROM
            sales
           WHERE
            uriagebi = 作業表の日付列の値);

みたいなことをFUNCTIONでやってしまうようなアプローチも場合によってはアリだと思いますが、今回はSELECT文だけでやってみます。

generate_series

PostgreSQLにはgenerate_seriesという関数があり、これを利用することで連続した値を求めることができます。例えば1から100までの連番を生成したければこのように書きます。

SELECT generate_series(1, 100, 1);

各引数の意味は(開始値, 上限値, 増分値)となり、上記の例では「1から100まで1ずつインクリメントする」という意味になります。日付も同じように生成できますので、開始日と終了日を指定してこのように書くと連続する日付データを取得できます。

SELECT generate_series(‘2020-05-01’::date, ‘2020-05-05’::date, ‘1day’);
   generate_series
———-———————————————————
2020-05-01 00:00:00+09
2020-05-02 00:00:00+09
2020-05-03 00:00:00+09
2020-05-04 00:00:00+09
2020-05-05 00:00:00+09
(5 rows)

generate_seriesを使って日毎に売上を集計する

generate_seriesで日付を生成した表とsales表と結合させます。この時LEFT JOINを利用するのがポイントです。

SELECT
 a.uriagebi, COALESCE(SUM(b.amount), 0)
FROM
 (SELECT generate_series(‘2020-05-01’::date, ‘2020-05-05’::date, ‘1day) uriagebi) a
LEFT JOIN
 sales b ON b.uriagebi = a.uriagebi
GROUP BY
 a.uriagebi
ORDER BY
 a.uriagebi;
       uriagebi.       | coalesce
———-———————————————————+—————————-
2020-05-01 00:00:00+09 |     1000
2020-05-02 00:00:00+09 |        0
2020-05-03 00:00:00+09 |     2000 
2020-05-04 00:00:00+09 |        0 
2020-05-05 00:00:00+09 |     2000 
(5 rows)

いかがでしょうか。集計らしくなりましたね。データの後処理の関係でこういったリクエストは多いと思いますので、ぜひ覚えておきましょう！

コメント