MySQL按周分组：如何让自定义时间周期的周数从1开始计数

MySQL 实现方式 #

直接用TIMESTAMPDIFF函数计算交易日期与周期起始日的周数差，再加1得到周期内的周序号：

select 
    TIMESTAMPDIFF(WEEK, '2022-05-01', sr.transactionDate) + 1 AS cycle_week_number,
    sum(sr.digitalEarnings) as total_digital_earnings
from
    sales_report as sr
where 
    sr.transactionDate BETWEEN '2022-05-01' AND '2022-12-31'
group by 
    cycle_week_number
order by 
    cycle_week_number;

说明：

• TIMESTAMPDIFF(WEEK, '2022-05-01', sr.transactionDate) 返回两个日期之间的周数差，周期起始日所在周的日期会返回0，加1后就对应周期内的第1周。
• 如果需要统一周的起始日（比如要求周一为一周第一天），可以调整起始日期参数，比如把'2022-05-01'替换为DATE_SUB('2022-05-01', INTERVAL WEEKDAY('2022-05-01') DAY)，这样会把起始日对齐到当周的周一。

其他数据库适配 #

核心逻辑都是计算日期差后加1，仅函数存在差异：

• PostgreSQL：

  select 
      EXTRACT(WEEK FROM (sr.transactionDate - '2022-05-01'::DATE)) + 1 AS cycle_week_number,
      sum(sr.digitalEarnings) as total_digital_earnings
  from sales_report as sr
  where sr.transactionDate BETWEEN '2022-05-01' AND '2022-12-31'
  group by cycle_week_number
  order by cycle_week_number;
  

• SQL Server：

  select 
      DATEDIFF(WEEK, '2022-05-01', sr.transactionDate) + 1 AS cycle_week_number,
      sum(sr.digitalEarnings) as total_digital_earnings
  from sales_report as sr
  where sr.transactionDate BETWEEN '2022-05-01' AND '2022-12-31'
  group by cycle_week_number
  order by cycle_week_number;
  

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