MYSQL8.0新特性窗口函数

CREATE TABLE `SQL_6`(
cid varchar (64),
sname varchar (4),
course varchar(10),
score int
);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','张三','语文',78);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小刚','语文',71);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','李四','数学',56);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','王五','数学',97);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小明','数学',54);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小刚','数学',67);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小红','数学',82);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','王五','语文',80);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','张三','数学',77);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小明','语文',58);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小红','语文',87);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','李四','语文',60);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','张三','英语',66);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小刚','英语',50);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','李四','地理',59);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','王五','地理',88);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小刚','地理',45);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小明','地理',66);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小红','地理',82);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','王五','英语',81);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','张三','地理',77);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小明','英语',55);
INSERT INTO sql_6(cid,sname,course,score) VALUES('002','小红','英语',87);
INSERT INTO sql_6(cid,sname,course,score) VALUES('001','李四','英语',61);

CREATE TABLE `student` (
  `cid` varchar(64) DEFAULT NULL COMMENT '班级（ID）',
  `sname` varchar(64) DEFAULT NULL COMMENT '学生姓名',
  `score` varchar(32) DEFAULT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一、窗口函数

可以看到over()里面可以进行partition分区(就是分组的效果)、order by可以排序、后面有个RANGE字段放在第三点讲，这个RANGE或者ROW会影响over的作用效果

这个over()就是所谓的窗口，我们可以在over()中控制滑动窗口的范围，从而取到不同的值

1、over(partition by 分组列名 ORDER BY 排序列名 升序或者降序  rows between unbounded preceding AND current ROW)

2、over(partition by 分组列名 rows BETWEEN unbounded preceding and unbounded following)

3、partition by 分组列名

4、rows ... between ... and ...

select *,sum(score) over (partition by cid ORDER BY score ASC rows between unbounded preceding AND current ROW) as '班级总分' from student;

二、函数分类

1、排序类

不可并列排名：ROW_NUMBER()；

跳跃可并列排名：RANK()；

连续可并列排名：DENSE_RANK()

列子如下：

SELECT *,ROW_NUMBER() over(partition by cid ORDER BY score DESC) as '不可并列排名',
      RANK() over(partition by cid ORDER BY score DESC) as '跳跃可并列排名',
			DENSE_RANK() over(partition by cid ORDER BY score DESC) as '连续可并列排名' from student; 

2、聚合类

函数：sum()、avg()、min()、max()、count()

列子：

select *,sum(score) over (partition by cid) as '班级总分' from student;

select *,sum(score) over (partition by cid rows BETWEEN unbounded preceding and unbounded following) as '班级总分' from student;

select *,sum(score) over (partition by cid ORDER BY score ASC) as '班级总分' from student;

select *,sum(score) over (partition by cid ORDER BY score ASC rows between unbounded preceding AND current ROW) as '班级总分' from student;

3、跨行类

后置函数：lag()；

前值函数：lead();

列子：

-- 同一班级内，成绩比自己低一名的分数是多少
SELECT *,LAG(score,1) over(partition by cid ORDER BY score) as '低一名的分数是多少' from student;

SELECT *,LAG(score,1,0) over(partition by cid ORDER BY score) as '低一名的分数是多少' from student;

-- 同一班级内，成绩比自己高2名的分数是多少
SELECT *,LEAD(score,2) over(partition by cid ORDER BY score) as '高2名的分数是多少' from student;

SELECT *,LEAD(score,2,0) over(partition by cid ORDER BY score) as '高2名的分数是多少' from student; 

CREATE TABLE SQL_9 ( player_id VARCHAR ( 2 ), score INT, score_time datetime );
INSERT INTO SQL_9 ( player_id, score, score_time )
VALUES
	( 'B3', 1, '2022-09-20 19:00:14' ),
	( 'A2', 1, '2022-09-20 19:01:04' ),
	( 'A2', 3, '2022-09-20 19:01:16' ),
	( 'A2', 3, '2022-09-20 19:02:05' ),
	( 'A2', 2, '2022-09-20 19:02:25' ),
	( 'B3', 2, '2022-09-20 19:02:54' ),
	( 'A4', 3, '2022-09-20 19:03:10' ),
	( 'B1', 2, '2022-09-20 19:03:34' ),
	( 'B1', 2, '2022-09-20 19:03:58' ),
	( 'B1', 3, '2022-09-20 19:04:07' ),
	( 'A2', 1, '2022-09-20 19:04:19' ),
	( 'B3', 2, '2022-09-20 19:04:31' );


-- 求连续三次得分的球员
WITH t1 AS
(
SELECT *,lag(player_id,1) over(ORDER BY score_time) AS last_player_id from player
)
SELECT DISTINCT player_id from t1 WHERE player_id = last_player_id GROUP BY player_id HAVING count(player_id) >= 2;

如何求连续区间? 

-- 求每个用户连续三天登录的
WITH t0 AS
(
SELECT DISTINCT user_id,login_date from sql_8
),
t1 AS 
(
SELECT *,lag(login_date,1) over(partition by user_id ORDER BY login_date) AS last_login_date,
					lag(login_date,2) over(partition by user_id ORDER BY login_date) AS last_two_login_date from t0 
),
t2 AS(
SELECT *,DATEDIFF(login_date,last_login_date) AS diff,
					DATEDIFF(login_date,last_two_login_date) AS diff2 from t1
					)

SELECT DISTINCT user_id from t2 WHERE diff = 1 AND diff2 = 2 GROUP BY user_id

CREATE TABLE log(
1og_id int
);
INSERT INTO log (1og_id) 
VALUES 
(1),
(2),
(3),
(7),
(8),
(10);

-- 编写SQL查询得到logs表中的连续区间的开始数字和结束数字。按照start_id排序
WITH t1 AS
(
SELECT *,log_id - ROW_NUMBER() over(ORDER BY log_id) as diff from log
)
SELECT min(log_id) AS start_id,max(log_id) AS end_id from t1 GROUP BY diff 

技巧
如何求连续区间?
1)行号过滤法
通过row_ number()生成连续行号，与区间列进行差值运算，得到的临时结果如果相同表示为同一连续区间
2)错位比较法
通过row_ number() / row_ number() + 1分别生成原生的和错位的连续行号列，进行连表操作
也可以通过lag/lead函数直接生成错位列