group by、grouping sets、rollup和cube方法详解

最新推荐文章于 2024-04-14 15:03:29 发布
最新推荐文章于 2024-04-14 15:03:29 发布
阅读量4.9k 收藏 24
点赞数 5
分类专栏： odps 文章标签： odps mysql grouping
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kangkangwanwan/article/details/109768648
版权

在平时的工作中，经常有按照不同维度筛选和统计数据的需求。拿视频会员订单数据来说吧，运营人员要查看深圳市的成功下单数或则深圳市某一种产品的成功下单数或者某一种产品的所有成功下单数时，每天的订单数又很大，现查的话按照不同的维度去查询又很慢。此时本篇文章或许会帮助到你。

  • group by：主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。
  • grouping sets：对分组集中指定的组表达式的每个子集执行group by，group by A,B grouping sets(A,B)就等价于 group by A union group by B,其中A和B也可以是一个集合，比如group by A,B,C grouping sets((A,B),(A,C))。
  • rollup：在指定表达式的每个层次级别创建分组集。group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。
  • cube:为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。
  • GROUPING_ID(column_name1,column_name2…)：GROUPING_ID ( [ ,...n]) 将 GROUPING () 在每个输出行中为其列列表中的每个列返回的对应值作为 0、1 字符串输入。GROUPING_ID 将该字符串解释为二进制数并返回对应的整数。

每个 GROUPING_ID 参数都必须是 GROUP BY 列表的一个元素。GROUPING_ID () 返回一个 integer 位图，其最低 N 位可能为文字。文字 bit 表明对应参数不是给定输出行的分组列。最低顺序 bit 对应于参数 N，第 N-1 个最低顺序 bit 对应于参数 1。

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/bb510624(v=sql.105)

数据库中会员订单的数据映射的对象如下：

insert into orderTempTable 
values ("深圳","钻石会员","钻石会员1个月",25), 
("深圳","钻石会员","钻石会员1个月",25), 
("深圳","钻石会员","钻石会员3个月",70), 
("深圳","钻石会员","钻石会员12个月",300), 
("深圳","铂金会员","铂金会员3个月",60), 
("深圳","铂金会员","铂金会员3个月",60), 
("深圳","铂金会员","铂金会员6个月",120), 
("深圳","黄金会员","黄金会员1个月",15), 
("深圳","黄金会员","黄金会员1个月",15), 
("深圳","黄金会员","黄金会员3个月",45), 
("深圳","黄金会员","黄金会员12个月",180), 
("北京","钻石会员","钻石会员1个月",25), 
("北京","钻石会员","钻石会员1个月",25), 
("北京","铂金会员","铂金会员3个月",60), 
("北京","黄金会员","黄金会员3个月",45), 
("上海","钻石会员","钻石会员1个月",25), 
("上海","钻石会员","钻石会员1个月",25), 
("上海","铂金会员","铂金会员3个月",60), 
("上海","黄金会员","黄金会员3个月",45)

接下来我们通过操作 orderTempTable 来看一下grouping sets、group by、rollup和cube具体如何使用。

1.group by

group by是SELECT语句的从句，用来指定查询分组条件，主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。使用group by从句时候，通过添加聚合函数（主要有COUNT()、SUM、MAX()、MIN()等）可以使数据聚合。

select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product

执行结果如下，可以看到结果中按照area,memberType,product来做聚合sum操作。

+-----+------------+----------+------+
|area |memberType |product |total |
+-----+------------+----------+------+
|深圳 |铂金会员 |铂金会员6个月 |120|
|深圳 |黄金会员 |黄金会员12个月|180|
|深圳 |钻石会员 |钻石会员3个月 |70 |
|深圳 |黄金会员 |黄金会员3个月 |45 |
|深圳 |钻石会员 |钻石会员12个月|300|
|北京 |黄金会员 |黄金会员3个月 |45 |
|深圳 |钻石会员 |钻石会员1个月 |50 |
|深圳 |黄金会员 |黄金会员1个月 |30 |
|深圳 |铂金会员 |铂金会员3个月 |120|
|北京 |钻石会员 |钻石会员1个月 |50 |
|北京 |铂金会员 |铂金会员3个月 |60 |
|上海 |黄金会员 |黄金会员3个月 |45 |
|上海 |钻石会员 |钻石会员1个月 |50 |
|上海 |铂金会员 |铂金会员3个月 |60 |
+----+----------+--------+---+

