【MYSQL】由排序问题引申到窗口函数

Aubye

于 2023-01-09 20:17:49 发布

阅读量132

点赞数

分类专栏： SQL 文章标签： mysql 数据库

原文链接：https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649247566&idx=1&sn=f9c7018c299498673b38221db2ecd5cd&chksm=835fc77eb4284e68b7528fd7f75eedb8868a6740704af8559f8a5cbdd2867a49ffa21bf4e531&token=426730634&lang=zh_CN#rd

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1 题目介绍

来源于力扣178题，由此总结排序函数
原文https://leetcode.cn/problems/rank-scores/
在这里插入图片描述

2 题目分析

2.1 排名函数

在此用到了排名函数，主要有四种，实现不同的排序方式，主要区别就是是否连续/排序值

row_number()函数
函数在排名是序号连续不重复，即使遇到表中的两个一样的数值亦是如此，则相同的数值也有不同的赋值，由下图可见，即使number里面有相同的值，在排序值里面也会将其定义为不同的排列值。

select *,row_number() OVER(order by number ) as row_num
from num

在这里的插入图片描述在这里插入图片描述

rank()函数
rank() 函数会把要求排序的值相同的归为一组且每组序号一样，排序不会连续执行。当number里面有相同的值，则会赋值相同的值，但是因相同值的出现，有一些排序值将会被跳过。

select *,rank() OVER(order by number ) as row_num
from num

在这里插入图片描述

dense_rank()函数

dense_rank() 排序是连续的，也会把相同的值分为一组且每组排序号一样，序号不会因为有相同的值则跳过

select *,dense_rank() OVER(order by number ) as row_num
from num

在这里插入图片描述

ntile()
ntile(group_num) 将所有记录分成group_num个组，每组序号一样，在实例中就是分为两个组，具体分组的方式就是根据number的大小进行

select *,ntile(2) OVER(order by number ) as row_num
from num

在这里插入图片描述

2.2 题目解答

由上面的排序函数可知，在使用的时候默认是升序排列，如果像分数这种需要降序排列，则需要利用desc（asc是升序），在题目中，用到的就是分数的排名，且其要求需要根据分数从高到低排序，则具体实现如下：

# Write your MySQL query statement below
select score, dense_rank() OVER(order by score desc) as "rank"#这个rank之所以要加引号，因为rank本身是个函数，直接写rank会报错
from Scores
order by score desc #因为需要降序排序，所以用到desc

在这里插入图片描述

3 窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：
1）专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。
2）聚合函数，如sum, avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中

3.1 分组排序函数使用

每个班级内：按班级分组，partition by用来对表分组。在这个例子中，指定了按“班级”分组（partition by 班级）；按成绩排名，order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。
为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

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

在这里插入图片描述

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 班级表

在这里插入图片描述

3.2 聚合函数使用

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

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号成绩的求和，以此类推。
不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算
在这里插入图片描述
比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。
如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。