データがない日付を直近の日付のデータで穴埋めして出力するSQL

Last updated at 2020-11-28Posted at 2020-11-28

やりたいこと

以下のようなデータをSQLで取得したい。RDBMSはPostgreSQLとする。

ゲームの得点を記録するシステムがある
データベースにはユーザーの名前、ゲームをした日、その日の得点が記録される
特定の期間（たとえば1週間）の間で、得点がどのように推移したか日毎に確認したい
ゲームをしていない日は直近の得点をその日の得点と見なす

例

ゲームの得点を記録するテーブル（challengesテーブル）のデータ

name	challenged_on	point
Alice	2020-11-22	2
Alice	2020-11-24	5
Alice	2020-11-27	1
Bob	2020-11-25	3
Bob	2020-11-27	6
Bob	2020-11-28	4

出力したいデータ（期間は2020-11-22から11-28まで）

name	challenged_on	point
Alice	2020-11-22	2
Alice	2020-11-23	2
Alice	2020-11-24	5
Alice	2020-11-25	5
Alice	2020-11-26	5
Alice	2020-11-27	1
Alice	2020-11-28	1
Bob	2020-11-22	0
Bob	2020-11-23	0
Bob	2020-11-24	0
Bob	2020-11-25	3
Bob	2020-11-26	3
Bob	2020-11-27	6
Bob	2020-11-28	4

たとえば、Aliceは11月23日はゲームをしていないが、直近の22日に2点を取ったので、23日の記録は2点になる。

Bobは11月25日に初めてゲームをしたので、11月22日から24日の得点は0になる。

解答例

回帰CTEを使ってみた。以下のSQLを実行すると、上の「出力したいデータ」が出力される。

-- 名前と日付の総組み合わせを作る
WITH dates_by_user AS (
  SELECT name,
         CAST(dt AS DATE) AS date
  FROM generate_series('2020-11-22'::date, '2020-11-28', '1 day') AS dt
  CROSS JOIN (SELECT DISTINCT name FROM challenges) AS names
),
-- 名前と日付に対応する得点を埋める（対応するレコードがなければ得点はNULL）
challenges_for_each_day AS (
  SELECT d.name,
         d.date,
         c.point,
         ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY d.name, d.date) AS rowno
  FROM dates_by_user d
  LEFT OUTER JOIN challenges c
    ON  c.challenged_on = d.date
    AND c.name = d.name
),
-- 再帰CTEは先頭のCTEでしか使えないので、CTE構文をネストさせる
filled_challenges AS (
  -- 再帰CTE
  WITH RECURSIVE r AS (
    -- 各nameごとの1件目のレコードを集める
    SELECT c.name,
           c.date,
           c.point,
           c.rowno
    FROM challenges_for_each_day c
    WHERE c.rowno = 1
    UNION ALL
    -- 翌日のレコードを再帰的に取得する
    SELECT c.name,
           c.date,
           -- 対象日の得点がNULLなら、前の日の得点をその日の得点とする
           COALESCE(c.point, r.point) AS point,
           c.rowno
    -- cは対象日の、rはその前日のレコード
    FROM challenges_for_each_day c
    INNER JOIN r
      ON  c.name = r.name
      AND c.rowno = r.rowno + 1
  )
  -- 再帰CTEの実行結果をそのまま返却
  SELECT *
  FROM r
)
-- 出力用に見た目や並び順を整えて完成
SELECT
   name,
   date,
   COALESCE(point, 0) AS point
FROM filled_challenges
ORDER BY name,
         date;

途中経過を少し解説

最初の2つのCTEの結果は次のようになる

WITH dates_by_user AS (
  SELECT name,
         CAST(dt AS DATE) AS date
  FROM generate_series('2020-11-22'::date, '2020-11-28', '1 day') AS dt
  CROSS JOIN (SELECT DISTINCT name FROM challenges) AS names
),
challenges_for_each_day AS (
  SELECT d.name,
         d.date,
         c.point,
         ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY d.name, d.date) AS rowno
  FROM dates_by_user d
  LEFT OUTER JOIN challenges c
    ON  c.challenged_on = d.date
    AND c.name = d.name
),
SELECT *
FROM challenges_for_each_day
ORDER BY name,
         date;

name	challenged_on	point	rowno
Alice	2020-11-22	2	1
Alice	2020-11-23	NULL	2
Alice	2020-11-24	5	3
Alice	2020-11-25	NULL	4
Alice	2020-11-26	NULL	5
Alice	2020-11-27	1	6
Alice	2020-11-28	NULL	7
Bob	2020-11-22	NULL	1
Bob	2020-11-23	NULL	2
Bob	2020-11-24	NULL	3
Bob	2020-11-25	3	4
Bob	2020-11-26	NULL	5
Bob	2020-11-27	6	6
Bob	2020-11-28	4	7

あとは上の結果のNULLの部分を「直近の得点」で埋めていけばよい。

直近の得点を取得するために使ったのが再帰CTEで、SQLでいうと WITH RECURSIVE r AS から始まる部分にあたる。

