如何基于多分组按日期计算MTD、QTD、YTD及LTD累计值

嘿，我完全懂你之前用嵌套while循环时那种“等得花儿都谢了”的痛苦——这种逐行遍历的方式在数据量上去之后，速度简直没法看。你说的关联查询思路是对的，但其实还有更高效的方案：SQL窗口函数，它能让数据库引擎一次性完成所有累计计算，性能碾压嵌套循环，代码也更简洁。咱们一步步来拆解：

先明确各累计值的逻辑 #

首先得把每个指标的计算规则理清楚，对应你的分组（GROUP_1=投资组合，GROUP_2=策略）：

• LTD（生命周期累计）：同一个投资组合+策略下，从最早记录到当前日期的所有每日利润总和
• YTD（年度累计）：同一个投资组合+策略下，和当前记录同一年的所有每日利润总和
• QTD（季度累计）：同一个投资组合+策略下，和当前记录同一年同季度的所有每日利润总和
• MTD（月度累计）：同一个投资组合+策略下，和当前记录同一年同一月的所有每日利润总和

方案1：窗口函数（推荐，性能最优） #

几乎所有现代关系型数据库（MySQL 8.0+/PostgreSQL/SQL Server/Oracle等）都支持窗口函数，用SUM() OVER()就能轻松实现所有累计值，只需要一次表扫描，效率极高。

示例SQL代码 #

SELECT
    Date,
    GROUP_1,
    GROUP_2,
    `Daily Profits`,
    -- MTD：按投资组合+策略+年月分区，累加每日利润
    SUM(`Daily Profits`) OVER (
        PARTITION BY GROUP_1, GROUP_2, YEAR(Date), MONTH(Date)
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS MTD,
    -- QTD：按投资组合+策略+年季度分区
    SUM(`Daily Profits`) OVER (
        PARTITION BY GROUP_1, GROUP_2, YEAR(Date), QUARTER(Date)
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS QTD,
    -- YTD：按投资组合+策略+年份分区
    SUM(`Daily Profits`) OVER (
        PARTITION BY GROUP_1, GROUP_2, YEAR(Date)
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS YTD,
    -- LTD：按投资组合+策略分区，从最早记录到当前行
    SUM(`Daily Profits`) OVER (
        PARTITION BY GROUP_1, GROUP_2
        ORDER BY Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS LTD
FROM
    your_table_name
ORDER BY
    GROUP_1, GROUP_2, Date;

代码说明 #

• PARTITION BY：指定分组维度，比如MTD需要按「投资组合+策略+年月」分组，确保只累加当月同组合同策略的数据
• ORDER BY Date：保证累计是按时间顺序从早到晚计算的
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：明确表示从当前分区的第一行累加到当前行（有些数据库默认就是这个范围，加上更清晰）

用你的示例数据跑这段代码，得到的结果会和你期望的完全一致，而且运行速度比嵌套循环快几个数量级——尤其是数据量上万甚至几十万的时候，差距会特别明显。

方案2：关联查询（你提到的思路） #

如果你用的数据库不支持窗口函数（比如MySQL 5.x），那关联查询也是可行的。核心思路是：把表和自己关联，关联条件是「同组合同策略」+「关联行的日期≤当前行的日期」，并且满足对应累计的时间范围（比如MTD需要关联行的年月=当前行的年月），然后对关联到的Daily Profits求和。

示例SQL代码 #

SELECT
    t1.Date,
    t1.GROUP_1,
    t1.GROUP_2,
    t1.`Daily Profits`,
    -- MTD：关联同组合同策略、同年月且日期≤当前行的记录，求和
    SUM(t2.`Daily Profits`) AS MTD,
    -- QTD：关联同组合同策略、同年同季度且日期≤当前行的记录，求和
    SUM(t4.`Daily Profits`) AS QTD,
    -- YTD：关联同组合同策略、同年且日期≤当前行的记录，求和
    SUM(t3.`Daily Profits`) AS YTD,
    -- LTD：关联同组合同策略、日期≤当前行的所有记录，求和
    SUM(t5.`Daily Profits`) AS LTD
FROM
    your_table_name t1
LEFT JOIN your_table_name t2
    ON t1.GROUP_1 = t2.GROUP_1
    AND t1.GROUP_2 = t2.GROUP_2
    AND YEAR(t1.Date) = YEAR(t2.Date)
    AND MONTH(t1.Date) = MONTH(t2.Date)
    AND t2.Date <= t1.Date
LEFT JOIN your_table_name t3
    ON t1.GROUP_1 = t3.GROUP_1
    AND t1.GROUP_2 = t3.GROUP_2
    AND YEAR(t1.Date) = YEAR(t3.Date)
    AND t3.Date <= t1.Date
LEFT JOIN your_table_name t4
    ON t1.GROUP_1 = t4.GROUP_1
    AND t1.GROUP_2 = t4.GROUP_2
    AND YEAR(t1.Date) = YEAR(t4.Date)
    AND QUARTER(t1.Date) = QUARTER(t4.Date)
    AND t4.Date <= t1.Date
LEFT JOIN your_table_name t5
    ON t1.GROUP_1 = t5.GROUP_1
    AND t1.GROUP_2 = t5.GROUP_2
    AND t5.Date <= t1.Date
GROUP BY
    t1.Date, t1.GROUP_1, t1.GROUP_2, t1.`Daily Profits`
ORDER BY
    t1.GROUP_1, t1.GROUP_2, t1.Date;

注意事项 #

• 这种方式需要多次自关联，性能比窗口函数差很多，数据量越大越明显
• 一定要给GROUP_1、GROUP_2、Date字段建立索引，否则关联查询会慢到离谱

为什么窗口函数比嵌套循环快？ #

嵌套while循环是逐行处理，每一行都要遍历之前的所有符合条件的记录，时间复杂度是O(n²)；而窗口函数是数据库引擎批量处理，一次扫描就能完成所有分组的累计计算，时间复杂度接近O(n)，效率提升不是一星半点。

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