2.grouping sets

grouping sets是group by子句更进一步的扩展, 它让你能够定义多个数据分组。这样做使聚合更容易, 并且因此使得多维数据分析更容易。

使用grouping sets在同一查询中定义多个分组

select area,memberType,product,sum(price) as total 
from orderTempTable 
group by area,memberType,product 
grouping sets(area,memberType,product)

grouping set相关于多个group by 用union all 拼接起来

上面的语句输出结果如下，可以看到使用grouping sets(area,memberType,product)会分别对这3个维度进行group by，也可以grouping sets ((area,memberType),(area,product)))此时相当于group by (area,memberType) union group by (area,product),也就是说grouping sets 后面可以指定你想要的各种维度组合。

+----+----------+--------+-----+ 
|area|memberType|product |total| 
+----+----------+--------+-----+ 
|null|null |铂金会员3个月 |240 | 
|null|铂金会员 |null |360 | 
|上海 |null |null |155 | 
|null|钻石会员 |null |520 | 
|null|null |钻石会员12个月|300 | 
|null|null |黄金会员12个月|180 | 
|null|null |钻石会员3个月 |70 | 
|null|null |黄金会员3个月 |135 | 
|深圳 |null |null |915 | 
|null|null |钻石会员1个月 |150 | 
|null|null |黄金会员1个月 |30 | 
|null|黄金会员 |null |345 | 
|北京 |null |null |155 | 
|null|null |铂金会员6个月 |120 | 
+----+----------+--------+-----+

3.rollup

rollup 是根据维度在数据结果集中进行的聚合操作。

group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。

group by A,B,C with rollup等价于grouping sets((A,B,C),(A,B),(A),())

select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with rollup 
-- 或者 
select area,memberType,product,sum(price) as total from orderTempTable group by rollup(area,memberType,product)

输出结果中，可以group by A,B,C with rollup，的确是上述几种group by的并集。

+----+----------+--------+-----+ 
|area|memberType|product |total| 
+----+----------+--------+-----+ 
|上海 |null |null |155 | 
|北京 |铂金会员 |null |60 | 
|北京 |钻石会员 |null |50 | 
|上海 |钻石会员 |钻石会员1个月 |50 | 
|深圳 |黄金会员 |黄金会员1个月 |30 | 
|深圳 |钻石会员 |钻石会员12个月|300 | 
|北京 |黄金会员 |黄金会员3个月 |45 | 
|深圳 |钻石会员 |钻石会员3个月 |70 | 
|北京 |铂金会员 |铂金会员3个月 |60 | 
|上海 |铂金会员 |null |60 | 
|上海 |钻石会员 |null |50 | 
|深圳 |黄金会员 |null |255 | 
|深圳 |null |null |915 | 
|上海 |黄金会员 |黄金会员3个月 |45 | 
|深圳 |铂金会员 |铂金会员3个月 |120 | 
|深圳 |钻石会员 |钻石会员1个月 |50 | 
|上海 |铂金会员 |铂金会员3个月 |60 | 
|北京 |黄金会员 |null |45 | 
|深圳 |铂金会员 |null |240 | 
|null|null |null |1225 | 
|深圳 |钻石会员 |null |420 | 
|北京 |null |null |155 | 
|北京 |钻石会员 |钻石会员1个月 |50 | 
|深圳 |黄金会员 |黄金会员12个月|180 | 
|深圳 |铂金会员 |铂金会员6个月 |120 | 
|深圳 |黄金会员 |黄金会员3个月 |45 | 
|上海 |黄金会员 |null |45 | 
+----+----------+--------+-----+

4.cube

group by A,B,C with cube，则首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。

