MySQL双表计算累计聚合序列求助：按价格表日期计算指定求和值

Hey，我碰到过类似的累计计算问题，大概率是表关联逻辑或者累计窗口的范围没设置对，咱们一步步梳理解决：

首先再确认下核心信息：

• 两张表结构：
  • prices：iid（标的ID）、trade_date（基准日期）、price（当日标的价格）
  • trades：iid（标的ID）、trade_date（交易发生日期）、nominal（交易数量）、price（交易时的价格）
• 你的核心需求：以prices表的每个日期为节点，计算截止到该日期时，对应标的所有交易的trades.nominal * prices.price累计总和（这里用的是prices表当前行的价格，不是交易本身的价格）

之前得不到预期结果，通常是两个问题：要么关联时没限制交易日期的范围，要么累计窗口的分组/排序逻辑错了。下面给两种可行的实现方案：

方案1：用窗口函数直接计算（MySQL 8.0+推荐） #

如果你的MySQL版本是8.0及以上，窗口函数是最简洁的方式，先关联两张表，再用窗口函数做累计：

SELECT
    p.iid,
    p.trade_date,
    p.price,
    -- 按标的分组，按日期排序，累计从第一行到当前行的总和
    SUM(t.nominal * p.price) OVER (
        PARTITION BY p.iid 
        ORDER BY p.trade_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_total
FROM
    prices p
-- 左连接确保prices的每一行都保留，关联条件要限制交易日期不晚于当前基准日期
LEFT JOIN trades t 
    ON p.iid = t.iid 
    AND t.trade_date <= p.trade_date
GROUP BY p.iid, p.trade_date, p.price, t.nominal, t.trade_date
ORDER BY p.iid, p.trade_date;

这里要注意几个关键点：

• LEFT JOIN必须加，不然如果某个基准日期没有交易记录，这一行就会被过滤掉
• 关联条件一定要加t.trade_date <= p.trade_date，不然会把未来的交易也算进去
• 窗口函数里的PARTITION BY p.iid是必须的，不然会把不同标的的累计值混在一起

方案2：兼容低版本MySQL（无窗口函数） #

如果你的MySQL版本低于8.0，没法用窗口函数，可以用子查询+自连接来实现：

SELECT
    p1.iid,
    p1.trade_date,
    p1.price,
    -- 累加所有不晚于当前基准日期的每日贡献
    SUM(COALESCE(daily_sum.daily_total, 0)) AS cumulative_total
FROM
    prices p1
LEFT JOIN (
    -- 先算出每个基准日期对应的当日交易贡献总和
    SELECT
        p.iid,
        p.trade_date,
        SUM(t.nominal * p.price) AS daily_total
    FROM prices p
    LEFT JOIN trades t 
        ON p.iid = t.iid 
        AND t.trade_date = p.trade_date
    GROUP BY p.iid, p.trade_date
) daily_sum 
    ON p1.iid = daily_sum.iid 
    AND daily_sum.trade_date <= p1.trade_date
GROUP BY p1.iid, p1.trade_date, p1.price
ORDER BY p1.iid, p1.trade_date;

这个方案先把每天的交易贡献算出来，再用自连接把所有历史日期的贡献加起来，COALESCE是用来处理没有交易的日期，避免出现NULL值。

你可以对照这几个点检查之前的SQL：

1. 有没有限制trades的交易日期不晚于prices的基准日期？
2. 累计计算时有没有按iid分组？
3. 有没有处理prices表中无对应交易的日期？

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