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

最新推荐文章于 2024-08-19 00:11:35 发布
最新推荐文章于 2024-08-19 00:11:35 发布
阅读量437 收藏
点赞数
文章标签： mysql grouping sets
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39649490/article/details/113224660
版权

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

数据库中会员订单的数据映射的对象如下：case class MemberOrderInfo(area:String,memberType:String,product:String,price:Int)

会员订单表中的数据如下：import sqlContext.implicits._

val orders=Seq(

MemberOrderInfo("深圳","钻石会员","钻石会员1个月",25),

MemberOrderInfo("深圳","钻石会员","钻石会员1个月",25),

MemberOrderInfo("深圳","钻石会员","钻石会员3个月",70),

MemberOrderInfo("深圳","钻石会员","钻石会员12个月",300),

MemberOrderInfo("深圳","铂金会员","铂金会员3个月",60),

MemberOrderInfo("深圳","铂金会员","铂金会员3个月",60),

MemberOrderInfo("深圳","铂金会员","铂金会员6个月",120),

MemberOrderInfo("深圳","黄金会员","黄金会员1个月",15),

MemberOrderInfo("深圳","黄金会员","黄金会员1个月",15),

MemberOrderInfo("深圳","黄金会员","黄金会员3个月",45),

MemberOrderInfo("深圳","黄金会员","黄金会员12个月",180),

MemberOrderInfo("北京","钻石会员","钻石会员1个月",25),

MemberOrderInfo("北京","钻石会员","钻石会员1个月",25),

MemberOrderInfo("北京","铂金会员","铂金会员3个月",60),

MemberOrderInfo("北京","黄金会员","黄金会员3个月",45),

MemberOrderInfo("上海","钻石会员","钻石会员1个月",25),

MemberOrderInfo("上海","钻石会员","钻石会员1个月",25),

MemberOrderInfo("上海","铂金会员","铂金会员3个月",60),

MemberOrderInfo("上海","黄金会员","黄金会员3个月",45)

)    //把seq转换成DataFrame

val memberDF:DataFrame =orders.toDF()    //把DataFrame注册成临时表

memberDF.registerTempTable("orderTempTable")

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

1.group by

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

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

b.够用grouping sets在同一查询中定义多个分组sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product grouping sets(area,memberType,product)")

上面的语句输出结果如下，可以看到使用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操作。sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with rollup")

输出结果中，可以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操作。sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with cube")+----+----------+--------+-----+|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   |

+----+----------+--------+-----+

5.应用

经过group by、grouping sets、rollup和cube处理过后的数据可以直接存储到MySQL等数据库中。文章开头中提到的需求，可以根据运营提出的筛选维度进行cube分析，就可以得到各种维度组合下的订单统计结果。比如要获取总的定单数。可以如下处理:val cubeDF = sqlContext.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with cube")

cubeDF .registerTempTable("resultTempTable")//下面SQL执行的结果就是所有订单的总数

