开窗函数

开窗函数
2020-05-10 11:51:35
开窗函数和聚合函数的区别如下：（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值
```
开窗函数和聚合函数的区别如下：
（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值
```
收起
展开全文

oracle开窗函数_SQL开窗函数

2020-12-09 07:35:35

在开窗函数出现之前存在着很多 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的...


	
在开窗函数出现之前存在着很多 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO  SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 SQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持
普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。
简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。开窗函数一般分为三类，聚合开窗函数，排序开窗函数和分桶开窗函数
01
创建练习库
创建练习库
1create table windowing_function_training(2id int,3studentId int,4language int,5math int,6english int,7classId string,8departmentId string9);
把数据插入到练习库
 1insert into table windowing_function_training values  2  (1,111,68,69,90,'class1','department1'), 3  (2,112,73,80,96,'class1','department1'), 4  (3,113,90,74,75,'class1','department1'), 5  (4,114,89,94,93,'class1','department1'), 6  (5,115,99,93,89,'class1','department1'), 7  (6,121,96,74,79,'class2','department1'), 8  (7,122,89,86,85,'class2','department1'), 9  (8,123,70,78,61,'class2','department1'),10  (9,124,76,70,76,'class2','department1'),11  (10,211,89,93,60,'class1','department2'),12  (11,212,76,83,75,'class1','department2'),13  (12,213,71,94,90,'class1','department2'),14  (13,214,94,94,66,'class1','department2'),15  (14,215,84,82,73,'class1','department2'),16  (15,216,85,74,93,'class1','department2'),17  (16,221,77,99,61,'class2','department2'),18  (17,222,80,78,96,'class2','department2'),19  (18,223,79,74,96,'class2','department2'),20  (19,224,75,80,78,'class2','department2'),21  (20,225,82,85,63,'class2','department2');
