先看这段像天书一样的 SQL ,看着就头疼。mys
复制代码SELECT
s1.name,
s1.subject,
s1.score,
sub.avg_score AS average_score_per_subject,
(SELECT COUNT(DISTINCT s2.score) + 1 FROM scores s2 WHERE s2.score > s1.score) AS score_rank
FROM scores s1
JOIN (
SELECT subject, AVG(score) AS avg_score
FROM scores
GROUP BY subject
) sub ON s1.subject = sub.subject
ORDER BY s1.score DESC;
这段SQL是干什么用的呢,就是为了计算一个成绩排名,简直大动干戈啊。
那有没有简化的方法呢?有的。
简化后的版本就是利用今天说的窗口函数。mysql
复制代码SELECT
name,
subject,
score,
AVG(score) OVER (PARTITION BY subject) AS average_score_per_subject,
RANK() OVER (ORDER BY score DESC) AS score_rank
FROM scores
ORDER BY score DESC;
是不是看上去就简洁清晰多了。
下面我们看看是什么样的功能。
首先创建一个表,包含姓名、学科、分数三个字段,用于后面功能的演示。mysql
复制代码CREATE TABLE `scores` (
`name` varchar(20) COLLATE utf8_bin NOT NULL,
`subject` varchar(20) COLLATE utf8_bin NOT NULL,
`score` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
然后向表中插入一些随机记录。mysql
复制代码INSERT INTO scores (name, subject, score) VALUES ('Student1', '化学', 75);
INSERT INTO scores (name, subject, score) VALUES ('Student2', '生物', 92);
INSERT INTO scores (name, subject, score) VALUES ('Student3', '物理', 87);
INSERT INTO scores (name, subject, score) VALUES ('Student4', '数学', 68);
INSERT INTO scores (name, subject, score) VALUES ('Student5', '英语', 91);
INSERT INTO scores (name, subject, score) VALUES ('Student6', '化学', 58);
INSERT INTO scores (name, subject, score) VALUES ('Student7', '物理', 79);
INSERT INTO scores (name, subject, score) VALUES ('Student8', '数学', 90);
INSERT INTO scores (name, subject, score) VALUES ('Student9', '数学', 45);
##什么是窗口函数
在 MySQL 8.x 版本中,MySQL 提供了窗口函数,窗口函数是一种在查询结果的特定窗口范围内进行计算的函数。
很早以前用 Oracle 和 MS SQL 的时候会用到里面的窗口函数,但是用 MySQL 后才发现,MySQL 竟然没有窗口函数,以至于一些负责的统计查询都要用各种子查询、join,层层嵌套,看上去很简单的需求,结果搞得 SQL 语句写的是龙飞凤舞,别人一看跟天书似的。就一个字儿,懵。
窗口函数主要的应用场景是统计和计算,例如对查询结果进行分组、排序和计算聚合,通过各个函数的组合,可以实现各种复杂的逻辑,而且比起 MySQL 8.0之前用子查询、join 的方式,性能上要好得多。
OVER() 是用于定义窗口函数的子句,它必须结合其他的函数才有意义,比如求和、求平均数。而它只用于指定要计算的数据范围和排序方式。mysql
复制代码function_name(...) OVER (
[PARTITION BY expr_list]
[ORDER BY expr_list]
[range]
)
用于指定分区字段,对不同分区进行分析计算,分区其实就列,可以指定一个列,也可以指定多个列。
用于对分区内记录进行排序,排序后可以与「范围和滚动窗口」一起使用。
用于指定分析函数的窗口,包括范围和滚动窗口。
指定窗口的起止行号,使用UNBOUNDED PRECEDING表示起点,UNBOUNDED FOLLOWING表示终点。
例如:mysql
复制代码SUM(salary) OVER (ORDER BY id
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING)
这会计算当前行及之前5行和之后5行的salary总和。
使用了基于当前行的滚动窗口
例如:mysql
复制代码SUM(salary) OVER (ORDER BY id
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
这会计算当前行及之前2行和之后2行的salary总和。
聚合函数
MAX(),MIN(),COUNT(),SUM()等,用于生成每个分区的聚合结果。
排序相关
ROW_NUMBER(),RANK(),DENSE_RANK()等,用于生成每个分区的行号或排名。
窗口函数
LAG(),LEAD(),FIRST_VALUE(),LAST_VALUE()等,用于基于窗口框生成结果。
1、按subject
列进行分区,并求出某学科的最大最小值
获取分数和此学科最高分mysql
复制代码SELECT subject,score, MAX(score) OVER (PARTITION BY subject) as `此学科最高分` FROM scores;
得出的结果是:
subject | score | 此学科最高分 |
---|---|---|
化学 | 75 | 75 |
化学 | 58 | 75 |
数学 | 68 | 90 |
数学 | 90 | 90 |
数学 | 45 | 90 |
物理 | 87 | 87 |
物理 | 79 | 87 |
生物 | 92 | 92 |
英语 | 91 | 91 |
2、获取学科的报名人数mysql
复制代码SELECT subject,score, count(name) OVER (PARTITION BY subject) as `报名此学科人数` FROM scores;
得到的结果为:
subject | score | 报名此学科人数 |
---|---|---|
化学 | 75 | 2 |
化学 | 58 | 2 |
数学 | 68 | 3 |
数学 | 90 | 3 |
数学 | 45 | 3 |
物理 | 87 | 2 |
物理 | 79 | 2 |
生物 | 92 | 1 |
英语 | 91 | 1 |
3、求学科的总分mysql
复制代码SELECT subject, SUM(score) OVER (PARTITION BY subject) as `此学科总分` FROM scores;
得到的结果:
subject | 此学科总分 |
---|---|
化学 | 133 |
化学 | 133 |
数学 | 203 |
数学 | 203 |
数学 | 203 |
物理 | 166 |
物理 | 166 |
生物 | 92 |
英语 | 91 |
4、使用 order by 求累加分数mysql
复制代码SELECT name,subject,score, SUM(score) OVER (order BY score) as `累加分数` FROM scores;
得到的结果:
name | subject | score | 累加分数 |
---|---|---|---|
Student9 | 数学 | 45 | 45 |
Student6 | 化学 | 58 | 103 |
Student4 | 数学 | 68 | 171 |
我们看这是怎么算出来的,OVER 函数里面是 order by 。
首先根据分数排序(默认升序),得到第一行分数是45,所以累加分数就是它自己,也就是45。
然后排序得到第二行 58,然后将第一行和第二行相加,这样得到累加分数就是45+58=103。
同理,第三行就是前三行的总和,也就是45+58+68=171。
以此类推,第 N 行就是1~N的累加和。
5、使用 order by + 范围
前面因为没有限定范围,所以就是前 N 行的累加,还可以限定范围。mysql
复制代码SELECT name,subject,score, SUM(score) OVER (order BY `score` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `累加分数` FROM scores;
这里的累加分数是指当前行+前一行+后一行的和。
获取的结果为:
name | subject | score | 累加分数 |
---|---|---|---|
Student9 | 数学 | 45 | 103 |
Student6 | 化学 | 58 | 171 |
Student4 | 数学 | 68 | 201 |
Student1 | 化学 | 75 | 222 |
Student7 | 物理 | 79 | 241 |
Student3 | 物理 | 87 | 256 |
Student8 | 数学 | 90 | 268 |
Student5 | 英语 | 91 | 273 |
第一行 103,是当前行 45+后一行(58)的和,等于103,因为没有前一行。
第二行171,是当前行58+前一行(45)+后一行(68)的和,等于171。
以此类型,后面的累加分数都是这样算出来的。
ROW_NUMBER() 函数用于为结果集中的每一行分配一个唯一的排序。
如下,对成绩进行排名,分数高的排在前面,如果有两个人分数相同,那仍然是一个第一,另一个第二。sql
复制代码SELECT name,subject,score, ROW_NUMBER() OVER (order BY `score` desc) as `排名` FROM scores;
查询结果为:
name | subject | score | 排名 |
---|---|---|---|
Student2 | 生物 | 92 | 1 |
Student5 | 英语 | 91 | 2 |
Student8 | 数学 | 90 | 3 |
Student3 | 物理 | 87 | 4 |
Student7 | 物理 | 79 | 5 |
如果不用 ROW_NUMBER()
,比如在 MySQL 5.7的版本中,就会像下面这样:mysql
复制代码SELECT s1.name, s1.subject, s1.score, COUNT(s2.score) + 1 AS `排名`
FROM scores s1
LEFT JOIN scores s2 ON s1.score < s2.score
GROUP BY s1.name, s1.subject, s1.score
ORDER BY s1.score DESC;
是不是比使用 ROW_NUMBER()
复杂的多。
RANK() 函数用于为结果集中的每一行分配一个排名值,它也是排名的,但是它和 ROW_NUMBER()
有,RANK()
函数在遇到相同值的行会将排名设置为相同的,就像是并列排名。
就像是奥运比赛,如果有两个人都是相同的高分,那可能就是并列金牌,但是这时候就没有银牌了,仅次于这两个人的排名就会变成铜牌。sql
复制代码SELECT name,subject,score, RANK() OVER (order BY `score` desc) as `排名` FROM scores;
查询结果为:
name | subject | score | 排名 |
---|---|---|---|
Student1 | 化学 | 92 | 1 |
Student2 | 生物 | 92 | 1 |
Student5 | 英语 | 91 | 3 |
Student8 | 数学 | 90 | 4 |
Student3 | 物理 | 87 | 5 |
DENSE_RANK() 也是用作排名的,和 RANK()
函数的差别就是遇到相同值的时候,不会跳过排名,比如两个人是并列金牌,排名都是1,那仅次于这两个人的排名就是2,而不像 RANK()
那样是3。sql
复制代码SELECT name,subject,score, DENSE_RANK() OVER (order BY `score` desc) as `排名` FROM scores;
查询结果为:
name | subject | score | 排名 |
---|---|---|---|
Student1 | 化学 | 92 | 1 |
Student2 | 生物 | 92 | 1 |
Student5 | 英语 | 91 | 2 |
Student8 | 数学 | 90 | 3 |
NTILE() 函数用于将结果集划分为指定数量的组,并为每个组分配一个编号。例如,将分数倒序排序并分成4个组,相当于有了4个梯队。sql
复制代码SELECT name,subject,score, NTILE(4) OVER (order BY `score` desc) as `组` FROM scores;
查询结果为:
name | subject | score | 组 |
---|---|---|---|
Student1 | 化学 | 92 | 1 |
Student2 | 生物 | 92 | 1 |
Student5 | 英语 | 91 | 1 |
Student8 | 数学 | 90 | 2 |
Student3 | 物理 | 87 | 2 |
Student7 | 物理 | 79 | 3 |
Student4 | 数学 | 68 | 3 |
Student6 | 化学 | 58 | 4 |
Student9 | 数学 | 45 | 4 |
LAG() 函数用于在查询结果中访问当前行之前的行的数据。它允许您检索前一行的值,并将其与当前行的值进行比较或计算差异。LAG()
函数对于处理时间序列数据或比较相邻行的值非常有用。
LAG()
函数完整的表达式为 LAG(column, offset, default_value)
,包含三个参数:
column:就是列名,获取哪个列的值就是哪个列名,很好理解。
offset: 就是向前的偏移量,取当前行的前一行就是1,前前两行就是2。
default_value:是可选值,如果向前偏移的行不存在,就取这个默认值。
例如比较相邻两个排名的分数差,可以这样写:sql
复制代码SELECT
name,
subject,
score,
ABS(score - LAG(score, 1,score) OVER (ORDER BY score DESC)) AS `分值差`
FROM
scores;
得到的结果为:
name | subject | score | 分值差 |
---|---|---|---|
Student1 | 化学 | 92 | 0 |
Student2 | 生物 | 92 | 0 |
Student5 | 英语 | 91 | 1 |
Student8 | 数学 | 90 | 1 |
Student3 | 物理 | 87 | 3 |
Student7 | 物理 | 79 | 8 |
Student4 | 数学 | 68 | 11 |
LEAD()
函数和 LAG()
的功能一致,只不过它的偏移量是向后偏移,也就是取当前行的后 N 行。
所以前面的比较相邻两行差值的逻辑,也可以向后比较。mysql
复制代码SELECT
name,
subject,
score,
score - LEAD(score, 1,score) OVER (ORDER BY score DESC) AS `分值差`
FROM
scores;
作者:古时的风筝
链接:https://juejin.cn/post/7290034121010233396
来源:稀土掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。