以下のtable_Aがあります。
dt | id | value |
---|---|---|
2019-01-03 | a | 1 |
2019-01-05 | a | 2 |
2019-01-02 | b | 1 |
2019-01-04 | b | 2 |
これを以下のtable_Bのようにしたいです。
dt | id | value |
---|---|---|
2019-01-03 | a | 1 |
2019-01-04 | a | null |
2019-01-05 | a | 2 |
2019-01-02 | b | 1 |
2019-01-03 | b | null |
2019-01-04 | b | 2 |
2019-01-05 | b | null |
僕が考えた方法は、以下のSQLで日付の連番のテーブル(テーブル名:table_dt)を作って、
sql
1SELECT 2 ((SELECT MIN(dt) FROM table_A)::date + (ROW_NUMBER() OVER()) - 1)::date AS dt 3FROM 4 table_A 5limit 6 3
以下のSQLで日付とIDのすべての組み合わせを作成して(テーブル名:dt_id)、
sql
1SELECT 2 dt, 3 id 4FROM 5 table_dt 6 CROSS JOIN 7 (SELECT 8 DISTINCT(id) 9 FROM 10 table_A 11 )
以下のSQLでdt_idとtable_Aをleft joinでつないで歯抜けをなくす方法です。
sql
1SELECT 2 t1.dt, 3 t1.id, 4 t2.value 5FROM 6 dt_id t1 7 LEFT JOIN 8 table_A t2 9 USING(dt, id)
しかし、この方法はcross joinを使っているためIDが何百万もあって365日分を求めるとものすごく遅くなってしまいます。
また、必要のない(id=a dt=2019-01-02 value=null)のレコードができてしまいます。
別の方法はないでしょうか。
ご教示の程よろしくお願いいたします。