窗口函数OVER(PARTITION BY)详细用法 |
您所在的位置:网站首页 › space函数的语法和用法 › 窗口函数OVER(PARTITION BY)详细用法 |
目录 一、函数写法 二、开窗的窗口范围ROWS与RANGE 1.范围限定用法 2.ROWS和RANGE的区别 (1) ROWS按行数限定 (2) RANGE按数据范围限定 order by 数字 例1 汇总数据范围为:[当前行值,当前行值+3] 例2 汇总数据范围为:[当前行值-3,当前行值] order by 时间 例1 [当前行日期,当前行日期+2] 例2 [当前行日期-2,当前行日期] 三、函数介绍 1.排序函数 2.聚合函数 3.比较函数 例1 lag 偏移为负数offset=-1 例2 lag取出向前第0行,即偏移为0 例3 lag取出向前第2行,即偏移为2 例4 换个字段,lag取出向前第2行,即向前偏移为2 例5 lead取出向后第2行,即向后偏移2 例6 lead取出向后第2行,即向后偏移2,不加默认值 一、函数写法函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句) 由三部分组成:函数名:如sum、max、min、count、avg等聚合函数以及lead、lag行比较函数等;over: 关键字,表示前面的函数是分析函数,不是普通的集合函数;分组子句:over关键字后面挂号内的内容; 分析子句又由下面三部分组成:PARTITION BY :分组子句,表示分析函数的计算范围,不同的组互不相干;ORDER BY: 排序子句,表示分组后,组内的排序方式;ROWS/RANGE:窗口子句,是在分组(PARTITION BY)后,组内的子分组(也称窗口),此时分析函数的计算范围窗口,而不是PARTITON。窗口有两种,ROWS和RANGE; 二、开窗的窗口范围ROWS与RANGE 1.范围限定用法 CURRENT ROW: 当前行UNBOUNDED:不受控制的,无限的UNBOUNDED PRECEDING: 区间的第一行UNBOUNDED FOLLOWING:区间的最后一行UNBOUNDED PRECEDING AND UNBOUNED FOLLOWING:针对当前所有记录的前一条、后一条记录,分组中的所有记录PRECEDING:在...之前,N PRECEDING: 当前行之前的N行,可以是数字用于RANGE数据范围限定,也可以是一个能计算出数字的表达式FOLLOWING:在...之后,N FOLLOWING:当前行之后的N行,可以是数字用于RANGE数据范围限定,也可以是一个能计算出数字的表达式ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW :指第一行至当前行的数据ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING :指当前行到最后一行的汇总ROWS BETWEEN 1 PRECEDING AND CURRENT ROW :指当前行的上一行(ROWNUM-1)到当前行的数据ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING :指当前行的上一行(ROWNUM-1)到当前行的下一行(ROWNUM+1)的数据RANGE BETWEEN CURRENT ROW AND 350 FOLLOWING:指当前行到当前行数据+350的范围内的数据RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING:指当前行数据幅度减5加5后的范围内的数据 2.ROWS和RANGE的区别ROWS按行数限定 RANGE按数据范围限定 (1) ROWS按行数限定表结构及测试数据: DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `video_id` int(0) NOT NULL COMMENT '视频ID', `dt` date NULL DEFAULT NULL, `if_follow` tinyint(0) NULL DEFAULT NULL COMMENT '是否关注' ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO `test` VALUES (2001, '2021-09-24', 1); INSERT INTO `test` VALUES (2001, '2021-10-03', 1); INSERT INTO `test` VALUES (2001, '2021-10-02', 1); INSERT INTO `test` VALUES (2001, '2021-10-01', 1); INSERT INTO `test` VALUES (2002, '2021-09-25', 1); INSERT INTO `test` VALUES (2002, '2021-09-25', 1); INSERT INTO `test` VALUES (2002, '2021-09-26', 1); INSERT INTO `test` VALUES (2002, '2021-09-27', 1); INSERT INTO `test` VALUES (2002, '2021-09-28', 1); INSERT INTO `test` VALUES (2002, '2021-09-29', 1); INSERT INTO `test` VALUES (2002, '2021-09-30', 1); INSERT INTO `test` VALUES (2002, '2021-10-01', 1); INSERT INTO `test` VALUES (2002, '2021-10-02', 1); INSERT INTO `test` VALUES (2002, '2021-10-03', 1);语句: select video_id,dt, sum(if_follow) over(partition by video_id order by dt rows BETWEEN CURRENT ROW and 1 following ) from test ; (2) RANGE按数据范围限定表结构及测试数据: DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `video_id` int(0) NOT NULL COMMENT '视频ID', `dt` date NULL DEFAULT NULL, `if_follow` tinyint(0) NULL DEFAULT NULL COMMENT '是否关注' ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO `test` VALUES (2001, '2021-09-24', 1); INSERT INTO `test` VALUES (2001, '2021-10-03', 9); INSERT INTO `test` VALUES (2001, '2021-10-02', 2); INSERT INTO `test` VALUES (2001, '2021-10-01', 6); INSERT INTO `test` VALUES (2002, '2021-09-25', 1); INSERT INTO `test` VALUES (2002, '2021-09-25', 1); INSERT INTO `test` VALUES (2002, '2021-09-26', 6); INSERT INTO `test` VALUES (2002, '2021-09-27', 1); INSERT INTO `test` VALUES (2002, '2021-09-28', 1); INSERT INTO `test` VALUES (2002, '2021-09-29', 8); INSERT INTO `test` VALUES (2002, '2021-09-30', 7); INSERT INTO `test` VALUES (2002, '2021-10-01', 1); INSERT INTO `test` VALUES (2002, '2021-10-02', 9); INSERT INTO `test` VALUES (2002, '2021-10-03', 1);下面这个语句执行会报错,因为当RANGE和PRECEDING/FOLLOWING一起使用时,order by的表达式必须为数字或者时间差 select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN 3 preceding and CURRENT ROW ) from test ;报错内容如下: select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN 3 preceding and CURRENT ROW ) from test > 3587 - Window '' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type order by 数字 例1 汇总数据范围为:[当前行值,当前行值+3] select video_id,dt, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN CURRENT ROW and 3 following) from test ; 例2 汇总数据范围为:[当前行值-3,当前行值] select video_id,dt, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN 3 PRECEDING and CURRENT ROW ) from test ; order by 时间order by表达式的类型为时间(date、datetime)时,必须使用Interval 例1 [当前行日期,当前行日期+2] select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN CURRENT ROW and interval 2 day following) from test ; 例2 [当前行日期-2,当前行日期] select video_id,dt, sum(if_follow) over(partition by video_id order by dt range BETWEEN interval 2 day PRECEDING and CURRENT ROW ) from test ; 三、函数介绍下面是mysql中能使用的 1.排序函数rank()函数,如果有并列情况,会占用下一个名次的位置,比如,成绩为100的学生有三个并列第一,那么99分的学生是第二名,通过rank()函数,名次是:1,1,1,4;dense()函数,如果有并列的情况,不会占用下一个名词,同用上个例子,名次是:1,1,1,2;row_number()函数,会忽略并列的情况,同用上述例子,名次是:1,2,3,4; 2.聚合函数count() over(partition by ... order by ...):求分组后的总数;max() over(partition by ... order by ...):求分组后的最大值;min() over(partition by ... order by ...):求分组后的最小值;avg() over(partition by ... order by ...):求分组后的平均值; 3.比较函数lag() over(partition by ... order by ...):取出向前第n行数据。 lead() over(partition by ... order by ...):取出向后第n行数据。 lag(arg1,arg2,arg3)、lead(arg1,arg2,arg3) 第一个参数是列名, 第二个参数是偏移的offset,不能为负数, 第三个参数是超出记录窗口时的默认值。 表结构及测试数据: DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `video_id` int(0) NOT NULL COMMENT '视频ID', `dt` date NULL DEFAULT NULL, `if_follow` tinyint(0) NULL DEFAULT NULL COMMENT '是否关注' ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO `test` VALUES (2001, '2021-09-24', 1); INSERT INTO `test` VALUES (2001, '2021-10-03', 9); INSERT INTO `test` VALUES (2001, '2021-10-02', 2); INSERT INTO `test` VALUES (2001, '2021-10-01', 6); INSERT INTO `test` VALUES (2002, '2021-09-25', 1); INSERT INTO `test` VALUES (2002, '2021-09-25', 1); INSERT INTO `test` VALUES (2002, '2021-09-26', 6); INSERT INTO `test` VALUES (2002, '2021-09-27', 1); INSERT INTO `test` VALUES (2002, '2021-09-28', 1); INSERT INTO `test` VALUES (2002, '2021-09-29', 8); INSERT INTO `test` VALUES (2002, '2021-09-30', 7); INSERT INTO `test` VALUES (2002, '2021-10-01', 1); INSERT INTO `test` VALUES (2002, '2021-10-02', 9); INSERT INTO `test` VALUES (2002, '2021-10-03', 1); 例1 lag 偏移为负数offset=-1语法错误,偏移offset,不能为负数 select video_id,dt, lag(dt,-1,'偏移超出了') over(order by dt ) from test ;1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,'偏移超出了') over(order by dt ) from test' at line 1 例2 lag取出向前第0行,即偏移为0 select video_id,dt, lag(dt,0,'偏移超出了') over(order by dt ) from test ;例3 lag取出向前第2行,即偏移为2 select video_id,dt, lag(dt,2,'偏移超出了') over(order by dt ) from test ; 例4 换个字段,lag取出向前第2行,即向前偏移为2 select video_id,dt, lag(video_id,2,'偏移超出了') over(order by dt ) from test ; 例5 lead取出向后第2行,即向后偏移2 select video_id,dt, lead(video_id,2,'偏移超出了') over(order by dt ) from test ; 例6 lead取出向后第2行,即向后偏移2,不加默认值 select video_id,dt, lead(video_id,2) over(order by dt ) from test ; 下面可能是Oracle函数,mysql没能使用出来: first_value() over()和last_value() over(),分别是求分组中第一个和最后一个 ratio_to_report() over(partition by ... order by ...):ratio_to_report() 括号中就是分子,over() 括号中就是分母 percent_rank() over(partition by ... order by ...) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |