SQL 累積集計での日付の歯抜け対応

Question

以下のテーブル(tableA)があったときに、tableBのように日付がないところを埋めてさらに累積の値で埋めたいです。
tableA

dt	購入金額	累積購入金額	地域
1/3	10	10	岩手
1/4	10	20	岩手
1/6	10	30	岩手

tableB

dt	購入金額	累積購入金額	地域
1/1	0	0	岩手
1/2	0	0	岩手
1/3	10	10	岩手
1/4	10	20	岩手
1/5	0	20	岩手
1/6	10	30	岩手

以下のコードを書いたのですが、どういうわけか連続の日ではなくなってしまいます。
どうすればtableBを作れるのでしょうか。

with    
dt_table AS (
SELECT
   ('2018-01-01'::date + (ROW_NUMBER() OVER()) - 1)::date AS dt
FROM
    table  -- 適当なテーブルを指定
limit
    366
)
SELECT
    d.dt,
    COALESCE(t.購入金額, 0) AS 購入金額,
    (CASE WHEN t.累積購入金額 IS NULL THEN COALESCE(MAX(t.累積購入金額) OVER(PARTITION BY t.地域 ORDER BY d.dt ROWS UNBOUNDED PRECEDING), 0) ELSE t.累積購入金額 END) AS 累積購入金額,
    (CASE WHEN t.地域 IS NULL THEN COALESCE(MAX(t.地域) OVER(PARTITION BY t.地域 ORDER BY d.dt ROWS UNBOUNDED PRECEDING), 0) ELSE t.地域 END) AS 地域
FROM
    dt_table d
    LEFT JOIN tableA t
        ON d.dt = t.dt

Answer 1

genarate_seriseが使えます。
postgres版

SELECT
    d.dt::date
   , COALESCE(t.購入金額, 0) AS 購入金額
   , coalesce((select 累積購入金額 from tableA where dt<=d.dt order by dt desc limit 1), 0) as 累積購入金額
   , '岩手' as 地域
FROM
    generate_series('2018/01/01'::date,'2018/12/31'::date, '1 days') as d(dt)
    left JOIN tableA t ON d.dt=t.dt and t.地域='岩手'
order by d.dt        

データが無い日から開始する場合、累積は0とすれば取れますけど、地域は無理ですね。
何らかの基準が無いと。
redshift版（但し環境がなく未確認）

with    
  dt_table AS (
    SELECT ('2018-01-01'::date + (ROW_NUMBER() OVER()) - 1)::date AS dt
    FROM table  -- 適当なテーブルを指定
    limit 366
)
select
    d.dt::date
   , COALESCE(t.購入金額, 0) AS 購入金額
   , coalesce((select 累積購入金額 from tableA where dt<=d.dt order by dt desc limit 1), 0) as 累積購入金額
   , '岩手' as 地域
FROM dt_table d LEFT JOIN tableA t ON d.dt=t.dt and t.地域='岩手'

Comments

[sysder]

ご回答ありがとうございます。
redshiftなのでgenerate_seriesが使えないのです。
また日付を作るところまではできています。
その次のselectでcaseで累積を引っ張って来る所が上手くいってないようです。

[sazi]

redshiftですか。後出ししないようにして下さい。
低速だと思いますが、累積部分は回答の内容でも大丈夫だと思います。

[sazi]

ん？結果は出ていて並びだけ正しくないってことですか？
もしそうなら、order by 付けるだけでは？

[sysder]

後出しすみませんでした。
with句の中のみを流すと日付が1日ごとに得られます。
with句のあとのleft joinで何故か日付が抜け落ちます。

[sazi]

結合が上手くいってないんじゃないでしょうか。
castして型を合わせてみて下さい。

[sysder]

型は一致してました。
selectのcase文の所を2つとも削除して流すと日付が連続してちゃんと出力されました。

[sazi]

多分、max使ってるからです。
暗黙でgroup by されているんでしょう。

[sysder]

なるほど。確かにそんな気がします。
LAGでやってみたら2日以上空きがあるとnullが返ってきて上手くいかなかったのですが、何か別のやり方はありませんか？

[sazi]

累計に関しては回答してますよ。
結局、無いものは作れないので、地域は難しいでしょうね。
起点としてのデータがあれば、再帰が使えればという所ですけど、使えないみたいですし。

[sazi]

地域を限定すれば固定にできるので、結局累計だけで良いという事になります。

[sysder]

1/1と1/2は諦めるとして、1/3以降を上手く集計する方法はないでしょうか。

[sazi]

追記しました。地域は限定するしか思いつきません。

[sazi]

地域のマスタがあれば、with部分で日付と地域を組み合わせた内容にすれば、良さそう。

[sysder]

ありがとうございます。
元データから地域マスタを作ってやってみます。
with句ではdtと地域のcross joinですか？
またleft joinのところは以下ですか？
dt_table d
LEFT JOIN tableA t
ON d.dt = t.dt
AND d.地域 = t.地域

[sazi]

ですね。で、利用はd.地域

[sysder]

何度もすみません。
coalesceの中にselect文を書いたら相関サブクエリはダメですというエラーが出ました。カッコを忘れずに付けたのですが…

[sazi]

じゃあ、ネストしてみて下さい。こちらにはredshiftの環境は無いので。

[sazi]

「サポートされていない相関サブクエリのパターン」に含まれていないか確認して下さい。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_correlated_subqueries.html

[sysder]

tableAの後にtをつけて、t.累積購入金額とやったら解決しました。謎です。
coalesce((select t.累積購入金額 from tableA t where...

[sysder]

とりあえず午後もガチャガチャやってみます。
またありがとうございました。
また何かありましたらよろしくお願いします。

[sysder]

お陰様でうまく集計できました。
大変助かりました。
ありがとうございました。

Answer 2

WITH句のなかでdt_table を呼び出さないといけないかと思います。
PostgreSQLで試しましたが、1~10までカウントアップするのは以下のSQLで作成できます。

WITH RECURSIVE temp AS(

	SELECT 1 AS CNT --初期値
	UNION ALL
	SELECT CNT + 1 FROM temp --増分
)
SELECT * FROM temp
LIMIT 10

あとは、これを日付型に置き換えたら良いかと思います。

WITH RECURSIVE temp AS(
SELECT TO_DATE('20180101', 'YYYYMMDD') AS CNT
UNION ALL
SELECT CNT + 1 FROM temp
)
SELECT * FROM temp LIMIT 365

Comments

[sysder]

ご回答ありがとうございます。
最初のwith句はちゃんと日付が連続して出ていることを確認しています。

[sysder]

ちなみにRedshiftを使ってます。