17370845950

SQL时间序列统计核心是将时间作为坐标轴，通过日期函数归约、窗口函数滚动计算、生成时间维度表对齐补零、LAG/LEAD实现同比环比，关键在时间切片、对齐与累积。

SQL 时间序列统计的核心在于把时间字段当作分组或排序的主轴，结合窗口函数、聚合函数和日期函数协同处理。关键不是“怎么写语句”，而是理解“时间如何被切片、对齐与累积”。

按时间粒度分组聚合：最常用也最容易出错

比如按天、按月统计订单量，不能直接 GROUP BY order_time（精度太高），而要先用日期函数归约：

• MySQL：用 DATE(order_time) 或 YEAR(order_time), MONTH(order_time)
• PostgreSQL：用 DATE_TRUNC('day', order_time) 或 TO_CHAR(order_time, 'YYYY-MM')
• 注意空缺问题：如果某天没数据，GROUP BY 不会自动补 0，需配合生成时间维度表（如用 generate_series）做 LEFT JOIN

滚动计算（滑动窗口）：用窗口函数替代自连接

求“最近 7 天日均销量”，不用写 7 表 JOIN，直接用 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW：

• 先按日期排序：ORDER BY DATE(order_time)
• 再定义窗口：AVG(sales) OVER (ORDER BY DATE(order_time) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
• 注意：若存在多条同一天记录，建议先按天聚合再开窗，避免重复放大

时间对齐与缺失填充：业务分析的真实难点

原始数据往往不连续，但报表需要“每天一行”。解决思路是构造完整时间序列，再关联原始数据：

• PostgreSQL 可用 generate_series('2025-01-01'::date, '2025-12-31'::date, '1 day')
• MySQL 8.0+ 可用递归 CTE 模拟；低版本常用数字辅助表 + DATE_ADD
• LEFT JOIN 后用 COALESCE(sales, 0) 填零，确保趋势图不跳变

同比/环比计算：别硬算，用 LAG/LEAD 更稳

环比（比上期）和同比（比去年同期）本质是跨行取值，窗口函数天然适配：

• LAG(sales, 1) OVER (ORDER BY month) → 上月销量
• LAG(sales, 12) OVER (ORDER BY year, month) → 去年同月（需保证排序字段能唯一标识周期）
• 计算比率时加判断：CASE WHEN prev_sales > 0 THEN (sales - prev_sales)/prev_sales END，防除零

基本上就这些。时间序列统计不复杂，但容易忽略对齐、空值、边界和精度问题。把时间当成一维坐标轴去思考，SQL 就只是在上面“画点、连线、填格子”。