Databricks SQL如何设置weekofyear以周日作为每周第一天

Databricks SQL 不支持类SQL Server标准SQL里的SET DATEFIRST全局周起始配置，所有以周日为周起始的周维度计算，都需要通过内置日期函数自定义逻辑实现。

问题原因 #

你当前使用的weekofyear()函数默认遵循ISO 8601标准，以周一为每周第一天，因此会把周日归属到上一周，才会出现周日对应周数返回26、预期为27的偏差。直接用自然日差除以7计算相对周，也会因为周起始对齐错误出现计算偏差。

正确实现逻辑 #

• 周数计算：将目标日期向后偏移1天，原本的周日会对齐到周一位置，再调用weekofyear()即可得到周日为起始的周序号。
• 相对周计算：先把待计算日期、当前日期都对齐到各自所属周的起始日（周日），再计算两个周起始日的天数差除以7取整，即可得到准确的相对周值，不会出现日期归属偏差。

对齐周起始日的通用逻辑：date_sub(目标日期, pmod(datediff(目标日期, '1900-01-07'), 7))，其中1900-01-07是已知的周日基准日期，pmod取模可以算出目标日期距离上周日的间隔天数，相减后就得到当周的起始周日日期。

修正后的完整代码 #

%sql
SELECT 
  year(calendarDate) * 10000 + month(calendarDate) * 100 + day(calendarDate) as `Date (YYYYMMDD)`,
  calendarDate as `Date`,
  date_format(calendarDate, 'EEEE') as `DayName`,
  weekofyear(date_add(calendarDate, 1)) as `Week`,
  datediff(
    date_sub(calendarDate, pmod(datediff(calendarDate, '1900-01-07'), 7)),
    date_sub(current_date(), pmod(datediff(current_date(), '1900-01-07'), 7))
  ) / 7 as `RelativeWeek`
FROM dates
WHERE calendarDate >= '2022-07-01' and calendarDate < '2022-07-20'

用2022年7月的日期验证：2022-07-10为周日，偏移1天后是2022-07-11，weekofyear返回值为27，符合预期。

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