一、函数写法
函数名(参数) 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后的范围内的数据
三、练习:
```bash
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是对order by 的值进行判断范围计算的,根据实际的值和当前的值进行判断取定数据范围的。
#preceding 在…之前
#following 在…之后
select dt,video_id,if_follow, sum(if_follow) over(partition by video_id order by if_follow range between 1 preceding and 1 following) as num from test ;
#current row 当前行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN CURRENT ROW and 3 following) as num from test ;
#unbounded preceding 区间的第一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN unbounded preceding and 3 following) as num from test ;
#unbounded following 区间的最后一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN current row and unbounded following) as num from test ;
#unbounded preceding and unbounded following 最后一行的值和第一行的值
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow range BETWEEN unbounded preceding and unbounded following) as num from test ;
row是对order by 的值进行判断范围计算的,根据实际的值和当前的值进行判断取定数据范围的
#preceding 在…之前
#following 在…之后
select dt,video_id,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows between 1 preceding and 1 following) as num from test ;
#current row 当前行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN CURRENT ROW and 3 following) as num from test ;
#unbounded preceding 区间的第一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN unbounded preceding and 3 following) as num from test ;
#unbounded following 区间的最后一行
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN current row and unbounded following) as num from test ;
#unbounded preceding and unbounded following 最后一行的值和第一行的值文章来源:https://www.toymoban.com/news/detail-439742.html
select video_id,dt,if_follow, sum(if_follow) over(partition by video_id order by if_follow rows BETWEEN unbounded preceding and unbounded following) as num from test ;
文章来源地址https://www.toymoban.com/news/detail-439742.html
到了这里,关于开窗函数的使用详解(窗口范围ROWS与RANGE图文详解)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!