如何使用bigquery-sql中的windows函数来获得某段时间内聚合值的差异

Question

销售额35

date_key	cust_id
2022-01-01	1	30
2022-01-02	1	35
2022-01-05	1	38
2022-01-10	1	20
2022-01-1	1	35
2022-01-01	2	20
2022-01-02	2	25
2022-01-04	2	38
2022-01-09	2	20
2022-015	1
2022-01-11	3	35

在您的查询中缺少引号'，字段customer_id和cust_id应该相同，对吧？

日期切换：between date('2022-01-05) and date('2022-01-01)

给定的时间间隔很奇怪，因为不清楚用户为什么需要它们。

具有window功能：

with tableSales as 
(Select date_sub(date("2022-01-11"), interval cast(rand()*10 as int64) day ) date_key, cust_id,
cast(rand()*100 as int64) as sales
from unnest([1,2,3]) cust_id, unnest(generate_array(1,10,1)) a
)
,tmp as 
(Select *,
sum(if(date_key between date('2022-01-06') and date('2022-01-11'), sales ,0 ) ) over (partition by cust_id) as currentperiod ,
sum(if(date_key between date('2022-01-01') and date('2022-01-05'), sales ,0 ) ) over (partition by cust_id) as previousperiod 
from tableSales
)
Select distinct cust_id, currentperiod, previousperiod from tmp

好吧，做一个"分组"要好得多：

with tableSales as 
(Select date_sub(date("2022-01-11"), interval cast(rand()*10 as int64) day ) date_key, cust_id,
cast(rand()*100 as int64) as sales
from unnest([1,2,3]) cust_id, unnest(generate_array(1,10,1)) a
)
,tmp as 
(Select cust_id,
sum(if(date_key between date('2022-01-06') and date('2022-01-11'), sales ,0 ) )   currentperiod ,
sum(if(date_key between date('2022-01-01') and date('2022-01-05'), sales ,0 ) )   previousperiod 
from tableSales
group by 1
)
Select distinct cust_id, currentperiod, previousperiod from tmp