SQL 多维度数据分析 SQL 案例

sql多维聚合需严守规则：group by非聚合列须全出现；窗口函数替代自连接实现安全同比/环比；where过滤行前数据，having过滤分组后结果；cube/rollup生成的NULL行是合法汇总标识，应配合grouping()识别。

GROUP BY 多字段组合时，select 列必须严格受限

SQL 标准要求：只要用了 GROUP BY，SELECT 子句里出现的非聚合列，必须全部出现在 GROUP BY 列表中。否则 mysql 5.7+（默认 ONLY_FULL_GROUP_BY 开启）或 postgresql 会直接报错 Error: column "xxx" must appear in the GROUP BY clause。

常见错误是写成：SELECT user_id, city, count(*) FROM orders GROUP BY user_id——这里 city 既没聚合也没分组，必然失败。

• 正确做法：按业务含义补全分组字段，比如想看「每个用户在每个城市的订单数」，就写 GROUP BY user_id, city
• 如果只是想取某个 city 的代表性值（如首条），得用聚合函数包裹：MAX(city) 或 ANY_VALUE(city)（MySQL），但要注意语义是否合理
• PostgreSQL 不支持 ANY_VALUE，必须显式聚合或重写逻辑

窗口函数替代自连接做多维度同比/环比

想算「每个城市每月销售额 vs 上月」，别急着写 JOIN orders o1 ON o1.city = o2.city AND o1.month = o2.month - INTERVAL '1 month'——自连接在数据量大时性能陡降，且日期边界容易出错。

窗口函数更稳、更可读：

SELECT   city,   month,   sales,   LAG(sales) OVER (PARTITION BY city ORDER BY month) AS prev_month_sales,   ROUND((sales - LAG(sales) OVER (PARTITION BY city ORDER BY month)) / NULLIF(LAG(sales) OVER (PARTITION BY city ORDER BY month), 0), 2) AS mom_growth FROM monthly_sales;

• PARTITION BY city 确保每个城市独立计算，不跨城混算
• NULLIF(..., 0) 防止除零，比 CASE WHEN ... = 0 THEN NULL ELSE ... END 更简洁
• 注意 ORDER BY month 必须明确，否则 LAG 行为不可预测；月份字段要是真正可排序类型（如 date，不是字符串 '2024-01'）

WHERE 和 HAVING 混用导致过滤失效

想查「订单数 > 10 且平均客单价 > 500 的城市」，错误写法：SELECT city, COUNT(*), AVG(amount) FROM orders GROUP BY city WHERE AVG(amount) > 500 HAVING COUNT(*) > 10——WHERE 不能用聚合结果，这句语法直接报错 ERROR: aggregate functions are not allowed in WHERE。

• 聚合前的条件（如 status = 'paid'）放 WHERE
• 聚合后的条件（如 COUNT(*) > 10、AVG(amount) > 500）必须放 HAVING
• 顺序固定：先 WHERE 过滤原始行 → 再 GROUP BY 分组 → 最后 HAVING 过滤分组结果

多维透视用 CUBE / ROLLUP 易忽略 NULL 含义

执行 SELECT city, product_type, SUM(sales) FROM sales GROUP BY CUBE(city, product_type) 后，结果里会出现 city = NULL 或 product_type = NULL 的行——这不是脏数据，而是汇总标识。

比如 (NULL, 'electronics') 表示「所有城市的 electronics 总和」；(NULL, NULL) 是全表总和。

• 别用 WHERE city IS NOT NULL 直接砍掉，会丢掉汇总行
• 需要区分层级时，配合 GROUPING() 函数：GROUPING(city) 返回 1 表示该行是 city 维度的汇总，0 表示明细
• CUBE 生成所有组合（2ⁿ），ROLLUP 只生成层级递进（如 city → city+type → total），选哪个取决于报表需求

多维分析真正的复杂点不在写法，而在理解每一行背后的聚合逻辑——尤其是 NULL 值代表什么，以及不同数据库对 CUBE 的支持程度（如 sqlite 不支持，MySQL 8.0+ 才支持）。