こんにちは、knsk765 です。普段、直接的な開発はしないんですけど SQL はよく書いてます。
今回は日別の集計で使える簡単なテクニックをご紹介。

準備

実行環境

PostgreSQL 14.5

テーブル

なんか売上のテーブルがある感じで、それを日別に集計します。

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,            -- 売上日
    product_name TEXT NOT NULL,         -- 商品名
    unit_price NUMERIC(10, 0) NOT NULL, -- 単価
    quantity INTEGER NOT NULL           -- 個数
);

INSERT INTO sales VALUES
(1, '2025-04-01', 'やくそう', 20, 5),
(2, '2025-04-01', 'どうのつるぎ', 120, 13),
(3, '2025-04-02', 'くさりかたびら', 320, 2),
(4, '2025-04-06', 'ブーメラン', 450, 9),
(5, '2025-04-06', 'はがねのつるぎ', 1000, 6);

データ

id	sale_date	product_name	unit_price	quantity
1	2025-04-01	やくそう	20	5
2	2025-04-01	どうのつるぎ	120	13
3	2025-04-02	くさりかたびら	320	2
4	2025-04-06	ブーメラン	450	9
5	2025-04-06	はがねのつるぎ	1000	6

日毎の集計を出す

まずは日毎の売上を出してみましょう。

SELECT sale_date, SUM(unit_price * quantity) totale_price
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

結果

sale_date	total_price
2025-04-01	1660
2025-04-02	640
2025-04-06	10050

売上なかった日わかりにくくね？

売上のデータがあった日はいいのですが、なかった日がパッと見でわからず見落としてしまいそうですよね。
できれば売上がなかった日もレコードとしては出力したいです。
下の表のようなイメージ。

sale_date	total_price
2025-04-01	1660
2025-04-02	640
2025-04-03	0
2025-04-04	0
2025-04-05	0
2025-04-06	10050
2025-04-07	0

これを実現するために順を追ってやっていきましょう。

指定範囲で連続する日のレコードを返したい

たとえば 2025-04-01 から 2025-04-07 まで連続したレコードを SQL で返そうと思ったら、どうすればいいですかね？こんなときは再帰クエリーの出番です。

WITH RECURSIVE cte_date AS (
  SELECT cast('2025-04-01' as date) "日付"
  UNION ALL
  SELECT "日付" + 1 FROM cte_date WHERE "日付" < '2025-04-07'
)
SELECT * FROM cte_date;

結果

日付
2025-04-01
2025-04-02
2025-04-03
2025-04-04
2025-04-05
2025-04-06
2025-04-07

再帰クエリーで開始日時から終了日時まで 1日ずつ加算していってます。
ちなみにこのあたりもうちょっと知りたい方は下記の記事をご覧ください。

blog.ingage.jp

準備ができたので合体させましょう。

データがない日も集計に出せるぜ

連続した日付をベースに、売上データをくっつけましょう。

WITH RECURSIVE cte_date AS (
  SELECT cast('2025-04-01' as date) "日付"
  UNION ALL
  SELECT "日付" + 1 FROM cte_date WHERE "日付" < '2025-04-07'
)
SELECT d."日付", SUM(unit_price * quantity) "売上"
FROM
  cte_date d
  LEFT JOIN sales s ON s.sale_date = d."日付" 
GROUP BY d."日付"
ORDER BY d."日付";

結果

日付	売上
2025-04-01	1660
2025-04-02	640
2025-04-03	(NULL)
2025-04-04	(NULL)
2025-04-05	(NULL)
2025-04-06	10050
2025-04-07	(NULL)

うーん。NULL じゃなくて 0 にしたいですね。ってことで coalesce 関数を使って NULL のときは 0 で出力しましょう。

WITH RECURSIVE cte_date AS (
  SELECT cast('2025-04-01' as date) "日付"
  UNION ALL
  SELECT "日付" + 1 FROM cte_date WHERE "日付" < '2025-04-07'
)
SELECT
  d."日付",
  coalesce(SUM(unit_price * quantity), 0) "売上"
FROM
  cte_date d
  LEFT JOIN sales s ON s.sale_date = d."日付" 
GROUP BY d."日付"
ORDER BY d."日付";

結果

日付	売上
2025-04-01	1660
2025-04-02	640
2025-04-03	0
2025-04-04	0
2025-04-05	0
2025-04-06	10050
2025-04-07	0

というわけで完成です。

インゲージ開発者ブログ

集計データで日付の抜けを埋めるSQLテクニック

準備