SQL GROUPING运算符详解

于 2024-07-17 12:00:00 发布
阅读量959 收藏 15
点赞数 22
分类专栏： # 有关SQL 文章标签： sql 数据库 大数据 数据仓库 职场发展
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012955829/article/details/140479852
版权

在大数据开发中,我们经常需要对数据进行分组和汇总分析。

image.png

目录

SQL提供了强大的GROUP BY子句来实现这一功能,而GROUPING运算符则是GROUP BY的一个重要补充。

本文将详细介绍GROUPING运算符的概念、用法以及实际应用场景。

1. GROUPING运算符概念

GROUPING运算符是用于区分普通分组行和超级聚合行(super-aggregate rows)的一个函数。它通常与ROLLUP、CUBE或GROUPING SETS子句一起使用,这些子句会生成额外的汇总行。

GROUPING运算符的作用是:

  • 当应用于一个列时,如果该行是由于该列而产生的分组操作,则返回0。
  • 如果该行是一个超级聚合行(即不是由该列产生的分组),则返回1。

这个特性使得我们能够轻松区分不同级别的汇总数据,从而进行更复杂的数据分析。

2. 语法和用法

GROUPING运算符的基本语法如下:

SELECT 
    column1, 
    column2, 
    GROUPING(column1) AS group_indicator,
    aggregate_function(column3)
FROM 
    table_name
GROUP BY 
    ROLLUP(column1, column2);

在这个例子中:

  • GROUPING(column1) 将返回0或1,表示该行是否是column1的分组结果。
  • ROLLUP(column1, column2) 会生成多级汇总,包括(column1, column2)、(column1)和总计行。

3. 实际应用示例

让我们通过一个具体的例子来说明GROUPING运算符的使用。

假设我们有一个销售数据表sales,包含以下字段:年份(year)、季度(quarter)、产品(product)和销售额(amount)。

