SQL时间序列统计需按时间粒度分组聚合，并处理时区、空缺时段与连续性；须明确目标、补全日期、统一时区、慎用聚合函数，再进阶滚动统计与同比环比。
SQL 时间序列统计的核心是把时间字段当作关键维度，按时间粒度（如日、周、月）分组聚合，同时处理好空缺时段、时区、连续性等问题。不是简单 GROUP BY 就完事，得结合业务目标设计窗口、补全、对齐逻辑。
一、明确时间粒度和统计目标
先问清楚：你要看“每天新增用户数”？“近7天滚动订单金额”？还是“每小时系统错误率趋势”？不同目标决定时间切分方式和聚合函数。
- 日级统计：用 DATE(时间字段) 或 TO_DATE(时间字段, 'YYYY-MM-DD')（不同数据库语法略有差异）
- 周统计：PostgreSQL 用 DATE_TRUNC('week', created_at)；MySQL 8.0+ 可用 YEARWEEK(created_at, 1)（周一为每周起点）
- 按自然月：用 DATE_FORMAT(created_at, '%Y-%m')（MySQL）或 TO_CHAR(created_at, 'YYYY-MM')（PostgreSQL）
二、基础分组聚合 + 处理时区与精度
原始数据的时间字段常带时分秒甚至毫秒，且可能存的是 UTC 时间。直接 GROUP BY 容易跨天错位。
- 统一转成本地时区再截断：比如 PostgreSQL 中 DATE((created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Shanghai')
- 避免用 WHERE created_at > '2024-01-01' 模糊过滤，改用 WHERE created_at >= '2024-01-01' AND created_at （闭开区间更准）
- 聚合时慎用 COUNT(*)：若统计“有订单的日期”，用 COUNT(*)；若统计“每日订单总数”，用 COUNT(id) 或 SUM(quantity)
三、补全缺失日期（关键！不然图表会断）
数据库里没有数据的那天，SQL 默认不返回记录——但你画折线图时需要“0”。必须主动补行。
- 生成连续日期序列：PostgreSQL 可用 GENERATE_SERIES；MySQL 8.0+ 用递归 CTE；旧版 MySQL 常借助数字表或 UNION ALL 拼出日期
- LEFT JOIN 补零：把日期序列作为主表，LEFT JOIN 实际业务表，再用 COALESCE(SUM(...), 0) 填 0
- 示例（PostgreSQL）：
WITH date_range AS (
SELECT GENERATE_SERIES('2024-01-01'::DATE, '2024-01-31'::DATE, '1 day'::INTERVAL)::DATE AS dt
),
daily_orders AS (
SELECT DATE(created_at) AS dt, COUNT(*) AS cnt
FROM orders WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
)
SELECT d.dt, COALESCE(o.cnt, 0) AS order_count
FROM date_range d
LEFT JOIN daily_orders o ON d.dt = o.dt;
四、进阶：滚动统计与同比环比
单纯“日汇总”只是起点。运营常要看“最近7天平均”或“比上周同一时间涨了多少”。
- 滚动均值：用窗口函数 AVG(cnt) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)（含当天共7天）
- 环比（比上一天）：cnt - LAG(cnt, 1) OVER (ORDER BY dt)；同比（比去年同日）：cnt - LAG(cnt, 365) OVER (ORDER BY dt)（需数据满一年）
- 注意：LAG/LEAD 遇到缺失日期会跳过——所以务必先补全日期再算环比，否则“1月1日”对不上“2023年1月1日”
基本上就这些。时间序列统计不复杂，但容易忽略时区、空缺、边界这三点。动手前先画个草图：横轴是什么时间粒度？纵轴要算什么？有没有必须对齐的参照周期？想清楚再写 SQL，少一半返工。
