mysql 开窗函数取上一条数据库开窗函数

转载

mob64ca140651e5 2023-12-23 23:27:47

文章标签 mysql 开窗函数取上一条 oracle开窗函数默认值最小值聚合函数 文章分类 MySQL 数据库

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数

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

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

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_02

创建练习库

创建练习库

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;

聚合开窗函数

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_03

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

mysql 开窗函数取上一条数据库开窗函数_mysql 开窗函数取上一条_05

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_07

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

mysql 开窗函数取上一条数据库开窗函数_mysql 开窗函数取上一条_09

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

mysql 开窗函数取上一条数据库开窗函数_默认值_11

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

mysql 开窗函数取上一条数据库开窗函数_mysql 开窗函数取上一条_13

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_15

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_17

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_19

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

mysql 开窗函数取上一条数据库开窗函数_mysql 开窗函数取上一条_21

排序开窗函数

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

mysql 开窗函数取上一条数据库开窗函数_聚合函数_23

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

mysql 开窗函数取上一条数据库开窗函数_mysql 开窗函数取上一条_25

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

mysql 开窗函数取上一条数据库开窗函数_oracle开窗函数_27

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

mysql 开窗函数取上一条数据库开窗函数_mysql 开窗函数取上一条_29

分桶开窗函数

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 开窗函数取上一条数据库开窗函数_默认值_31