告别繁琐查询：SQL多级分组，让数据分析更简单！

这是「活用数据」的第 19 篇原创文章

本文通过案例教学，深入浅出地展示了如何利用SQL中的GROUPING SETS和ROLLUP技术，实现多级分组聚合查询，优化数据分析流程，提升查询效率。

如果没有相应的数据库环境，可以使用SQL在线运行网站：SQL Fiddle进行相关练习验证。

引言

在数据分析中，SQL聚合函数是不可或缺的工具，它们帮助我们从大量数据中提取有意义的信息。然而，传统的GROUP BY子句在处理多级分组时可能会显得力不从心。

本文主要介绍如何通过GROUPING SETS 和ROLLUP 语句，使得我们可以在不同的分组级别上应用聚合函数，而无需编写多个查询，提高查询效率，并简化复杂的分析需求。

下面主要从常用的PostgreSQL和MySQL，通过一个简单的案例，分别介绍如何具体实现相应的SQL查询。

简单案例

以下是一个简单案例，包括创建表结构、插入数据和执行分组聚合查询的步骤。

创建表结构

首先，创建一个简单的销售表sales，包含销售ID、品类、子品类、销售金额。

CREATE TABLE sales (
    sale_id INT,
    category VARCHAR(255),
    sub_category VARCHAR(255),
    amount DECIMAL(10, 2)
);

插入数据

接下来，向sales表中插入一些示例数据。

INSERT INTO sales (sale_id, category, sub_category, amount) VALUES
(1, 'Electronics', 'Cameras', 250.00),
(2, 'Electronics', 'Laptops', 800.00),
(3, 'Clothing', 'Shoes', 100.00),
(4, 'Clothing', 'Hats', 50.00),
(5, 'Electronics', 'Smartphones', 700.00),
(6, 'Home', 'Furniture', 450.00);

查询数据

最后，查询一下我们插入的示例数据。

SELECT * FROM sales;

使用GROUPING SETS进行分组聚合查询

SELECT
    category,
    sub_category,
    SUM(amount) AS total_sales,
    GROUPING(category) AS category_grouped,
    GROUPING(sub_category) AS sub_category_grouped
FROM
    sales
GROUP BY GROUPING SETS (
    (category, sub_category),
    (category),
    ()
)
ORDER BY 
    category NULLS FIRST, 
    sub_category NULLS FIRST
;

这个查询将执行以下操作：

1. 计算所有记录的总销售金额（空的分组集合 ()）。
2. 按 category 分组计算每个类别的销售总和。
3. 按 category 和 sub_category 分组计算每个子类别在每个类别下的销售总和。
4. 按照汇总行、品类汇总行、子品类汇总行进行排序，也就是从最粗的汇总粒度到最细的粒度一层层向下展示。

GROUPING(category) 和 GROUPING(sub_category) 函数用于指示当前行是否为一个汇总行。如果 GROUPING 函数的结果是 0，则表示相应的列是非汇总的；如果是 1，则表示它是汇总的。

查询结果：

使用ROLLUP进行分组聚合查询

MySQL数据库不支持上面的GROUPING SETS写法，但是我们可以使用ROLLUP实现完全一样的效果：

SELECT
    category,
    sub_category,
    SUM(amount) AS total_sales
FROM
    sales
GROUP BY
    category, sub_category
WITH ROLLUP
ORDER BY 
    category,
    sub_category
;

在MySQL中，排序时默认情况下NULL值会被视为最小值，因此在使用ASC进行升序排序时，NULL值会排在最前面。

在PostgreSQL中同样支持ROLLUP语法，但是SQL应该改写如下：

SELECT
    category,
    sub_category,
    SUM(amount) AS total_sales
FROM
    sales
GROUP BY
    ROLLUP (category, sub_category)
ORDER BY 
    category NULLS FIRST, 
    sub_category NULLS FIRST
;

小结

1. 多级分组的挑战：传统的 GROUP BY 子句在处理多级分组时可能会不够灵活，需要编写多个查询来实现。
2. GROUPING SETS 的应用：在支持该功能的数据库系统中（如 PostgreSQL），可以使用 GROUPING SETS 来简化多级分组的查询，避免编写多个查询。
3. ROLLUP 的使用：对于不支持 GROUPING SETS 的数据库系统（如 MySQL），可以使用 ROLLUP 来实现类似的多级汇总效果。
4. GROUPING 函数：GROUPING 函数帮助我们识别汇总行，其中 GROUPING 函数的值为 0 表示非汇总行，为 1 表示汇总行。
5. 排序规则：在不同的数据库系统中，NULL 值的排序行为可能不同。在 MySQL 中，NULL 值默认在升序时排在前面，而在 PostgreSQL 中，可以使用 NULLS FIRST 或 NULLS LAST 明确指定 NULL 的排序位置。

通过这篇文章，读者应该能够理解多级分组聚合的概念，掌握在不同数据库系统中实现该功能的方法，并能够根据实际需求选择合适的 SQL 语句进行数据分析。