SQL实现自定义周序号：以每年1月1日为周起点、年末统一规整为52周

原有逻辑的问题定位 #

• 最初使用的date_trunc('week', transaction_date)为ISO标准周逻辑，固定以每周一为周起点，会将1月1日所在周的周一作为第1周起点，无法匹配“1月1日为第1周起点”的规则
• 此前试写的ceil(extract(day from transaction_date)/7)存在核心逻辑偏差：extract(day from 日期)返回的是当月的日序号（1-31），而非自然年累计天数，会出现跨月日期重复计算周数的问题，根本无法按年累计统计周数

最终实现SQL（适配PostgreSQL语法，和原有date_trunc语法同源） #

least(
  ceil(extract(doy from transaction_date)::numeric / 7),
  52
) as week_number

规则匹配逻辑 #

• 第一步：extract(doy from transaction_date) 获取日期在所属自然年的累计天序数（day of year），从1月1日开始计数，1月1日返回1，1月8日返回8，完全匹配“第1周以当年1月1日为起点”的基础要求
• 第二步：ceil(累计天/7) 实现每间隔7天累加周数：第1-7天归为第1周，第8-14天归为第2周，和样例中1月8日为第2周的预期完全对齐
• 第三步：用least(计算结果, 52) 统一规整年末周数：由于平年全年365天、闰年全年366天，计算得到的最大周数为ceil(366/7)=53，截断后所有超出52个整周范围的年末剩余天数，全部归入第52周，不会出现第53周的统计结果

样例结果验证 #

完全匹配给出的预期输出：

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