如何找到累积的一些行(不是列)在SQL?

Question

SQL (Redshift)如何创建按行而不是按列的累积和。例如,

输入表:

id, week_1, week_2, week_3, week_4
__________________________________
'A',    10,     20,     30,     40
'B',    20,     40,     60,     80

所需输出:

id, week_1, week_2, week_3, week_4
__________________________________
'A',    10,     30,     60,     100
'B',    20,     60,    120,     200

方法将按id分组，然后按week_1…列的行计算累加和。

Answer 1

您可以保持简单，像这样汇总每个列:

select id,
week1,
(week1 + week2) as week2,
(week1 + week2 + week3) as week3,
(week1 + week2 + week3 + week4) as week4
from t;

如果你想尝试这个解决方案，这里有一个DB Fiddle的例子。