SQL中的开窗函数(窗口函数)_Mysql

来源:脚本之家  责任编辑:小易  
目录
窗口函数1.1 排序窗口函数rank1.2 rank(), dense_rank(), row_number()区别1.3 排序截取数据lag(),lead(),ntile(),cume_dist()1.4 聚合函数作为窗口函数1.5 over(- - rows between and )

窗口函数

简单理解,就是对查询的结果多出一列,这一列可以是聚合值,也可以是排序值。开窗函数一般就是说的是over()函数,其窗口是由一个 OVER 子句 定义的多行记录开窗函数一般分为两类,聚合开窗函数和排序开窗函数。

简单来说,窗口函数有以下功能:

1)同时具有分组和排序的功能

2)不减少原表的行数

3)语法如下:

<窗口函数> over (partition by <用于分组的列名>
? ? ? ? ? ? ? ? order by <用于排序的列名> [rows between ?? and ???])

<窗口函数>的位置,可以放以下两种函数:

1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2) 聚合函数,如sum(). avg(), count(), max(), min()等,rows between…and…

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

3)业务需求“在每组内排名”,比如:

排名问题:每个部门按业绩来排名topN问题:找出每个部门排名前N的员工进行奖励

1.1 排序窗口函数rank

-- 如果我们想在每个班级内按成绩排名,得到下面的结果。
select *,
? ?rank() over (partition by 班级
? ? ? ? ? ? ? ? ?order by 成绩 desc) as ranking
from 班级表;

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”,这句话可以分为两部分:

1)每个班级内:按班级分组

partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)

2)按成绩排名

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

通过下图,我们就可以理解partiition by(分组)和order by(在组内排序)的作用了。 

img

group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

preview

注意事项

partition子句可是省略,省略就是不指定分组,只是按成绩由高到低进行了排序。但是,这就失去了窗口函数的功能,所以一般不要这么使用。窗口函数原则上只能写在select子句中

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

得到结果:

img

从上面的结果可以看出:

rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。

1.3 排序截取数据lag(),lead(),ntile(),cume_dist()

LAG(col,n,default_val):获取往前第n行数据,col是列名,n是往上的行数,当第n行为null的时候取default_valLEAD(col,n, default_val):往后第n行数据,col是列名,n是往下的行数,当第n行为null的时候取default_valNTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。cume_dist(),计算某个窗口或分区中某个值的累积分布。假定升序排序,则使用以下公式确定累积分布:

小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值。

1.4 聚合函数作为窗口函数

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

我们来看一下窗口函数是聚合函数时,会出来什么结果:

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

img

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

不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,

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

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

1.5 over(- - rows between and )

sum()/... over ([partition by 列名] [order by 列名] [rows between ... and ...] )
-- 从起点到当前行数据聚合
between unbounded preceding and current row?
-- 往前2行到往后1行的数据聚合
between 2 preceding and 1 following?

rows必须跟在Order by 子句之后,对排序的结果进行限制,使用固定的行数来限制分区中的数据行数量。

OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。CURRENT ROW:当前行n PRECEDING:往前n行数据n FOLLOWING:往后n行数据UNBOUNDED:起点,unbounded preceding 表示从表数据的起点, unbounded following表示到后面的终点
select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3,?
-- 由起点到当前行的窗口聚合,和sum3一样
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,?
-- 当前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 当前行的前面一行和后面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding AND 1 following) as sum6,
-- 当前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name ?| subject ?| score ?| sum1 ?| sum2 ?| sum3 ?| sum4 ?| sum5 ?| sum6 ?| sum7 ?|
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孙悟空 ? | 数学 ? ? ? | 12 ? ? | 359 ? | 185 ? | 12 ? ?| 12 ? ?| 12 ? ?| 31 ? ?| 185 ? |
| 沙悟净 ? | 数学 ? ? ? | 19 ? ? | 359 ? | 185 ? | 31 ? ?| 31 ? ?| 31 ? ?| 104 ? | 173 ? |
| 猪八戒 ? | 数学 ? ? ? | 73 ? ? | 359 ? | 185 ? | 104 ? | 104 ? | 92 ? ?| 173 ? | 154 ? |
| 唐玄奘 ? | 数学 ? ? ? | 81 ? ? | 359 ? | 185 ? | 185 ? | 185 ? | 154 ? | 154 ? | 81 ? ?|
| 猪八戒 ? | 英语 ? ? ? | 11 ? ? | 359 ? | 80 ? ?| 11 ? ?| 11 ? ?| 11 ? ?| 26 ? ?| 80 ? ?|
| 孙悟空 ? | 英语 ? ? ? | 15 ? ? | 359 ? | 80 ? ?| 26 ? ?| 26 ? ?| 26 ? ?| 49 ? ?| 69 ? ?|
| 唐玄奘 ? | 英语 ? ? ? | 23 ? ? | 359 ? | 80 ? ?| 49 ? ?| 49 ? ?| 38 ? ?| 69 ? ?| 54 ? ?|
| 沙悟净 ? | 英语 ? ? ? | 31 ? ? | 359 ? | 80 ? ?| 80 ? ?| 80 ? ?| 54 ? ?| 54 ? ?| 31 ? ?|
| 孙悟空 ? | 语文 ? ? ? | 10 ? ? | 359 ? | 94 ? ?| 10 ? ?| 10 ? ?| 10 ? ?| 31 ? ?| 94 ? ?|
| 唐玄奘 ? | 语文 ? ? ? | 21 ? ? | 359 ? | 94 ? ?| 31 ? ?| 31 ? ?| 31 ? ?| 53 ? ?| 84 ? ?|
| 沙悟净 ? | 语文 ? ? ? | 22 ? ? | 359 ? | 94 ? ?| 53 ? ?| 53 ? ?| 43 ? ?| 84 ? ?| 63 ? ?|
| 猪八戒 ? | 语文 ? ? ? | 41 ? ? | 359 ? | 94 ? ?| 94 ? ?| 94 ? ?| 63 ? ?| 63 ? ?| 41 ? ?|
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+

以上为个人经验,希望能给大家一个参考,也希望大家多多支持真格学网。

您可能感兴趣的文章:SQL开窗函数的具体实现详解SQL中的开窗函数详解可代替聚合函数使用Sql Server 开窗函数Over()的使用实例详解MySQL?开窗函数

  • 本文相关:
  • mysql中符号@的作用
  • centos7上mysql8.0rpm方式安装教程图解
  • mysql数据库基础入门之常用命令小结
  • linux下mysql开启远程访问权限 防火墙开放3306端口
  • mysql导入csv的4种报错的解决方法
  • mysql存储引擎基础知识
  • 一次mysql使用in大数据量的优化记录
  • 教你如何通过日志文件恢复mysql数据
  • 使用linux的shell脚本定时处理mysql超时
  • mysql 5.7版本修改密码的简单方法
  • oracle中的over到底是什么函数
  • sql server 有没有 sum over
  • 网站首页网页制作脚本下载服务器操作系统网站运营