MySQL周环比差异百分比查询修正：当前周仅对比已完成天数

问题说明 #

原脚本对非当前周的整周环比计算正常，但当前周未结束时，会用本周已产生的数据和上周整周数据对比，不符合需求。正确逻辑应为：若今日是周三，本周统计周一至周三的累计数据，对比上周周一至周三的累计数据计算环比。

修正后的脚本 #

-- 设置今日日期变量，以及当前周已度过的天数（周一=1，周二=2...周日=7）
SET @today = CURDATE();
SET @current_week_day = WEEKDAY(@today) + 1;

WITH DailyStats AS (
    -- 按天统计订单与NC数据，标注年份、周数、周内天数
    SELECT
        YEAR(purchased_est) AS year_sales,
        WEEK(purchased_est, 1) AS week_num,
        WEEKDAY(purchased_est) + 1 AS day_of_week, -- 周一为1，保证与周起始规则一致
        COUNT(*) AS daily_orders,
        IFNULL(SUM(net_comp), 0) AS daily_nc
    FROM revenue
    WHERE purchased_est < @today
    GROUP BY YEAR(purchased_est), WEEK(purchased_est, 1), WEEKDAY(purchased_est) + 1
),
WeeklyCumulative AS (
    -- 计算每周内从周一到对应天数的累计值
    SELECT
        year_sales,
        week_num,
        day_of_week,
        SUM(daily_orders) OVER (PARTITION BY year_sales, week_num ORDER BY day_of_week) AS cumulative_orders,
        SUM(daily_nc) OVER (PARTITION BY year_sales, week_num ORDER BY day_of_week) AS cumulative_nc
    FROM DailyStats
),
WeekPeriodStats AS (
    -- 提取各周的有效统计值：非当前周取整周总和，当前周取到今日的累计值
    SELECT
        year_sales,
        week_num,
        CASE
            WHEN year_sales = YEAR(@today) AND week_num = WEEK(@today, 1) THEN
                (SELECT cumulative_orders FROM WeeklyCumulative WHERE year_sales = YEAR(@today) AND week_num = WEEK(@today, 1) AND day_of_week = @current_week_day)
            ELSE MAX(cumulative_orders)
        END AS period_orders,
        CASE
            WHEN year_sales = YEAR(@today) AND week_num = WEEK(@today, 1) THEN
                (SELECT cumulative_nc FROM WeeklyCumulative WHERE year_sales = YEAR(@today) AND week_num = WEEK(@today, 1) AND day_of_week = @current_week_day)
            ELSE MAX(cumulative_nc)
        END AS period_nc
    FROM WeeklyCumulative
    GROUP BY year_sales, week_num
)
-- 计算周环比差异百分比
SELECT
    current.week_num AS WeekNum,
    current.year_sales AS Year,
    CONCAT(
        ROUND(
            (current.period_orders - COALESCE(prev.period_orders, 0)) / NULLIF(prev.period_orders, 0) * 100,
            2
        ),
        '%'
    ) AS '%Orders Difference',
    CONCAT(
        ROUND(
            (current.period_nc - COALESCE(prev.period_nc, 0)) / NULLIF(prev.period_nc, 0) * 100,
            2
        ),
        '%'
    ) AS '%NC Difference'
FROM WeekPeriodStats current
LEFT JOIN WeekPeriodStats prev
    ON (current.year_sales = prev.year_sales AND current.week_num = prev.week_num + 1)
    OR (current.week_num = 1 AND current.year_sales = prev.year_sales + 1 AND prev.week_num = 53) -- 处理跨年周场景
ORDER BY current.year_sales DESC, current.week_num DESC;

关键逻辑解释 #

1. DailyStats：拆分数据到天粒度，确保后续能计算周内累计值
2. WeeklyCumulative：用窗口函数计算每周从周一到对应天数的累计订单和NC值，比如周三的行存储的是周一至周三的总和
3. WeekPeriodStats：区分当前周和非当前周，非当前周取整周累计最大值（即整周总和），当前周取到今日的累计值
4. 环比关联：当前周关联上周相同天数的累计值，非当前周关联上周整周值，同时处理跨年周的特殊情况
5. 异常处理：用NULLIF避免除以0报错，COALESCE处理上周无数据的场景

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