grouping sets 和窗口函数不能直接嵌套使用，必须先用 grouping sets 完成多粒度聚合，再在外层查询中对结果应用窗口函数，并利用 grouping() 函数识别分组层级以实现分区和排序。
GROUPING SETS 和窗口函数在 Spark SQL 中不能直接嵌套使用——这是最常被误解的点。GROUPING SETS 是聚合阶段的语法，作用于 GROUP BY 子句；而窗口函数（如 ROW_NUMBER()、SUM() OVER）必须在 SELECT 列表中执行，且只能在聚合之后（即 GROUP BY 完成后）作用于已聚合的结果行。两者处于 SQL 执行逻辑的不同阶段，强行混用会报错：AnalysisException: Window function cannot be used in GROUP BY 或类似提示。
GROUPING SETS 输出结果后才能加窗口函数
你必须把 GROUPING SETS 当作“第一层聚合”，先得到多粒度汇总结果，再对这个结果集（视图或子查询）应用窗口函数。
-
GROUPING SETS本身不支持OVER子句，也不能出现在窗口定义中 - 窗口函数的输入必须是已分组、已聚合后的行集合，例如
SELECT ... FROM (SELECT ... GROUP BY GROUPING SETS(...)) t - 如果想对每个 grouping set 的结果分别做排名或累计，需在子查询中用
GROUPING()函数识别当前行所属的 grouping set 级别，并作为PARTITION BY字段
如何用 GROUPING() 区分不同分组层级
GROUPING() 是关键辅助函数，它对每个分组列返回 0（该列参与了当前 grouping set）或 1（该列为 NULL，表示未参与），可用于构造统一排序/分区逻辑。
- 例如：对
(city, car_model)、(city)、()三个 grouping set，GROUPING(city)和GROUPING(car_model)的组合能唯一标识层级 - 常用技巧：用
GROUPING(city) * 10 + GROUPING(car_model)生成层级 ID，再用于PARTITION BY或ORDER BY - 注意：
GROUPING()只在含GROUPING SETS/CUBE/ROLLUP的查询中有效，否则报错
实际可运行的两步写法示例
以下 SQL 在 Spark 3.5+ 中可直接执行，统计各维度销量并按城市层级加累计和排名：
SELECT
city,
car_model,
sum_quantity,
SUM(sum_quantity) OVER (
PARTITION BY GROUPING(city)
ORDER BY city NULLS FIRST, car_model NULLS FIRST
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumsum_by_city_level,
ROW_NUMBER() OVER (
PARTITION BY GROUPING(city)
ORDER BY sum_quantity DESC
) AS rank_in_level
FROM (
SELECT
city,
car_model,
SUM(quantity) AS sum_quantity
FROM dealer
GROUP BY GROUPING SETS ((city, car_model), (city), ())
) t
ORDER BY GROUPING(city), city NULLS FIRST, car_model NULLS FIRST;
- 内层子查询完成多维聚合，输出含 NULL 的混合粒度行
- 外层用
GROUPING(city)将“全局”“城市级”“城市+车型级”三类结果分开计算窗口 -
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW显式指定帧，避免默认RANGE在 NULL 上出错
容易忽略的兼容性与性能坑
Spark 对 GROUPING SETS + 窗口的组合支持较新，旧版本（
- Spark 3.2 及更早版本不支持在
GROUPING SETS查询的顶层直接引用GROUPING()做窗口分区，必须包一层子查询 - 窗口函数中的
ORDER BY若含 NULL 字段（如car_model在城市级分组中为 NULL），需显式写NULLS FIRST，否则排序结果不稳定 - 大数据量下，
GROUPING SETS本身会触发多次 shuffle；再叠加窗口函数，可能引发额外 partitioning，建议用DISTRIBUTE BY提前重分布
真正难的不是语法拼接，而是理解 GROUPING SETS 产出的是“扁平化多粒度结果集”，它没有原始行结构，所有窗口计算都基于这个聚合后的二维表——一旦忘了这点，就容易在 PARTITION BY 里误用原始字段或漏掉 GROUPING() 标识。