WITH RECURSIVE r AS (
  -- 各nameごとの1件目のレコードを集める
  SELECT c.name,
         c.date,
         c.point,
         c.rowno
  FROM challenges_for_each_day c
  WHERE c.rowno = 1
  UNION ALL
  -- 翌日のレコードを再帰的に取得する
  SELECT c.name,
         c.date,
         -- 対象日の得点がNULLなら、前の日の得点をその日の得点とする
         COALESCE(c.point, r.point) AS point,
         c.rowno
  -- cは対象日の、rはその前日のレコード
  FROM challenges_for_each_day c
  INNER JOIN r
    ON  c.name = r.name
    AND c.rowno = r.rowno + 1
)
-- 再帰CTEの実行結果をそのまま返却
SELECT *
FROM r

ただし、この説明は非常にややこしいので、ここでは割愛させていただく。
今回、再帰CTEを利用するにあたって以下のサイトを参考にさせてもらったので、詳しく知りたい方はこちらをご覧いただきたい。
再帰 CTE で牛の授精回数を数えてみる - Developers Note

応用：対象期間の前に発生した得点も考慮する

たとえばBobは11月10日と11日にゲームの記録が残っていたとする。
対象期間が11月22日-28日であっても、「直近の得点」という意味で11月11日の得点を11月22日-24日に表示するためにはどうすればいいか？

ゲームの得点を記録するテーブル（challengesテーブル）のデータ

name	challenged_on	point
Alice	2020-11-22	2
Alice	2020-11-24	5
Alice	2020-11-27	1
Bob	2020-11-10	10
Bob	2020-11-11	20
Bob	2020-11-25	3
Bob	2020-11-27	6
Bob	2020-11-28	4

出力したいデータ（期間は2020-11-22から11-28まで）

name	challenged_on	point
Alice	2020-11-22	2
Alice	2020-11-23	2
Alice	2020-11-24	5
Alice	2020-11-25	5
Alice	2020-11-26	5
Alice	2020-11-27	1
Alice	2020-11-28	1
Bob	2020-11-22	20
Bob	2020-11-23	20
Bob	2020-11-24	20
Bob	2020-11-25	3
Bob	2020-11-26	3
Bob	2020-11-27	6
Bob	2020-11-28	4

解答例

さきほどのSQLを以下のように修正すること対応できる。

ここでは「対象期間より前の直近の得点」を old_challenges というCTEに格納した。
あとは、再帰CTE内で1件目のデータを取得する際に、「その日の得点がなければ、old_challengesの得点を使用する」という条件分岐（COALESCE(c.point, oc.point)）を入れればOK。

 -- 名前と日付の総組み合わせを作る
 WITH dates_by_user AS (
   SELECT name,
          CAST(dt AS DATE) AS date
   FROM generate_series('2020-11-22'::date, '2020-11-28', '1 day') AS dt
   CROSS JOIN (SELECT DISTINCT name FROM challenges) AS names
 ),
 -- 名前と日付に対応する得点を埋める（対応するレコードがなければ得点はNULL）
 challenges_for_each_day AS (
   SELECT d.name,
          d.date,
          c.point,
          ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY d.name, d.date) AS rowno
   FROM dates_by_user d
   LEFT OUTER JOIN challenges c
     ON  c.challenged_on = d.date
     AND c.name = d.name
 ),
+old_challenges AS (
+  SELECT c0.name,
+         c0.point
+  FROM challenges c0
+  WHERE (c0.name, c0.challenged_on) = (
+    SELECT c1.name,
+           MAX(c1.challenged_on)
+    FROM challenges c1
+    WHERE c1.challenged_on < '2020-11-22'
+    GROUP BY c1.name
+  )
+),
 -- 再帰CTEは先頭のCTEでしか使えないので、CTE構文をネストさせる
 filled_challenges AS (
   -- 再帰CTE
   WITH RECURSIVE r AS (
     -- 各nameごとの1件目のレコードを集める
     SELECT c.name,
            c.date,
-           c.point,
+           COALESCE(c.point, oc.point) AS point,
            c.rowno
     FROM challenges_for_each_day c
+    LEFT OUTER JOIN old_challenges oc
+      ON oc.name = c.name
     WHERE c.rowno = 1
     UNION ALL
     -- 翌日のレコードを再帰的に取得する
     SELECT c.name,
            c.date,
            -- 対象日の得点がNULLなら、前の日の得点をその日の得点とする
            COALESCE(c.point, r.point) AS point,
            c.rowno
     -- cは対象日の、rはその前日のレコード
     FROM challenges_for_each_day c
     INNER JOIN r
       ON  c.name = r.name
       AND c.rowno = r.rowno + 1
   )
   -- 再帰CTEの実行結果をそのまま返却
   SELECT *
   FROM r
 )
 -- 出力用に見た目や並び順を整えて完成
 SELECT
    name,
    date,
    COALESCE(point, 0) AS point
 FROM filled_challenges
 ORDER BY name,
          date;

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up