查看练习库数据
1SELECT * from autonavi_analysis_dev.windowing_function_training;
02
聚合开窗函数
1 count
 1-- count 开窗函数 (选择department1做为例子) 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        ,COUNT(math) OVER() AS count1    -- 以符合条件的所有行作为窗口 7        ,COUNT(math) OVER(PARTITION BY classId) AS count2    -- 以按classId分组的所有行作为窗口 8        ,COUNT(math) OVER(PARTITION BY classId ORDER BY math) AS count3    -- 以按classId分组、按math排序的所有行作为窗口 9        ,COUNT(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS count4    -- 以按classId分组、按math排序、按 当前行+往前1行+往后2行的行作为窗口10FROM    windowing_function_training11WHERE   departmentId = 'department1'
-- 结果解释,以studentid=115为例子:
-- count1为所有的行数9
-- count2为分区class1中的行数5
-- count3为分区class1中math值<=93的行数4
-- count4为分区class1中math值向前+1行向后+2行(实际只有1行)的总行数3;这个是排序后数行数
2 sum
 1-- sum开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        -- 以符合条件的所有行作为窗口 7        ,sum(math) OVER() AS sum1 8        -- 以按classId分组的所有行作为窗口 9        ,sum(math) OVER(PARTITION BY classId) AS sum210        -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口11        ,sum(math) OVER(PARTITION BY classId ORDER BY math) AS sum312        -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口13        ,sum(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS sum414FROM    windowing_function_training15WHERE   departmentId = 'department1'16;
-- 结果解释，以studentid=115为例子:
-- sum1为所有的行数汇总
-- sum2为分区class1中的行数汇总
-- sum3为分区class1中math值<=93的汇总
-- sum44为分区class1中math值向前+1行向后+2行(实际只有1行)的汇总 
3 min
 1SELECT  studentId 2        ,math 3        ,departmentId 4        ,classId 5        -- 以符合条件的所有行作为窗口 6        ,min(math) OVER() AS min1 7        -- 以按classId分组的所有行作为窗口 8        ,min(math) OVER(PARTITION BY classId) AS min2 9        -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口10        ,min(math) OVER(PARTITION BY classId ORDER BY math) AS min311        -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口12        ,min(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS min413FROM    windowing_function_training14WHERE   departmentId = 'department1'15;
-- 结果解释，以studentid=115为例子:
-- min1为所有的行最小值
-- min2为分区class1中的所有行最小值
-- min3为分区class1中math值<=93的所有行最小值
-- min4为分区class1中math值向前+1行向后+2行(实际只有1行)的所有行最小值
4 max
 1SELECT  studentId 2        ,math 3        ,departmentId 4        ,classId 5        -- 以符合条件的所有行作为窗口 6        ,max(math) OVER() AS max1 7        -- 以按classId分组的所有行作为窗口 8        ,max(math) OVER(PARTITION BY classId) AS max2 9        -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口10        ,max(math) OVER(PARTITION BY classId ORDER BY math) AS max311        -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口12        ,max(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS max413FROM    windowing_function_training14WHERE   departmentId = 'department1'15;
-- 结果解释，以studentid=115为例子:
-- max1为所有的行最大值
-- max2为分区class1中的所有行最大值
-- max3为分区class1中math值<=93的所有行最大值
-- max4为分区class1中math值向前+1行向后+2行(实际只有1行)的所有行最大值
5 avg
 1-- avg 开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        -- 以符合条件的所有行作为窗口 7        ,avg(math) OVER() AS avg1 8        -- 以按classId分组的所有行作为窗口 9        ,avg(math) OVER(PARTITION BY classId) AS avg210        -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口11        ,avg(math) OVER(PARTITION BY classId ORDER BY math) AS avg312        -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口13        ,avg(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS avg414FROM    windowing_function_training15WHERE   departmentId = 'department1'16;
-- 结果解释，以studentid=115为例子:
-- avg1为所有的行平均值
-- avg2为分区class1中的所有行平均值
-- avg3为分区class1中math值<=93的所有行平均值
-- avg4为分区class1中math值向前+1行向后+2行(实际只有1行)的所有行平均值
6 first_value
 1-- first_value 开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        -- 以符合条件的所有行作为窗口 7        ,first_value(math) OVER() AS first_value1 8        -- 以按classId分组的所有行作为窗口 9        ,first_value(math) OVER(PARTITION BY classId) AS first_value210        -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口11        ,first_value(math) OVER(PARTITION BY classId ORDER BY math) AS first_value312        -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口13        ,first_value(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS first_value414FROM    windowing_function_training15WHERE   departmentId = 'department1'16;
-- 结果解释，以studentid=115为例子:
-- first_value1为所有的行第一个值
-- first_value2为分区class1中的所有行第一个值
-- first_value3为分区class1中math值<=93的所有行第一个值
-- first_value4为分区class1中math值向前+1行向后+2行(实际只有1行)的所有行第一个值
7 last_value
 1-- last_value 开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        -- 以符合条件的所有行作为窗口 7        ,last_value(math) OVER() AS last_value1 8        -- 以按classId分组的所有行作为窗口 9        ,last_value(math) OVER(PARTITION BY classId) AS last_value210        -- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口11        ,last_value(math) OVER(PARTITION BY classId ORDER BY math) AS last_value312        -- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口13        ,last_value(math) OVER(PARTITION BY classId ORDER BY math ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS last_value414FROM    windowing_function_training15WHERE   departmentId = 'department1'16;
-- 结果解释，以studentid=115为例子:
-- first_value1为所有的行第一个值
-- first_value2为分区class1中的所有行第一个值
-- first_value3为分区class1中math值<=93的所有行第一个值
-- first_value4为分区class1中math值向前+1行向后+2行(实际只有1行)的所有行第一个值
8 lag
 1-- lag 开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        --窗口内 往上取第二个 取不到时赋默认值60 7        ,lag(math,2,60) OVER(PARTITION BY classId ORDER BY math) AS lag1 8        --窗口内 往上取第二个 取不到时赋默认值NULL 9        ,lag(math,2) OVER(PARTITION BY classId ORDER BY math) AS lag210FROM    windowing_function_training11WHERE   departmentId = 'department1'12;
-- lag(col,n,default) 用于统计窗口内往上第n个值。
--     col:列名
--     n:往上第n行
--     default:往上第n行为NULL时候，取默认值,不指定则取NULL
-- 结果解释，以studentid=112为例；第3行 lag1:窗口内(69 74 80) 当前行80 向上取第二个值为69
-- 倒数第3行 lag2:窗口内(70 74) 当前行74 向上取第二个值为NULL
9 lead
 1-- lead开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        --窗口内 往下取第二个 取不到时赋默认值60 7        ,lead(math,2,60) OVER(PARTITION BY classId ORDER BY math) AS lead1 8        --窗口内 往下取第二个 取不到时赋默认值NULL 9        ,lead(math,2) OVER(PARTITION BY classId ORDER BY math) AS lead210FROM    windowing_function_training11WHERE   departmentId = 'department1'12;
-- lead(col,n,default) 用于统计窗口内往下第n个值。
--     col:列名
--     n:往下第n行
--     default:往下第n行为NULL时候，取默认值,不指定则取NULL
-- 结果解释:
--   以studentid=112为例 窗口内向下第二个值为空，lead1赋值60
10 cume_dist
 1-- cume_dist 开窗函数 2SELECT  studentId 3        ,math 4        ,departmentId 5        ,classId 6        -- 统计小于等于当前分数的人数占总人数的比例 7        ,cume_dist() OVER(ORDER BY math) AS cume_dist1 8        -- 统计大于等于当前分数的人数占总人数的比例 9        ,cume_dist() OVER(ORDER BY math DESC) AS cume_dist210        -- 统计分区内小于等于当前分数的人数占总人数的比例11        ,cume_dist() OVER(PARTITION BY classId ORDER BY math) AS cume_dist312FROM    windowing_function_training13WHERE   departmentId = 'department1'14;
-- 这是一个非常有用的函数，计算某个窗口或分区中大于或者小于某个值的累积分布。
--假定升序排序，则使用以下公式确定累积分布：
-- 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
-- 结果解释:
--     第三行:
--         cume_dist1=小于等于80的人数为6/总人数9=0.6666666666666666
--         cume_dist2=大于等于80的人数为4/总人数9=0.4444444444444444
--         cume_dist3=分区内小于等于80的人数为3/分区内总人数5=0.6
03
排序开窗函数
1 rank
 1-- rank 开窗函数 2SELECT  * 3        -- 对全部学生按数学分数排序  4        ,rank() OVER(ORDER BY math) AS rank1 5        -- 对院系 按数学分数排序 6        ,rank() OVER(PARTITION BY departmentId ORDER BY math) AS rank2 7        -- 对每个院系每个班级 按数学分数排序 8        ,rank() OVER(PARTITION BY departmentId,classId ORDER BY math) AS rank3 9FROM    windowing_function_training10;
-- 解释：
-- rank 开窗函数基于 OVER 子句中的 ORDER BY 确定一组值中一个值的排名。如果存在partition BY ,则为每个分区组中的每个值排名。
-- 排名可能不是连续的。例如，如果两个行的排名为 1，则下一个排名为 3。
-- 例如studentid = 115,在整体排序中为15，在院系排序中为8，在院系内的班级中排序为4
2 dense_rank
 1-- dense_rank 开窗函数 2SELECT  * 3        -- 对全部学生按数学分数排序 4        ,dense_rank() OVER(ORDER BY math) AS dense_rank1 5        -- 对院系 按数学分数排序 6        ,dense_rank() OVER(PARTITION BY departmentId ORDER BY math) AS dense_rank2 7        -- 对每个院系每个班级 按数学分数排序 8        ,dense_rank() OVER(PARTITION BY departmentId,classId ORDER BY math) AS dense_rank3 9FROM    windowing_function_training10;
-- 解释：
-- dense_rank与rank有一点不同,当排名一样的时候,接下来的行是连续的。如两个行的排名为 1，则下一个排名为 2。
-- 例如studentid = 115,在整体排序中为10，在院系排序中为7，在院系内的班级中排序为4
3 row_number
1-- row_number 开窗函数2SELECT  studentid3        ,departmentid4        ,classid5        ,math6        -- 对分区departmentid,classid内的数据按math排序7        ,row_number() OVER(PARTITION BY departmentid,classid ORDER BY math) AS row_number8FROM    windowing_function_training9;
-- 从1开始对分区内的数据排序。
-- 解释:row_number函数和rank还有dense_rank的区别在于，同一分区,相同值，不同序
-- 如studentid=213 studentid=214 值都为94 排序为5,6
4 percent_rank
 1-- percent_rank 开窗函数 2SELECT  studentid 3        ,departmentid 4        ,classid 5        ,math 6        ,row_number() OVER(PARTITION BY departmentid,classid ORDER BY math) AS row_number 7        ,percent_rank() OVER(PARTITION BY departmentid,classid ORDER BY math) AS percent_rank 8FROM    windowing_function_training 9where departmentId = 'department1'10;
-- 计算给定行的百分比排名。可以用来计算超过了百分之多少的人。如360小助手开机速度超过了百分之多少的人。
-- (当前行的rank值-1)/(分组内的总行数-1)
-- 和cume_dist()有些像，但这里指的是超过，而cume_dist()是包含自己的累计
-- 结果解释:
--     studentid=115,percent_rank=(4-1)/(5-1)=0.75
--     studentid=123,percent_rank=(3-1)/(4-1)=0.6666666666666666
04
分桶开窗函数
1 ntile
1-- ntile 开窗函数2SELECT  *3        -- 对分区内的数据分成两组4        ,ntile(2) OVER(PARTITION BY departmentid ORDER BY math) AS ntile15        -- 对分区内的数据分成三组6        ,ntile(3) OVER(PARTITION BY departmentid ORDER BY math) AS ntile27FROM    windowing_function_training8;
-- 这里用order了，所以是现在分区内做了排序；将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，
-- 并返回给定行所在的组的排名。
-- 结果解释:
--     例如studentid = 115
--         ntile1:对分区的数据均匀分成2组后，当前行为department1中的第二组
--         ntile2:对分区的数据均匀分成3组后，当前行为department1中的第三组

收起

展开全文

Apache SparkSQL 开窗函数与排序开窗函数

2020-04-10 00:41:15

文章目录SparkSQL 开窗函数聚合函数和开窗函数开窗函数分类聚合开窗函数SparkSQL 排序开窗函数ROW_NUMBER顺序排序RANK跳跃排序DENSE_RANK连续排序NTILE分组排名 SparkSQL 开窗函数 ...开窗函数的引入是为了既显示聚集前...


文章目录
SparkSQL 开窗函数
聚合函数和开窗函数的区别
开窗函数分类
聚合开窗函数
排序开窗函数
1，ROW_NUMBER()顺序排序
2，RANK() 跳跃排序(并列的跳过)
3，DENSE_RANK() 连续排序
4，NTILE() 分组排名

SparkSQL 开窗函数
开窗函数的引入是为了既显示聚集前的数据，又显示聚集后的数据，

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

开窗函数是将一行变成多行；

聚合函数如果要显示其他的列必须将列加入到group by中

开窗函数可以不使用group by，直接将所有信息显示出来
开窗函数分类

聚合开窗函数

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

排序开窗函数

排序函数(列) OVER(选项)，这里的选项可以是ORDER BY 子句，也可以是 OVER(PARTITION BY 子句 ORDER BY 子句)，但不可以是 PARTITION BY 子句。
聚合开窗函数
package demo07

import org.apache.spark.sql.SparkSession

object Test01 {
  case class Score(name: String, clazz: Int, score: Int)

  def main(args: Array[String]): Unit = {

    val spark = SparkSession.builder().appName("Test01")
      .master("local[*]")
      .getOrCreate()

    spark.sparkContext.setLogLevel("WARN")

    import spark.implicits._

    val scoreDF = 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()

  }

}


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

OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。

SQL标准允许将所有聚合函数用做聚合开窗函数。

示例1 OVER()的用法：

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

+-----------+
|count(name)|
+-----------+
|         11|
+-----------+

+----+-----+-----+----------+
|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|
+----+-----+-----+----------+

观察上述结果，可以发现，over()的作用就是可以让你

在显示聚合结果的同时，还正常显示其它列。

这里的over跟as比较相似，也可以起别名

示例2 PARTITION BY的用法：

OVER 关键字后的括号中还可以添加选项用以改变进行聚合运算的窗口范围。

如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。

与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。
查询按照班级分组后每组的人数：

OVER(PARTITION BY class)表示对结果集按照 class 进行分区，并且计算当前行所属的区的聚合计算结果。
spark.sql("select name, class, score, count(name) over(partition by class) 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|
+----+-----+-----+----------+

排序开窗函数
1，ROW_NUMBER()顺序排序
row_number() over(order by score) as rownum 表示按score 升序的方式来排序，并得出排序结果的序号
注意：在排序开窗函数中使用 PARTITION  BY 子句需要放置在ORDER  BY 子句之前。
示例1
spark.sql("select name, class, score, row_number() over(order by score) rank from scores").show()

+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
|  a8|    3|   45|   1|
|  a9|    3|   55|   2|
|  a4|    2|   74|   3|
|  a2|    1|   78|   4|
| a10|    3|   78|   5|
|  a1|    1|   80|   6|
|  a5|    2|   92|   7|
|  a3|    1|   95|   8|
|  a6|    3|   99|   9|
|  a7|    3|   99|  10|
| a11|    3|  100|  11|
+----+-----+-----+----+

每个班级都分别按成绩排名
spark.sql("select name, class, score, row_number() over(partition by class order by score) rank from scores").show()

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

2，RANK() 跳跃排序(并列的跳过)
rank() over(order by score) as rank表示按 score升序的方式来排序，并得出排序结果的排名号。

这个函数求出来的排名结果可以并列（并列第一/并列第二），并列排名之后的排名将是并列的排名加上并列数

简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第三名，也就是没有了第二名，但是有两个第一名
查询所有人的成绩，按从小到大排序，相同分数的人名次相同
spark.sql("select name, class, score, rank() over(order by score) rank from scores").show()       

+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
|  a8|    3|   45|   1|
|  a9|    3|   55|   2|
|  a4|    2|   74|   3|
| a10|    3|   78|   4|
|  a2|    1|   78|   4|
|  a1|    1|   80|   6|
|  a5|    2|   92|   7|
|  a3|    1|   95|   8|
|  a6|    3|   99|   9|
|  a7|    3|   99|   9|
| a11|    3|  100|  11|
+----+-----+-----+----+

分别查询每个班级的成绩，按从小到大排序，相同分数的人名次相同
spark.sql("select name, class, score, rank() over(partition by class order by score) rank from scores").show()

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

3，DENSE_RANK() 连续排序
dense_rank() over(order by score) as dense_rank 表示按score 升序的方式来排序，并得出排序结果的排名号。

这个函数并列排名之后的排名是并列排名加１

简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第二名，也就是两个第一名，一个第二名
查询所有人的成绩，按从小到大排序，相同分数的人名次相同，排名得连着
spark.sql("select name, class, score, dense_rank() over(order by score) rank from scores").show()

+----+-----+-----+----+
|name|class|score|rank|
+----+-----+-----+----+
|  a8|    3|   45|   1|
|  a9|    3|   55|   2|
|  a4|    2|   74|   3|
|  a2|    1|   78|   4|
| a10|    3|   78|   4|
|  a1|    1|   80|   5|
|  a5|    2|   92|   6|
|  a3|    1|   95|   7|
|  a6|    3|   99|   8|
|  a7|    3|   99|   8|
| a11|    3|  100|   9|
+----+-----+-----+----+

分别查询每个班级的成绩，按从小到大排序，相同分数的人名次相同，排名得连着
spark.sql("select name, class, score, dense_rank() over(partition by class order by score) rank from scores").show()

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

4，NTILE() 分组排名
ntile(6) over(order by score)as ntile表示按 score 升序的方式来排序，然后 6 等分成 6 个组，并显示所在组的序号。
查询所有人的成绩，等分成6个组，并显示所在组的序号
spark.sql("select name, class, score, ntile(6) over(order by score) rank from scores").show()

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

分别查询每个班级的成绩，等分成6个组，并显示所在组的序号
spark.sql("select name, class, score, ntile(6) over(partition by class order by score) rank from scores").show()

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

收起

展开全文

spark

rank 开窗函数_SQL-开窗函数

2021-01-02 10:21:30

碎语学习的正态曲线：入门容易，精通难积累的正态曲线：先越读越多，后越读越少什么是开窗函数很多场景比如排序，累计求和等，如果没有开窗函数，那么就要使用很复杂的子查询或是存储过程才能做到。为了解决这些问题...

碎语
学习的正态曲线：入门容易，精通难
积累的正态曲线：先越读越多，后越读越少
什么是开窗函数
很多场景比如排序，累计求和等，如果没有开窗函数，那么就要使用很复杂的子查询或是存储过程才能做到。为了解决这些问题，就有了现在的开窗函数，MySQL是从8.0版本之后才有了开窗函数，如果要使用，那么必须下载MySQL8.0以上的版本
开窗函数主要是为了给行定义一个窗口,可以理解就是一个分组，但是和group by 的区别在于它不是返回一个聚合值，而是每一行都能返回一个值。举个例子，咱们要求每个月销量和当年总销量以及每个月的销量占比，这时候用sum() 和开窗就很容易了
数据参考：[数据分析师经常遇到的SQL场景解析]数据分析师经常遇到的SQL场景解析
select    sal_year,    sal_month,    sum_sale,#销量    sum(sum_sale) over(partition by sal_year) as cumu_sal,##年总销量    sum_sale/(sum(sum_sale) over(partition by sal_year)) as ratio  ##每个月销量占全年销量的占比from     (select        year(date(order_date) )as sal_year,        month(date(order_Date) )as sal_month,        sum(sales) as sum_sale    from         chaoshi.order    group by         year(date(order_date) ),        month(date(order_Date) )    )aorder by    sal_year,    sal_month;    
部分结果预览
开窗结构
函数+开窗函数：row_number() over()
row_number() over(partition by xx order by yy rows between zz and aa)
partition by：分组，顾名思义就是以什么字段进行分组，形式跟group by 一样
order by : 排序，对分完组后的数据，进行组内的排序
rows between :窗口，计算的窗口，between后可以跟如下的内容：unbounded preceding:第一行
unbounded following:最后一行
current row:当前行
N preceding:前N行
N following:后N行
一般省略了rows的时候默认都是从开窗后的第一行到当前行，后面的具体例子会讲解
row_number() 就是一个函数，开窗一般都是与排序和聚合函数一起使用
函数
排名开窗函数
row_number ():排序之后不管有没有重复值都是一直往上再加序号
dense_rank():排序之后遇到重复值会生成一样的序号，但是接下来的序号不连续
rank()：排序之后遇到重复值会生成一样的序号，接下来的序号连续，具体如下栗子
栗子
with test as (##创建了一个临时表select    1 as num from     dualunion allselect    2 as num from     dualunion allselect    2 as num from     dualunion allselect    3 as num from     dualunion allselect    4 as num from     dual)select     num ,    row_number() over(order by num ) as row_number1,    rank() over(order by num) as rank1,    dense_rank() over(order by num) as dense_rank1from     test ##上面的临时表test
结果
结果
聚合开窗函数
可以和很多聚合函数一起使用，如：sum()/count()/min()/max()
结束语
如果我不能让您看懂，那是我的问题，如果有疑问可以关注我，然后私聊我，我会尽最大的努力帮助你

收起

展开全文

oracle开窗函数_SQL开窗函数

Apache SparkSQL 开窗函数与排序开窗函数

文章目录

SparkSQL 开窗函数

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

开窗函数分类

聚合开窗函数

排序开窗函数

1，ROW_NUMBER()顺序排序

2，RANK() 跳跃排序(并列的跳过)

3，DENSE_RANK() 连续排序

4，NTILE() 分组排名

rank 开窗函数_SQL-开窗函数

碎语

什么是开窗函数

开窗结构

函数

排名开窗函数

栗子

结果

聚合开窗函数

结束语

开窗函数

【转载】Swiper 动态渲染数据，Loop 属性不生效

FFmpeg4.3黄金系列课程：c++版

easyconnect是什么软件

ArcGIS Pro2.6和ArcGIS Enterprise学习

以 PyCharm 为例介绍 win10 手动添加程序到右键菜单/右键以pycharm project方式打开文件夹

自动驾驶多传感器融合标定方法汇总

CSS 如何以正方形显示图片 以width宽度自适应

最新人教版五年级下册语文第七单元基础过关知识整理.doc

HTML5手机抽奖游戏活动静态页面集合.zip

Python去掉字符串中间的空格

Kotlin协程极简入门与解密

Codeforces Round #636 (Div. 3)（E待补）

JSON解析

ACC系列（一）：ACC的功能、分类及系统组成

git-远程分支和文件管理

树莓派搭建开发环境所需的工具.zip

最新人教版语文五年级上册第六单元测试卷.doc

Iconleak-Atrous-Console

备战2021软考网络规划设计师顺利通关培训套餐

单片机完全学习课程全五季套餐

CSS 如何以正方形显示图片以width宽度自适应