实用sql技巧，开窗函数
最近在牛客解sql题目时，经常有一类题目要求对表中的某一列进行排序
在查看提供的题解时经常看到使用开窗函数的解决方案
虽然不用开窗函数也能通过子查询的方式获取正确答案，但是
比较复杂难以理解，
所以很有必要学习
原题放送
在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:
第1行表示id为1的用户通过了4个题目;
…
第6行表示id为6的用户通过了4个题目;
请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下:
思考：
题目只有一张表，两个字段，看似很简单，
如果没有t_rank要组合的话只需要
select*from passing_number order by number desc;
但我们现在需要组合出t_rank,
在不使用开窗函数情况下的解题思路：
我开始时想通过聚合函数构造一个字段，但事情并不简单，需要得出排名并非能够通过一个聚合解决
需要使用通过子查询来得出排名的值
具体实现（参考该题的第一名的解答）：
要得到通过题目的排名，比如你通过了8分,，你同学a也通过了8分，找到大于等于你的成绩，一个9分，一个8分，一个8分，去重复，就一个9，一个8，count一下总数，第2名，如果有三个同学通过了7个呢，同理，9，8，8，7，7，7 后面比这个少的，已经死在了筛选条件，去重，9，8，7，count=3，
确实，如果我们得了10分，没有人超过我们，那么找到大于等于我的成绩，及10，count一下就是1了，即第一名，
故总结为
select COUNT(DISTINCT number) from passing_number where passing_number.number>=X
#X为我们的得分数
如此，加上最初的那句简单sql，即可以得出本题的一种答案
select a.id,a.number,
(select COUNT(DISTINCT b.number) from passing_number as b where b.number >= a.number) from passing_number as a order by a.number desc;
#将外查询的number的值给内查询作为参数即可通过内查询得出该分数对应的排名
显然这个逻辑还是有一点复杂的，
下面先显示使用开窗函数的答案
SELECT
*,
dense_rank() over ( ORDER BY number DESC ) `rank`
FROM
passing_number
ORDER BY
`rank`,
id;
这个答案也可以达到一样的效果
这个sql语句则简洁很多，现在需要理解的就只是这个开窗函数了
开窗函数
前言：老版本的mysql并不支持开窗函数，如需使用请使用mysql8以上的版本
开窗函数的定义：
它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。
开窗函数与聚合函数：
聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条。
聚合函数也可以用于开窗函数中。
开窗函数的使用
从开窗函数的定义中，可以了解到开窗函数比普通的聚合函数更加的灵活，功能也更加强大。但并不是很好理解，那么先从它的使用开始吧
开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])
开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由
over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：
partition by子句：
按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
order by子句：
按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
frame子句：
当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
常用的开窗函数：
这里直接在官网查了一下，网址如下MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions
使用案例
以上大部分函数的使用效果用例都可以在上面的官网中看到示例，
下面是在实际中的使用：
如上面的原题，
分析其开窗函数的含义：
SELECT
*,
dense_rank() over ( ORDER BY number DESC ) `rank`
FROM
passing_number
ORDER BY
`rank`,
id;
#分析：使用的是dense_rank()即不跳排名的方式计算所有行（因为其并没有写partition by...）的排名通过number的倒序
突然发现了新东西貌似比较有趣，明天接着更
具体使用：
构造一张表，在其中插入一些信息
create table `studentgrade`(
id int(11) PRIMARY KEY auto_increment,
sname VARCHAR(20),
sex VARCHAR(5),
theclass VARCHAR(20),
grade_ch_zn DOUBLE(5,2),
grade_en DOUBLE(5,2),
grade_math DOUBLE(5,2)
)engine = InnoDB charset = utf8 auto_increment = 1;
insert into studentgrade(sname,sex,theclass,grade_ch_zn,grade_en,grade_math) values('billy','man','classOne','111.11','78.43','103.50');
insert into studentgrade(sname,sex,theclass,grade_ch_zn,grade_en,grade_math) values('Tony','man','classTwo','95.76','105.23','77.64');
insert into studentgrade(sname,sex,theclass,grade_ch_zn,grade_en,grade_math) values('Lily','woman','classOne','124.75','80.56','101.45');
insert into studentgrade(sname,sex,theclass,grade_ch_zn,grade_en,grade_math) values('Mark','man','classOne','105.39','90.21','109.67');
insert into studentgrade(sname,sex,theclass,grade_ch_zn,grade_en,grade_math) values('Lisa','woman','classTwo','99.50','112.45','101.45');
排名函数
我们先利用几个开窗函数中的排名函数来测试一下：
select sname,dense_rank() over (order by grade_math desc) as `数学成绩排名(名次连续)`,
rank() over(order by grade_math desc) as `数学成绩排名(名次不连续)`,
percent_rank() over (order by grade_math) as `排名百分比`,
row_number() over (order by id) as `记录所在的行号`
from studentgrade order by `数学成绩排名(名次连续)`;
#我的over里没写partition by部分则默认是表中所有的行，
#使用order by 为dense_rank()函数指定排序条件即按数学成绩的高分顺序计算排序
#percent_rank()函数的计算规则为(rank - 1) / (rows - 1)，rows为分区的行数.
#在over字句里没有指定分区，则分区为整张表，即rows=表的行数，即rows = 5
#此次的rank即按percent_rank()的over字句条件算出的rank()
#即以Mark学生为例，他的rank 按 grade_math的正序排正好为5
#所以Mark学生的percent_rank()的值即为（5-1）/(5-1) = 1;
#其他学生即按上类推
#突然发现此处的percent_rank()可以解释为数学成绩超越总记录的百分之多少
结果：
累计分布和ntile函数
再测试一下计算累计分布值，和Bucket number的开窗函数
我先查一下bucket number，发现网上并没有这个东西的准确信息，只能在官网里看其定义了
以上表的数据测试一下：
select sname,cume_dist() over w,ntile(2) over w
from studentgrade WINDOW w as (order by grade_math);
#计算累积分布值，与查询被分成两个桶的记录们的桶号
#Window关键子的用法是给当前窗口起别名
Window关键字的用法可以参考
MySQL8中的关键字window与窗口函数_程序员先森的博客-优快云博客
结果为
LAG与LEAD函数（前后）
测试：
select sname,grade_math,lag(grade_math) over ( order by grade_math) as `滞后一位的成绩`,
lead(grade_math) over ( order by grade_math) as `向前一位的成绩`
from studentgrade order by id;
#将grade_math传入lag和lead作为参数，则可以计算出分区（没填默认为整张表）中
#在参数值前后的值
结果：
分区的使用
通过partition by 关键字确定开窗函数的分区，
例：
select *,avg(grade_ch_zn) over (partition by theclass order by grade_ch_zn) as `前一个学生和后一个学生的语文平均分`
from studentgrade;
#指定theclass属性作为分区的条件，即一个相同的theclass值为一组进行计算，当分区值（即theclass值）变化后#则重新初始化计算
#yan (105.39 + 111.11)/2 = 108.25
结果：
Frame字句的格式
官网的介绍：MySQL :: MySQL 8.0 Reference Manual :: 12.21.3 Window Function Frame Specification
与窗口函数一起使用的窗口的定义可以包括Frame子句。 Frame是当前分区的子集，Frame子句指定如何定义子集。
Frame是相对于当前行确定的，这使得Frame能够根据当前行在其分区内的位置在分区内移动。
例子： 通过将一个框架定义为从分区开始到当前行的所有行，您可以计算每行的运行总数。
通过将框架定义为在当前行的任一侧扩展 N 行，您可以计算滚动平均值。
官网的介绍很复杂，这里有一篇更优秀的文章可以参考
https://blog.youkuaiyun.com/kejiayuan0806/article/details/103297893
扫一扫
1796
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
