如何在SQL（Redshift）中按行（而非列）计算累积和？

针对你需要按id分组、在每行内从week_1到week_4依次计算累积和的需求，这里有两种实用的解决办法：

方法一：直接列累加（适合列数固定且较少的场景） #

如果你的周数列数量不多且固定，直接通过算术运算累加最直接，代码简洁易读：

SELECT
    id,
    week_1,
    week_1 + week_2 AS week_2,
    week_1 + week_2 + week_3 AS week_3,
    week_1 + week_2 + week_3 + week_4 AS week_4
FROM your_table;

执行后就能得到每行从week_1开始的累积和结果，和你给出的期望输出完全一致。

方法二：UNPIVOT转成行计算再转回列（适合列数较多的场景） #

如果后续周数列可能增加，或者当前列数较多，用转置的方式更灵活：

-- 第一步：把横向的周列转成纵向的行
WITH unpivoted AS (
    SELECT
        id,
        -- 提取周数的数字部分，用于排序
        CAST(REPLACE(week_col, 'week_', '') AS INT) AS week_num,
        value
    FROM your_table
    UNPIVOT (
        value FOR week_col IN (week_1, week_2, week_3, week_4)
    ) AS unpvt
),
-- 第二步：按id分组、周数排序计算累积和
cumulative AS (
    SELECT
        id,
        week_num,
        SUM(value) OVER (PARTITION BY id ORDER BY week_num) AS cum_sum
    FROM unpivoted
)
-- 第三步：把纵向的累积和转回横向的列
SELECT
    id,
    MAX(CASE WHEN week_num = 1 THEN cum_sum END) AS week_1,
    MAX(CASE WHEN week_num = 2 THEN cum_sum END) AS week_2,
    MAX(CASE WHEN week_num = 3 THEN cum_sum END) AS week_3,
    MAX(CASE WHEN week_num = 4 THEN cum_sum END) AS week_4
FROM cumulative
GROUP BY id;

这种方法通过三次转换实现需求，扩展性更强，后续加新的周列时，只需要修改UNPIVOT和最后SELECT里的CASE语句即可。

内容的提问来源于stack exchange，提问作者jbuddy_13