按客户最新交易日期聚合名下全账户总余额的SQL实现方法

• 字段名匹配错误：原SQL窗口函数的PARTITION BY子句中写的是customer_id，但实际表字段为cust_id，字段不匹配会直接引发执行报错或逻辑偏移。
• 分区逻辑粒度错误：直接按cust_id分区做行号排序时，同一个客户名下、同一交易日期下的多个账户记录会被随机分配1、2、3的行号，筛选rn=1时仅会随机捞取单个账户的单条余额记录，既没有覆盖客户的所有关联账户，也没有按要求做余额聚合，结果必然和预期偏差。

核心逻辑 #

• 先计算每个cust_id对应的全局最新交易日期，作为客户维度的时间锚点
• 对客户名下的每个acc_no，抓取不超过上述时间锚点的最近一次交易余额，保证即使某账户在最新交易日期当天没有记录，也能取到该时间点对应的有效余额
• 按客户维度对所有关联账户的快照余额做求和聚合，输出对应结果

可直接执行的参考SQL #

WITH cust_latest_trade AS (
    -- 计算每个客户的全局最新交易日期
    SELECT
        cust_id,
        MAX(DATE) AS latest_date
    FROM `a.table`
    GROUP BY cust_id
),
account_snapshot AS (
    -- 抓取每个客户-账户组合在客户最新交易日期前的最近一笔余额
    SELECT
        t.cust_id,
        t.acc_no,
        t.total_balance,
        ROW_NUMBER() OVER (
            PARTITION BY t.cust_id, t.acc_no
            ORDER BY t.DATE DESC
        ) AS account_rn
    FROM `a.table` t
    INNER JOIN cust_latest_trade clt
        ON t.cust_id = clt.cust_id
        AND t.DATE <= clt.latest_date
)
-- 按客户维度聚合账户余额
SELECT
    clt.latest_date AS DATE,
    asnap.cust_id,
    SUM(asnap.total_balance) AS total_balance
FROM account_snapshot asnap
INNER JOIN cust_latest_trade clt
    ON asnap.cust_id = clt.cust_id
WHERE asnap.account_rn = 1
GROUP BY clt.latest_date, asnap.cust_id;

方案适配说明 #

• 完全覆盖“同个cust_id下不同acc_no的交易记录分布在不同日期”的场景，比如样例中A203客户的acc4最后一次交易在1/1、acc5最后一次交易在5/1，方案会自动抓取两个账户在客户最新时间锚点5/1的有效余额（acc4取1/1的200，acc5取5/1的165），求和得到365的预期结果，不会因为某账户在最新日期当天无交易就漏算。
• 执行结果无随机性，所有计算逻辑由明确的排序规则和聚合规则控制，不会出现单账户随机取值的问题。

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