MySQL窗口函数

MySQL窗口函数

一.什么是窗口函数

基本含义

窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也是在窗口范围内执行的函数

基本语法

窗口函数有over关键字，指定函数执行的范围，可分为三部分，分组子句(partition by)，

排序子句(order by)，窗口子句(rows)

<函数名> over (partition by <分组的列> order by <排序的列> rows between <起始行> and <终止行>)

注意Mysql8才支持窗口函数

二.窗口的确定

演示表格

cid(班级id)	sname（学生姓名）	score（分数）
001	张三	78
001	李四	82
002	小明	90
001	王五	67
002	小红	85
002	小刚	62

例子：

select *, sum(score) over (partition by cid order by score rows between unbounded preceding and unbounded following) as '班级总分' from SQL_S

-- 上面的sql等价于
select *, sum(socre) over (partition by cid) as '班级总分' from SQL_S

-- 也就是说默认不写rows子句，就是上面的第一个sql的样子

													-- 可以对比用group by的写法			
							select cid, sum(score) from SQL_S group by cid

分组子句（partition by）

不分组可以写成partition by null或者直接不写

后面可以跟多个列，如partition by cid, sname

注意partition by与group by的区别

1）前者不会压缩行，但是数后者会

2）后者只能选取分组的列和聚合的列进行查询，也就是说group by后生成的结果集与原生表的行数和列数都不同

排序子句（order by）

不排序可以写成order by null或者直接不写

asc或不写表示升序，desc表示降序

后面可以跟多个列，如order by cid, sname

窗口子句（rows）

窗口子句的描述

1）起始行：N preceding/unbounded preceding（以当前行往上推，直到推不动。上边界）

2）当前行：current row

3）终止行：N following/unbounded following（以当前行往下推，直到推不动。下边界）

举例：

unbounded preceding and current row：从之前所有的行到当前行

unbounded following and current row：从之后所有的行到当前行

2 preceding and current row：从前面2行到当前行

current row and 1 following：从当前行到后面一行

current row and unbounded following：从当前行到之后所有的行

current row and unbounded preceding：从当前行到之前所有的行

-- 加上order by，独立排序
select *, sum(score) over (partition by cid order by score) as '班级总分' from SQL_S

-- 上面sql语句等价于
select *, sum(score) over (partition by cid order by score unbounded preceding and current row) as '班级总分' from SQL_S

三.函数分类

1.排序类

rank，dense_rank，row_number

-- 按班级分组后打上序列，不考虑并列
select *, row_number() over (partition by cid order by score desc) '不可并列'

-- 按班级分组后作跳跃排名，考虑并列
select *, rank() over (partition by cid order by score desc) '跳跃可并列'

-- 按班级分组后连续排名，考虑并列
select *, dense_rank() over (partition by cid order by score desc) '连续可并列 

2.聚合类

sum，avg，count，max，min

3.跨行类

lag（置后），lead（领先）

-- 【跨行类】
-- lag/lead函数 
/*
  参数1：比较的列
  参数2：偏移量
  参数3：找不到的默认值
*/

-- 同一班级内，成绩比自己第一名的分数是多少
select *, lag(score, 1, 0) over (partition by cid order by score) as '低一名的分数' from SQL_S

-- 同一班级内，成绩比自己高两名的分数是多少
select *, lead(score, 2, 0) over (partition by cid order by score) as '高两名的分数' from SQL_S

datediff()

定义：是用于计算两个日期之间间隔的函数。它接受三个参数，第一个参数是计算单位，第二个参数是较小的日期，第三个参数是较大的日期，它返回的是两个日期之间间隔的数量。

DATEDIFF() 函数支持的计算单位包括：年 (year)、月 (month)、周 (week)、日 (day)、小时 (hour)、分钟 (minute) 和秒 (second)。

SELECT DATEDIFF('day', '2022-05-15', '2023-05-15') AS days_diff

-- 这个查询会返回 365

四.技巧

1.分组内TopN公式

select * from
(
	select *, row_number() over (partition by 分组列 order by 比较列) as '别名'
    from 表名
) as temp
where '别名' <= N

2.窗口函数 -> 生成辅助列（相当于高级语言的临时变量）

3.with语句 -> 生成临时表（相当于高级语言的局部方法）

把复杂的问题拆分成多个子问题并用临时表去表达