如何基于含cumulative_sum列的账户表按月份计算汇总累计值？

这个需求完全可以实现，核心思路是先补全每个账户在所有月份的记录，再匹配每个账户对应月份的最新累计值，最后按月份聚合计算总和并处理账户合并与月份标记。

实现步骤与SQL代码（以支持窗口函数的数据库为例，如MySQL 8+、PostgreSQL、SQL Server） #

WITH all_month_account AS (
    -- 生成所有月份与账户的全量组合，确保每个账户在每个月份都有记录
    SELECT m.month, a.account
    FROM (SELECT DISTINCT month FROM your_table) m
    CROSS JOIN (SELECT DISTINCT account FROM your_table) a
),
latest_cumulative AS (
    SELECT 
        ama.month,
        ama.account,
        -- 取该账户在当前月份及之前的最新累计值（因累计值递增，MAX即为最新）
        MAX(t.cumulative_sum) OVER (
            PARTITION BY ama.account 
            ORDER BY ama.month 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS latest_sum,
        -- 标记当前月份该账户是否有原始数据
        CASE WHEN t.month IS NOT NULL THEN 1 ELSE 0 END AS has_original_data
    FROM all_month_account ama
    LEFT JOIN your_table t 
        ON ama.account = t.account AND ama.month = t.month
)
SELECT 
    -- 对存在账户无原始数据的月份添加*标记
    CASE WHEN SUM(1 - has_original_data) > 0 THEN CONCAT(month, '*') ELSE month END AS month,
    SUM(latest_sum) AS cumulative_sum,
    GROUP_CONCAT(DISTINCT account ORDER BY account SEPARATOR ', ') AS account
FROM latest_cumulative
GROUP BY month
ORDER BY month;

代码逻辑说明 #

1. all_month_account CTE：通过交叉连接所有唯一月份和唯一账户，生成每个账户对应所有月份的组合，解决部分账户在某些月份无数据的问题。
2. latest_cumulative CTE：左连接原表获取原始数据，用窗口函数MAX() OVER (...)提取每个账户到当前月份的最新累计值，同时标记该月份是否有原始数据。
3. 最终聚合查询：按月份分组，计算累计值总和，用GROUP_CONCAT合并账户列表，对存在缺失原始数据的月份添加*标记。

兼容老版本数据库（如MySQL 5.x）的替代方案 #

如果数据库不支持窗口函数，可以用关联子查询实现：

SELECT 
    -- 判断当前月份是否有账户无原始数据，有则添加*
    CASE WHEN EXISTS (
        SELECT 1 
        FROM (SELECT DISTINCT month FROM your_table) m
        CROSS JOIN (SELECT DISTINCT account FROM your_table) a
        LEFT JOIN your_table t ON a.account = t.account AND m.month = t.month
        WHERE m.month = agg.month AND t.month IS NULL
    ) THEN CONCAT(agg.month, '*') ELSE agg.month END AS month,
    agg.cumulative_sum,
    agg.account
FROM (
    SELECT 
        m.month,
        -- 子查询获取每个账户到当前月份的最新累计值
        SUM(
            (SELECT MAX(cumulative_sum) FROM your_table t WHERE t.account = a.account AND t.month <= m.month)
        ) AS cumulative_sum,
        GROUP_CONCAT(a.account ORDER BY a.account SEPARATOR ', ') AS account
    FROM (SELECT DISTINCT month FROM your_table) m
    CROSS JOIN (SELECT DISTINCT account FROM your_table) a
    GROUP BY m.month
) agg
ORDER BY agg.month;

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