如何在SQL中初始化余额为0并实现每日成本汇总？

嗨，我来帮你搞定这个需求！其实核心就是两步：先算出每个ID每天的成本总和，再基于初始0值来递推每日的起始余额。下面给你详细的思路和SQL示例：

核心思路拆解 #

• 第一步：聚合单日成本总和：先把原始数据按ID和日期分组，计算出每个ID每天的day_cost_amount（单日成本总和）。
• 第二步：递推起始余额：利用窗口函数来计算累计值，因为每个ID的初始余额是0，第一天的起始余额直接设为0，之后每天的起始余额等于前一天的起始余额加上前一天的单日成本总和——这本质上就是截至前一天的所有单日成本的累计和。

具体SQL实现（推荐窗口函数方案，高效简洁） #

假设你的原始数据表名为costs，包含字段：id（用户ID）、cost_date（成本发生日期）、cost_amount（单笔成本金额）。

WITH daily_costs AS (
    -- 第一步：计算每个ID每天的单日成本总和
    SELECT
        id,
        cost_date,
        SUM(cost_amount) AS day_cost_amount
    FROM costs
    GROUP BY id, cost_date
)
SELECT
    id,
    cost_date,
    day_cost_amount,
    -- 第二步：计算起始余额，初始为0，后续为截至前一天的累计成本
    COALESCE(
        SUM(day_cost_amount) OVER (
            PARTITION BY id
            ORDER BY cost_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS start_balance
FROM daily_costs
ORDER BY id, cost_date;

代码解释 #

1. daily_costs CTE：通过GROUP BY id, cost_date聚合每个ID每天的所有成本，得到day_cost_amount。
2. 起始余额计算：
   • SUM(day_cost_amount) OVER (...)是窗口函数，按id分区、cost_date排序，计算当前行之前所有行的day_cost_amount总和（也就是截至前一天的累计成本）。
   • 对于每个ID的第一天，因为没有前一天的数据，窗口SUM的结果是NULL，用COALESCE(..., 0)把它转换成0，正好满足初始余额为0的要求。
   • 后续日期的start_balance自动等于前一天的start_balance加上前一天的day_cost_amount，完全符合你的递推规则。

备选方案：递归CTE（适合理解递推逻辑） #

如果你更想直观体现“次日余额=前一日余额+前一日成本”的递推过程，可以用递归CTE：

WITH daily_costs AS (
    SELECT
        id,
        cost_date,
        SUM(cost_amount) AS day_cost_amount
    FROM costs
    GROUP BY id, cost_date
),
balance_recursive AS (
    -- 递归起始点：每个ID的第一天，start_balance设为0
    SELECT
        id,
        cost_date,
        day_cost_amount,
        0 AS start_balance
    FROM daily_costs dc
    WHERE cost_date = (SELECT MIN(cost_date) FROM daily_costs WHERE id = dc.id)
    
    UNION ALL
    
    -- 递归递推：后续每天的start_balance = 前一天的start_balance + 前一天的day_cost_amount
    SELECT
        dc.id,
        dc.cost_date,
        dc.day_cost_amount,
        br.start_balance + br.day_cost_amount AS start_balance
    FROM daily_costs dc
    JOIN balance_recursive br 
        ON dc.id = br.id 
        AND dc.cost_date = (SELECT MIN(cost_date) FROM daily_costs WHERE id = dc.id AND cost_date > br.cost_date)
)
SELECT * FROM balance_recursive ORDER BY id, cost_date;

这个方案更直观展示递推逻辑，但数据量大时性能不如窗口函数，所以优先推荐第一个方案。

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