SQL实现跨账户按时间取各账户最新记录的数量累加求和

你的需求是计算每条记录对应的所有已出现账户的最新quantity值总和——当前行所属账户用当前quantity，其他账户取它们在当前行之前的最后一条记录的quantity，再求和。下面是具体的实现方法：

核心思路 #

1. 先给所有记录按date+id确定全局顺序，同时给每个账户内的记录按时间标记序号，方便快速定位各账户的最新记录。
2. 对每条记录，关联所有已出现的账户，获取它们截至当前记录的最新quantity值。
3. 将这些最新值求和，得到目标的running_quantity。

示例SQL（PostgreSQL环境） #

WITH ordered_records AS (
    -- 标记全局顺序和每个账户内的记录顺序
    SELECT 
        id, 
        date, 
        account_id, 
        quantity,
        ROW_NUMBER() OVER (ORDER BY date, id) AS global_order,
        ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY date, id) AS account_row_num
    FROM your_table
)
SELECT 
    main.id,
    main.date,
    main.account_id,
    SUM(latest.quantity) AS running_quantity
FROM ordered_records main
-- 横向连接获取各账户最新值
LEFT JOIN LATERAL (
    SELECT quantity
    FROM ordered_records sub
    WHERE sub.account_id IN (
        -- 截至当前记录的所有已出现账户
        SELECT DISTINCT account_id 
        FROM ordered_records 
        WHERE global_order <= main.global_order
    )
    AND sub.account_row_num = (
        -- 该账户截至当前的最新记录序号
        SELECT MAX(account_row_num)
        FROM ordered_records
        WHERE account_id = sub.account_id
        AND global_order <= main.global_order
    )
) latest ON TRUE
GROUP BY main.id, main.date, main.account_id
ORDER BY main.global_order;

适配其他数据库的写法 #

• SQL Server：把LATERAL JOIN换成CROSS APPLY即可，其余逻辑一致。
• MySQL 8.0+：同样支持LATERAL JOIN，直接使用上述代码。

结果验证 #

执行后会得到你期望的输出：

性能优化建议 #

如果数据量较大，建议给(date, id)、(account_id, date, id)建立复合索引，能大幅提升关联查询的效率。

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