如何编写SQL按分组自定义周起止规则聚合销售数据实现周度对比

以下方案以MySQL语法为例，其他数据库可对应替换日期函数即可实现。

核心周度聚合查询 #

假设你的日度销售源表名为daily_sales，包含字段Dates（建议存储为日期类型，字符串格式需提前转换）、Group、Net Sales、Volume Sold，直接使用以下语句即可得到需求的周度汇总结果：

SELECT
    -- 格式化周起始日期为DD/MM/YYYY格式输出
    DATE_FORMAT(week_start_date, '%d/%m/%Y') AS `Weekly Date`,
    `Group`,
    SUM(`Net Sales`) AS `Net Sales`,
    SUM(`Volume Sold`) AS `Volume Sold`
FROM (
    SELECT
        *,
        -- 按分组规则计算每条日度记录对应的周起始日期
        CASE 
            WHEN `Group` = 'Ambient' THEN DATE_SUB(Dates, INTERVAL (WEEKDAY(Dates) - 1) DAY)
            WHEN `Group` = 'Chilled' THEN DATE_SUB(Dates, INTERVAL (WEEKDAY(Dates) - 2) DAY)
        END AS week_start_date
    FROM daily_sales
    -- 可在这里加日期过滤条件，限定需要统计的时间范围
    WHERE Dates >= '2020-10-01' AND Dates <= '2020-11-30'
) AS t
GROUP BY week_start_date, `Group`
ORDER BY `Group`, week_start_date;

逻辑说明 #

• 内层子查询先匹配每一条日度数据的所属周：
  • Ambient组周规则为周二到周一：MySQL的WEEKDAY函数返回值周一为0、周二为1，通过偏移计算直接取当周周二作为周起始日期
  • Chilled组周规则为周三到周二：WEEKDAY函数周三返回值为2，同理偏移计算取当周周三作为周起始日期
• 外层按周起始日期和分组聚合，输出的结果和你给出的示例格式完全一致。

支持促销周对比的扩展查询 #

你可以先把促销日期整理为一张promo_weeks表，包含字段Group、promo_start_date、promo_end_date，使用以下关联查询即可添加促销周标记，方便对比：

SELECT
    DATE_FORMAT(t.week_start_date, '%d/%m/%Y') AS `Weekly Date`,
    t.`Group`,
    SUM(t.`Net Sales`) AS `Net Sales`,
    SUM(t.`Volume Sold`) AS `Volume Sold`,
    -- 标记当前周是否为促销周
    IF(p.`Group` IS NOT NULL, '是', '否') AS `是否促销周`
FROM (
    SELECT
        *,
        CASE 
            WHEN `Group` = 'Ambient' THEN DATE_SUB(Dates, INTERVAL (WEEKDAY(Dates) - 1) DAY)
            WHEN `Group` = 'Chilled' THEN DATE_SUB(Dates, INTERVAL (WEEKDAY(Dates) - 2) DAY)
        END AS week_start_date,
        -- 计算周结束日期用于匹配促销表
        CASE 
            WHEN `Group` = 'Ambient' THEN DATE_ADD(DATE_SUB(Dates, INTERVAL (WEEKDAY(Dates) - 1) DAY), INTERVAL 6 DAY)
            WHEN `Group` = 'Chilled' THEN DATE_ADD(DATE_SUB(Dates, INTERVAL (WEEKDAY(Dates) - 2) DAY), INTERVAL 6 DAY)
        END AS week_end_date
    FROM daily_sales
    WHERE Dates >= '2020-10-01' AND Dates <= '2020-11-30'
) AS t
LEFT JOIN promo_weeks p 
    ON t.`Group` = p.`Group`
    AND t.week_start_date = p.promo_start_date
    AND t.week_end_date = p.promo_end_date
GROUP BY t.week_start_date, t.`Group`, `是否促销周`
ORDER BY t.`Group`, t.week_start_date;

其他数据库适配说明 #

如果使用非MySQL数据库，替换对应日期函数即可：

• PostgreSQL：WEEKDAY对应EXTRACT(DOW FROM Dates) - 1，日期偏移用Dates - INTERVAL 'n days'，日期格式化用TO_CHAR
• SQL Server：WEEKDAY对应DATEPART(WEEKDAY, Dates) - 2（需先执行SET DATEFIRST 1设置周一为一周起始），日期偏移用DATEADD

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