【SparkSQL】扩展 ---- 聚合操作

---

文章目录

---

一、groupBy() ---- 分组

• 按照列将DataSet分组，并返回一个RelatinalGroupedDataSet对象，然后通过该对象，对分组内容进行聚合操作

@Test
  def test(): Unit ={
    // 配置环境
    val spark = SparkSession.builder()
      .master("local[6]")
      .appName("groupBy")
      .getOrCreate()
    // 导入隐式转换  
    import spark.implicits._
    // 定义数据schema
    val schema = StructType(
      List(
        StructField("id",LongType),
        StructField("year",IntegerType),
        StructField("month",IntegerType),
        StructField("day",IntegerType),
        StructField("hour",IntegerType),
        StructField("season",IntegerType),
        StructField("pm",DoubleType)
      )
    )
    // 读取文件
    val df = spark.read
      .option("header",value = true)
      .schema(schema)
      .csv("dataset/beijing_pm_nan.csv")

    // 1.清洗数据
    val cleanDF = df.where('pm =!= Double.NaN )

    // 2.分组
    val grouped: RelationalGroupedDataset = cleanDF.groupBy('year,'month)

avg ---- 平均值

//  2.1 使用functions函数进行聚合
    import org.apache.spark.sql.functions._
    // 求平均值
    // select avg(pm) from .... group by ...
    grouped.agg(avg('pm) as "pm_avg")
      .orderBy('pm_avg desc)
      .show()

---

stddev ---- 方差

// 求方差
    grouped.agg(stddev('pm) as "pm_stddev")
      .orderBy('pm_stddev desc)
      .show()

---

用GroupedDataset的API进行聚合

//  2.2 使用GroupedDataset的API进行聚合
    grouped.avg("pm")
      .select($"avg(pm)" as "pm_avg")
      .orderBy($"pm_avg")
      .show()
  }

---

二、多维聚合

• 多维聚合就是将数据进行多次聚合最后将结果集合并，在一张表中呈现出多次聚合的结果

// 读取数据集
    // 配置环境
    val spark = SparkSession.builder()
      .master("local[6]")
      .appName("groupBy")
      .getOrCreate()
    // 导入隐式转换  
    import spark.implicits._
    // 指定结构信息schema
    val schema = StructType(
      List(
        StructField("source",StringType),
        StructField("year",IntegerType),
        StructField("month",IntegerType),
        StructField("day",IntegerType),
        StructField("hour",IntegerType),
        StructField("season",IntegerType),
        StructField("pm",DoubleType)
      )
    )
    // 读取数据集
    val df: DataFrame = spark.read
      .option("header",value = true)
      .schema(schema)
      .csv("dataset/beijing_pm_final.csv")

import org.apache.spark.sql.functions._

  // 需求一：按照来源、年份分组求pm均值
  // select source,year,avg(pm) as pm from ... group by source,year
  val df1 = df.groupBy('source, 'year)
    .agg(avg("pm") as "pm")

  // 需求二：按照来源分组求pm均值
  // select source,avg(pm) from ... group by source
  val df2 = df.groupBy('source)
    .agg(avg("pm") as "pm")
    .select('source, lit(null) as "year", 'pm)  // 在进行union的时候必须列数相同，在这里通过lit函数构造一列空值

  // 合并数据集
  df1.union(df2)
    .sort('source, 'year asc_nulls_last, 'pm)
    .show()

• 需求一：按照来源、年份分组求pm均值



• 需求二：按照来源分组求pm均值



• 合并数据集



• 在排序的时候如果含有空值设置排布。

---

1.rollup()

• rollup操作符就是groupBy的一个扩展，它会对传入的列进行滚动groupBy，groupBy的次数为列数量+1，最后一次是对整个数据集进行聚合，主要依据是第一个列。

// 定义数据集
 val sales = Seq(
    ("beijing",2016,100),
    ("beijing",2016,100),
    ("beijing",2017,98),
    ("shanghai",2017,97),
    ("guangdong",2018,97),
    ("guangdong",2018,99),
    ("shenzhen",2017,98),
    ("jiangsu",2016,95),
    ("jiangsu",2016,100),
    ("nanjin",2018,96)
  ).toDF("city","year","amount")

  // 每个城市每年的销售
  // 每个城市的总体销售
  // 所有城市销售总和
  // 滚动分组  AB,A,NULL
  import org.apache.spark.sql.functions._
  sales.rollup('city,'year)
    .agg(sum("amount") as "amount")
    .sort('city asc_nulls_last,'year asc_nulls_last)
    .show()

案例：PM统计值多维聚合

// 声明数据结构信息schema
val schema = StructType(
   List(
      StructField("source",StringType),
      StructField("year",IntegerType),
      StructField("month",IntegerType),
      StructField("day",IntegerType),
      StructField("hour",IntegerType),
      StructField("season",IntegerType),
      StructField("pm",DoubleType)
  )
)
// 读取数据集
val df = spark.read
  .option("header",value = true)
  .schema(schema)
  .csv("dataset/beijing_pm_final.csv")
// 需求1：每个pm来源，每年pm值统计的平均值
// 需求2：每个pm来源，整体上的pm值统计的平均值
// 需求3：全局所有的计量者
import org.apache.spark.sql.functions._
df.rollup('source,'year)
  .agg(avg('pm) as "pm" )
  .sort('source asc_nulls_last,'year asc_nulls_last)
  .show()

变形：将每年作为主列，结果完全不同

// 需求1：每年pm值，每个pm来源统计的平均值
// 需求2：每年pm值，整体上的pm值统计的平均值
// 需求3：全局所有的计量者
df.rollup('year,'source)
  .agg(avg('pm) as "pm" )
  .sort('year asc_nulls_last,'source asc_nulls_last)
  .show()

模型：

rollup聚合不均衡，始终将第一个列作为主列进行分组。

---

2.cube()

• cube可以看成是对rollup的扩展。group by A,B,C with cube，则首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。

// 指定数据结构信息schema
val schema = StructType(
   List(
     StructField("source",StringType),
     StructField("year",IntegerType),
     StructField("month",IntegerType),
     StructField("day",IntegerType),
     StructField("hour",IntegerType),
     StructField("season",IntegerType),
     StructField("pm",DoubleType)
   )
 )
// 读取数据集
 val df = spark.read
   .option("header",value = true)
   .schema(schema)
   .csv("dataset/beijing_pm_final.csv")
// 导入function函数
 import org.apache.spark.sql.functions._
 df.cube('source,'year)
   .agg(avg('pm) as "pm" )
   .sort('source asc_nulls_last,'year asc_nulls_last)
   .show()

---

3.cubeSQL

• 使用SQL语句进行分组聚合，但是SQL中需要用到的是grouping set。grouping sets是group by子句更进一步的扩展, 它让你能够定义多个数据分组。这样做使聚合更容易, 并且因此使得多维数据分析更容易

// 定义数据结构信息
val schema = StructType(
  List(
    StructField("source", StringType),
    StructField("year", IntegerType),
    StructField("month", IntegerType),
    StructField("day", IntegerType),
    StructField("hour", IntegerType),
    StructField("season", IntegerType),
    StructField("pm", DoubleType)
  )
)
// 读取数据集
val df = spark.read
  .option("header", value = true)
  .schema(schema)
  .csv("dataset/beijing_pm_final.csv")
// 创建视图
df.createOrReplaceTempView("pm_final")
// cubeSQL
val result = spark.sql(
  "select source,year,avg(pm) as pm from pm_final" +
    "group by source,year " +
    "grouping sets ((source,year),(source),(year),()) " +
    "order by source asc nulls last,year asc nulls last")
result.show()

• 结果与cube()的使用完全一致。

---

4.RelationalGroupedDataSet

---