优化SELECT查询中重复表达式的执行效率（MySQL）

原代码问题梳理 #

1. 逻辑错误：
   • 月度偏移的LAG函数错误使用7作为偏移量（应对应月度天数，如30天左右）
   • 多处百分比计算字段混用（如IG年度变化误用Debt Held by the Public减year_b4_ig，TDPO月度变化误用month_b4_ig）
2. 性能瓶颈：
   • 重复调用相同窗口函数，导致数据库多次计算相同结果
   • 冗余CASE表达式增加编译时间
   • 重复的百分比计算代码冗余，降低可读性与执行效率

优化方案 #

1. 自定义函数复用百分比计算逻辑 #

创建通用函数处理百分比变化计算，避免重复编写复杂表达式：

DELIMITER //
CREATE FUNCTION calculate_percent_change(current_val DECIMAL(20,6), lag_val DECIMAL(20,6)) 
RETURNS DECIMAL(10,3)
DETERMINISTIC
BEGIN
    IF lag_val = 0 THEN
        RETURN NULL;
    END IF;
    RETURN ROUND(((current_val - lag_val) / lag_val) * 100, 3);
END //
DELIMITER ;

2. 拆分CTE预计算通用参数与窗口结果 #

将计算拆分到多层CTE，提前计算月份对应前置行数、LAG值和所有移动平均线，避免重复计算：

WITH base_data AS (
    SELECT 
        `Record Date`,
        `Debt Held by the Public` AS dhpb,
        `Intragovernmental Holdings` AS ig,
        `Total Public Debt Outstanding` AS tdpo,
        -- 简化月份对应的前置行数逻辑
        CASE MONTH(`Record Date`)
            WHEN 2 THEN 27
            WHEN 4 THEN 29
            WHEN 9 THEN 29
            WHEN 11 THEN 29
            ELSE 30
        END AS month_preceding_rows,
        -- 修正LAG偏移量，对应周/月/年度
        LAG(`Debt Held by the Public`, 7) OVER (ORDER BY `Record Date`) AS week_b4_dhpb,
        LAG(`Debt Held by the Public`, 30) OVER (ORDER BY `Record Date`) AS month_b4_dhpb,
        LAG(`Debt Held by the Public`, 365) OVER (ORDER BY `Record Date`) AS year_b4_dhpb,
        LAG(`Intragovernmental Holdings`, 7) OVER (ORDER BY `Record Date`) AS week_b4_ig,
        LAG(`Intragovernmental Holdings`, 30) OVER (ORDER BY `Record Date`) AS month_b4_ig,
        LAG(`Intragovernmental Holdings`, 365) OVER (ORDER BY `Record Date`) AS year_b4_ig,
        LAG(`Total Public Debt Outstanding`, 7) OVER (ORDER BY `Record Date`) AS week_b4_tdpo,
        LAG(`Total Public Debt Outstanding`, 30) OVER (ORDER BY `Record Date`) AS month_b4_tdpo,
        LAG(`Total Public Debt Outstanding`, 365) OVER (ORDER BY `Record Date`) AS year_b4_tdpo
    FROM DebtPenny_19930401_20250623
),
moving_averages AS (
    SELECT 
        *,
        -- 预计算所有移动平均线，仅计算一次
        ROUND(AVG(dhpb) OVER (ORDER BY `Record Date` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 3) AS dhpb_weekly_ma,
        ROUND(AVG(dhpb) OVER (ORDER BY `Record Date` ROWS BETWEEN month_preceding_rows PRECEDING AND CURRENT ROW), 3) AS dhpb_monthly_ma,
        ROUND(AVG(dhpb) OVER (ORDER BY `Record Date` ROWS BETWEEN 364 PRECEDING AND CURRENT ROW), 3) AS dhpb_annual_ma,
        ROUND(AVG(ig) OVER (ORDER BY `Record Date` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 3) AS ig_weekly_ma,
        ROUND(AVG(ig) OVER (ORDER BY `Record Date` ROWS BETWEEN month_preceding_rows PRECEDING AND CURRENT ROW), 3) AS ig_monthly_ma,
        ROUND(AVG(ig) OVER (ORDER BY `Record Date` ROWS BETWEEN 364 PRECEDING AND CURRENT ROW), 3) AS ig_annual_ma,
        ROUND(AVG(tdpo) OVER (ORDER BY `Record Date` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 3) AS tdpo_weekly_ma,
        ROUND(AVG(tdpo) OVER (ORDER BY `Record Date` ROWS BETWEEN month_preceding_rows PRECEDING AND CURRENT ROW), 3) AS tdpo_monthly_ma,
        ROUND(AVG(tdpo) OVER (ORDER BY `Record Date` ROWS BETWEEN 364 PRECEDING AND CURRENT ROW), 3) AS tdpo_annual_ma
    FROM base_data
)
SELECT 
    `Record Date`,
    -- 使用自定义函数简化百分比计算
    calculate_percent_change(dhpb, week_b4_dhpb) AS DHPB_percent_change_weekly,
    dhpb_weekly_ma AS DHPB_WEEKLY_MOVING_AVERAGE,
    calculate_percent_change(dhpb, month_b4_dhpb) AS DHPB_percent_change_monthly,
    dhpb_monthly_ma AS DHPB_MONTHLY_MOVING_AVERAGE,
    calculate_percent_change(dhpb, year_b4_dhpb) AS DHPB_percent_change_annual,
    dhpb_annual_ma AS DHPB_ANNUAL_MOVING_AVERAGE,
    
    calculate_percent_change(ig, week_b4_ig) AS IG_percent_change_weekly,
    ig_weekly_ma AS IG_WEEKLY_MOVING_AVERAGE,
    calculate_percent_change(ig, month_b4_ig) AS IG_percent_change_monthly,
    ig_monthly_ma AS IG_MONTHLY_MOVING_AVERAGE,
    calculate_percent_change(ig, year_b4_ig) AS IG_percent_change_annual,
    ig_annual_ma AS IG_ANNUAL_MOVING_AVERAGE,
    
    calculate_percent_change(tdpo, week_b4_tdpo) AS TDPO_percent_change_weekly,
    tdpo_weekly_ma AS TDPO_WEEKLY_MOVING_AVERAGE,
    calculate_percent_change(tdpo, month_b4_tdpo) AS TDPO_percent_change_monthly,
    tdpo_monthly_ma AS TDPO_MONTHLY_MOVING_AVERAGE,
    calculate_percent_change(tdpo, year_b4_tdpo) AS TDPO_percent_change_annual,
    tdpo_annual_ma AS TDPO_ANNUAL_MOVING_AVERAGE
FROM moving_averages;

3. 递归CTE的适用性说明 #

递归CTE更适合处理层级数据（如组织架构）或生成连续日期序列，在当前时间序列分析场景中，递归无法有效复用表达式或提升性能，反而会增加查询复杂度，因此不推荐使用。

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