SparkSQL的开窗函数

1. 简介

开窗函数简介:

• 开窗函数是为了既显示聚集前的数据，又显示聚集后的数据。即在每一行的最后一列添加聚合函数的结果。
• 窗用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用 GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

聚合函数和开窗函数区别:

• 聚合函数是将多行变成一行，count,avg…
• 开窗函数是将一行变成多行；
• 聚合函数如果要显示其他的列必须将列加入到group by中
• 开窗函数可以不使用group by，直接将所有信息显示出来

开窗函数分类:

1.聚合开窗函数

• 聚合函数(列) OVER(选项)，这里的选项可以是PARTITION BY 子句，但不可以是 ORDER BY 子句。

2.排序开窗函数

• 排序函数(列) OVER(选项)，这里的选项可以是ORDER BY 子句，也可以是 OVER(PARTITION BY 子句 ORDER BY 子句)，但不可以是 PARTITION BY 子句。

2. 聚合开窗函数

• OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。
• 开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。

3. 排序开窗函数

• ROW_NUMBER顺序排序 123
• RANK 跳跃排序 1 2 4
• DENSE_RANK 连续排序 1223

4. Spark开窗函数代码演示

import org.apache.spark.sql.{DataFrame, SparkSession}

/**
 * @author liu a fu
 * @date 2021/1/26 0026
 * @version 1.0
 * @DESC   Spark的开窗函数演示
 */
case class Score(name: String, clazz: Int, score: Int)

object _15SessionAndWindos {
  def main(args: Array[String]): Unit = {
    //1-环境准备
    val spark: SparkSession = SparkSession
      .builder()
      .appName(this.getClass.getSimpleName.stripSuffix("$"))
      .master("local[*]")
      .getOrCreate()
    spark.sparkContext.setLogLevel("WARN")

    //从rdd转化到df需要导入隐式转换
    import spark.implicits._

    //2-读取数据
    val scoreDF: DataFrame = spark.sparkContext.makeRDD(Array(
      Score("a1", 1, 80),
      Score("a2", 1, 78),
      Score("a3", 1, 95),
      Score("a4", 2, 74),
      Score("a5", 2, 92),
      Score("a6", 3, 99),
      Score("a7", 3, 99),
      Score("a8", 3, 45),
      Score("a9", 3, 55),
      Score("a10", 3, 78),
      Score("a11", 3, 100)
    )).toDF("name", "class", "score")

    //视图
    scoreDF.createOrReplaceTempView("scores")
    scoreDF.show()

    //1-聚合类的开窗函数
    spark.sql("select count(name) from scores").show()
    //+-----------+
    //|count(name)|
    //+-----------+
    //|         11|
    //+-----------+

    /**
     *  总的count(name)数显示出来了
     */
    spark.sql("select name,class,score,count(name) over() as name_count from scores").show()

    /**
     * +----+-----+-----+----------+
     * |name|class|score|name_count|
     * +----+-----+-----+----------+
     * |  a1|    1|   80|        11|
     * |  a2|    1|   78|        11|
     * |  a3|    1|   95|        11|
     * |  a4|    2|   74|        11|
     * |  a5|    2|   92|        11|
     * |  a6|    3|   99|        11|
     * |  a7|    3|   99|        11|
     * |  a8|    3|   45|        11|
     * |  a9|    3|   55|        11|
     * | a10|    3|   78|        11|
     * | a11|    3|  100|        11|
     * +----+-----+-----+----------+
     */


    //每一个班的count(name)显示出来  partition by 按照班级分区
    spark.sql("select name,class,score,count(name) over(partition by class) as name_count from scores").show()

    /**
     * |name|class|score|name_count|
     * +----+-----+-----+----------+
     * |  a1|    1|   80|         3|
     * |  a2|    1|   78|         3|
     * |  a3|    1|   95|         3|
     * |  a6|    3|   99|         6|
     * |  a7|    3|   99|         6|
     * |  a8|    3|   45|         6|
     * |  a9|    3|   55|         6|
     * | a10|    3|   78|         6|
     * | a11|    3|  100|         6|
     * |  a4|    2|   74|         2|
     * |  a5|    2|   92|         2|
     * +----+-----+-----+----------+
     */

    //2-排序类开窗函数
    //row_number 123
    spark.sql("select name,class,score,row_number() over(order by score) rank from scores").show()
    spark.sql("select name,class,score,row_number() over(partition by class order by score) rank from scores").show()

    //rank 124
    spark.sql("select name,class,score,rank() over(order by score) rank from scores").show()
    spark.sql("select name,class,score,rank() over(partition by class order by score) rank from scores").show()

    //dense_rank 223
    spark.sql("select name,class,score,dense_rank() over(order by score) rank from scores").show()
    spark.sql("select name,class,score,dense_rank() over(partition by class order by score) rank from scores").show()

    spark.stop()
  }

}