select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with cube 
-- 或者 
select area,memberType,product,sum(price) as total from orderTempTable group by cube(area,memberType,product)
+----+----------+--------+-----+ 
|area|memberType|product |total| 
+----+----------+--------+-----+ 
|深圳 |null |黄金会员12个月|180 | 
|深圳 |null |钻石会员3个月 |70 | 
|深圳 |null |黄金会员3个月 |45 | 
|null|null |铂金会员3个月 |240 | 
|北京 |null |铂金会员3个月 |60 | 
|null|铂金会员 |null |360 | 
|上海 |null |null |155 | 
|北京 |铂金会员 |null |60 | 
|null|钻石会员 |null |520 | 
|北京 |钻石会员 |null |50 | 
|上海 |钻石会员 |钻石会员1个月 |50 | 
|深圳 |黄金会员 |黄金会员1个月 |30 | 
|null|null |钻石会员12个月|300 | 
|深圳 |钻石会员 |钻石会员12个月|300 | 
|null|黄金会员 |黄金会员12个月|180 | 
|null|铂金会员 |铂金会员6个月 |120 | 
|null|黄金会员 |黄金会员3个月 |135 | 
|深圳 |null |钻石会员1个月 |50 | 
|深圳 |null |黄金会员1个月 |30 | 
|北京 |黄金会员 |黄金会员3个月 |45 | 
|null|null |黄金会员12个月|180 | 
|上海 |null |铂金会员3个月 |60 | 
|null|null |钻石会员3个月 |70 | 
|深圳 |钻石会员 |钻石会员3个月 |70 | 
|null|null |黄金会员3个月 |135 | 
|北京 |铂金会员 |铂金会员3个月 |60 | 
|北京 |null |黄金会员3个月 |45 | 
|上海 |铂金会员 |null |60 | 
|上海 |钻石会员 |null |50 | 
|深圳 |黄金会员 |null |255 | 
|null|黄金会员 |黄金会员1个月 |30 | 
|深圳 |null |null |915 | 
|null|钻石会员 |钻石会员12个月|300 | 
|上海 |黄金会员 |黄金会员3个月 |45 | 
|深圳 |铂金会员 |铂金会员3个月 |120 | 
|null|null |钻石会员1个月 |150 | 
|深圳 |钻石会员 |钻石会员1个月 |50 | 
|null|null |黄金会员1个月 |30 | 
|北京 |null |钻石会员1个月 |50 | 
|上海 |铂金会员 |铂金会员3个月 |60 | 
|上海 |null |黄金会员3个月 |45 | 
|null|钻石会员 |钻石会员3个月 |70 | 
|深圳 |null |铂金会员6个月 |120 | 
|null|黄金会员 |null |345 | 
|北京 |黄金会员 |null |45 | 
|深圳 |null |铂金会员3个月 |120 | 
|深圳 |铂金会员 |null |240 | 
|null|null |null |1225 | 
|深圳 |钻石会员 |null |420 | 
|北京 |null |null |155 | 
|null|铂金会员 |铂金会员3个月 |240 | 
|上海 |null |钻石会员1个月 |50 | 
|null|钻石会员 |钻石会员1个月 |150 | 
|深圳 |null |钻石会员12个月|300 | 
|北京 |钻石会员 |钻石会员1个月 |50 | 
|深圳 |黄金会员 |黄金会员12个月|180 | 
|深圳 |铂金会员 |铂金会员6个月 |120 | 
|深圳 |黄金会员 |黄金会员3个月 |45 | 
|null|null |铂金会员6个月 |120 | 
|上海 |黄金会员 |null |45 | 
+----+----------+--------+-----+

grouping的使用，当维度中有null值是，和汇聚结果的null值分不开，可以使用grouping()来处理：

create table temp_city as 
select 'beijing' city,100 as cnt 
union all 
select 'beijing' city,200 as cnt 
union all 
select 'shanghai' city,1000 as cnt 
union all 
select 'shanghai' city,2000 as cnt 
union all 
select null as city,10000 as cnt 
;
-------------
select IF(GROUPING(city)=0,city,'all') as city
    ,sum(cnt)as cnt 
from temp_city
group by cube(city)
;
-------结果------
city	cnt
\N	10000
all	12300
beijing	300
shanghai	2000

 

5.应用

经过group by、grouping sets、rollup和cube处理过后的数据可以直接存储到MySQL等数据库中。

参考链接：

https://www.jianshu.com/p/45cf609f5a61

https://www.cnblogs.com/springy/p/9375236.html

https://blog.csdn.net/BingoXing/article/details/81513891

https://my.oschina.net/u/2000675/blog/2050360

grouping_id : https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/bb510624(v=sql.105)

