如何让SQL WEEK()函数以每年1月1日为起始并按整周分组？

我想编写SQL查询生成年度每周销售总额，用于图表对比分析。但发现WEEK()函数的周0或周1并不总是从每年1月1日开始，导致图表中对应日期区间的数据不准确。我试过WEEK()的所有模式参数，发现没有选项能让周从1月1日开始且为完整7天（各模式测试结果见下方代码示例）。现有查询SQL如下，需要实现准确的同日期区间跨年度对比（如2024年1月1-7日与2025年同期对比）。

更新说明：因闰年问题，决定改用1-53的周数标签替代日期区间标签。

// 1月1日-4日归为周0（周数范围0-53），1月5日起为周1
WEEK(orders.created_at, 0) AS week
 
// 1月1日-5日归为周1（周数范围0-53），本该从0开始但实际从1开始，1月6日起为周2
WEEK(orders.created_at, 1) AS week 

// 1月5日-11日归为周1（周数范围1-53），1月1日-4日被归到了周52！1月12日起为周2
WEEK(orders.created_at, 2) AS week
 
// 1月1日-5日归为周1（周数范围1-53），1月6日起为周2
WEEK(orders.created_at, 3) AS week 

// 1月1日-4日归为周1（周数范围0-53），没有周0，1月5日起为周2
WEEK(orders.created_at, 4) AS week 

// 1月1日-5日归为周0（周数范围0-53），1月6日起为周2
WEEK(orders.created_at, 5) AS week 

// 1月1日-4日归为周1（周数范围1-53），1月5日起为周2
WEEK(orders.created_at, 6) AS week 

// 1月6日-12日归为周1（周数范围1-53），1月1日-5日被归到了周53！1月13日起为周2
WEEK(orders.created_at, 7) AS week 

SELECT
    YEAR(orders.created_at) AS year,
    WEEK(orders.created_at) AS week,
    MIN(orders.created_at) AS created_date_min,
    MAX(orders.created_at) AS created_date_max,
    SUM(orders_products.final_price) AS total
FROM
    `orders_products`
INNER JOIN `orders` ON `orders_products`.`order_id` = `orders`.`id`
GROUP BY
    `year`,
    `week`
ORDER BY
    `year` ASC,
    `week` ASC

要实现从每年1月1日开始划分完整7天的周（周数1-53），可以通过计算日期在当年的偏移天数生成自定义周数：

SELECT
    YEAR(orders.created_at) AS year,
    -- 计算当年第几天，除以7后向上取整得到周数（1-53）
    CEIL(DAYOFYEAR(orders.created_at) / 7) AS week,
    MIN(orders.created_at) AS created_date_min,
    MAX(orders.created_at) AS created_date_max,
    SUM(orders_products.final_price) AS total
FROM
    `orders_products`
INNER JOIN `orders` ON `orders_products`.`order_id` = `orders`.`id`
GROUP BY
    `year`,
    week
ORDER BY
    `year` ASC,
    week ASC

说明： #

• DAYOFYEAR(orders.created_at) 获取日期在当年的第几天（1-366）
• CEIL(DAYOFYEAR(...) /7) 将天数除以7后向上取整，确保1月1-7日为周1，8-14日为周2，以此类推
• 闰年的366天会被分成53周（最后一周仅2天），符合1-53的周数标签需求
• 跨年度对比时，每年的第N周对应的日期区间完全一致，完美解决同区间对比的准确性问题

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