MySQL

您所在的位置：网站首页 › 分数降序排列 › MySQL

MySQL

2024-07-12 12:58| 来源: 网络整理| 查看: 265

这两天刷了一遍45道mysql面试题，其中排序问题，用5.7版本的方法解决有些复杂，而mysql 8.0之后的版本可以用窗口函数解决，相对容易些。

注：查看mysql版本

select version();

查询结果：在这里插入图片描述

01-什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法： over (partition by order by ) 的位置，可以放以下两种函数：

专用窗口函数：rank, dense_rank, row_number等专用窗口函数。

聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

02-如何使用 2.1 专用窗口函数rank

如对每个班级内按成绩排名

select *, rank() over (partition by 班级 order by 成绩 desc) as ranking from 班级表

partition by用来对表分组，在这个例子中，我们指定了按“班级”分组（partition by 班级）；

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

2.2其他专业窗口函数

专用窗口函数rank, dense_rank, row_number有什么区别呢？

select *, rank() over (order by 成绩 desc) as ranking, dense_rank() over (order by 成绩 desc) as dese_rank, row_number() over (order by 成绩 desc) as row_num from 班级表

得到结果：

在这里插入图片描述可以看出：

rank函数：有并列名次时，会保留名次空缺； dense_rank函数：有并列名次时，不会保留名次空缺； row_number函数：不考虑并列名次的情况

注：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

举个例子

题目：查询学生的总成绩，并进行排名，总分重复时保留名次空缺

使用窗口函数

SELECT z.sid, z.sum_score, rank() over (ORDER BY z.sum_score DESC) AS ranking FROM (SELECT sid, SUM(score) sum_score FROM sc GROUP BY SId) z

使用Mysql5.7版本的方法

SELECT a.sid, @rank:=IF(@sco=sum_score,'',@rank+1) AS rn, @sco:=sum_score sum_score FROM (SELECT sid, SUM(score) sum_score FROM sc GROUP BY sid ORDER BY sum_score DESC) a, (SELECT @sco:=NULL,@rank:=0) b;

查询结果：在这里插入图片描述

2.3 聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名

select *, sum(成绩) over (order by 学号) as current_sum, avg(成绩) over (order by 学号) as current_avg, count(成绩) over (order by 学号) as current_count, max(成绩) over (order by 学号) as current_max, min(成绩) over (order by 学号) as current_min from 班级表

输出结果：在这里插入图片描述如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

这样使用窗口函数有什么用呢？

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

03-总结窗口函数 3.1 函数语法 over (partition by order by )

的位置，可以放以下两种函数：

1）专用窗口函数，比如rank, dense_rank, row_number等

2）聚合函数，如sum. avg, count, max, min等

3.2 窗口函数有以下功能

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名

3.3 注意事项

窗口函数原则上只能写在select子句中

【本文地址】

MySQL

MySQL

今日新闻

推荐新闻