[bigquery]日別売上累積を集計する時に、特定日付の売上が無い場合の対処方法

2024-08-19 2024-08-19

WITH date_range AS (

  -- データの最小日付から最大日付までの連続した日付を生成
  SELECT 
　　　　　　　　DATE(d) AS date
  FROM
　　　　　　　UNNEST(GENERATE_DATE_ARRAY(
    　　(SELECT MIN(DATE(日付)) FROM dataset.table),
   　　 (SELECT MAX(DATE(日付)) FROM dataset.table),
   　　　　　INTERVAL 1 DAY
  )) AS d
)

,　product_dates AS (

  -- ユニークな商品IDのリストを作成
  SELECT 
    DISTINCT 商品ID
  FROM
    dataset.table
)

,　all_combinations AS (

  -- 全ての日付と商品IDの組み合わせを生成
  SELECT 
    date, 商品ID
  FROM
    date_range
  CROSS JOIN
    product_dates
)

,　filled_data AS (

  -- 元のデータと全ての組み合わせを結合し、欠損している日付のデータを0で埋める
  SELECT 
    ac.date AS 日付,
    ac.商品ID,
    COALESCE(t.売上, 0) AS 売上
  FROM 
　　　　　　　　all_combinations ac
  LEFT　JOIN 
　　　　　　　dataset.table t
    ON ac.date = DATE(t.日付)
    AND ac.商品ID = t.商品ID
)

, cumulative_sales AS (

  -- 各商品ごとに累積売上を計算
  SELECT
    日付,
    商品ID,
    売上,
    SUM(売上) OVER (PARTITION BY 商品ID ORDER BY 日付) AS 累積売上
  FROM 
    filled_data
)

-- 全商品の累積売上を日付ごとに合計し、「全体」として集計
SELECT
  日付,
  '全体' AS 商品ID,
  SUM(累積売上) AS 累積売上
FROM 
  cumulative_sales
GROUP BY 
  日付
ORDER BY 
  日付

上記の手順を利用すれば、欠損している日付のデータも考慮した正確な全体の累積売上が得られます。