SQL分组内趋势分析实现方法与实战案例

先说个常见场景：数据表里存了各区域每个月的订单金额，业务方扔过来一句“帮我算一下各区域环比增长”，你打开SQL窗口，顺手在GROUP BY后面接了个LAG(SUM(amount))——结果报错，或者算出来的数字怎么看都不对。

其实道理很简单：GROUP BY是聚合逻辑，LAG()是窗口逻辑，两套体系不能直接混在一起用。想按组做趋势分析，得先分两步走——先聚合出各区域各时间节点的基础指标，再在这些指标上套窗口函数。

GROUP BY 后怎么算同比/环比？

窗口函数必须配合OVER子句，而OVER里的分区逻辑（PARTITION BY）和GROUP BY的聚合逻辑是两套体系。想按组做趋势，得先确保时间维度有序、组内可排序，再用窗口函数“组内打序”，最后聚合或比较。

关键点有几个：

• 必须有明确的时间字段（如 order_date、report_month），且类型为 DATE 或可排序字符串（如 '2024-01'）
• PARTITION BY 要和你想分析的“组”完全一致，比如 PARTITION BY region, product_category
• 排序（ORDER BY）必须写在 OVER 里，不能依赖外部 ORDER BY，否则 LAG() 结果不可靠
• 常见错误：SELECT region, A VG(sales), LAG(A VG(sales)) OVER (PARTITION BY region ORDER BY month) —— 错！聚合函数不能嵌套在窗口函数里；得先算出每组每月的销售，再套窗口

用 CTE 先展开，再套窗口函数

典型做法是分两步：第一步用 GROUP BY 算出各组各时间点的基础指标；第二步把结果当临时表，用 LAG() 算环比、LAG() OVER (... ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) 算同比滚动均值等。

WITH monthly_sales AS (  SELECT     region,    DATE_TRUNC('month', order_date) AS month,    SUM(amount) AS total_sales  FROM orders  GROUP BY region, DATE_TRUNC('month', order_date)),trended AS (  SELECT    region,    month,    total_sales,    LAG(total_sales) OVER (PARTITION BY region ORDER BY month) AS prev_month_sales,    ROUND(      (total_sales - LAG(total_sales) OVER (PARTITION BY region ORDER BY month)) * 100.0 / NULLIF(LAG(total_sales) OVER (PARTITION BY region ORDER BY month), 0),      2    ) AS mom_pct  FROM monthly_sales)SELECT * FROM trended WHERE mom_pct IS NOT NULL;

注意 NULLIF(..., 0) 防除零，这是线上 SQL 经常漏掉的点，容易出生产事故。

不同数据库对 LAG 的兼容性差异

LAG() 在主流数据库都支持，但默认参数和空值处理有区别：

• PostgreSQL / Redshift：LAG(expr, offset, default)，第三个参数是前移越界时返回的默认值，不填则为 NULL
• MySQL 8.0+：同上，但 offset 必须是常量，不能是列或表达式
• Spark SQL：支持，但若 ORDER BY 字段含 NULL，行为可能因版本而异（建议提前 COALESCE(time_col, '1970-01-01')）
• BigQuery：支持，但 ROWS BETWEEN 指定范围时，CURRENT ROW 包含当前行，容易误算成“含自身的均值”

趋势方向判断别只看符号

光算出 mom_pct > 0 不代表真在增长——如果基础值极小（比如上月 1 元、本月 2 元），+100% 毫无业务意义。更稳妥的方式是加双条件：

• 绝对增量阈值：total_sales - prev_month_sales >= 1000
• 相对稳定性过滤：prev_month_sales > 5000（排除噪音组）
• 连续性验证：用 COUNT(*) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 看最近三期是否全有数据

真实场景中，趋势分析最耗神的往往不是写 SQL，而是界定“什么才算有效趋势”——这个边界得和业务方对齐，而不是由 LAG() 自动决定。