Presto SQL提取年周值异常解决：如何设置偏移让第1周从年初开始

问题原因 #

你遇到的错误核心是使用了ISO周规则的%v格式符：ISO周定义每年第1周为包含当年第一个周四的周，因此每年年初的几天可能会被归入上一年的最后一周，你拼接的%Y为当前公历年，就会出现2021-53这种不匹配的错误结果。

解决方案 #

你需要的是自定义周数规则，强制每年第1周从当年1月1日开始，可使用以下两种方案实现： #

方案1：按自然天数切块计算（1月1日-7日为第1周）

直接通过当年已过天数向上取整得到周数：

SELECT 
  concat(
    date_format(from_unixtime((CAST(my_timestamp AS BIGINT) + 19800000)/1000), '%Y'),
    '-',
    lpad(cast(ceil(day_of_year(from_unixtime((CAST(my_timestamp AS BIGINT) + 19800000)/1000)) / 7) AS VARCHAR), 2, '0')
  ) AS week

该方案周数计算逻辑为：当年第1-7天为第1周，第8-14天为第2周，以此类推，完全和自然年对齐，不会出现跨年周数错误。 #

方案2：按周历对齐（1月1日所在周为第1周，周起始为周一）

如果需要按周历维度统计，可通过年起始日期的周差计算：

WITH local_date AS (
  SELECT from_unixtime((CAST(my_timestamp AS BIGINT) + 19800000)/1000) AS dt
)
SELECT 
  concat(
    date_format(dt, '%Y'),
    '-',
    lpad(cast(date_diff('week', date_trunc('year', dt), dt) + 1 AS VARCHAR), 2, '0')
  ) AS week
FROM local_date

该方案以周一为周起始，将1月1日所在的完整周算作当年第1周，同样不会出现跨年周数匹配错误。

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