MySQL窗口函数

Mysql窗口函数
MySQL从8.0开始支持窗口函数

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>);
– over关键字用于指定函数的窗口范围,
– partition by 用于对表分组,
– order by子句用于对分组后的结果进行排序。
注意:窗口函数是对where或者group by子句处理后的结果再进行二次操作,因此会按照SQL语句的运行顺序,窗口函数一般放在select子句中(from前)

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

1、序号函数:row_number() / rank() / dense_rank()
ROW_NUMBER():顺序排序 —— 1、2、3
RANK():并列排序,跳过重复序号 —— 1、1、3
DENSE_RANK():并列排序,不跳过重复序号 —— 1、1、2

SELECT *,
ROW_NUMBER() over(ORDER BY sales desc) as pro_ROW_NUMBER,
rank() over(ORDER BY sales desc) as pro_rank,
DENSE_RANK() over(ORDER BY sales desc) as pro_DENSE_RANK
from food
where product=‘包子’;

在这里插入图片描述
2、分布函数:percent_rank() / cume_dist()
基本不用

3、前后函数:lag(expr,n) / lead(expr,n)
expr后面还会涉及到,统一解释一下:expr可以是表达式,也可以是列名

SELECT *,
lag(sales,1) over win as pro_lag,
lead(sales,1) over win as pro_lead
from food
WINDOW win as (PARTITION BY product ORDER BY sales desc);
在这里插入图片描述
把窗口提取出来设置了别名为:win
partition by 是对窗口内容进行分组处理;
order by 是对窗口内容分组后进行排序;
计算当前行与前n行(共n+1行)的聚合窗口函数:
SELECT *,SUM(sales) OVER win as ‘近三个月利润相加’
FROM food
WINDOW win as (PARTITION BY product ORDER BY month ROWS 2 PRECEDING);
在这里插入图片描述
计算当前行与前n1行、后n2行的聚合窗口函数:
SELECT *,
SUM(sales) OVER win as ‘前一个月到下一个月利润相加’
FROM food
WINDOW win as (PARTITION BY product ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
在这里插入图片描述
4、头尾函数:FIRST_VALUE(expr)、LAST_VALUE(expr)
头尾函数应用于:返回第一个或最后一个expr的值;
SELECT *,
FIRST_VALUE(sales) over win as ‘当前最大月收入’,
LAST_VALUE(sales) over win as ‘当前最小月收入’
from food
WINDOW win as (PARTITION BY product ORDER BY month);
在这里插入图片描述
5、其他函数:nth_value() / nfile()
nfile()不常用,NTH_VALUE用途:返回窗口中第n个expr的值
截止到当前,排名第2和第3的成绩的利润:
SELECT *,
nth_value(sales,2) over win as ‘当前排名第二的月收入’,
nth_value(sales,3) over win as ‘当前排名第三的月收入’
from food
WINDOW win as (PARTITION BY product ORDER BY month);
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值