周柃君

2020-08-23   阅读量: 1085

Mysql

开窗函数

扫码加入数据分析学习群

名称

描述

CUME_DIST()

计算一组值中一个值的累计分布

DENSE_RANK()

根据该ORDER BY句子为分区中的每一行分配一个等级,它将相同的等级分配给具有相等值的行。如果两行或更多行具有相同的排名,则排名值序列中讲没有间隙

FIRST_VALUE()

返回相对于窗口框架第一行的指定表达式的值

LAG()

返回分区中当前行之前的第N行的值。如果不存在前一行,则返回NULL

LAST_VALUE()

返回相对于窗口框架中最后一行的指定表达式的值

LEAD()

返回分区汇总当前行之后的第N行

NTH_VALUE()

从窗口框架的第N行返回参数的值

PERCENT_RANK()

计算分区或结果集中行的百分数等级

RANK()

与DENSE_RANK()函数相似,不同之处在于当两行或者更多航具有相同的登记时,等级值序列中存在间隙

ROW_NUMBER()

为分区中的每一行分配一个顺序整数

开窗函数的定义:

开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算,只不过它更具有灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果,

例:

-- 聚合函数用于开窗函数

-- 查询所有员工的工资总和

select sum(sal) as 工资总和 from emp;-- 聚合函数的工资总和

select *,sum(sal) over() as 工资总和 from emp;-- 开窗函数,不会影响表中的记录行数

#当over没有指定分区/排序/滑动窗口时,计算的表中所有记录作为一个区,默认计算的是分区内的所有值

-- 计算各个部门的工资总和

select deptno,sum(sal) as 工资总和

from emp

group by deptno;-- 普通聚合函数

select * ,sum(sal) over(partition by deptno) as 工资总和 from emp;-- 开窗函数,over内指定了分区

# 当over中没有指定排序和滑动窗口时,默认计算分区内的所有行

-- 计算各部门的累计工资(普通的聚合函数计算不出来)需要首先对数据进行分区,再指定一个顺序(如果不指定就会按照表中的顺序进行)

select * ,sum(sal) over(partition by deptno order by hiredate ) as 累计工资 from emp;-- 一般用于计算累计销售额,累计利润

# 在over中指定了排序,但是没有滑动窗口默认计算的是分区内的第一行到当前行所有值

-- 查询各部门员工按照入职日期排序,计算前一行和后一行的员工平均工资,所有的聚合函数都可以用来开窗,滑动窗口可以基于行,也可以基于值

select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) as 移动平均工资 from emp;

# 当over指定了滑动窗口,默认计算分区内滑动窗口范围内的行,时间序列模型(数据波动性很大,产品存在一个正常的周期性波动,消除周期性的影响)可以考虑计算周期内移动平均利润,这样计算平均利润波动平缓

-- 序号函数

-- 查询所有员工的工资排名

select *,row_number() over(order by sal desc) as 工资排名 from emp; -- row_number属于无参函数

# 可以显示并列的排名 dense_rank(),rank()

select *,

row_number() over(order by sal desc) as 工资排名1,

rank() over(order by sal desc) as 工资排名2,

dense_rank() over(order by sal desc) as 工资排名3

from emp;

#各部门工资排名

select *,dense_rank() over(partition by deptno order by sal desc) as 工资排名 from emp;

-- 查询各部门工资排名第二的员工

select *,dense_rank() over(partition by deptno order by sal desc) as 工资排名

from emp;-- 开窗函数的本身就是聚合运算,因此开窗函数也不能出现在where子句中,子查询嵌套

select *

from

(select *,dense_rank() over(partition by deptno order by sal desc) as 工资排名 from emp) as t

where 工资排名=2;-- 通过子查询进行引用

-- 查询各部门每位员工跟前一个员工入职时间间隔

select *,

lag(hiredate,1) over(partition by deptno order by hiredate) as 前一位员工的入职日期,

datediff(hiredate,lag(hiredate,1) over(partition by deptno order by hiredate)) as间隔天数

from emp;-- 常用于两个订单下单时间间隔时间 lag(字段,当前行前几行),select中的字段是同时执行的


37.8633 3 0 关注作者 收藏

评论(0)


暂无数据

推荐课程

推荐帖子