SQLで非連続日付の抜けている日付データ作成方法

Question

非連続日付のデータで抜けている日付のデータを作成したいと考えています。
間で抜けている日付の項目値には抜けている日付以前の最大の日付のデータから
項目値を複製してデータを作成したいです。
（下部にデータイメージを作りました）
ループを使用しないで一括のINSERT、UPDATE等を駆使して作成する方法が
ありましたらご教授お願い致します。

元データ

キー	日付	項目１	項目２
0001	2021/01/12	1	2
0001	2021/01/15	3	4
0001	2021/01/16	5	6
0001	2021/01/20	7	8

作成したいデータ

キー	日付	項目１	項目２
0001	2021/01/12	1	2
0001	2021/01/13	1	2
0001	2021/01/14	1	2
0001	2021/01/15	3	4
0001	2021/01/16	5	6
0001	2021/01/17	5	6
0001	2021/01/18	5	6
0001	2021/01/19	5	6
0001	2021/01/20	7	8

Comments

[m.ts10806]

「任意の期間の連続した日付を作る」やり方なら調べたら出てきます。
まず自身で思うようにやってみてください。
※連続させるのだから結局ループは必須ですよ

[sazi]

@m.ts10806さん
連続した値を持つ表を準備すれば、SQLのみで行えるのでループは不要になります。

Answer 1

このような要件だと、スクリプトやPL/SQLなどで対応したほうが良いかと思いますが、どうしても1回のクエリで実現したいのであれば、以下のようになるかと思います。

INSERT INTO t (キー, 日付, 項目1, 項目2)
WITH 
p (キー, 日付) AS (
    SELECT キー, MIN(日付) 
    FROM t 
    GROUP BY キー
  UNION ALL
    SELECT キー, (日付 + INTERVAL '1' DAY)
    FROM p
    WHERE 日付 < (SELECT MAX(日付) FROM t WHERE p.キー = t.キー)
),
q (キー, 日付, 項目1, 項目2, rnk) AS (
  SELECT 
    p.キー,
    p.日付,
    t.項目1,
    t.項目2,
    RANK() OVER (PARTITION BY p.キー, p.日付 ORDER BY t.日付 DESC)
  FROM p
  JOIN t ON p.キー = t.キー AND t.日付 <= p.日付
)
SELECT キー, 日付, 項目1, 項目2
FROM q 
WHERE rnk = 1
AND NOT EXISTS (
  SELECT * FROM t WHERE t.キー = q.キー AND t.日付 = q.日付
)

やっていることは単純で、おおよそ以下の通りです。

• 連続した日付は再帰SQLを使って生成 (ループを使っているといえばその通りですが…)
• RANK関数とJOINを組み合わせることで、直近の日付からのデータの複製を実現
• INSERT SELECTでデータを投入

Comments

[tttbbb]

詳細なサンプル含め、丁寧なご回答ありがとうございます。
こちらでやりたかったことができそうです。
RANK関数が肝ですね、とても勉強になりました。

Answer 2

Oracle 連続した日付を取得 とLEFT OUTER JOINしては？