如何在谷歌云sql或python数据帧中每滚动3行进行分组

Question

假设我有一个数据集。

Year | Month | Count
2017 | 1     | 3
2017 | 2     | 4
2017 | 3     | 2
2017 | 4     | 1
2017 | 5     | 5
2017 | 6     | 6

我希望按结果分组为

group 1    | total count
month1,2,3 |    9
month2,3,4 |    7
month3,4,5 |    8
...

我想知道有没有一种方法可以在谷歌云SQL中做到这一点？如果没有，我可以在python数据帧中执行此操作吗？

Answer 1

您可以使用lead()和sum()。假设month是字符串：

select concat(month, ',', lead(month) over (order by year, month), ',',
lead(month, 2) over (order by year, month)
),
sum(count) over (order by year, month rows between current row and 2 following) as total
from t;

或者：

with t as (
select 2017 as year, 1 as month, 3 as count union all
select 2017, 2, 4 union all
select 2017, 3, 2 union all
select 2017, 4, 1 union all
select 2017, 5, 5 union all
select 2017, 6, 6
)
select array_to_string(array_agg(cast(count as string)) over (order by year, month rows between current row and 2 following), ','),
sum(count) over (order by year, month rows between current row and 2 following)
from t;

使用array_agg()作为窗口函数可能比使用lead()麻烦一些。然而，对于更多的元素，它会更简单。