【PostgreSQL】レコードが存在しない日付を補完する。

APIサーバを拵えてると、データが存在しなかった日付に0を入れて返したいときがある。そんな時の備忘録

環境

psql (PostgreSQL) 12.0

下準備

今回は説明用に以下のテーブルとレコードを使用する。

-- 使用金額テーブル
CREATE TABLE money_record
(id INTEGER NOT NULL, -- 主キー
purchase_date TIMESTAMP, --記録日時(yyyy/MM/dd HH:mm:ss)
using_amount INTEGER NOT NULL, -- 使用金額
PRIMARY KEY (id)
);
-- サンプルレコード
INSERT INTO money_record values( 1, '2021-01-31', 100);
INSERT INTO money_record values( 2, '2021-02-02', 210);
INSERT INTO money_record values( 3, '2021-02-02',  50);
INSERT INTO money_record values( 4, '2021-02-03',  90);
INSERT INTO money_record values( 5, '2021-02-04',  10);
INSERT INTO money_record values( 6, '2021-02-04', 180);
INSERT INTO money_record values( 7, '2021-02-06',  70);
INSERT INTO money_record values( 8, '2021-02-07', 200);
INSERT INTO money_record values( 9, '2021-02-07',   5);
INSERT INTO money_record values(10, '2021-02-07',  25);
COMMIT;

一週間分のレコードを日別に集計

色々やり方はあるが、シンプルな感じでSQLを作る。

-- 2021-02-01 から 2021-02-07 の使用金額を日別に集計
SELECT
   purchase_date
   , SUM(using_amount) AS 使用金額 
FROM
   money_record 
WHERE
   purchase_date BETWEEN '2021-02-01' AND '2021-02-07' 
GROUP BY
   purchase_date
ORDER BY 
   purchase_date ASC;

実行結果はこう

しかし、これだと「2021/02/01」と「2021/02/05」のレコードが歯抜けになっている。
【レコードが存在しない = 使用金額が0】
であったことを明確に表現したい場合は、ひと手間加える必要がある。

日付マスタを作成

結論から述べると
前項の導出表と、指定範囲分の日付マスタの論理和を取れば良い。

ここでミソとなる部分が日付マスタを作成するSQLである。
作成には「generate_series関数」を使用する。

そんでもって大体こんな雰囲気のSQLが出来る。

-- 2021-02-01 から 2021-02-07 の日付マスタを作成する
SELECT
   generate_series
   , 0 as value 
FROM
   generate_series( 
       '2021-02-01' ::Date
       , '2021-02-07' ::Date
       , '1 day'
   );
   

実行結果

集合表を作成

先程の2つの導出表をUNION ALL句で結合する。

SELECT
   purchase_date AS 日付
   , SUM(using_amount) AS 使用金額 
FROM
   money_record 
WHERE
   purchase_date BETWEEN '2021-02-01' AND '2021-02-07' 
GROUP BY
   purchase_date 
UNION ALL 
SELECT
   generate_series AS 日付
   , 0 as 使用金額 
FROM
   generate_series( 
       '2021-02-01' ::Date
       , '2021-02-07' ::Date
       , '1 day'
   );

実行結果

後は日付でグループ化するだけでOK
最終的に以下のSQLになる。

SELECT
   日付
   , SUM(使用金額) AS 使用金額 
FROM
   ( 
       SELECT
           purchase_date AS 日付
           , SUM(using_amount) AS 使用金額 
       FROM
           money_record 
       WHERE
           purchase_date BETWEEN '2021-02-01' AND '2021-02-07' 
       GROUP BY
           purchase_date 
       UNION ALL 
       SELECT
           generate_series AS 日付
           , 0 as 使用金額 
       FROM
           generate_series( 
               '2021-02-01' ::Date
               , '2021-02-07' ::Date
               , '1 day'
           )
   ) tr 
GROUP BY
   tr.日付 
ORDER BY
   tr.日付 ASC;

実行結果

無事、「2021/02/01」と「2021/02/05」のレコードが表示されました！

generate_series関数ですが、Postgres特有の関数らしくOracleやMySQLだと別の手段で日付マスタを作成する必要がありそうです。

PostgreSQL 9.3.2文書 より
参考: https://www.postgresql.jp/document/9.3/html/functions-srf.html