MySQL多列窗口函数求和性能优化问题咨询

核心思路：避免重复分区排序 #

你的问题本质是每个sum() OVER()窗口函数可能重复执行PARTITION BY player_id ORDER BY date的排序操作，100+列就会重复100+次，导致性能暴跌。解决关键是只做一次分区排序，再批量计算所有列的求和。

具体优化方案 #

方案1：用CTE+自连接实现批量求和 #

先通过CTE预先生成带行号的有序数据集，再通过自连接关联当前行及后续5行，一次性计算所有列的连续和：

WITH ordered_stats AS (
    SELECT 
        player_id,
        date,
        points,
        small_points,
        -- 这里列出所有需要求和的100+整数列
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY date) AS rn
    FROM stats
)
SELECT 
    o1.player_id,
    o1.date AS start,
    SUM(o2.points) AS total_points,
    SUM(o2.small_points) AS total_small_points,
    -- 对应列出所有列的SUM结果
FROM ordered_stats o1
JOIN ordered_stats o2 
    ON o1.player_id = o2.player_id 
    AND o2.rn BETWEEN o1.rn AND o1.rn + 5
GROUP BY o1.player_id, o1.date, o1.rn;

方案2：用临时表加速（适合大表/低版本MySQL） #

如果CTE的性能不够理想，可将有序数据集存入带索引的临时表，进一步提升自连接效率：

-- 创建带索引的临时表
CREATE TEMPORARY TABLE ordered_stats (
    player_id INT,
    date DATE,
    points INT,
    small_points INT,
    -- 列出所有需要求和的列
    rn INT,
    INDEX idx_pid_rn (player_id, rn)
) AS
SELECT 
    player_id,
    date,
    points,
    small_points,
    -- 列出所有需要求和的列
    ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY date) AS rn
FROM stats;

-- 执行自连接求和
SELECT 
    o1.player_id,
    o1.date AS start,
    SUM(o2.points) AS total_points,
    SUM(o2.small_points) AS total_small_points,
    -- 对应列出所有列的SUM结果
FROM ordered_stats o1
JOIN ordered_stats o2 
    ON o1.player_id = o2.player_id 
    AND o2.rn BETWEEN o1.rn AND o1.rn + 5
GROUP BY o1.player_id, o1.date, o1.rn;

配套索引优化 #

一定要给stats表创建以下索引，让预排序过程直接利用索引有序性，避免全表排序：

CREATE INDEX idx_player_date ON stats(player_id, date);

为什么这个方案更高效 #

• 窗口函数方案中，每个sum() OVER()都可能独立执行分区和排序，100+列就会重复100+次相同的排序操作；
• 优化方案只做一次PARTITION BY player_id ORDER BY date生成行号，后续的求和全基于已排序的行号关联，彻底避免重复排序；
• 临时表的(player_id, rn)索引可以让自连接的关联操作更快，减少匹配时间。

内容的提问来源于stack exchange，提问作者Radosław Lis