BigQueryで累積値を求める

Window関数 を使う。

データ

日付	アイテム	売上
2020-02-18	COMPグミ	100
2020-02-19	COMPグミ	100
2020-02-19	COMPグミ	100

上記データから特定の日付までの累積売上を求める

理想

日付	売上	累積売上
2020-02-18	100	100
2020-02-19	100	200
2020-02-19	100	300

SQL

# standardSQL
WITH SAMPLE_DATA AS(
SELECT * FROM UNNEST(ARRAY<STRUCT<time TIMESTAMP, item STRING, sales INT64>>
[
  ("2020-02-18 00:00:00+00", "COMPグミ", 100)
  , ("2020-02-19 00:00:00+00", "COMPグミ", 100)
  , ("2020-02-20 00:00:00+00", "COMPグミ", 100)
])
)

SELECT DATE(time) AS date
, sales
, SUM(sales) OVER (ORDER BY time ASC) AS sales_running
FROM SAMPLE_DATA

結果

日付	売上	累積売上
2020-02-18	100	100
2020-02-19	100	200
2020-02-19	100	300

解説

• SUM() で合計するカラムを指定する
• OVER() でどういうまとまりで、どういう順序で計算するか指定する
  • ここでは、 ORDER BY time ASC で順番しか指定していない
  • ここではやっていないが、 PARTITION BY を用いるとまとまりを指定できる

参考資料

• https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/