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

1、group by、grouping sets、rollup和cube理论

• 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")

2、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	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

3、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

4、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	155
北京	北京	null	60
北京	北京	null	50
上海	上海	钻石会员1个月	50
深圳	深圳	黄金会员1个月	30
深圳	深圳	钻石会员12个月	300
北京	北京	黄金会员3个月	45
深圳	深圳	钻石会员3个月	70
北京	北京	铂金会员3个月	60
上海	上海	null	60
上海	上海	null	50
深圳	深圳	null	255
深圳	深圳	null	915
上海	上海	黄金会员3个月	45
深圳	深圳	铂金会员3个月	120
深圳	深圳	钻石会员1个月	50
上海	上海	铂金会员3个月	60
北京	北京	null	45
深圳	深圳	null	240
null	null	null	122
深圳	深圳	null	420
北京	北京	null	155
北京	北京	钻石会员1个月	50
深圳	深圳	黄金会员12个月	180
深圳	深圳	铂金会员6个月	120
深圳	深圳	黄金会员3个月	45
上海	上海	null	45

5、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个月	W120
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