使用窗口函数计算累计求和时补全缺失记录

你的问题核心在于原表只包含有交易发生的账户-日期记录，但目标输出需要每个日期下所有出现过的账户都显示一行，并展示到该日期为止的累计总额，哪怕当天该账户没有交易。

解决方案思路 #

要实现这个需求，需要先构造出所有可能的「账户-日期」组合，再将原交易数据关联进去，最后用窗口函数计算累计值：

1. 提取原表中所有唯一的日期和唯一的账户
2. 交叉连接这两个集合，得到所有账户与所有日期的组合
3. 将这个组合表与原交易表左连接，补全每个组合的交易金额（无交易则为0）
4. 用窗口函数按账户分区、日期排序，计算累计总额

完整SQL代码 #

-- 生成所有账户-日期组合，关联交易数据后计算累计总额
WITH all_dates AS (
    SELECT DISTINCT Date FROM tab1
),
all_accounts AS (
    SELECT DISTINCT Account FROM tab1
),
account_date_combinations AS (
    SELECT aa.Account, ad.Date
    FROM all_accounts aa
    CROSS JOIN all_dates ad
)
SELECT 
    adc.Account,
    adc.Date,
    SUM(COALESCE(t.Amount, 0)) OVER (
        PARTITION BY adc.Account 
        ORDER BY adc.Date
    ) AS `累计总额（Running Total）`
FROM account_date_combinations adc
LEFT JOIN tab1 t 
    ON adc.Account = t.Account 
    AND adc.Date = t.Date
ORDER BY adc.Date, adc.Account;

代码解释 #

• all_dates 和 all_accounts CTE分别提取原表中所有唯一的日期和账户
• account_date_combinations 通过交叉连接生成所有可能的账户-日期对，确保每个日期下所有账户都有记录
• 左连接原表后，用COALESCE(t.Amount, 0)将无交易的金额转为0，避免累计时忽略这些日期
• 窗口函数SUM(...) OVER (PARTITION BY Account ORDER BY Date)会按账户分组，按日期顺序累加金额，得到到当前日期的累计总额

执行这段SQL后，就能得到你想要的目标输出。

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