CREATE TABLE sales (
    year INT,
    quarter INT,
    product VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
(2023, 1, 'ProductA', 1000),
(2023, 1, 'ProductB', 1500),
(2023, 2, 'ProductA', 1200),
(2023, 2, 'ProductB', 1800),
(2024, 1, 'ProductA', 1100),
(2024, 1, 'ProductB', 1600);

现在,我们想要得到按年份和季度的销售总额,同时包括年度总计和全部总计。我们可以使用GROUPING运算符来区分不同级别的汇总:

SELECT 
    year,
    quarter,
    SUM(amount) AS total_sales,
    GROUPING(year) AS year_grouping,
    GROUPING(quarter) AS quarter_grouping
FROM 
    sales
GROUP BY 
    ROLLUP(year, quarter)
ORDER BY 
    year, quarter;

这个查询的结果可能如下:

year   | quarter | total_sales | year_grouping | quarter_grouping
-------|---------|-------------|---------------|------------------
2023   | 1       | 2500.00     | 0             | 0
2023   | 2       | 3000.00     | 0             | 0
2023   | NULL    | 5500.00     | 0             | 1
2024   | 1       | 2700.00     | 0             | 0
2024   | NULL    | 2700.00     | 0             | 1
NULL   | NULL    | 8200.00     | 1             | 1

在这个结果中:

  • year_grouping为0,quarter_grouping为0的行表示具体的年份和季度数据。
  • year_grouping为0,quarter_grouping为1的行表示年度总计。
  • year_grouping为1,quarter_grouping为1的行表示全部总计。

4. GROUPING运算符的优势

image.png

  1. 数据区分: 通过GROUPING运算符,我们可以轻松区分普通分组数据和不同级别的汇总数据。

  2. 灵活的报表生成: 结合CASE语句,我们可以为不同级别的汇总添加描述性标签,使报表更易读。

  3. 性能优化: 相比于使用UNION ALL来生成多个级别的汇总,使用GROUPING SETS或ROLLUP配合GROUPING运算符通常能获得更好的性能。

  4. 复杂分析支持: 在数据仓库和OLAP应用中,GROUPING运算符为复杂的多维分析提供了强大支持。

5. 高级应用场景

image.png

5.1 与CASE语句结合使用

GROUPING运算符经常与CASE语句结合使用，以创建更具可读性的报表。例如：

SELECT 
    CASE 
        WHEN GROUPING(year) = 1 THEN 'All Years'
        ELSE CAST(year AS VARCHAR)
    END AS year,
    CASE 
        WHEN GROUPING(quarter) = 1 THEN 'All Quarters'
        ELSE CAST(quarter AS VARCHAR)
    END AS quarter,
    SUM(amount) AS total_sales
FROM 
    sales
GROUP BY 
    ROLLUP(year, quarter)
ORDER BY 
    year, quarter;

这个查询会将NULL值替换为更有意义的标签，使报表更易于理解。

image.png

5.2 多维数据分析

在数据仓库环境中，GROUPING运算符可以用于复杂的多维数据分析。例如，我们可以同时按年份、季度和产品进行分组：

SELECT 
    CASE WHEN GROUPING(year) = 1 THEN 'All Years' ELSE CAST(year AS VARCHAR) END AS year,
    CASE WHEN GROUPING(quarter) = 1 THEN 'All Quarters' ELSE CAST(quarter AS VARCHAR) END AS quarter,
    CASE WHEN GROUPING(product) = 1 THEN 'All Products' ELSE product END AS product,
    SUM(amount) AS total_sales,
    GROUPING(year) + GROUPING(quarter) + GROUPING(product) AS grouping_level
FROM 
    sales
GROUP BY 
    CUBE(year, quarter, product)
ORDER BY 
    grouping_level, year, quarter, product;

image.png

这个查询使用CUBE而不是ROLLUP，它会生成所有可能的组合。grouping_level列显示了汇总的层级，0表示最详细的级别，3表示总计。

6. 性能考虑和优化技巧

image.png

虽然GROUPING运算符功能强大，但在处理大量数据时也需要注意性能问题。以下是一些优化建议：

  1. 索引优化：确保在用于分组的列上建立适当的索引。

  2. 分区表：对于大型表，考虑使用分区来提高查询性能。

  3. 物化视图：对于经常运行的复杂分组查询，可以考虑创建物化视图。

  4. 并行查询：在支持的数据库系统中，利用并行查询功能来加速大规模数据的分组操作。

7. GROUPING运算符的局限性

尽管GROUPING运算符非常有用，但也存在一些局限性：

  1. 可读性：复杂的GROUPING查询可能难以阅读和维护。

  2. 特定语法：不同的数据库系统可能有略微不同的语法实现。

  3. 性能开销：在某些情况下，使用GROUPING可能导致额外的性能开销。

8. 最佳实践

image.png

  1. 适度使用：不要过度使用GROUPING和复杂的ROLLUP/CUBE操作，以免影响查询的可读性和性能。

  2. 文档化：对于复杂的GROUPING查询，提供清晰的注释和文档。

  3. 测试：在大数据集上充分测试GROUPING查询的性能。

  4. 替代方案：考虑是否可以使用其他方法（如多个简单查询加UNION ALL）来实现相同的结果。

GROUPING运算符是SQL中进行高级数据分析和报表生成的强大工具。通过本文的深入探讨，我们不仅了解了它的基本用法，还探索了高级应用场景、性能优化技巧以及最佳实践。

在大数据开发中，熟练运用GROUPING运算符可以大大提高数据分析的效率和灵活性。

然而，也要注意在实际应用中权衡其优缺点，选择最适合特定场景的解决方案。

9. GROUPING与其他高级SQL功能的结合

GROUPING运算符的强大之处不仅在于它本身，还在于它能与其他高级SQL功能结合使用，从而实现更复杂的数据分析任务。

9.1 GROUPING与窗口函数

窗口函数是另一个强大的SQL分析工具。当与GROUPING结合使用时，我们可以在不同的聚合级别上执行窗口计算。例如：

WITH grouped_sales AS (
    SELECT 
        CASE WHEN GROUPING(year) = 1 THEN 'All Years' ELSE CAST(year AS VARCHAR) END AS year,
        CASE WHEN GROUPING(quarter) = 1 THEN 'All Quarters' ELSE CAST(quarter AS VARCHAR) END AS quarter,
        SUM(amount) AS total_sales,
        GROUPING(year) + GROUPING(quarter) AS grouping_level
    FROM 
        sales
    GROUP BY 
        ROLLUP(year, quarter)
)
SELECT 
    *,
    SUM(total_sales) OVER (ORDER BY grouping_level, year, quarter) AS cumulative_sales,
    total_sales / SUM(total_sales) OVER () * 100 AS percentage_of_total
FROM 
    grouped_sales
ORDER BY 
    grouping_level, year, quarter;

这个查询不仅使用了GROUPING来创建多级汇总，还使用窗口函数计算了累计销售额和总销售额的百分比。

9.2 GROUPING与递归CTE

在某些复杂的分析场景中，我们可能需要将GROUPING与递归公共表表达式（Recursive CTE）结合使用。例如，假设我们有一个包含产品层次结构的表：

CREATE TABLE product_hierarchy (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    parent_id INT
);

INSERT INTO product_hierarchy VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Smartphones', 1);

-- 假设sales表新增了product_id列
ALTER TABLE sales ADD COLUMN product_id INT;
-- 更新sales表的数据
UPDATE sales SET product_id = 
    CASE 
        WHEN product = 'ProductA' THEN 3  -- Laptops
        WHEN product = 'ProductB' THEN 5  -- Smartphones
    END;

现在，我们可以使用递归CTE和GROUPING来创建一个包含层次结构的销售报表：

WITH RECURSIVE product_tree AS (
    SELECT product_id, product_name, parent_id, 0 AS level
    FROM product_hierarchy
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.product_id, c.product_name, c.parent_id, p.level + 1
    FROM product_hierarchy c
    JOIN product_tree p ON c.parent_id = p.product_id
),
sales_with_hierarchy AS (
    SELECT 
        pt.product_id,
        pt.product_name,
        pt.level,
        s.year,
        SUM(s.amount) AS total_sales
    FROM 
        sales s
    JOIN 
        product_tree pt ON s.product_id = pt.product_id
    GROUP BY 
        ROLLUP(pt.product_id, pt.product_name, pt.level, s.year)
)
SELECT 
    CASE WHEN GROUPING(product_id) = 1 THEN 'All Products' ELSE product_name END AS product,
    CASE WHEN GROUPING(year) = 1 THEN 'All Years' ELSE CAST(year AS VARCHAR) END AS year,
    total_sales,
    GROUPING(product_id) AS product_grouping,
    GROUPING(year) AS year_grouping,
    level
FROM 
    sales_with_hierarchy
ORDER BY 
    level, product_grouping, year_grouping, product, year;

这个复杂的查询结合了递归CTE、GROUPING和ROLLUP，生成了一个包含产品层次结构的多级销售报表。

10. 实际应用案例分析

10.1 电子商务销售分析

假设我们是一个大型电子商务平台的数据分析师，需要生成一个综合销售报表。这个报表需要按照不同的维度（年份、季度、产品类别、销售渠道）进行多级汇总。

首先，让我们创建一个更复杂的销售数据表：

CREATE TABLE ecommerce_sales (
    sale_id INT PRIMARY KEY,
    year INT,
    quarter INT,
    product_category VARCHAR(50),
    channel VARCHAR(20),
    amount DECIMAL(10, 2)
);

INSERT INTO ecommerce_sales VALUES
(1, 2023, 1, 'Electronics', 'Online', 1000),
(2, 2023, 1, 'Electronics', 'In-store', 800),
(3, 2023, 1, 'Clothing', 'Online', 500),
(4, 2023, 2, 'Electronics', 'Online', 1200),
(5, 2023, 2, 'Clothing', 'In-store', 600),
(6, 2024, 1, 'Electronics', 'Online', 1100),
(7, 2024, 1, 'Clothing', 'Online', 700);

现在，我们可以使用GROUPING运算符来创建一个全面的销售分析报表：

SELECT 
    CASE 
        WHEN GROUPING(year) = 1 THEN 'All Years'
        ELSE CAST(year AS VARCHAR)
    END AS year,
    CASE 
        WHEN GROUPING(quarter) = 1 THEN 'All Quarters'
        ELSE CAST(quarter AS VARCHAR)
    END AS quarter,
    CASE 
        WHEN GROUPING(product_category) = 1 THEN 'All Categories'
        ELSE product_category
    END AS category,
    CASE 
        WHEN GROUPING(channel) = 1 THEN 'All Channels'
        ELSE channel
    END AS channel,
    SUM(amount) AS total_sales,
    COUNT(*) AS transaction_count,
    AVG(amount) AS avg_transaction_value,
    GROUPING(year) + GROUPING(quarter) + GROUPING(product_category) + GROUPING(channel) AS grouping_level
FROM 
    ecommerce_sales
GROUP BY 
    CUBE(year, quarter, product_category, channel)
ORDER BY 
    grouping_level, year, quarter, product_category, channel;

这个查询提供了一个全面的销售概览，包括：

  • 总销售额
  • 交易次数
  • 平均交易值
  • 按年、季度、产品类别和销售渠道的各种组合的汇总

通过这个报表，我们可以快速识别销售趋势，比较不同产品类别和销售渠道的表现，并进行时间序列分析。

10.2 性能优化实践

在处理大规模数据时，上述查询可能会面临性能挑战。以下是一些优化建议：

  1. 索引优化：在year、quarter、product_category和channel列上创建适当的索引。

  2. 分区表：如果数据量非常大，可以考虑按年份或季度对表进行分区。

  3. 物化视图：对于经常运行的复杂GROUPING查询，可以创建物化视图：

CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    year,
    quarter,
    product_category,
    channel,
    SUM(amount) AS total_sales,
    COUNT(*) AS transaction_count,
    AVG(amount) AS avg_transaction_value
FROM 
    ecommerce_sales
GROUP BY 
    CUBE(year, quarter, product_category, channel);
  1. 并行查询：在支持的数据库系统中，确保复杂的GROUPING查询能够利用并行执行计划。

结论

通过这篇深入的技术博客，我们不仅探讨了SQL GROUPING运算符的基本概念和用法，还深入研究了它与其他高级SQL功能的结合应用，以及在实际业务场景中的应用和优化策略。

GROUPING运算符作为一个强大的数据分析工具，能够帮助数据分析师和开发者更高效地处理多维数据聚合和报表生成任务。

然而，需要注意的是，随着查询复杂度的增加，可能会对性能产生影响。因此，在实际应用中，需要权衡查询的复杂性和性能需求，选择最适合特定场景的解决方案。通过合理使用索引、分区、物化视图等优化技术，我们可以在保证查询灵活性的同时，也能获得良好的性能表现。

希望这篇全面的技术博客能为您在大数据开发和数据分析工作中提供有价值的参考和指导。无论是日常的报表生成还是复杂的数据挖掘任务，掌握GROUPING运算符及其相关技术都将大大提升您的数据处理能力。

数据小羊
关注
专栏目录
SQL中Group By的使用
10-02 6003
转：http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html#m5 1、概述 “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 2、原始表 3、简单Group By ...
SQL：GROUPING运算符
ISWW
04-22 350
一、ROLLUP同时得出合计和小计 二、GROUPING函数 三、CUBE 四、GROUPING SETS
SQL grouping
前端精读周刊
04-06 2978
SQL grouping 解决 OLAP 场景总计与小计问题，其语法分为几类，但要解决的是同一个问题：ROLLUP 与 CUBE 是封装了规则的 GROUPING SETS，而 GROUPING SETS 则是最原始的规则。为了方便理解，让我们从一个问题入手，层层递进吧。底表以上是示例底表，共有 8 条数据，城市1、城市2 两个城市，下面各有地区1～4，每条数据都有该数据...
sql:grouping运算符
热门推荐
qq_41805514的博客
08-17 1万+
只使用group by子句和聚合函数是无法同时得出小计和合计的，想要同时得到，可以使用grouping运算符。 同时得到合计行 使用group by 只能得到每组的合计值，不能得到总计： select product_type, sum(sale_price) from Product group by product_type; 通常计算合计是分别计算出合计行和按照商品种类进行汇总...
Oracle PL/SQL之GROUPING 函数
t0nsha's Blog
06-11 2108
在使用ROLLUP或CUBE进行GROUP操作时会产生内容为空的字段，即值为NULL的字段，那我们如何区分这些显示为空的字段到底是该字段本身值就是NULL还是由ROLLUP和CUBE生成的呢？这时可以用GROUPING(groupingfield) 来解决，如果列中的值来自事实数据，则 GROUPING 函数返回 0；如果列中的值是ROLLUP或CUBE操作所生成的 NULL，则返回 1。 eg：
SQL指令之----GROUP BY
wangping01的专栏
09-24 421
如果我们的需求变成是要算出每一间店 (store_name) 的营业额 (sales)，那怎么办呢？在这个情况下，我们要做到两件事：第一，我们对于 store_name 及 Sales 这两个栏位都要选出。第二，我们需要确认所有的 sales 都要依照各个 store_name 来分开算。这个语法为：SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY
SQL知识之GROUPING运算符
恋恋风尘的博客
04-11 718
1、GROUPING运算符 GROUPING运算符主要包括 GROUPING SET()、CUBE、ROLLUP 2、GROUPING SET() 假设有如下student表： +-----+-------+------+---------------------+-------+------+ | sno | sname | ssex | sbirthday | clas...
【spark床头书系列】Spark SQL示例用法所有函数示例权威详解二【建议收藏】
最新发布
wang2leee的博客
11-26 1008
Spark SQL示例用法所有函数示例权威详解二
Microsoft SQL Server 2008技术内幕：T-SQL查询(第二卷)
10-22
1.3 逻辑查询处理阶段详解 1.3.1 步骤1：FROM阶段 1.3.2 步骤2：WHERE阶段 1.3.3 步骤3：GROUP BY阶段 1.3.4 步骤4：HAVING阶段 1.3.5 步骤5：SELECT阶段 1.3.6 步骤6：排序用的ORDER BY阶段 1.4 逻辑查询...
SQLServer2008技术内幕T-SQL查询包含源代码及附录A
10-27
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容，以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
Microsoft+SQL+Server+2008技术内幕：T-SQL查询_源代码及附录 中文版
02-21
1.3 逻辑查询处理阶段详解5 1.3.1 步骤1：FROM阶段5 1.3.2 步骤2：WHERE阶段9 1.3.3 步骤3：GROUP BY阶段10 1.3.4 步骤4：HAVING阶段11 1.3.5 步骤5：SELECT阶段12 1.3.6 步骤6：排序用的ORDER BY阶段13 1.4 ...
sql 中的grouping函数
weixin_30364147的博客
12-08 190
代码declare@Temptable(Codevarchar(10),IDint,SIntint)insertinto@Temp(Code,ID,SInt)select'01',1,3unionselect'01',2,3unionselect'01',2,3unionselect'02',1,3unionselect'02',3,5unionselect'02...
Grouping 运算符
Good_StudyDaydayUp的博客
10-11 189
Grouping 运算符 需要得到小计值和合计值时 这样的操作麻烦,而且开销大 因此引入了Grouping 运算符 ①Rollup ②Cube ③Grouping Sets Rollup: Rollup 就是一次计算出不同聚合键的组合结果 比较下面使用Rollup和不使用时结果的区别 多了五行结果 一个是总合计值,4个是每种类型的小计值 说明Rollup 即归纳的意思,逐层向上归纳 让Nu...
grouping运算符
weixin_44100044的博客
02-24 357
使用group by子句和聚合函数无法得出合计值，引入grouping运算符即可解决。 参考https://www.jb51.net/article/91232.htm，数据源同 参考https://www.iteye.com/blog/xls-1096711，解释更齐全 grouping运算符分为如下三种： （1）rollup 对单一列进行聚合计算 select area,sum(personincome)from earnings group by rollup(area) 对多列进行聚合计算 s
SQL—解决多维度随机组合查询场景：grouping sets函数
weixin_46010244的博客
08-02 1940
通过grouping sets函数，极大提高我们的开发效率，即使有更多维度组合的增加，只需要在grouping sets函数中新增即可，当然案例中姓名不具有实质性分组聚合意义，数据的展示问题需要做进一步处理，但相较于大量的union，grouping sets函数已经可以极大的对代码进行调优。
SQL基础之GROUPING
weixin_30896825的博客
08-29 203
1.grouping sets 　　记得前几天第一次接触grouping sets时，笔者的感觉是一脸懵逼。 　　后来一不小心看到msdn上对grouping sets的说明，顿时豁然开朗，其实grouping sets就是由多个group by联合起来，关系如下。 　　select A , B from table group by grouping sets(A, B) 等价于 ...
8.2 GROUPING运算符
weixin_49593047的博客
06-30 67
与ROLL UP或者CUBE能够得到规定的(业务上成为“固定的”)结果相对，GROUPING SETS用于从中取出个别条件对应的不固定的结果。如果希望从中选取出“商品种类”和“登记日期”各自作为聚合键的结果，反之，不想得到“合计记录和使用2个聚合键的记录”时，可以使用GROUPING SETS。这样一来，为了得到想要的结果，需要执行2次几乎相同的 SELECT语句，再将其结果进行连接，十分繁琐！不知道有没有发现，上面的数据中有一部分，见下图，看快了很容易看错，比如把4000看作分组求合的值。
sql grouping sets
09-01
SQL GROUPING SETS 是一种用于在单个查询中执行多个聚合操作的扩展功能。它允许您在一个查询中同时进行多个聚合计算，并将结果以不同的组合方式呈现。 GROUPING SETS 可以用于在一个查询中计算多个聚合值，而不需要使用 UNION ALL 连接多个独立的子查询。通过使用 GROUPING SETS，您可以在一个查询中执行多个 GROUP BY 操作，并返回单个结果集。 以下是 GROUPING SETS 的用法示例： SELECT column1, column2, SUM(column3) AS sum_column3 FROM table GROUP BY GROUPING SETS ((column1), (column2), (column1, column2)); 在上面的示例中，我们对 column1 和 column2 进行了 GROUP BY，同时计算了 column3 的总和。最终的结果集将包含按 column1 分组、按 column2 分组以及按 column1 和 column2 同时分组的各个结果。 GROUPING SETS 还支持 CUBE 和 ROLLUP 子句，这些子句提供了更高级的聚合功能。CUBE 子句生成所有可能的组合，而 ROLLUP 子句生成按层次递减的子总体。这些子句可以与 GROUPING SETS 一起使用，以更灵活地生成聚合结果。 希望以上信息能够帮助您理解 SQL GROUPING SETS 的概念和用法。如果您有任何进一步的问题，请随时提问。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

数据小羊

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值