开窗函数

一、简单概述

开窗函数与聚合函数一样，都是对行的集合进行聚合计算。
开窗用于定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的行和和聚合列。
开窗函数的调用格式为：函数名（列） OVER(选项)

第一大类：聚合开窗函数 -》聚合函数 OVER(选项)，这里的选项可以是PARTITION BY子句，但不可以是ORDER BY子句。
第二大类：排序开窗函数-》排序函数（列）OVER（选项），这里的选项可以是ORDER BY子句，也可以是OVER (PARTITION BY 子句 ORDER BY 子句），但不可以只是PARTITION BY子句。

二、数据准备

        val sparkConf = new SparkConf().setAppName("session").setMaster("local[*]")

        val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()

        import sparkSession.implicits._
        val scoreDF = sparkSession.sparkContext.makeRDD(Array(
            ("a1", 1, 80),
            ("a2", 1, 81),
            ("a3", 2, 80),
            ("a4", 2, 83),
            ("a5", 2, 70),
            ("a6", 3, 30),
            ("a7", 4, 50),
            ("a8", 4, 80),
            ("a9", 4, 90),
            ("a10", 5, 100)
        )).toDF("name", "class", "score")
		// 创建临时表
        scoreDF.createOrReplaceTempView("score")
    }

三、聚合开窗函数

OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做聚合函数。

sparkSession.sql("select name, class, score, count(name) over() name_count from score").show()

查询结果如下图所示：

+----+-----+-----+----------+
|name|class|score|name_count|
+----+-----+-----+----------+
|  a1|    1|   80|        10|
|  a2|    1|   81|        10|
|  a3|    2|   80|        10|
|  a4|    2|   83|        10|
|  a5|    2|   70|        10|
|  a6|    3|   30|        10|
|  a7|    4|   50|        10|
|  a8|    4|   80|        10|
|  a9|    4|   90|        10|
| a10|    5|  100|        10|
+----+-----+-----+----------+

sparkSession.sql("select name, class, score, sum(score) over() total_score from score").show()

查询结果如下图所示：
±—±----±----±---------+
|name|class|score|total_score|
±—±----±----±---------+
| a1| 1| 80| 744|
| a2| 1| 81| 744|
| a3| 2| 80| 744|
| a4| 2| 83| 744|
| a5| 2| 70| 744|
| a6| 3| 30| 744|
| a7| 4| 50| 744|
| a8| 4| 80| 744|
| a9| 4| 90| 744|
| a10| 5| 100| 744|
±—±----±----±---------+

        sparkSession.sql("select name, class, score, count(name) over(partition by class) name_count from score").show()

查询结果如下图所示：
±—±----±----±---------+
|name|class|score|name_count|
±—±----±----±---------+
| a1| 1| 80| 2|
| a2| 1| 81| 2|
| a6| 3| 30| 1|
| a10| 5| 100| 1|
| a7| 4| 50| 3|
| a8| 4| 80| 3|
| a9| 4| 90| 3|
| a3| 2| 80| 3|
| a4| 2| 83| 3|
| a5| 2| 70| 3|
±—±----±----±---------+

排序开窗函数

对于排序开窗函数来讲，它支持的开窗函数分别为：ROW_NUMBER(行号)、RANK(排名)、DENSE_RANK(密集排名)和NTILE（分组排名）。

默认是升序，如需降序:order by 字段 asc

        sparkSession.sql("select name, class, score, row_number() over(order by score) rank from score").show()

查询结果如下图所示：
±—±----±----±—+
|name|class|score|rank|
±—±----±----±—+
| a6| 3| 30| 1|
| a7| 4| 50| 2|
| a5| 2| 70| 3|
| a1| 1| 80| 4|
| a3| 2| 80| 5|
| a8| 4| 80| 6|
| a2| 1| 81| 7|
| a4| 2| 83| 8|
| a9| 4| 90| 9|
| a10| 5| 100| 10|
±—±----±----±—+

        sparkSession.sql("select name, class, score, rank() over(order by score) rank from score").show()

rank出现跳跃，总数据条数等于rank最大值
查询结果如下图所示：
±—±----±----±—+
|name|class|score|rank|
±—±----±----±—+
| a6| 3| 30| 1|
| a7| 4| 50| 2|
| a5| 2| 70| 3|
| a1| 1| 80| 4|
| a3| 2| 80| 4|
| a8| 4| 80| 4|
| a2| 1| 81| 7|
| a4| 2| 83| 8|
| a9| 4| 90| 9|
| a10| 5| 100| 10|
±—±----±----±—+

        sparkSession.sql("select name, class, score, dense_rank() over(order by score) rank from score").show()

rank 不会出现跳跃，总数据条数大于等于rank的最大值
查询结果如下图所示：
±—±----±----±—+
|name|class|score|rank|
±—±----±----±—+
| a6| 3| 30| 1|
| a7| 4| 50| 2|
| a5| 2| 70| 3|
| a1| 1| 80| 4|
| a3| 2| 80| 4|
| a8| 4| 80| 4|
| a2| 1| 81| 5|
| a4| 2| 83| 6|
| a9| 4| 90| 7|
| a10| 5| 100| 8|
±—±----±----±—+

        sparkSession.sql("select name, class, score, ntile(6) over(order by score) rank from score").show()

将组数据分为设定的组数，然后组内进行排序
查询结果如下图所示：
±—±----±----±—+
|name|class|score|rank|
±—±----±----±—+
| a6| 3| 30| 1|
| a7| 4| 50| 1|
| a5| 2| 70| 2|
| a1| 1| 80| 2|
| a3| 2| 80| 3|
| a8| 4| 80| 3|
| a2| 1| 81| 4|
| a4| 2| 83| 4|
| a9| 4| 90| 5|
| a10| 5| 100| 6|
±—±----±----±—+