SQL 如何用 SQL 做基础时间序列分析？_网络技术

第一步是用时间函数截断精度再GROUP BY，如PostgreSQL用DATE(created_at)或DATE_TRUNC('day', created_at)，避免直接GROUP BY时间字段导致无聚合。

怎么用 GROUP BY + 时间函数做聚合切片

时间序列分析的第一步，是把原始数据按时间粒度（比如天、小时）归并。关键不是“序列”，而是“对齐”——让不同记录落在同一时间桶里再统计。

常见错误是直接 GROUP BY created_at，结果每条记录都独立成组，完全没聚合。必须先用时间函数截断精度：

PostgreSQL：DATE(created_at) 或 DATE_TRUNC('day', created_at)
MySQL：DATE(created_at) 或 DATE_FORMAT(created_at, '%Y-%m-%d')
SQL Server：CAST(created_at AS DATE) 或 CONVERT(DATE, created_at)

示例：查每天订单数

SELECT DATE(order_time) AS day, COUNT(*) AS cnt
FROM orders
WHERE order_time >= '2025-01-01'
GROUP BY DATE(order_time)
ORDER BY day;

如何补全缺失日期（避免图表断点）

原始数据往往有空缺——比如某天没订单，GROUP BY 就不会返回那行，画折线图时直接断掉。必须主动构造完整日期序列再左连接。

各数据库生成连续日期的方式差异大，核心思路一致：用递归 CTE 或数字表生成日期序列，再 LEFT JOIN 原始聚合结果。

PostgreSQL 推荐用 GENERATE_SERIES()：SELECT CURRENT_DATE - i AS day FROM GENERATE_SERIES(0, 6) AS i
MySQL 8.0+ 可用递归 CTE，但需设置 cte_max_recursion_depth
SQLite 没原生支持，得靠 UNION ALL 拼有限范围（不推荐超 30 天）

补全后记得用 COALESCE(cnt, 0) 把 NULL 转成 0，否则前端仍可能报错或渲染异常。

计算同比/环比为什么不能只靠 LAG()？

LAG() 看似能取上一行，但时间序列的“上一期”未必是物理上一行——比如你按周聚合，但某周数据缺失，LAG(cnt) 会跳到再上一周，而非严格前 7 天。

真正可靠的方案是显式关联时间偏移：

用 LEFT JOIN 关联自身，条件为 t1.day = t2.day + INTERVAL '7 days'（PostgreSQL/MySQL）
或用窗口函数配合 ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW（仅 PostgreSQL 支持 RANGE + INTERVAL）

注意：LAG() 在日期连续、无缺失时可用，但生产环境必须假设数据不全——别省这一步校验。

WHERE 和 HAVING 在时间过滤中容易搞混

想查“过去 30 天内日均订单 > 100 的产品”，有人写成：

SELECT product_id, AVG(cnt) AS avg_daily
FROM (SELECT product_id, DATE(order_time) AS day, COUNT(*) AS cnt
      FROM orders GROUP BY product_id, DATE(order_time)) t
WHERE day >= CURRENT_DATE - 30  -- ❌ 错！day 来自子查询，不是原始表字段
GROUP BY product_id
HAVING AVG(cnt) > 100;

正确做法是：时间过滤尽量在最内层作用于原始时间字段，而不是聚合后的别名；HAVING 只管聚合结果条件，不管时间范围。

更安全的写法：

SELECT product_id, AVG(daily_cnt) AS avg_daily
FROM (
  SELECT product_id, DATE(order_time) AS day, COUNT(*) AS daily_cnt
  FROM orders
  WHERE order_time >= CURRENT_DATE - INTERVAL '30 days'  -- ✅ 过滤原始时间
  GROUP BY product_id, DATE(order_time)
) t
GROUP BY product_id
HAVING AVG(daily_cnt) > 100;

时间字段的过滤层级一旦错，就可能漏数据或引入脏数据——尤其当表里有未来时间或 NULL 时间时，影响比想象中大。

# mysql # 前端 # ai # 为什么 # sql # NULL # select # date # union # 递归 # 切片 # postgresql # 数据库 # 原始数据 # 落在 # 报错 # 而非 # 不全 # 跳到 # 先用 # 每条 # 中大

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】