oracle开窗函数_SQL开窗函数

19a2e54ddfa6e1cd7bf69b32a7a97937.png

在开窗函数出现之前存在着很多 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在2003年ISO  SQL标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 SQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持

普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中的每行都返回一个值。

简单理解,就是对查询的结果多出一列,这一列可以是聚合值,也可以是排序值。开窗函数一般分为三类,聚合开窗函数,排序开窗函数和分桶开窗函数

2aefc61575178df22ccdecf5dd6275b7.png

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;这个是排序后数行数

496944f79d7cd3e2f7e1877c14410919.png496944f79d7cd3e2f7e1877c14410919.png

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行)的汇总 

e5e097e000e01c134420b72a4d1d3357.pnge5e097e000e01c134420b72a4d1d3357.png

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行)的所有行最小值

f485fdeae69c200219395bb4551ee528.pngf485fdeae69c200219395bb4551ee528.png

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行)的所有行最大值

0be86546edca2905ffdd23ef6cb27ff9.png0be86546edca2905ffdd23ef6cb27ff9.png

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行)的所有行平均值

15e970acc505d9bd10000f98594745b4.png15e970acc505d9bd10000f98594745b4.png

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行)的所有行第一个值

208505301d537b7c80d1601f03a507e7.png208505301d537b7c80d1601f03a507e7.png

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行)的所有行第一个值

62c5a59e0ca3decfbd773bb430957791.png62c5a59e0ca3decfbd773bb430957791.png

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

6321cf7c624e9949ffdb11d23c460da9.png6321cf7c624e9949ffdb11d23c460da9.png

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

e2bef431ffdbb821929c15590b43278b.pnge2bef431ffdbb821929c15590b43278b.png

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

37dca7d528c1088a18ce8b1bc6f5bae1.png37dca7d528c1088a18ce8b1bc6f5bae1.png

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

9928dc669b1abfedb358a9c1e22b46ac.png9928dc669b1abfedb358a9c1e22b46ac.png

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

d41e5e6eec78459ff378e89fa90f3ec5.pngd41e5e6eec78459ff378e89fa90f3ec5.png

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

861a74d7b36e404db46dfdad8d85fd42.png861a74d7b36e404db46dfdad8d85fd42.png

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

1dacc20e93af44611d5cdf36ad09bcc2.png1dacc20e93af44611d5cdf36ad09bcc2.png

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

d302f7131e7548d049ca7ceeacbc1c3c.pngd302f7131e7548d049ca7ceeacbc1c3c.png

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页