应用背景
在数据统计时,结合order by关键词和limit关键词是可以解决很多的topN问题,
比如:
- 从二手房数据集中查询出某个地区的最贵的10套房,
- 从学员信息表中查询出年龄最小的3个学员等。
但是,如果需求变成 - 从二手房数据集中查询出各个地区最贵的10套房,
- 从学员信息表中查询出各个科系下年龄最小的3个学员,
该如何解决呢?
其实这类问题的核心就是,筛选出组内的topN,而不是从全部数据集中挑选出topN。遇到这种既需要分组也需要排序的问题,直接上开窗函数就能解决了。
使用说明
1. 定义
开窗函数是在满足某种条件的记录集合上执行的特殊函数,对于每条记录在此窗口内执行函数。
从mysql8.0版本之后开始支持开窗函数。
开窗函数和group by有什么区别呢?
需要注意,开窗函数的本质还是聚合运算,对比起group by,它更具灵活性,是对表中字段的值进行聚合运算,它作用于表中数据的每一行,使窗口内每一行使用与该行相关的行进行计算并返回计算结果,而group by通常是对整个表进行分组聚合运算。
开窗函数和普通聚合函数之间又有什么关联呢?
- 聚合函数是将多条记录聚合为一条,而开窗函数是每条记录都会执行,有几条记录执行完还是几条,返回多条值
- 聚合函数也可以用于开窗函数中
2. 语法
func_name()
OVER([PARTITION BY <part_by_condition>] [ORDER BY <order_by_list> ASC|DESC] [range|rows])
SELECT
iname,
idate,
sales,
first_value(sales) OVER (PARTITION BY iname order by sales range between 50 preceding and 1 following) AS last_sales
FROM
sales;
开窗函数语句解析:
函数分为两部分,一部分是函数名称,开窗函数的数量比较少,总共才11个开窗函数+聚合函数(所有的聚合函数都可以用作开窗函数)。根据函数的性质,有的需要写参数,有的不需要写参数。
另一部分为over语句,over()是必须要写的,里面的参数都是非必须参数,可以根据需求有选择地使用:
第一个参数是partition by + 字段,含义是根据此字段将数据集分为多份
第二个参数是order by + 字段,每个窗口的数据依据此字段进行升序或降序排列
第三个参数是 range|rows子句:用于给当前分区窗口指定显示范围,通常用来作为滑动窗口使用
3. 函数说明
暂时无法在飞书文档外展示此内容
3.1 序号函数
3.1.1 ROW_NUMBER()
返回序号值,遇到相同的值时序号为(排序:1,2,3)
3.1.2 RANK()
返回序号值,遇到相同的值时序号为(排序:1,1,3)
3.1.3 DENSE_RANK()
返回序号值,遇到相同的值时序号为(排序:1,1,2)
使用实例
有一个员工每日的销售表sales
求每天销售的第一名怎么求?
解决办法:典型的,找出组内的topN的问题
SELECT
idate,
iname,
sales,
ROW_NUMBER () OVER (PARTITION BY idate ORDER BY sales desc) AS sales_order
FROM
sales;
三个都是加序号,无非就是遇到相同的值是排序加的序号不同。
3.2 分布函数
3.2.1 PERCENT_RANK()
值为:(rank-1)/(rows-1)
SELECT
iname,
idate,
sales,
PERCENT_RANK() OVER (PARTITION BY idate order by sales) AS last_sales
FROM
sales;
3.2.2 CUME_DIST()
值为:<=当前rank值的行数/总行数
SELECT
iname,
idate,
sales,
CUME_DIST() OVER (PARTITION BY idate order by sales) AS last_sales
FROM
sales;
实际就是求小于等于当前值的占总数比
3.3 前后函数
3.3.1 LAG(expr,n)
返回当前行的前n行的expr的值
3.3.2 LEAD(expr,n)
返回当前行的后n行的expr的值
适用场景:计算同环比
为什么SQL单表不能计算同比?
因为SQL计算中,行与行之间无法进行计算。如果用自连接呢?创建所有字段的虚拟结果集,数据计算量过大
查询每个员工上一个产生销售额的距离当前销售记录的间隔天数?
SELECT
idate,
iname,
sales,
LAG (idate,1) OVER (PARTITION BY iname order by idate) AS last_date
FROM
sales;
3.4 头尾函数
3.4.1 FIRST_VALUE(expr)
返回第一个expr的值
3.4.2 LAST_VALUE(expr)
返回最后一个expr的值
适用场景:求组内字段第一个值和最后一个值
查询每个员工第一天的销售额和最后一天的销售额?
SELECT
iname,
idate,
sales,
first_value(sales) OVER (PARTITION BY iname order by idate) AS first_sales,
last_value(sales) OVER (PARTITION BY iname order by idate) AS last_sales
FROM
sales;
统计不对,为什么?
1. 当over中指定分区,但是没有指定排序和滑动窗口范围时,默认计算当前分区内的所有记录。
2. 当over中指定分区,指定排序时,未指定滑动窗口范围时,默认计算从第一行到当前行。
range和rows的区别:
range是逻辑窗口,根据order by子句排序后,当前行对应的值计算。
rows是物理窗口,根据order by子句排序后,当前行对应的序号计算(与当前行的值无关,只与排序后的序号相关)。
基于行(row): 通常使用 rows between frame_start and frame_end 语法来表示行范围
基于值(range): 和基于行类似,通常使用 range between frame_start and frame_end 语法来表示值范围
(不管是基于行还是基于值得计算,都是以order by后的字段作为参照依据)
最终答案:
SELECT
iname,
idate,
sales,
first_value(sales) OVER (PARTITION BY iname order by idate rows between unbounded preceding and unbounded following) AS first_sales,
last_value(sales) OVER (PARTITION BY iname order by idate rows between unbounded preceding and unbounded following) AS last_sales
FROM
sales;
range是逻辑窗口,根据order by子句排序后,当前行对应的值计算。滑动值
实例:
SELECT
iname,
idate,
sales,
first_value(sales) OVER (PARTITION BY iname order by sales range between 50 preceding and 1 following) AS last_sales
FROM
sales;
3.5 其他函数
3.5.1 NTH_VALUE(expr,n)
返回第n个expr的值
实例:求每个人每天的销售额排名第二的金额是多少?
SELECT
iname,
idate,
sales,
NTH_VALUE(sales, 2) OVER (PARTITION BY iname order by sales desc) AS last_sales
FROM
sales;
大家想想,上面这个sql的这个结果是对的吗?
答案是错的。
排序的第一行的数据是空的,为什么?
还是刚刚那个问题,当over中指定分区,指定排序时,未指定滑动窗口范围时,默认计算从第一行到当前行。所以第一行之只有自己一行,没有第二名。所以是空的。
正确答案是跟前面一样要加上滑动窗口取所有值。
3.5.2 NTILE(n)
将有序数据分为n个桶,记录等级数
个人理解是给定一个最高等级值,然后会根据你order by的值,按比例归到规定的等级值里面去。
实例:
SELECT
iname,
idate,
sales,
NTILE(3) OVER (PARTITION BY idate order by sales) AS last_sales
FROM
sales;
经典使用实例
删除重复数据
例: 一个销售订单数据表,因为事务或者其他原因产生了重复数据如下表所示,现需要对重复的数据进行删除,按订单号为唯一每个订单只需留下一条创建时间最新的数据。
通过增加序号列,然后再筛选出需要删除的id,最后再执行删除。
SELECT
id
FROM
(
SELECT t.*, ROW_NUMBER () over (
PARTITION BY order_no
ORDER BY create_date DESC
) AS rown
FROM sales_order t
) r
WHERE
rown > 1
求用户连续登录天数、连续签到天数等问题。
例: 一个用户登陆记录表,求
- 查看每位用户连续登录的情况
- 查看每位用户最大连续登录的天数
- 查看在某个时间段里连续登录天数超过N天的用户
[图片]
问题1:查看每位用户连续登录的情况
根据实际经验,我们知道在一段时间内,用户可能出现多次连续登录,这些信息我们都要输出,所以最后结果输出的字段可以是用户ID、首次登录日期、结束登录日期、连续登录天数这四个。
数据预处理:由于统计的窗口期是天数,所以可以对登录时间字段进行格式转换,将其变成日期格式然后再去重(去掉用户同一天内多次登录的情况)
为方便后续代码查看,将处理结果放置新表中,一步一步操作
create table user_login_date(
select distinct user_id, date(login_time) login_date from user_login);
处理后的数据如下:
select * from user_login_date;
第一种情况:查看每位用户连续登陆的情况
对用户登录数据进行排序
create table user_login_date_1(
select *,
rank() over(partition by user_id order by login_date) irank
from user_login_date);
#查看结果
select * from user_login_date_1;
** 增加辅助列,帮助判断用户是否连续登录**
create table user_login_date_2(
select *,
date_sub(login_date, interval irank DAY) idate #data_sub从指定的日期减去指定的时间间隔
from user_login_date_1);
查看结果
select * from user_login_date_2;
计算每位用户连续登录天数
select user_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(login_date) as days
from user_login_date_2
group by user_id,idate;** =【整合代码,解决用户连续登录问题】=====**
select user_id,
min(login_date) start_date,
max(login_date) end_date,
count(login_date) days
from (select *,date_sub(login_date, interval irank day) idate
from (select *,rank() over(partition by user_id order by login_date) irank
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c
group by user_id,idate;
[图片]
问题2:查看每位用户最大连续登录的天数计算每个用户最大连续登录天数
select user_id,max(days) from
(select user_id,
min(login_date) start_date,
max(login_date) end_date,
count(login_date) days
from (select *,date_sub(login_date, interval irank day) idate
from (select *,rank() over(partition by user_id order by login_date) irank
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c
group by user_id,idate) as d
group by user_id;
问题3:查看在某个时间段里连续登录天数超过5天的用户
用当前记录后第五次登录日期 - login_date+1,如果等于5,说明是连续登录五天的,如果得到空值或者大于5,说明没有连续登录五天,代码和结果如下:
计算第5次登录日期与当天的差值
select *,datediff(idate5,login_date)+1 days
from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5
from user_login_date) as a;
#找出相差天数为5的记录
select distinct user_id from(
select ,datediff(idate5,login_date)+1 days from (
select t.,LEAD(login_date,4) over(PARTITION by user_id order by login_date) as idate5 from (select distinct user_id, date(login_time) login_date from user_login) t
)s)b where days = 5;