日付範囲で持っているデータに対して日付ごとに値を集計する
やりたいこと
以下のサンプルデータのように、1行に数字データと適用範囲(開始日/終了日)があるデータに対し、日付ごとに集計をかけたい。
sampleデータ
id | start_date | end_date | count |
---|---|---|---|
1 | 2020-01-01 | 2020-01-15 | 20 |
2 | 2020-01-01' | 2020-01-15 | 2 |
3 | 2020-01-05' | 2020-01-06 | 5 |
4 | 2020-01-03' | 2020-01-12 | 7 |
5 | 2020-01-10' | 2020-01-14 | 1 |
6 | 2020-01-02' | 2020-01-13 | 4 |
7 | 2020-01-12' | 2020-01-15 | 9 |
8 | 2020-01-08' | 2020-01-10 | 2 |
9 | 2020-01-02' | 2020-01-08 | 4 |
10 | 2020-01-12' | 2020-01-14 | 10 |
期待する結果
以下のように、適用範囲内にある数字をすべてサマリーして日付ごとに結果を出す。
DATE | AMOUNT |
---|---|
2020-01-01 | 22 |
2020-01-02 | 30 |
2020-01-03 | 37 |
2020-01-04 | 37 |
2020-01-05 | 42 |
2020-01-06 | 42 |
2020-01-07 | 37 |
2020-01-08 | 39 |
2020-01-09 | 35 |
2020-01-10 | 36 |
2020-01-11 | 34 |
2020-01-12 | 53 |
2020-01-13 | 46 |
2020-01-14 | 42 |
2020-01-15 | 31 |
SQL作成
日付の表を作成し、ここに集計データを後から横づけする形で作成していく。
日付の表
まず日付の表を作ってみる。
WITH SAMPLE AS ( SELECT 1 AS ID, DATE('2020-01-01') AS START_DATE, DATE('2020-01-15') AS END_DATE, 20 AS COUNT UNION ALL SELECT 2, DATE('2020-01-01'), DATE('2020-01-15'), 2 UNION ALL SELECT 3, DATE('2020-01-05'), DATE('2020-01-06'), 5 UNION ALL SELECT 4, DATE('2020-01-03'), DATE('2020-01-12'), 7 UNION ALL SELECT 5, DATE('2020-01-10'), DATE('2020-01-14'), 1 UNION ALL SELECT 6, DATE('2020-01-02'), DATE('2020-01-13'), 4 UNION ALL SELECT 7, DATE('2020-01-12'), DATE('2020-01-15'), 9 UNION ALL SELECT 8, DATE('2020-01-08'), DATE('2020-01-10'), 2 UNION ALL SELECT 9, DATE('2020-01-02'), DATE('2020-01-08'), 4 UNION ALL SELECT 10, DATE('2020-01-12'), DATE('2020-01-14'), 10 ), CALENDER AS ( SELECT GENERATE_DATE_ARRAY( (SELECT MIN(START_DATE) FROM SAMPLE), (SELECT MAX(END_DATE) FROM SAMPLE) ) AS DT ) SELECT DATE FROM CALENDER, UNNEST(DT) AS DATE
GENERATE_DATE_ARRAY関数は、開始日から終了日までの日付の配列を返してくれる。開始日に、SAMPLE表の最小値、終了日にSAMPLE表の最大値を設定している。
- GENERATE_DATE_ARRAY関数については以下を参照 cloud.google.com
GENERATE_DATE_ARRAY関数を使って作成される配列をUNNEST関数を使って行に変換している。
UNNEST関数については以下を参照
出力
開始日から終了日まで立てに並んだ表ができた。
DATE |
---|
2020-01-01 |
2020-01-02 |
2020-01-03 |
2020-01-04 |
2020-01-05 |
2020-01-06 |
2020-01-07 |
2020-01-08 |
2020-01-09 |
2020-01-10 |
2020-01-11 |
2020-01-12 |
2020-01-13 |
2020-01-14 |
2020-01-15 |
この表に集計値をくっつけていく。
SQL完成系
WITH SAMPLE AS ( SELECT 1 AS ID, DATE('2020-01-01') AS START_DATE, DATE('2020-01-15') AS END_DATE, 20 AS COUNT UNION ALL SELECT 2, DATE('2020-01-01'), DATE('2020-01-15'), 2 UNION ALL SELECT 3, DATE('2020-01-05'), DATE('2020-01-06'), 5 UNION ALL SELECT 4, DATE('2020-01-03'), DATE('2020-01-12'), 7 UNION ALL SELECT 5, DATE('2020-01-10'), DATE('2020-01-14'), 1 UNION ALL SELECT 6, DATE('2020-01-02'), DATE('2020-01-13'), 4 UNION ALL SELECT 7, DATE('2020-01-12'), DATE('2020-01-15'), 9 UNION ALL SELECT 8, DATE('2020-01-08'), DATE('2020-01-10'), 2 UNION ALL SELECT 9, DATE('2020-01-02'), DATE('2020-01-08'), 4 UNION ALL SELECT 10, DATE('2020-01-12'), DATE('2020-01-14'), 10 ), CALENDER AS ( SELECT GENERATE_DATE_ARRAY( (SELECT MIN(START_DATE) FROM SAMPLE), (SELECT MAX(END_DATE) FROM SAMPLE) ) AS DT ) SELECT DATE, SUM(COUNT) AMOUNT FROM CALENDER, UNNEST(DT) AS DATE INNER JOIN SAMPLE AS S ON S.START_DATE<= DATE and S.END_DATE >= DATE GROUP BY DATE ORDER BY DATE
出力結果
期待した通りの結果が得られた
DATE | AMOUNT |
---|---|
2020-01-01 | 22 |
2020-01-02 | 30 |
2020-01-03 | 37 |
2020-01-04 | 37 |
2020-01-05 | 42 |
2020-01-06 | 42 |
2020-01-07 | 37 |
2020-01-08 | 39 |
2020-01-09 | 35 |
2020-01-10 | 36 |
2020-01-11 | 34 |
2020-01-12 | 53 |
2020-01-13 | 46 |
2020-01-14 | 42 |
2020-01-15 | 31 |