开窗函数

2021-03-30 16:39:25

一、什么是开窗函数 开窗函数/分析函数：over() 开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。 开窗函数的调用格式为：函数名(列名) OVER(partition by 列名 order by列名) 。 ...

一、什么是开窗函数

开窗函数/分析函数：over()

开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。

开窗函数的调用格式为：

函数名(列名) OVER(partition by 列名 order by列名) 。

如果你没听说过开窗函数，看到上面开窗函数的调用方法，你可能还会有些疑惑。但只要你了解聚合函数，那么理解开窗函数就非常容易了。

我们知道聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。

但有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。

开窗函数和聚合函数的区别如下：

（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。

（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。

注：常见主流数据库目前都支持开窗函数，但mysql数据库目前还不支持。

二、常见面试题

1. 分区排序：row_number () over()

有如下学生成绩表：students_grades

查询每门课程course_name前三名的学生姓名及成绩，要求输出列格式如下：

course_name, number, stu_name, grades

查询语句如下：

2.几个排序函数row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别

（1） row_number() over()：对相等的值不进行区分，相等的值对应的排名相同，序号从1到n连续。

（2） rank() over()：相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。

（3） dense_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。

（4） ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。

学生成绩表同上，查询语句如下：

查询结果如下：

收起

展开全文

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中的第三组

收起

展开全文

mysql分析函数开窗函数_开窗函数（分析函数）

2021-03-04 01:05:55

开窗函数(分析函数)2.7.1 特点：开窗函数也就是在满足某种条件的记录集合上执行的特殊函数；对于每条记录都要在此窗口内执行函数；有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，...

开窗函数(分析函数)
2.7.1 特点：开窗函数也就是在满足某种条件的记录集合上执行的特殊函数；
对于每条记录都要在此窗口内执行函数；
有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；
有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口；
开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。
2.7.2 开窗函数和普通聚合函数的区别聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条
聚合函数也可以用于开窗函数中。
2.7.3 语法开窗函数名([]) over([partition by ] [order by  [desc]] [])partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
关于over的参数over参数为空，没有指定分区、排序、滑动窗口时，开窗函数将整个表作为一个区，默认计算的是所有值；
over指定了分区，未指定排序和滑动窗口，开窗函数默认计算分区内的所有值；
over指定了分区、排序，未指定滑动窗口，开窗函数默认计算的时第一行到当前行的值；
2.7.4 滑动窗口范围指定的两种方式方式一：基于行
对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：current row     边界是当前行，一般和其他范围关键字一起使用
unbounded preceding    边界是分区中的第一行
unbounded following    边界是分区中的最后一行
expr preceding    边界是当前行减去expr的值
expr following    边界是当前行加上expr的值
移动平均可用来消除差额较大的情况，平滑数据方式二：基于范围
和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：interval 7 day preceding。
2.7.5 开窗函数分类动态窗口函数：first_value() / last_value()/nth_value()/聚合函数用于开窗
如没有指定排序和滑动窗口范围，默认计算的是分区内的所有记录。 指定分区和排序后，如没有指定滑动窗口范围，默认计算的是分区内的第一行到当前行。静态窗口函数：row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() / lead()/ntile()
不管是否指定滑动窗口范围，窗口都是固定的，所以指定的滑动窗口范围无效。
2.7.6 按照函数功能不同，MySQL支持的开窗函数分为如下几类：序号函数：row_number() / rank() / dense_rank()
函数名显示内容显示样式row_number()显示分区中不重复不间断的序号1，2，3，4...dense_rank()显示分区中重复不间断的序号1，1，2，3，3...rank()显示分区中重复间断的序号1，1，3，4...分布函数：percent_rank() / cume_dist()percent_rank()：和之前的rank()函数相关，每行计算：(rank - 1) / (rows - 1)
其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数。rank() over(partition by user_no order by amount desc) rank1,
percent_rank() over(partition by user_no order by amount desc) percentcume_dist()： 分组内小于等于当前rank值的行数/分组内总行数
示例：查询大于等于当前订单金额的订单比例rank() over(partition by user_no order by amount desc) rank1,
cume_dist() over(partition by user_no order by amount desc) cume前后函数：lag() / lead()
分区中位于当前行前n行(lag)/后n行(lead)的记录值
示例：查询上一个订单距离当前订单的间隔天数lag(create_date,1) over(partition by user_no order by create_date) last_date头尾函数：first_value() / last_value()
分区中的第一个/最后一个指定参数的值
示例：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额first_value(amount) over(partition by user_no order by create_date) first_amount,
last_value(amount) over(partition by user_no order by create_date) last_amount其他函数：nth_value() / nfile()nth_value(expr,n)：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名
示例：每个用户订单中显示金额排名第二和第三的订单金额nth_value(amount,2) over(partition by user_no order by amount) second_amount,
nth_value(amount,3) over(partition by user_no order by amount) third_amount
2.  nfile(n)：将分区中的有序数据分为n个桶，记录桶号
示例：将每个用户的订单按照订单金额分成3组
此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用nfile(n)对数据进行分组，由于记录数不一定被n整除，所以数据不一定完全平均，然后将不同桶号的数据再分配。

收起

展开全文

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 开窗函数_开窗函数（分析函数）
2021-01-12 14:45:38
开窗函数(分析函数)2.7.1 特点：开窗函数也就是在满足某种条件的记录集合上执行的特殊函数；对于每条记录都要在此窗口内执行函数；有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，...
收起

开窗函数

oracle开窗函数_SQL开窗函数

mysql分析函数开窗函数_开窗函数（分析函数）

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

文章目录

SparkSQL 开窗函数

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

开窗函数分类

聚合开窗函数

排序开窗函数

1，ROW_NUMBER()顺序排序

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

3，DENSE_RANK() 连续排序

4，NTILE() 分组排名

rank 开窗函数_开窗函数（分析函数）

开窗函数