MySQL的函数

MySQL的函数-窗口函数

介绍

MySQL8.0新增窗口函数，窗口函数又被称为开窗函数，与Oracle窗口函数类似，属于MySQL的一大特点。

非聚合窗口函数是相对于聚合函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

分类

语法结构

window_function (expr) OVER(
PARTITION BY...
ODER BY...
FRAME_CLAUSE
)

其中，window_function是窗口函数的名称；expr是参数，有些函数不需要参数；over句子包含三个选项：

分区（PARTITION BY）

PARTITION BY 选项用于将数据行拆分成多个分区（组），它的作用类似于group by 分组。如果省略了partition by，所有的数据作为一个组计算

排序（ODER BY）

OVER子句中的ODER BY选项用于指定分区内的排序方式，与ODER BY子句的作用类似

以及窗口大小（frame_clause）

frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()可以用来实现分组排序，并添加序号。

格式

row_number()|rank()|dense_rank() over(
partition by...
oder by...
)

操作

use mydb4;
create table employee(
dname varchar(20),
eid varchar(20),
ename varchar(20),
hiredate date,
salary double
);

-- 对每个部门的员工按照薪资排序，并给出排名
select
dname,
ename,
salary,
row_number() over(paitition by dname oder by salary desc)as rn
from employee;

开窗聚合函数-SUM,AVG,MIN,MAX

概念

在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MIN()、COUNT()）,可以动态计算在指定的窗口内的各种聚合函数值。

操作

select 
dname,
ename,
salary,
sum(salary) over(partition by dname oder by hiredate) as pv1
from employee;

select dname,
ename,
salary,
sum(salary) over(partition by cookieid) as pv3
from itcast_t1;-- 如果没有 oder by排序语句 默认把分组内的所有数据进行sum操作

分布函数-CUME_DIST和PERCENT_RANK

介绍-CUME_DIST

用途：分组内小于、等于当前rank值的函数/分组内总行数

应用场景：查询小于等于当前薪资（salary）的比例

操作

select
dname,
ename,
salary,
cume_dist() over(oder by salary) as rn1, -- 没有partition语句所有的数据位于一组
cume_dist() over(partition by dept oder by salary) as rn2
from employee;

介绍-PERCENT_RANK

用途：每行按照公式（rank1）/（rows-1）进行计算。其中，rank为RANK（）函数产生的序号，rows为当前窗口的记录总行数

应用场景：不常用

操作

select
dname,
ename,
salary,
rank() over(partition by dname oder by salary desc) as rn,
percent_rank() over(partition by dname oder by salary desc) as rn2
from employee;

前后函数-LAG和LEAD

介绍

用途：返回位于当前行的前n行（LAG(expr，n)）或后n行（LEAD(expr，n)）的expr的值

应用场景：查询前一名同学的成绩和当前同学成绩的差值

操作

select
dname,
ename,
hiredate,
salary,
lag(hiredate,1,'2000-01-01') over(partition by dname oder by hiredate) as last_1_time,
lag(hiredate,2) over(partition by dname oder by hiredate) as last_2_time
from employee;

头尾函数-FIRST_VALUE和LAST-VALUE

介绍

用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LATS_VALUE(expr)）expr的值

应用场景：截至到当前，按照日期排序查询第一个入职和最后一个入职员工的薪资

操作

-- 注意，如果不指定ODER BY，则会进行排序混乱，会出现错误的结果
select
dname,
ename,
hiredate,
salary,
first_value(salary) over(partition by dname oder by hiredate) as first,
last_value(salary) over(partition by dname oder by hiredate) as last
from employee;

其他函数-NTH_VALUE(expr,n)、NTILE(n)

介绍-NTH_VALUE(expr,n)

用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

应用场景：截止到当前薪资，显示每个员工的薪资中排名第二或第三的薪资

操作

-- 查询每个部门截至目前薪资排在第二和第三的员工信息
select
dname,
ename,
hiredate,
salary,
nth_value(salary,2) over(partition by dname oder by hiredate) as second_score,
nth_value(salary,3) over(partition by dname oder by hiredate) as second_score,
from employee

介绍-NTILE

用途：将分区中的有序数据分为n个等级，记录等级数

应用场景：将每个部门员工按照入职日期分成三组

操作

-- 根据入职日期将每个部门的员工分成三组
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname oder by hiredate) as rn
from employee;