盛装吾步
关注
  • 5
    点赞
  • 踩
  • 24
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
group by grouping sets
Think different
11-15 931
<br />group by 后带grouping sets子句 效果就是只返回小记记录，即只返回按单个列分组后的统计数据，不返回多个列组合分组的统计数据。<br /> 例1：Group by grouping sets(A )<br /> 产生的分组种数：1 种；<br /> 第一种：group by A<br /> 返回结果集：即为以上一种分组的统计结果集。<br /><br /> 例2：Group by grouping sets(A ,B)<br /> 产生的分组种数：2 种；<br /
Oracle 分析，分组函数 group by 高级用法 grouping sets，rollup和cube区别
dch215810的专栏
09-05 1498
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)...
SparkSQL 中group by、grouping sets、rollup和cube方法详解
LFQ244117370的博客
08-17 2681
group by:主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。 grouping sets：对分组集中指定的组表达式的每个子集执行group by，group by A,B grouping sets(A,B)就等价于 group by A union group by B,其中A和B也可以是一个集合，比如group by A,B,C grouping sets((A,B),(A,C)),最后在对每个分组进行union rollup：在指定表达式的.
Hive：GROUP BY GROUPING SETS
最新发布
ALX3li的博客
04-14 212
聚合函数grouping sets
group by grouping sets的使用
xue_yanan的博客
10-12 4816
用来进行分组自定义汇总，可以来指定主要的总数组合。 group by grouping sets((list),(list),......),list是圆括号中的一个列序列，这个组个生成一个总数。要增加一个总和，必须增加一个null分组集。 举例：select * from te; ID   T_CODE  T_NAME    T_AMOUNT     T_DEPT      T_PROJ
分组函数之rollup、cube函数、grouping sets函数
Samooyou的博客
06-27 3626
rollup 通常和group by语句一起使用，是根据维度在分组的结果集中进行聚合操作（通常为汇总，这取决于SELECT后的聚合函数）。最常用的场景是：为每个分组返回一个小计，同时为所有分组返回总计。1、不带rollup的goup by ： Group by A ,B产生的分组种数：1种；返回结果集：也就是这一种分组的结果集。2、带rollup但group by与rollup之间没有任何内容：1）Group by rollup(A ,B) 产生的分组种数：3种；返回结果集：为以上三种分组统计结果集的并集且
Oracle group by 扩展函数详解（grouping sets、rollup、cube）
鱼丸丶粗面
09-05 5121
文章目录1 概述2 分组函数2.1 group by：一起分组（1）2.2 grouping sets：单独分组（N）2.3 rollup：累计累加（N + 1）2.4 cube：交叉列表（2^N）3 扩展列3.1 grouping：列是否为空3.2 grouping_id：非空列合计3.3 group_id：标识重复组 1 概述 1. 一般求和函数：聚合函数 | 列 + group by 列 (1) 聚合函数: count(1)、sum()、avg()、max()、min() 2. 高级分组函
SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE
09-11
然而，GROUP BY还可以与GROUPING、ROLLUP和CUBE运算符结合使用，以实现更复杂的多维度汇总功能。这些高级的汇总方法在数据分析和报表生成中非常有用，尤其是在处理大型数据集时。 GROUPING运算符主要用于识别汇总行...
GROUP BY子句（rollup,cube,grouping sets）实例说明
08-22
GROUP BY 子句有多种形式，本文将通过实例来说明 GROUP BY 子句的三种样式：GROUP BY、GROUP BY ROLLUP、GROUP BY CUBE、GROUP BY GROUPING SETS。 1. GROUP BY 语句 GROUP BY 语句是最基本的分组语句，它可以根据...
Oracle ROLLUP和CUBE 用法
03-16
Oracle数据库中的ROLLUP和CUBE是两种用于生成数据汇总的SQL操作，它们在数据分析和报告制作中非常有用。本文将详细介绍这两个概念以及如何在实际应用中使用它们。 首先，我们来理解`ROLLUP`。ROLLUP操作是GROUP BY...
rollup cube grouping sets的用法
01-26
rollup cube grouping sets的用法 详细，讲述与实例想结合
Spark Sql中的group by语句
weixin_46300771的博客
03-07 5910
每天都在和你在一起 Spark Group By函数将相同的数据收集到DataFrame/DataSet上的组，并对分组后的数据执行聚合函数。 count() 返回每个组的行数 mean() 返回每个组的平均值 max() 返回每个组的最大值 min() 返回每个组的最小值 sum() 返回每个组的值的总计 avg() 返回每个组的平均值 使用agg函数，可以一次进行多次聚合运算 创建DataFrame val data = Seq(("Ja...
[Hive学习]聚合增强函数解析：grouping sets、cube、rollup、grouping判断
alie_123的博客
12-13 516
本文介绍了hive聚合增强函数：grouping sets、cube、rollup、grouping判断的使用方法及注意细节。
大数据之hive：hive新功能之GROUPING SETS，Cube, Rollup
weixin_43597208的博客
09-05 514
目录一、GROUPING SETS1、概述2、实战二、Cube1、概述2、实战三、Rollup1、概述2、实战 一、GROUPING SETS 1、概述 GROUPING SETS作为GROUP BY的子句,可以简单理解为多条group by语句通过union all把查询结果聚合起来； 2、实战 查看表test_03字段 hive (default)> desc test_03; OK col_name data_type comment user_id bigint
通过GROUP BY grouping sets提升数据分组统计效率
热门推荐
做最好的自己
03-05 1万+
使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集 示例:(sql server 2008 R2) 创建测试数据库及表并插入测试数据 use master CREATE DATABASE db_sales go use db_sales go CREATE TABLE [dbo].[t
HIVE相关方法优化之聚合函数GROUP SETS
weixin_40809627的博客
07-05 3396
1、背景 在数据开发统计相关计算过程中，group by 和group sets 是我们经常用的方法。但当统计指标过程中如果维度特别多，会使得数据任务运行特别慢，导致任务延迟经常出现。针对上面问题对group sets 常用指标进行调研优化。 2、 ...
SQL 中 group By rollup 与 group By cube,grouping函数使用说明
嗅嗅匆匆的博客
05-05 2918
group By 语句不仅适用于Oracle ，其他数据库也同样适用，本文以SQLserver 数据库为例。 group By 语句除了最基本的语法外，还支持ROLLUP和CUBE语句。CUBE ROLLUP 是用于统计数据的。例如求数据的合计与小计。 grouping 函数使用 1. rollup 假设使用group by rollup(a,b)，首先会对(a,b)进行group by ...
group by grouping sets用法示例
networms的专栏
11-26 918
SELECT CASE             WHEN aab301 IS not NULL and akb020 is null THEN              小计             WHEN aab301 IS NULL THEN              合计             ELSE              ||aab301          END
Group By Grouping Sets
立业的博客
03-12 200
Group by分组函数的自定义，与group by配合使用可更加灵活的对结果集进行分组，Grouping sets会对各个层级进行汇总，然后将各个层级的汇总值union all在一起，但却比单纯的group by + union all 效率要高 1 创建数据 CREATE TABLE employee ( name NVARCHAR2(10), gender ...
mysql的WITH ROLLUP和oracle的GROUP BY GROUPING SETS 、
05-26
ROLLUP 和 CUBE 都是用于在聚合查询中生成汇总行的操作。虽然它们在语法上略有不同，但它们都可以完成相同的功能，即生成聚合结果的子总计和总计行。 WITH ROLLUP 是 MySQL 中的一个扩展，它允许将多个列的值进行分组，并在结果集中生成子总计和总计行。与普通的 GROUP BY 相比，WITH ROLLUP 会对每个分组列都生成一个子总计行，并在最后一行生成一个总计行。例如： ``` SELECT col1, col2, SUM(amount) FROM table GROUP BY col1, col2 WITH ROLLUP; ``` 这条语句会将 col1 和 col2 的值进行分组，并生成相应的子总计行和总计行。 GROUP BY GROUPING SETS 和 ROLLUP/CUBE 类似，也是用于生成聚合结果的子总计和总计行。但是它的语法更加灵活，可以指定多个不同的分组集合，并在结果集中生成相应的子总计和总计行。例如： ``` SELECT col1, col2, SUM(amount) FROM table GROUP BY GROUPING SETS ((col1), (col2), (col1, col2)); ``` 这条语句会生成三组不同的分组集合，并分别在结果集中生成相应的子总计行和总计行。 总的来说，WITH ROLLUP 和 GROUP BY GROUPING SETS 都是用于生成聚合结果的子总计和总计行，它们的语法略有不同，但在功能上是相似的。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值