掌握MySQL窗口函数window functions这一篇就够了！（详解+示例+代码，简单粗暴）

前言 

        我们在另一篇文章中全面详细地讲解了MySQL数据库的基础知识和语法，文中重点给大家介绍了三个窗口函数：row_number(),rank(),dense_rank()。

         对MySQL数据库感兴趣和想复习MySQL的小伙伴，墙裂推荐大家参考本人另一篇文章：

一文学会MySQL数据库——小白零基础保姆级教程，从安装-配置环境开始——不能再细了！（持续更新，求赞求收藏！）

        本文从基础到进阶全面讲解窗口函数，目的是通过全面详细的讲解，以及示例+代码的形式，帮助大家彻底掌握并熟练使用MySQL窗口函数！ 

1.窗口函数简介

1.1什么是窗口函数

窗口函数是类似于可以返回聚合值的函数，例如SUM()，COUNT()，MAX()。但是窗口函数又与普通的聚合函数不同，它不会对结果进行分组，使得输出中的行数与输入中的行数相同。

通俗的讲就是：窗口函数指的是over()函数, 它相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数, 结合什么函数一起使用。

1.2格式

可以结合窗口函数一起使用的函数 over(partition by 分组字段 order by 排序字段 asc|desc rows between 起始行 and 结束行)

• partition by：你只需将它看成group by子句，但是在窗口函数中，你要写partition by
• order by：order by和普通查询语句中的order by没什么不同。注意，输出的顺序要仔细考虑

 可以结合窗口函数一起使用的函数：

        聚合函数：count(),sum(),max(),min(),avg()

        排序函数：row_number(),rank(),dense_rank(),ntile(数字)

        其他函数：lag(),lead(),first_value(),last_value()

1.3窗口函数的优点

• 简单：窗口函数更易于使用。与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。

• 快速：这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。

• 多功能性：最重要的是，窗口函数具有多种功能，比如，添加移动平均线，添加行号和滞后数据，等等。

1.4注意事项

1.MySQL 8.0 之后，加入了窗口函数功能，简化了数据分析工作中查询语句的书写。所以如果你的MySQL版本不是8.0及之后, 将无法使用窗口函数。

2.如果不写partition by, 则是全局统计, 如果写了, 则是局部统计(即: 只统计该分组的数据)
3. 如果不写order by, 则统计组内所有的数据, 如果写了, 则统计组内第一行 至 当前行的数据.
4. rows between ... and...可以指定操作行的范围, 包左包右, 涉及到的关键字如下:
            unbounded preceding     # 表示: 第1行.
            n preceding             # 向上的n行
            current row             # 当前行.
            n following             # 向下的n行
            unbounded following     # 表示: 最后1行.
5. ntile(数字) 表示把数据分成几份, 即: 几分之几, 优先参考最小分区.
           例如: 7条数据, 操作之后, 就是: 1, 1, 1       2, 2       3, 3

2.讲解：案例

2.1准备数据

# 创建员工表
create table employee
(
    id int,                #编号
    name varchar(20),      #姓名
    deg varchar(20),       #职位
    salary int,            #工资
    dept varchar(20)       #部门
);
# 插入数据
insert into employee value (1201,'gopal','manager',50000,'TP');
insert into employee value (1202, 'manisha', 'cto', 50000, 'TP');
insert into employee value (1203, 'khalil', 'dev', 30000, 'AC');
insert into employee value (1204, 'prasanth', 'dev', 30000, 'AC');
insert into employee value (1206, 'kranthi', 'admin', 20000, 'TP');



# 创建网站点击量表
create table website_pv_info
(
    cookieid   varchar(20), # 用户id
    createtime varchar(20), # 访问时间
    pv         int          # 页面浏览量
);
# 插入数据
insert into website_pv_info value ('cookie1', '2018-04-10', 1);
insert into website_pv_info value ('cookie1', '2018-04-11', 5);
insert into website_pv_info value ('cookie1', '2018-04-12', 7);
insert into website_pv_info value ('cookie1', '2018-04-13', 3);
insert into website_pv_info value ('cookie1', '2018-04-14', 2);
insert into website_pv_info value ('cookie1', '2018-04-15', 4);
insert into website_pv_info value ('cookie1', '2018-04-16', 4);
insert into website_pv_info value ('cookie2', '2018-04-10', 2);
insert into website_pv_info value ('cookie2', '2018-04-11', 3);
insert into website_pv_info value ('cookie2', '2018-04-12', 5);
insert into website_pv_info value ('cookie2', '2018-04-13', 6);
insert into website_pv_info value ('cookie2', '2018-04-14', 3);
insert into website_pv_info value ('cookie2', '2018-04-15', 9);
insert into website_pv_info value ('cookie2', '2018-04-16', 7);



