开窗函数详解及实例演示

一.  概述:

开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列.即: 它相当于给表新增一列, 至于新增什么内容, 取决于窗口函数, 结合什么函数一起使用. 

二. 格式

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

三. 分类

1. 聚合开窗函数 

格式:  sum()  |  max()  |  min()  |  avg()  |  count()  over()

例如一: 

1) 数据准备

# 网站点击量表
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) 演示partition by 

细节1:  窗口函数, 相当于给表新增一列

细节2:  如果不写partition by, 则是全局统计.

            如果写了partition by, 则是局部统计(即: 只统计该分组的数据)

--  不写partition by, 全局统计(统计表中所有的数据)
select *, sum(pv) over() as total_pv from website_pv_info;   

-- 写partition by, 局部统计(只统计该分组的数据.)
select *, sum(pv) over(partition by cookieid) as cook_total from website_pv_info; 

3) 演示order by 

细节三: 如果不写order by, 则统计组内所有的数据.

             如果写了order by, 则统计组内第一行 至 当前行的数据.

-- 如果不写order by, 则统计组内所有的数据,
select *, sum(pv) over(partition by cookieid) as cook_total from website_pv_info;  

-- 如果写了order by, 局部统计（统计组内第一行 至 当前行的内容）.
select *, sum(pv) over(partition by cookieid order by createtime) from website_pv_info;                               

4) 演示 : rows between ... and...

细节4: 

rows between ... and...可以指定操作行的范围, 包左包右, 涉及到的关键字如下:

• unbounded preceding # 表示: 第1行.
• n preceding # 向上的n行.
• current row # 当前行.
• n following # 向下的n行.
• unbounded following # 表示: 最后1行.

         示例一: 起始行( unbounded preceding) 到 当前行(current row)

-- 起始行 到 当前行, 等同于只写order by
select
    *,
    sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row ) as n1  # 起始行 到 当前行
from website_pv_info;  # 写order by, 局部统计(统计组内第一行 至 当前行的内容)

 

        示例二: 当前行及 向前3行(n preceding ) 向后1行 (n following)

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

 

2. 排名开窗函数

格式 : row_number() | rank()  |  dense_rank()  | ntile() over (partition by 分组字段 order by 排序字段)  

1) 演示  row_number() | rank()  |  dense_rank() over()

细节:

1.row_number() : 为每一组的行按顺序生成一个连续序号. 例: 1. 2. 3. 4

2.rank() : 为每一组的行生成一个序号，与row_number()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的. 例: 1. 2. 2. 4

3.dense_rank() : 与rank()类似，不同的是如果有相同的序号，那么接下来的序号不会间断.例:1. 2. 2. 3

# 需求: 根据点击量(pv)做排名.   根据cookieid分组, 根据点击量降序排名.
select
    *,                  
    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;

2) 演示: ntile() over() 

细节4: ntile(数字) : 表示把数据分成几份, 采用均分思想, 每份之间的差值不超过1. 即: 几分之几, 优先参考最小分区. 例如: 7条数据, 操作之后, 就是: 1, 1, 1,   2, 2,   3, 3

# ntile(数字,表示分成几份)  采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2
select
    *,
    ntile(3) over(partition by cookieid order by pv desc) as nt
from website_pv_info;

3. 其他开窗函数

• lag() over() : 向上偏移
• lead() over(): 向上偏移
• first_value() over() : 第一个值
• last_value() over() : 最后一个值

        1) 演示 lag()over() : 

细节1:   lag()函数的参数解释:

• 参1: 要操作的列
• 参2: 向上的第n行
• 参3: 默认值,找不到数据的时候, 用默认值填充.

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) 演示: first_value() over()

 细节2 : first_value 取分组内排序后，截止到当前行，第一个值.

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

 

四. 应用

1. 求某个分组下的最大/最小值/TOPN值对应信息, 用排序开窗函数.

2.对某个分组求和/个数/均值, 用聚合开窗函数

3. 判断连续登录问题 以及同比增长率/ 环比增长率, 使用偏移开窗函数.