目录
一、函数写法
二、开窗的窗口范围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.比较函数
例1lag 偏移为负数offset=-1
例2lag取出向前第0行，即偏移为0
例3lag取出向前第2行，即偏移为2
例4换个字段，lag取出向前第2行，即向前偏移为2
例5lead取出向后第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 BETWEENCURRENT ROW and3 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 BETWEENCURRENT ROW andinterval 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);例1lag 偏移为负数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
例2lag取出向前第0行，即偏移为0
select video_id,dt, lag(dt,0,'偏移超出了') over(order by dt ) from test ;
例3lag取出向前第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 ;
例5lead取出向后第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 …)