SQL按年/季/月/周聚合统计时数据跨期归属错误修复方案

1. 时间解析未指定业务时区：from_iso8601_timestamp默认返回UTC时区时间，如果你存储的时间戳是东八区等本地时间，直接转日期会出现时差偏移——比如东八区5月31日23点的记录，转UTC后会落到6月1日，直接导致5月数据被统计到6月，周维度跨天错配也是相同时差问题导致。
2. 周统计规则不匹配：默认extract(week from ...)遵循ISO周标准，规则为每周从周一开始，每年第一周需包含至少4个当年日期，年初、年末的日期经常会被归入相邻年份的周，和国内常用的自然周统计逻辑不符。
3. SQL语法逻辑错误：写死period值为MONTHLY的同时，按年/季/月/周四个时间维度同时分组，会导致同周期数据被拆成多行；HAVING count(7)>=1是对常量计数，永远成立，完全起不到过滤作用；order by 7 desc列引用错位，无法按用户数正确排序。

仅需3处改动即可满足严格按自然周期统计的要求：

1. 所有时间解析、抽取逻辑强制指定业务使用的本地时区，彻底消除时差偏移
2. 周维度改用自然周起始日期做分组依据，避免ISO周规则的跨期问题
3. 按实际统计粒度调整分组字段，修正无效的过滤、排序逻辑

修复后的参考SQL：

SELECT
  extract(year from date(from_iso8601_timestamp(timestamp_derived) AT TIME ZONE 'Asia/Shanghai')) AS year,
  extract(quarter from date(from_iso8601_timestamp(timestamp_derived) AT TIME ZONE 'Asia/Shanghai')) AS quarter,
  extract(month from date(from_iso8601_timestamp(timestamp_derived) AT TIME ZONE 'Asia/Shanghai')) AS month,
  -- 自然周按周一为起始，若要周日为起始可调整偏移计算逻辑
  date_add(
    'day', 
    - (extract(day_of_week from date(from_iso8601_timestamp(timestamp_derived) AT TIME ZONE 'Asia/Shanghai')) - 2),
    date(from_iso8601_timestamp(timestamp_derived) AT TIME ZONE 'Asia/Shanghai')
  ) AS week_start,
  -- 按当前统计粒度修改period值：年统计填'YEARLY'、季度填'QUARTERLY'、月填'MONTHLY'、周填'WEEKLY'
  'MONTHLY' as period,
  Region,
  COUNT(DISTINCT user_id) as users
FROM login as l
INNER JOIN logfiles as f
  ON l.id = f.user_id
WHERE 
  extract(year from date(from_iso8601_timestamp(timestamp_derived) AT TIME ZONE 'Asia/Shanghai')) = 2022  
-- 统计对应粒度时删除不需要的分组字段：比如统计月维度就删掉week_start
GROUP BY year, quarter, month, week_start, period, Region
ORDER BY users desc

• 代码中'Asia/Shanghai'为东八区示例，替换为你业务实际使用的时区即可
• 如果业务本身要求按ISO周统计，不要搭配自然年字段分组，需额外抽取ISO周对应的年份字段，否则年初、年末会出现年、周归属不匹配的问题
• 单条查询只统计一个时间粒度即可，不要同时按年/季/月/周多维度分组，否则会导致数据拆分过细，无法得到每个区域单周期单行的统计结果

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