PostgreSQL中按日期分组填充A3金额的SQL优化方案咨询

原表t1结构及数据 #

需求说明 #

按日期分组，将AccountName为'A3'的行的Amount字段填充为同日期下A1与A2的Amount之和，预期结果如下：

预期结果 #

当前实现痛点 #

目前采用多个CTE拆分不同日期、结合Case语句嵌套查询及Union的方式实现，但因存在24个不同Date值，导致SQL脚本冗长且维护性差。现咨询：

• 是否可通过按Date分组避免创建大量CTE？
• 是否有更优方式构造A3行的Amount求和逻辑，替代Case内的多嵌套查询？

优化方案 #

方案1：使用窗口函数（推荐） #

利用窗口函数按Date分组计算A1+A2的总和，再用Case语句替换A3的Amount值，无需拆分日期或Union：

SELECT 
    AccountName,
    Date,
    CASE 
        WHEN AccountName = 'A3' 
        THEN SUM(CASE WHEN AccountName IN ('A1','A2') THEN Amount END) OVER (PARTITION BY Date)
        ELSE Amount
    END AS Amount
FROM t1;

方案2：预计算日期总和后关联查询 #

先按Date分组计算A1+A2的总和，再与原表关联填充A3的值：

WITH DateTotal AS (
    SELECT 
        Date,
        SUM(Amount) AS A1A2Total
    FROM t1
    WHERE AccountName IN ('A1','A2')
    GROUP BY Date
)
SELECT 
    t.AccountName,
    t.Date,
    CASE 
        WHEN t.AccountName = 'A3' THEN dt.A1A2Total
        ELSE t.Amount
    END AS Amount
FROM t1 t
JOIN DateTotal dt ON t.Date = dt.Date;

这两种方案都不需要针对每个日期单独写CTE，仅通过一次分组或窗口函数即可处理所有日期，大幅简化脚本，且扩展性强（新增日期无需修改代码）。

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