sql时间序列统计需遵循“时间 切片+ 分组聚合 + 连续性校验”三层逻辑：一明确时间粒度（年月日 / 自然月 / 滚动 7 天 / 工作日分离）；二主动补全空值与断点；三用窗口函数实现跨周期对比；四统一处理时区与业务时间。
SQL 时间序列统计不是简单套函数，核心在于理解“时间切片 + 分组聚合 + 连续性校验”三层逻辑。跳过这层，容易写出看似能跑、实则漏数据或错分桶的查询。
一、明确时间粒度：先定“切片规则”，再写 SQL
时间粒度不是随便选的，它直接决定统计口径是否合理。比如分析用户活跃，按“天”看趋势没问题；但算“每小时订单转化率”，就得确认业务是否真支持小时级决策。
常用切片方式（用标准 SQL 兼容写法）：
- 年月日：
date(order_time)或TO_DATE(order_time, 'yyYY-MM-DD') - 自然月：
DATE_TRUNC('month', order_time)（postgresql / BigQuery），或STR_TO_DATE(DATE_FORMAT(order_time, '%Y-%m-01'), '%Y-%m-%d')（mysql） - 滚动 7 天：需自连接或窗口函数，不能只靠 GROUP BY
- 工作日 / 节假日分离：建议提前建日历维表（calendar_dim），关联比 CASE WHEN 更稳定
二、处理空值与断点：时间序列最易踩坑的环节
原始数据往往有缺失——某天没订单、某小时没日志。直接 GROUP BY 会自动跳过这些时间点，导致折线图断开、同比计算偏移。
正确做法是“主动补全”：
- 生成连续时间序列（如近 30 天日期列表），再 LEFT JOIN 业务表
- 用
COALESCE(count_col, 0)把NULL转为 0，而非保留空值 - 若需识别“真实零值”和“数据缺失”，应在 etl 层打标（如 is_data_available 字段），不在查询层模糊处理
三、跨周期对比：别硬写子查询，用窗口函数更稳
环比（vs 上期）、同比（vs 去年同周）、移动平均——这些需求如果用多层子查询或 WITH 嵌套，可读性差、性能低、还难复用。
推荐统一用窗口函数表达：
- 环比（日）：
LAG(cnt, 1) OVER (ORDER BY dt) - 同比（周）：
LAG(cnt, 52) OVER (ORDER BY year_week)（假设按周分组，year_week 格式如 ’2024-W05’） - 7 日移动平均：
AVG(cnt) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
注意：窗口函数 ORDER BY 必须严格单调（如 dt 不能重复），否则排序不确定会导致结果抖动。
四、时区与业务时间：一个常被忽略的“隐性维度”
数据库 存的是 UTC 时间？还是服务器本地时间？业务指标要求按“用户所在地时间”统计？这直接影响切片结果。
关键动作：
- 查清源头时间字段的时区含义（日志埋点、支付回调、DB 写入时机）
- 统一转换到业务时区再切片，例如：
order_time AT TIME ZONE 'Asia/Shanghai' - 避免在 WHERE 里用时区转换（如
WHERE order_time::timestamptz AT TIME ZONE 'Asia/Shanghai' > '2024-01-01'），应先转换再过滤，否则无法走索引
基本上就这些。时间序列统计不复杂，但容易忽略上下文——粒度定义不清、空值处理随意、时区混用、对比逻辑硬 编码，都会让结果失真。把这四步拆开理顺，再复杂的趋势分析也能稳住底盘。
