Mysql8.0开窗函数使用说明

应用背景

在数据统计时，结合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

求用户连续登录天数、连续签到天数等问题。

例: 一个用户登陆记录表，求

1. 查看每位用户连续登录的情况
2. 查看每位用户最大连续登录的天数
3. 查看在某个时间段里连续登录天数超过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;