日付範囲で持っているデータに対して日付ごとに値を集計する

やりたいこと

以下のサンプルデータのように、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関数については以下を参照

  cloud.google.com

  developers-jp.googleblog.com

出力

開始日から終了日まで立てに並んだ表ができた。

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