概念简述
- group by:主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。
- grouping sets：对分组集中指定的组表达式的每个子集执行group by；Eg: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操作(没有分组条件，所有的分组字段均为null的情况下)，最后将结果进行union。
- cube:为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作（没有分组条件，所有的分组字段均为null的情况下)，最后将结果进行union。
测试数据准备
package cn.ted.dmp.bank.product.sms_freq
import org.apache.spark.SparkContext
import org.apache.spark.sql.{DataFrame, SparkSession}
/**
* Author: LiYahui
* Date: Created in 2019/4/2 13:13
* Description: TODO spark的多维分析
* Version: V1.0
*/
object CubeDemo {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession.builder()
.appName(s"${this.getClass.getSimpleName}")
.master("local[*]")
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
.getOrCreate()
val sc: SparkContext = spark.sparkContext
sc.setLogLevel("WARN")
//测试数据
val honorSeq = Seq(
MemberInfo("战争学院区", "王者", "王者1连胜", 25),
MemberInfo("战争学院区", "王者", "王者1连胜", 25),
MemberInfo("战争学院区", "王者", "王者3连胜", 70),
MemberInfo("战争学院区", "王者", "王者12连胜", 300),
MemberInfo("战争学院区", "大师", "大师3连胜", 60),
MemberInfo("战争学院区", "大师", "大师3连胜", 60),
MemberInfo("战争学院区", "大师", "大师6连胜", 120),
MemberInfo("战争学院区", "黄金", "黄金1连胜", 15),
MemberInfo("战争学院区", "黄金", "黄金1连胜", 15),
MemberInfo("战争学院区", "黄金", "黄金3连胜", 45),
MemberInfo("战争学院区", "黄金", "黄金12连胜", 180),
MemberInfo("裁决之地区", "王者", "王者1连胜", 25),
MemberInfo("裁决之地区", "王者", "王者1连胜", 25),
MemberInfo("裁决之地区", "大师", "大师3连胜", 60),
MemberInfo("裁决之地区", "黄金", "黄金3连胜", 45),
MemberInfo("诺克萨斯区", "王者", "王者1连胜", 25),
MemberInfo("诺克萨斯区", "王者", "王者1连胜", 25),
MemberInfo("诺克萨斯区", "大师", "大师3连胜", 60),
MemberInfo("诺克萨斯区", "黄金", "黄金3连胜", 45)
)
import spark.implicits._
val honorDF: DataFrame = honorSeq.toDF()
honorDF.createOrReplaceTempView("temp")
spark.stop()
sc.stop()
}
case class MemberInfo(area: String, grade: String, honor: String, value: Int)
}
维度分析
group by
- group by是SELECT语句的从句，用来指定查询分组条件，主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。使用group by从句时候，通过添加聚合函数（主要有COUNT()、SUM、MAX()、MIN()等）可以使数据聚合.
代码:
//group by sum sql风格
val groupByHonorDF: DataFrame = spark.sql("select area,grade,honor,sum(value) as total_value from temp group by area,grade,honor")
groupByHonorDF.show()
//DSL风格
import org.apache.spark.sql.functions._
val groupByHonorDFDSL: DataFrame = honorDF.groupBy("area", "grade", "honor")
.agg(sum("value").alias("total_value"))
groupByHonorDFDSL.show()
结果展示：
+-----+-----+------+-----------+
| area|grade| honor|total_value|
+-----+-----+------+-----------+
|诺克萨斯区| 黄金| 黄金3连胜| 45|
|战争学院区| 王者|王者12连胜| 300|
|裁决之地区| 王者| 王者1连胜| 50|
|战争学院区| 黄金|黄金12连胜| 180|
|诺克萨斯区| 大师| 大师3连胜| 60|
|战争学院区| 大师| 大师3连胜| 120|
|诺克萨斯区| 王者| 王者1连胜| 50|
|战争学院区| 王者| 王者3连胜| 70|
|裁决之地区| 黄金| 黄金3连胜| 45|
|战争学院区| 黄金| 黄金3连胜| 45|
|战争学院区| 黄金| 黄金1连胜| 30|
|战争学院区| 大师| 大师6连胜| 120|
|裁决之地区| 大师| 大师3连胜| 60|
|战争学院区| 王者| 王者1连胜| 50|
+-----+-----+------+-----------+
grouping sets
- a.grouping sets是group by子句更进一步的扩展, 它让你能够定义多个数据分组。这样做使聚合更容易, 并且因此使得多维数据分析更容易。
- b.够用grouping sets在同一查询中定义多个分组;
eg：group by A,B grouping sets(A,B)就等价于 group by A union group by B；
group by A,B,C grouping sets((A,C),(A,B))就等价于 group by A,C union group by A,B;
代码：
//grouping sets
val groupingHonorDF: DataFrame = spark.sql("select area,grade,honor,sum(value) as total_value from temp group by area,grade,honor grouping sets(area,grade,honor)")
groupingHonorDF.show()
结果：
+-----+-----+------+-----------+
| area|grade| honor|total_value|
+-----+-----+------+-----------+
|战争学院区| null| null| 915|
| null| null| 黄金3连胜| 135|
| null| null| 王者1连胜| 150|
|诺克萨斯区| null| null| 155|
| null| null|王者12连胜| 300|
| null| 黄金| null| 345|
|裁决之地区| null| null| 155|
| null| null|黄金12连胜| 180|
| null| 大师| null| 360|
| null| null| 大师6连胜| 120|
| null| null| 黄金1连胜| 30|
| null| 王者| null| 520|
| null| null| 大师3连胜| 240|
| null| null| 王者3连胜| 70|
+-----+-----+------+-----------+
说明：由group by area,grade,honor grouping sets(area,grade,honor)，再结合结果可以看到，对grouping sets(area,grade,honor)中的每个字段分别进行group by，最后对结果进行union。
延伸：group by area,grade,honor grouping sets ((area,grade),(area,honor))) 等价于
group by(area,grade) union group by (area,honor)结果；
rollup
- rollup 是根据维度在数据结果集中进行的聚合操作。
- group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对各个分组结果进行union操作。
代码：
//sql风格
val rollupHonorDF: DataFrame = spark.sql("select area,grade,honor,sum(value) as total_value from temp group by area,grade,honor with rollup")
rollupHonorDF.show()
val rollupHonorDFDSL: DataFrame = honorDF.rollup("area", "grade", "honor")
.agg(sum("value").alias("total_value"))
rollupHonorDFDSL.show()
结果：
| area|grade| honor|total_value|
+-----+-----+------+-----------+
|战争学院区| 王者|王者12连胜| 300|
|战争学院区| 大师| null| 240|
|战争学院区| null| null| 915|
|诺克萨斯区| null| null| 155|
|裁决之地区| 黄金| 黄金3连胜| 45|
|裁决之地区| 大师| null| 60|
|诺克萨斯区| 黄金| 黄金3连胜| 45|
|诺克萨斯区| 王者| 王者1连胜| 50|
|裁决之地区| null| null| 155|
|战争学院区| 黄金|黄金12连胜| 180|
|诺克萨斯区| 黄金| null| 45|
|裁决之地区| 王者| null| 50|
|战争学院区| 黄金| 黄金3连胜| 45|
|裁决之地区| 黄金| null| 45|
|战争学院区| 黄金| null| 255|
| null| null| null| 1225|
|战争学院区| 王者| 王者3连胜| 70|
|战争学院区| 大师| 大师6连胜| 120|
|战争学院区| 王者| 王者1连胜| 50|
|战争学院区| 大师| 大师3连胜| 120|
+-----+-----+------+-----------+
cube
- group by A,B,C with cube，则首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。
代码
val rollupHonorDFDSL: DataFrame = honorDF.cube("area", "grade", "honor")
.agg(sum("value").alias("total_value"))
rollupHonorDFDSL.show()
val cubeHonorDF: DataFrame = spark.sql("select area,grade,honor,sum(value) as total_value from temp group by area,grade,honor with cube")
cubeHonorDF.show()
结果：
+-----+-----+------+-----------+
| area|grade| honor|total_value|
+-----+-----+------+-----------+
|战争学院区| 王者|王者12连胜| 300|
|战争学院区| 大师| null| 240|
|战争学院区| null| null| 915|
|战争学院区| null| 王者1连胜| 50|
| null| null| 黄金3连胜| 135|
|战争学院区| null|王者12连胜| 300|
| null| null| 王者1连胜| 150|
| null| 王者|王者12连胜| 300|
|诺克萨斯区| null| null| 155|
| null| null|王者12连胜| 300|
|裁决之地区| 黄金| 黄金3连胜| 45|
| null| 黄金| null| 345|
|裁决之地区| 大师| null| 60|
|诺克萨斯区| 黄金| 黄金3连胜| 45|
|诺克萨斯区| 王者| 王者1连胜| 50|
|裁决之地区| null| null| 155|
|战争学院区| null| 大师6连胜| 120|
|战争学院区| null| 大师3连胜| 120|
|战争学院区| 黄金|黄金12连胜| 180|
|诺克萨斯区| 黄金| null| 45|
+-----+-----+------+-----------+
应用
再数据分析的时候，可以将各维度的结果进行统计，存入mysql中，进行查询分析。
