MySQL 實戰疑難 SQL 整理：按日/月累計統計與動態累加

MySQL 實戰疑難 SQL 整理：按日/月累計統計與動態累加

業務常見需求：統計每日/每月營收、用戶增長等指標的「歷史累計值」（Running Total）。本文提供 MySQL 8.0 窗口函數與舊版本相關子查詢兩套標準解法，並對比性能優劣。

一、 業務場景與測試數據

CREATE TABLE t_margin_profit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    op_time DATETIME NOT NULL,
    price DECIMAL(18,2) NOT NULL,
    INDEX idx_op_time (op_time)
);

-- 模擬 2024 年 1 月每日數據
INSERT INTO t_margin_profit (op_time, price) VALUES
('2024-01-01', 100), ('2024-01-01', 200),
('2024-01-02', 150),
('2024-01-03', 300), ('2024-01-03', 50),
-- ... 至 1 月 31 日
('2024-01-31', 500);

二、 MySQL 8.0+ 標準解法：窗口函數（推薦）

1. 按日累計

SELECT 
    DATE(op_time) AS stat_date,
    SUM(price) AS daily_total,
    SUM(SUM(price)) OVER (ORDER BY DATE(op_time) ROWS UNBOUNDED PRECEDING) AS cumulative_total
FROM t_margin_profit
WHERE YEAR(op_time) = 2024 AND MONTH(op_time) = 1
GROUP BY DATE(op_time)
ORDER BY stat_date;

2. 按月累計（跨年）

SELECT 
    DATE_FORMAT(op_time, '%Y-%m') AS stat_month,
    SUM(price) AS monthly_total,
    SUM(SUM(price)) OVER (ORDER BY DATE_FORMAT(op_time, '%Y-%m') ROWS UNBOUNDED PRECEDING) AS cumulative_total
FROM t_margin_profit
WHERE op_time >= '2023-01-01'
GROUP BY DATE_FORMAT(op_time, '%Y-%m')
ORDER BY stat_month;

優勢：單掃描、語法清晰、性能最優、支持 RANGE/ROWS 精確窗口控制。

三、 MySQL 5.6/5.7 兼容解法：相關子查詢

SELECT 
    DATE(a.op_time) AS stat_date,
    (SELECT SUM(price) 
     FROM t_margin_profit 
     WHERE DATE(op_time) <= DATE(a.op_time)
       AND YEAR(op_time) = 2024 AND MONTH(op_time) = 1
    ) AS cumulative_total
FROM t_margin_profit a
WHERE YEAR(a.op_time) = 2024 AND MONTH(a.op_time) = 1
GROUP BY DATE(a.op_time)
ORDER BY stat_date;

原理：外層按天分組，內層子查詢對每組計算「≤ 當天」的總和。

性能隱患：N+1 問題 — 外層每行執行一次子查詢。數據量大時（>10萬行）明顯變慢。

四、 進階優化：自連接 + 聚合（舊版本最優）

SELECT 
    a.stat_date,
    a.daily_total,
    SUM(b.daily_total) AS cumulative_total
FROM (
    SELECT DATE(op_time) AS stat_date, SUM(price) AS daily_total
    FROM t_margin_profit
    WHERE YEAR(op_time) = 2024 AND MONTH(op_time) = 1
    GROUP BY DATE(op_time)
) a
JOIN (
    SELECT DATE(op_time) AS stat_date, SUM(price) AS daily_total
    FROM t_margin_profit
    WHERE YEAR(op_time) = 2024 AND MONTH(op_time) = 1
    GROUP BY DATE(op_time)
) b ON b.stat_date <= a.stat_date
GROUP BY a.stat_date, a.daily_total
ORDER BY a.stat_date;

將「逐行子查詢」轉為「集合自連接」，利用索引範圍掃描，性能提升數量級。

五、 通用模板：存儲過程動態累計

DELIMITER $$
CREATE PROCEDURE `sp_cumulative_stats`(
    IN p_table VARCHAR(64),
    IN p_date_col VARCHAR(64),
    IN p_value_col VARCHAR(64),
    IN p_start_date DATE,
    IN p_end_date DATE,
    IN p_group_unit ENUM('DAY','MONTH')  -- DAY/MONTH
)
READS SQL DATA
BEGIN
    DECLARE v_sql TEXT;
    
    SET @date_expr = CASE p_group_unit
        WHEN 'DAY' THEN 'DATE'
        WHEN 'MONTH' THEN 'DATE_FORMAT'
    END;
    
    SET @group_expr = CASE p_group_unit
        WHEN 'DAY' THEN CONCAT(@date_expr, '(', p_date_col, ')')
        WHEN 'MONTH' THEN CONCAT(@date_expr, '(', p_date_col, ', ''%Y-%m'')')
    END;
    
    SET @order_expr = @group_expr;
    
    -- 8.0+ 使用窗口函數
    IF @@version >= '8.0' THEN
        SET v_sql = CONCAT('
            SELECT ', @group_expr, ' AS stat_period,
                   SUM(', p_value_col, ') AS period_total,
                   SUM(SUM(', p_value_col, ')) OVER (ORDER BY ', @order_expr, ' ROWS UNBOUNDED PRECEDING) AS cumulative_total
            FROM ', p_table, '
            WHERE ', p_date_col, ' BETWEEN ''', p_start_date, ''' AND ''', p_end_date, '''
            GROUP BY ', @group_expr, '
            ORDER BY stat_period
        ');
    ELSE
        -- 5.6/5.7 使用自連接
        SET v_sql = CONCAT('
            SELECT a.stat_period, a.period_total, SUM(b.period_total) AS cumulative_total
            FROM (
                SELECT ', @group_expr, ' AS stat_period, SUM(', p_value_col, ') AS period_total
                FROM ', p_table, '
                WHERE ', p_date_col, ' BETWEEN ''', p_start_date, ''' AND ''', p_end_date, '''
                GROUP BY ', @group_expr, '
            ) a
            JOIN (
                SELECT ', @group_expr, ' AS stat_period, SUM(', p_value_col, ') AS period_total
                FROM ', p_table, '
                WHERE ', p_date_col, ' BETWEEN ''', p_start_date, ''' AND ''', p_end_date, '''
                GROUP BY ', @group_expr, '
            ) b ON b.stat_period <= a.stat_period
            GROUP BY a.stat_period, a.period_total
            ORDER BY a.stat_period
        ');
    END IF;
    
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 調用示例
CALL sp_cumulative_stats('t_margin_profit', 'op_time', 'price', '2024-01-01', '2024-01-31', 'DAY');

六、 避坑指南

七、 總結

累計統計是時序分析的基石。MySQL 8.0 窗口函數 SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) 是終極方案；舊版本首選「預聚合 + 自連接」避開 N+1 陷阱。封裝為參數化存儲過程，兼容多版本、多業務表，構建可復用的分析能力組件。