多GROUPING SETS组合的SQL分组逻辑及等效UNION ALL实现问询

核心规律：多个GROUPING SETS组合时，本质是对每个GROUPING SETS内部的分组集做笛卡尔积组合，最终的分组结果等于所有组合出来的分组集分别执行GROUP BY后，用UNION ALL合并的结果。

下面用movies示例表具体演示：

1. 创建示例表与数据 #

CREATE TABLE movies (
    title VARCHAR(100),
    studio VARCHAR(50),
    genre VARCHAR(50),
    release_year INT,
    box_office BIGINT
);

INSERT INTO movies VALUES
('Inception', 'Warner Bros', 'Sci-Fi', 2010, 836800000),
('The Dark Knight', 'Warner Bros', 'Action', 2008, 1004600000),
('Toy Story', 'Pixar', 'Animation', 1995, 373600000),
('Coco', 'Pixar', 'Animation', 2017, 807000000),
('Avengers: Endgame', 'Marvel', 'Action', 2019, 2797800000);

2. 单个GROUPING SETS的等效写法（回顾） #

比如GROUP BY GROUPING SETS(studio, ())，等效于两个GROUP BY查询的UNION ALL：

-- 按studio分组统计
SELECT studio, NULL AS genre, NULL AS release_year, SUM(box_office) AS total
FROM movies
GROUP BY studio
UNION ALL
-- 全局汇总
SELECT NULL, NULL, NULL, SUM(box_office) AS total
FROM movies;

3. 多个GROUPING SETS组合的等效写法 #

假设执行GROUP BY GROUPING SETS(studio, ()), GROUPING SETS(genre, release_year)，拆解步骤：

• 第一个GROUPING SETS的分组集：(studio)、()
• 第二个GROUPING SETS的分组集：(genre)、(release_year)
• 笛卡尔积组合后得到4个分组集：(studio, genre)、(studio, release_year)、(genre)、(release_year)

对应的等效UNION ALL写法就是这4个分组查询的合并：

-- 组合1：studio + genre分组
SELECT studio, genre, NULL AS release_year, SUM(box_office) AS total
FROM movies
GROUP BY studio, genre
UNION ALL
-- 组合2：studio + release_year分组
SELECT studio, NULL AS genre, release_year, SUM(box_office) AS total
FROM movies
GROUP BY studio, release_year
UNION ALL
-- 组合3：仅genre分组
SELECT NULL AS studio, genre, NULL AS release_year, SUM(box_office) AS total
FROM movies
GROUP BY genre
UNION ALL
-- 组合4：仅release_year分组
SELECT NULL AS studio, NULL AS genre, release_year, SUM(box_office) AS total
FROM movies
GROUP BY release_year;

4. 重复GROUPING SETS的特殊情况 #

如果重复写同一个GROUPING SETS，比如GROUP BY GROUPING SETS(studio), GROUPING SETS(studio)，等效于两次相同GROUP BY查询的UNION ALL，结果会包含重复的分组行（和原GROUPING SETS语句结果一致）：

SELECT studio, SUM(box_office) AS total
FROM movies
GROUP BY studio
UNION ALL
SELECT studio, SUM(box_office) AS total
FROM movies
GROUP BY studio;

而如果是重复非GROUPING SETS的元素，比如GROUP BY studio, studio，则和GROUP BY studio完全等价，不会改变分组结果。

内容的提问来源于stack exchange，提问作者David542