google-bigquery - 从 BigQuery 中观察到的日期算起过去 7 天的总和

Question

我有一个表格，我想在该表格上计算自观察日起过去 7 天的收入总和。这是我的 table -

with temp as 
(
select DATE('2019-06-29') as transaction_date, "x"as id, 0 as revenue
union all
select DATE('2019-06-30') as transaction_date, "x"as id,  80 as revenue
union all
select DATE('2019-07-04') as transaction_date, "x"as id, 64 as revenue
union all
select DATE('2019-07-06') as transaction_date, "x"as id, 64 as revenue
union all
select DATE('2019-07-11') as transaction_date, "x"as id, 75 as revenue
union all
select DATE('2019-07-12') as transaction_date, "x"as id, 0 as revenue
)


select * from temp

我想为每个 transaction_date 计算过去 7 天的总和。例如，对于具有 transaction_date = 2019-07-12 的最后一条记录，我想添加另一列，将 revenue 从 2019 开始的最后 7 天加起来-07-12(直到 2019-07-05)，因此新的 rollup_revenue 列的值为 0 + 75 + 64 = 139。同样，我需要计算每个 ID 的所有日期的汇总。

注意 - ID 可能会或可能不会每天出现。

我试过自助加入，但我无法弄明白。

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT *, 
  SUM(revenue) OVER(
    PARTITION BY id ORDER BY UNIX_DATE(transaction_date) 
    RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
  ) rollup_revenue 
FROM `project.dataset.temp`

您可以使用您问题中的样本数据进行测试，如下例所示

#standardSQL
WITH `project.dataset.temp` AS (
  SELECT DATE '2019-06-29' AS transaction_date, 'x' AS id, 0 AS revenue UNION ALL
  SELECT '2019-06-30', 'x', 80 UNION ALL
  SELECT '2019-07-04', 'x', 64 UNION ALL
  SELECT '2019-07-06', 'x', 64 UNION ALL
  SELECT '2019-07-11', 'x', 75 UNION ALL
  SELECT '2019-07-12', 'x', 0
)
SELECT *, 
  SUM(revenue) OVER(
    PARTITION BY id ORDER BY UNIX_DATE(transaction_date) 
    RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
  ) rollup_revenue 
FROM `project.dataset.temp`
-- ORDER BY transaction_date   

结果

Row transaction_date    id  revenue rollup_revenue   
1   2019-06-29          x   0       0    
2   2019-06-30          x   80      80   
3   2019-07-04          x   64      144  
4   2019-07-06          x   64      208  
5   2019-07-11          x   75      139  
6   2019-07-12          x   0       139