sqlContext.sql("select * from resultTempTable where area=null and memberType=null and p

作者：腾飞的大象

链接：https://www.jianshu.com/p/45cf609f5a61

weixin_39649490
关注
hive 函数使用详解
congge_study的博客
02-12 7666
hive 函数使用详解
flink1.14 sql基础语法（一） flink sql表查询详解
segegefe的博客
09-09 1962
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
Oracle PL/SQL之GROUP BY GROUPING SETS
t0nsha's Blog
06-12 4127
使用GROUP BY GROUPING SETS相当于把需要GROUP的集合用UNION ALL联合起来。当GROUPING SETS里面的分组元素越多时，使用GROUPING SETS比使用UNION ALL性能更好，这可能和使用GROUPING SETS只需要访问一次表有关。如下两段查询的结果是相等的： q1（GROUPING SETS）：SELECT department_id, job
group by、grouping sets、rollup和cube方法详解
kw-home
11-18 6101
在平时的工作中，经常有按照不同维度筛选和统计数据的需求。拿视频会员订单数据来说吧，运营人员要查看深圳市的成功下单数或则深圳市某一种产品的成功下单数或者某一种产品的所有成功下单数时，每天的订单数又很大，现查的话按照不同的维度去查询又很慢。此时本篇文章或许会帮助到你。 group by：主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。 grouping sets：对分组集中指定的组表达式的每个子集执行group by，group by A,B grouping s
MySQL数据库入门与实践(八)：窗口函数与 grouping运算符
最新发布
qq_47370197的博客
08-19 1063
这一小节介绍了窗口函数，主要是 avg,sum 一类的聚合函数以及rank,dense_rank,row number三个专用窗口函数，注意求平均值中的滑动窗口求平均值，采用 rows between and 的形式。以及grouping 函数中的 with rollup 实现多层求和，grouping 对求和带来的 null 数据进行重命名格式。
SparkSQL 中group by、grouping sets、rollup和cube方法的详解
xdx_dili的博客
09-27 1400
SparkSQL 中group by、grouping sets、rollup和cube方法的详解
GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例
还不算晕的专栏
10-31 2172
oracle group by中rollup和cube的区别： Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。CUBE ROLLUP 是用于统计数据的。 使用SCOTT用户的EMP表测试 1.仅使用GROUP BY分组，GROUP BY后的单列可以用括号，也可以不用。以下两种写法作用一样： SCOTT@bys1>SELECT deptno, avg
group by grouping sets
Think different
11-15 975
<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 /
MySQL、HQL、impala、presto 的语法常见区别（持续更新中）
LdyLly的博客
11-24 6206
工作中会用到hive和presto，但语法和MySQL存在差异。及时记录，持续更新
SQL面试常见函数
话数Science
10-01 324
[1,2,3]1,2,3
SQLAlchemy完全入门
热门推荐
虚幻私塾
01-10 1万+
Python微信订餐小程序课程视频 https://edu.csdn.net/course/detail/36074 Python实战量化交易理财系统 https://edu.csdn.net/course/detail/35475 最近想要学习SQLAlchemy, 发现网上的中文文档大多是机翻的, 读起来特别变扭, 因此对照着最新的英文文档梳理了一遍, 写下来记录一下 目前SQLAlchemy的版本为1.4.x, 风格处于1.x过渡到2.0的时代. 为了尽量让这篇文章的兼容之后的版本, 本文将讲述1.
GROUP BY子句（rollup,cube,grouping sets）实例说明
08-22
GROUP BY子句（rollup,cube,grouping sets）实例说明
hive分组拓展函数（grouping sets、with cube、rollup）
qq_41725214的博客
07-04 365
（可以自定义，比如A,B,C，可以自己定义(A),(A,C),(B,C),(A,B,C)等等）
GROUP BY GROUPING SETS((),
pxcong007的专栏
09-19 212
通过grouping sets 设置多个分组，网上看到的一个例子： SQL> SELECT CASE 2 WHEN a.deptno IS NULL THEN 3 '合计' 4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 5 '...
Oracle group by 扩展函数详解（grouping sets、rollup、cube）
鱼丸丶粗面
09-05 5530
文章目录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. 高级分组函
sparksql中grouping sets的使用方法
weixin_43597208的博客
02-28 1360
关于grouping sets的使用，通俗的说，grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法，等价与多个group by 的语句union all 的结果。注意事项， grouping__id，spark sql分组id是纬度被选中则为0，没被选中则为1，而hive则刚好相反，选中为1，没被选中为0。上述sql,我不关心grouping__id，我的需求是有些维度取不到时，用ALL代替，如何处理？
分组函数之rollup、cube函数、grouping sets函数
Samooyou的博客
06-27 4664
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种；返回结果集：为以上三种分组统计结果集的并集且
SQL中with cube,with rollup和grouping set 高阶聚合函数的理解和使用
weixin_45896475的博客
02-04 4883
数据 name course score 小红,sql,85 小红,c#,90 小明,sql,93 小明,c#,82 小李,sql,88 小李,c#,0 建表 create table s( name string, course string, score int )row format delimited fields terminated by ','; load data local i...
高级分组函数 之ROLLUP，CUBE，GROUPING SETS
qq_39499221的博客
10-09 415
ROLLUP，CUBE，GROUPING SETS 函数可以理解为Group By 分组函数封装后的精简用法，相当于多个union all 的组合显示效果，但是要比 多个 union all 的效率要高。 1.group by 函数适用场景：           1） 聚合函数，比如 count ,sum,avg等等。           2）筛选只能 having，不能是 where。 ...
mysql的WITH ROLLUP和oracle的GROUP BY GROUPING SETS 、
05-26
GROUP BY GROUPING SETS 和 ROLLUP/CUBE 类似，也是用于生成聚合结果的子总计和总计行。但是它的语法更加灵活，可以指定多个不同的分组集合，并在结果集中生成相应的子总计和总计行。例如： ``` SELECT col1, col2,...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值