SQL与Pandas的年度周计算结果不一致问题排查

• 周数计算标准不统一
  SQL的week(date)函数行为随数据库类型差异极大：

  • MySQL：默认模式下以周日为一周起始，且将1月1日所在周直接算作当年第1周（哪怕该周仅1天）；若需遵循ISO标准，需使用week(date, 1)参数；
  • PostgreSQL：extract(week from date)默认遵循ISO 8601，但可通过数据库配置修改规则；
  • SQL Server：datepart(week, date)默认以周日为起始，1月1日所在周即为当年第1周。
    而Pandas的df['date_time'].dt.weekofyear（新版本推荐用df['date_time'].dt.isocalendar().week）默认严格遵循ISO 8601标准：周一为一周起始，一年的第一周必须包含当年至少4天——这意味着1月1日可能被划到上一年的最后一周，12月底的日期也可能被归为下一年的第一周。

• 一周起始日定义不同
  多数SQL数据库默认以周日作为一周的第一天，而Pandas周计算的默认起始日是周一。比如1月1日是周日时，SQL会将其归入当年第1周，但Pandas会把它划到上一年的第52/53周，直接导致周数结果差异。

• 跨年周的划分逻辑冲突
  针对年末或年初的临界日期，两者处理逻辑完全不同：

  • 例：2023年12月31日是周日，MySQL默认规则下会算为2023年第52周；但按ISO标准，这一天属于2024年第1周，Pandas会返回1；
  • 再例：2025年1月1日是周三，SQL若以周日为起始会算作第1周，Pandas同样算第1周；但如果2025年1月1日是周日，SQL算第1周，Pandas则会将其归为2024年的第53周。

• 函数版本与参数配置差异

  • SQL的week()函数大多支持参数调整规则，比如MySQL的week(date, 3)会完全匹配ISO 8601标准；
  • Pandas旧版本的weekofyear存在时区处理或边界计算bug，官方已推荐用isocalendar().week替代，若仍使用旧函数可能导致结果偏差。

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