MariaDB按年周分组取周首日 基于区域动态设置周起始日方案

两类周一为周首日的规则差异 #

两种规则的核心区别是每年第一周的判定标准：

• firstdayofweek取值为1/3（周首日为周一+第一周超过3天）：遵循ISO 8601国际标准，要求每年的第一周至少有4天落在新的一年，不足4天的话，该周会被计入上一年的最后一周。
  举个例子：2023年1月1日为周日，所属周的时间范围是2022-12-26（周一）到2023-01-01（周日），该周只有1天落在2023年，不足4天，因此按该规则计算的YEARWEEK('2023-01-01',1)结果为202253。
• firstdayofweek取值为5/7（仅周首日为周一）：只要某一周包含1月1日，不管有多少天落在新的一年，该周都会被算作新年的第一周。
  还是拿2023年1月1日举例，按该规则计算的YEARWEEK('2023-01-01',5)结果为202301。

美国地区周规则对应参数说明 #

美国地区常用周规则不对应参数值5。
美国通用周规则为「周首日为周日，包含1月1日的周为当年第一周」，对应firstdayofweek取值为0。参数5的周首日为周一，不符合美国地区的使用习惯，更适配部分亚洲、中东地区的周规则要求。

动态周规则报表实现方案 #

针对你按区域动态生成周分组、返回周首日的需求，可以按以下逻辑实现：

1. 提前建立区域与firstdayofweek参数的映射关系，无匹配区域时默认传3（ISO标准周一为周首日，适用范围最广）即可：
   • 美国、加拿大、墨西哥等美洲国家：映射为0
   • 欧盟、中国、澳大利亚等国家/地区：映射为3
2. 分组统计并返回周首日的参考SQL如下，其中@week_mode为动态传入的firstdayofweek参数，日期字段为dt，业务表名为daily_data：

SELECT
  YEARWEEK(dt, @week_mode) AS report_yearweek,
  -- 计算当前周的首日日期
  DATE_SUB(dt, INTERVAL (WEEKDAY(dt) + IF(@week_mode IN (0,2,4,6), 1, 0)) DAY) AS week_first_day,
  SUM(stat_value) AS total_stat -- 替换为你需要的聚合指标
FROM daily_data
GROUP BY report_yearweek, week_first_day

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