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

## やりたいこと

以下のようなデータを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を実行すると、上の「出力したいデータ」が出力される。

```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の結果は次のようになる

```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
),
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` から始まる部分にあたる。

```sql
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](http://developersnote.jp/postgresql/postgresql-kawaii-yo.html)

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

たとえば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。

```diff
 -- 名前と日付の総組み合わせを作る
 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;
```