mysql last_value窗口函数 mysql窗口函数 是用来干嘛

对于SQL语句的使用，我们知道怎样使用它的增删改查，但是如果遇到这样的查询需求时，怎样得到各部门工资排名前N名员工列表，环比如何计算，查找各部门每人工资占部门工资的百分比。对于这写类型的查询需求，传统的查询语句解决起来非常的繁琐，但要解决此类问题，最方面的就是使用窗口函数。

             

 

窗口函数的定义

        什么是窗口函数，窗口的概念十分重要，可以理解为记录集合，窗口函数也就是满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数，有的函数，随着记录不同，窗口大小都是固定的，这种属于静态窗口，有的函数则相反，不同的记录对应着不同的函数。窗口函数也叫OLAP函数，可以对数据库数据进行实时分析处理

        窗口函数和聚合函数有所不同，聚合函数时将多个记录聚合在一起，而窗口函数时每条记录都会执行，有几条记录执行完还是几条

按照功能划分，可以将窗口函数分为下面5种类型

• 序号函数：row_number()/rank()/dense_rank()
• 分布函数：percent_rank()/cume_dist()
• 前后函数：lag()/lead()
• 头尾函数：first_val()/last_val()
• 其他函数：nth_value()/nfile()

1、窗口函数的使用

# 窗口函数语法
<窗口函数> over (partition by <要分组的列名> order By <要排序的列名>)

1、窗口函数。这里主要介绍两种常见的窗口函数，（1）专用函数（row_number()、rank()、dense_rank()）（2）聚合函数作为窗口函数使用（sum()、avg()）

2、partition by 子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行，可以省略

3、原则上窗口函数只能写在select 子句中，不用在where和group by子句中使用、

4、order by 子句中可以通过desc、asc指定降序或者升序，如果省略，默认会按照升序asc进行排序

示例1：按照每个班级进行排名
select *,rank() over(partition by 班级 over By 成绩 desc)as 排名
from table1

结果展示：

学号	班级	成绩	排名
001	1	90	1
002	1	80	2
003	1	70	3
004	2	90	1
005	2	80	2

示例2：函数之间的对比展示
select *，rank() over (order by 成绩 desc)as 排名1,
          dense_rank() over(order by 成绩 desc)as 排名2,
          row_number() over(order by 成绩 desc)as 排名3
from table1;

学号	班级	成绩	排名1	排名2	排名3
001	1	98	1	1	1
002	3	97	2	2	2
003	2	96	3	3	3
004	1	95	4	4	4
005	1	95	4	4	5
006	3	90	6	5	6

解释：

rank()函数：计算排序时，如果有相同位次的记录，会占用下一名次的位置

dense_rank()函数：计算排序时，如果有相同位次的记录，不占用下一名次位置

row_number函数：计算排序时，不会出现相同位次的记录，是连续的位次

示例3：使用聚合函数作为窗口函数
select *，sum(成绩) over (order by 学号)as 总和,
          avg(成绩) over (order by 学号)as 平均值,
          count(成绩) over (order by 学号)as 计数,
          min(成绩) over (order by 学号)as 最小值
from 班级表

2、常见窗口函数案例

1、排名问题

示例：排名函数之间的对比展示
select *，rank() over (order by 成绩 desc)as 排名1,
          dense_rank() over(order by 成绩 desc)as 排名2,
          row_number() over(order by 成绩 desc)as 排名3
from table1;

学号	班级	成绩	排名1	排名2	排名3
001	1	98	1	1	1
002	3	97	2	2	2
003	2	96	3	3	3
004	1	95	4	4	4
005	1	95	4	4	5
006	3	90	6	5	6

2、topN问题

示例：找出每组最大的N条记录
select * from (select * ,row_number() over (partition by 课程号1
order by 成绩 desc)as 排名 from table)as a 
where 排名<=2;

学号	课程号	成绩	排名
001	1	98	1
002	1	97	2
003	2	87	1
004	2	80	2
005	3	60	1

3、每组之间进行比较

示例：每组之间的比较（查找单科成绩高于该科目平均成绩的名单）
select * from (select *，avg(成绩) over (partition by 科目)
as 各科平均成绩 from table)as a
where a.成绩>a.各科平均成绩

学号	科目	成绩	各科平均成绩
001	科1	90	89
002	科2	98	90
003	科3	78	76
004	科4	89	67

---