SQL中SUMIF的替代实现：汇总周末票房并计算环比

核心逻辑 #

• 筛选出《猫王》的周六、周日票房记录
• 将同一周的周末两天数据归为一组，计算单周末总票房
• 用窗口函数LAG()拉取上一周末的总票房，直接计算环比变化百分比，避免CASE WHEN带来的多列问题

MySQL 实现代码 #

假设票房表名为box_office，包含字段：date（放映日期）、dbo（当日国内票房）、movie_name（电影名称）。

WITH weekend_box AS (
    SELECT
        -- 以每周日作为该周末的标识，想改周六的话调整日期计算逻辑即可
        DATE_SUB(date, INTERVAL (DAYOFWEEK(date) - 1) DAY) + INTERVAL 6 DAY AS weekend_end_date,
        SUM(dbo) AS weekend_total_dbo
    FROM box_office
    WHERE
        movie_name = '猫王'
        -- 筛选周日(DAYOFWEEK=1)和周六(DAYOFWEEK=7)
        AND DAYOFWEEK(date) IN (1,7)
    GROUP BY weekend_end_date
    ORDER BY weekend_end_date
)
SELECT
    weekend_end_date,
    weekend_total_dbo,
    -- 处理第一个周末无对比数据的情况，变化百分比设为NULL
    CASE
        WHEN LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date) IS NULL THEN NULL
        ELSE ROUND(
            (weekend_total_dbo - LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date)) 
            / LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date) * 100,
            2
        )
    END AS week_over_week_change_pct
FROM weekend_box;

SQL Server 实现代码 #

WITH weekend_box AS (
    SELECT
        -- 以每周日作为周末标识
        DATEADD(DAY, 6 - DATEPART(WEEKDAY, date), date) AS weekend_end_date,
        SUM(dbo) AS weekend_total_dbo
    FROM box_office
    WHERE
        movie_name = '猫王'
        -- 筛选周日(WEEKDAY=1)和周六(WEEKDAY=7)，注意SQL Server默认周日为一周第一天，可通过SET DATEFIRST调整
        AND DATEPART(WEEKDAY, date) IN (1,7)
    GROUP BY DATEADD(DAY, 6 - DATEPART(WEEKDAY, date), date)
    ORDER BY weekend_end_date
)
SELECT
    weekend_end_date,
    weekend_total_dbo,
    CASE
        WHEN LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date) IS NULL THEN NULL
        ELSE ROUND(
            (weekend_total_dbo - LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date)) 
            / LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date) * 100,
            2
        )
    END AS week_over_week_change_pct
FROM weekend_box;

Oracle 实现代码 #

WITH weekend_box AS (
    SELECT
        -- 以每周日作为周末标识
        TRUNC(date, 'IW') + INTERVAL '6' DAY AS weekend_end_date,
        SUM(dbo) AS weekend_total_dbo
    FROM box_office
    WHERE
        movie_name = '猫王'
        -- 这里默认周日是1、周六是7，可根据NLS_TERRITORY设置调整
        AND TO_CHAR(date, 'D') IN ('1','7')
    GROUP BY TRUNC(date, 'IW') + INTERVAL '6' DAY
    ORDER BY weekend_end_date
)
SELECT
    weekend_end_date,
    weekend_total_dbo,
    CASE
        WHEN LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date) IS NULL THEN NULL
        ELSE ROUND(
            (weekend_total_dbo - LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date)) 
            / LAG(weekend_total_dbo) OVER (ORDER BY weekend_end_date) * 100,
            2
        )
    END AS week_over_week_change_pct
FROM weekend_box;

关键说明 #

• 周末分组：示例用每周日作为周末的统一标识，如果你想以周六为节点，修改日期计算逻辑即可。
• 环比计算：LAG()窗口函数直接获取上一周的总票房，输出结果是一行对应一个周末的总票房和变化率，完全符合单行分组的需求。
• 空值处理：第一个周末没有上一周的数据，所以变化百分比设为NULL，你也可以根据需求改成0或者其他标记。

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