开窗函数和窗口函数_CLOSE和OPEN开关

开窗函数和窗口函数_CLOSE和OPEN开关开窗函数,窗口范围ROWS与RANGE_窗口函数range

一、函数写法

函数名(参数) 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 最后一行的值和第一行的值

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 ;

在这里插入图片描述

今天的文章开窗函数和窗口函数_CLOSE和OPEN开关分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/88888.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注