如何通过SQL实现各组内的趋势分析？

GROUP BY 后计算同比/环比需分两步：先用 GROUP BY 聚合出各组各时间点基础指标，再用 CTE 套窗口函数 LAG() 计算差值或比率；必须确保时间字段有序、PARTITION BY 与分组一致、避免聚合嵌套窗口，并用 NULLIF 防除零。

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

直接在 GROUP BY 里用 LAG() 或 LEAD() 是行不通的——窗口函数必须配合 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, AVG(sales), LAG(AVG(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() 自动决定。