# 网站访问记录表
create table website_url_info
(
    cookieid   varchar(20), # 用户id
    createtime varchar(20), # 访问时间
    url        varchar(20)  # 访问的url页面
);
# 插入数据
insert into website_url_info value ('cookie1', '2018-04-10 10:00:02', 'url2');
insert into website_url_info value ('cookie1', '2018-04-10 10:00:00', 'url1');
insert into website_url_info value ('cookie1', '2018-04-10 10:03:04', '1url3');
insert into website_url_info value ('cookie1', '2018-04-10 10:50:05', 'url6');
insert into website_url_info value ('cookie1', '2018-04-10 11:00:00', 'url7');
insert into website_url_info value ('cookie1', '2018-04-10 10:10:00', 'url4');
insert into website_url_info value ('cookie1', '2018-04-10 10:50:01', 'url5');
insert into website_url_info value ('cookie2', '2018-04-10 10:00:02', 'url22');
insert into website_url_info value ('cookie2', '2018-04-10 10:00:00', 'url11');
insert into website_url_info value ('cookie2', '2018-04-10 10:03:04', '1url33');
insert into website_url_info value ('cookie2', '2018-04-10 10:50:05', 'url66');
insert into website_url_info value ('cookie2', '2018-04-10 11:00:00', 'url77');
insert into website_url_info value ('cookie2', '2018-04-10 10:10:00', 'url44');
insert into website_url_info value ('cookie2', '2018-04-10 10:50:01', 'url55');

员工表：

 

 网站点击量表：

网站访问记录表：

2.2案例

2.2.1窗口函数初体验

需求: 统计每个所有员工的工资, 并将其展示在每个员工数据的最后.
思路1: 聚合函数直接实现, 能计算出: 所有员工的工资总和, 但是无法达到想要的效果.   

select sum(salary) as total_salary from employee;

 

 思路2: 窗口函数可以实现我们要的效果.

select * ,sum(salary) over() as total_salary from employee;

 

 2.2.2窗口函数 + 聚合函数

本文以sum()函数为例，其他函数使用方法相同，具体使用哪种聚合函数需要看需求 

需求：求出网站总的pv数 所有用户所有访问加

注意：不写partition by, 全局统计(统计表中所有的数据)

select *, sum(pv) over() from website_pv_info;

需求: 求出每个用户总pv数
方式1: sum() + group by 一起使用. 

select cookieid, sum(pv) as total_pv from website_ov_info group by cookieid;

 

方式2: 聚合函数 + 窗口函数一起使用

注意：写partition by, 局部统计(只统计该分组的数据.)

select * sum(pv) over(partition by cookieid) from website_pv_info;

 

演示：
如果不写order by, 则统计组内所有的数据, 如果写了, 则统计组内第一行 至 当前行的数据.

select *, sum(pv) over(patrtition by cookieid order by createtime) 
from website_pv_info; 

# 上述代码等同于：
select *, sum(pv) 
    over(partition by cookieid order by 
    rows between unbounded preceding and current row) as n1
from website_pv_info;
    

 需求: 统计每个cookieid的pv(访问量), 只统计: 当前行及 向前3行 向后1行

select *,sum(pv) over(partition by cookied order by createtime
    rows between 3 preceding and 1 following)
from website_pv_info; # 写order by, 局部统计(统计组内第一行 至 当前行的内容)

 2.2.3窗口函数 + 排序

 需求: 根据点击量(pv)做排名.   根据cookieid分组, 根据点击量降序排名.

select *,
    #                  根据cookieid分组,    根据点击量降序排名
    row_number() over(partition by cookieid order by pv desc) as rn, #排名为: 1, 2, 3, 4
    rank() over(partition by cookieid order by pv desc) as rk,       #排名为: 1, 2, 2, 4
    dense_rank() over(partition by cookieid order by pv desc) as dr  #排名为: 1, 2, 2, 3
from website_pv_info;

 

 ntile(数字,表示分成几份)  采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分,

即: 7份成3份, 则是: 3, 2, 2

select *，
    #                 根据cookieid分组, 根据点击量降序排名
    ntile(3) over(partition by cookieid order by pv desc) as nt
from website_pv_info;

上述的ntile()是将数据分成指定的 份数, 方便我们取样(采样), 例如: 将数据按组分成5份, 只获取每组的第1份数据, 则计算量的数据量至少降低 5 倍.
例如: 上述的数据是分成了3份, 如果我要每组的第一份怎么办?   采用子查询.

select * from (
    select *,
          #                根据cookieid分组,   根据点击量降序排名.
          ntile(3) over (partition by cookieid order by pv desc) as nt
    from website_pv_info
) t1 where t1.nt = 1;

 2.2.4窗口函数 + 其他

 1. LAG用于统计窗口内往上第n行值

select *，
    # 获取上一行的createtime类的值,根据cookieid分组,根据创建时间排序
    lag(createtime) over(partition by cookieid order by createtime)
from website_url_info;

  

lag()完整写法如下： 

select
    *,
    # lag()函数的参数解释: 参1：要操作的列,  参2: 向上的第n行, 参3: 默认值,找不到数据的时候, 用默认值填充.
    lag(createtime, 2, '1970-01-01 00:00:00') 
over(partition by cookieid order by  createtime)
from website_url_info;

2.  LEAD 用于统计窗口内往下第n行值

select *,
    # lead()函数的参数解释:参1:要操作的列, 参2:向上的第n行,参3:默认值,找不到数据的时候,用默认值填充.
    lead(createtime,2,'1970-01-01 00:00:00')
    over(partition by cookieid order by creaetime)
from website_url_info;

 

3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值 

select *,
    first_value(createtime) over(partition by cookieid order by createtime)
from website_url_info;

 4. LAST_VALUE  取分组内排序后，截止到当前行，最后一个值

select *,
    last_value(createtime) over(partition by cookieid order by createtime)
from website_url_info;

思考：窗口函数+lag/ lead/first_value/last_value()函数 分别会在什么样的业务场景下使用呢？