窗口函数介绍:

概述:

​ 窗口函数指的是 over()函数, 它可以结合特定的函数一起使用, 完成不同的功能.
​ 目的/作用:
​ 窗口函数 = 给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.

格式:

​ 能和窗口函数一起使用的函数 over(partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行)
​ 能和窗口函数一起使用的函数解释:

聚合函数: count(), sum(), max(), min(), avg()
排序函数: row_number(), rank(), dense_rank(), ntile()
其它函数: lag(), lead(), first_value(), last_value()

这里要注意ntile函数ntiile( 3 )—>表示分成3份

细节:

       1. 窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
       2. 如果不写partition by, 表示: 统计表中所有的数据, 如果写了表示统计组内所有的数据.
       3. 如果不写order by, 表示: 统计组内所有的数据, 如果写列, 表示统计组内第一行截止到当前行的数据.
       4. rows between表示统计的范围, 它可以写的关键字如下:
        unbounded preceding   第一行
        unbounded following   最后一行
        n preceding           向上几行
        n following           向下几行
        current row           当前行
       5. ntile(数字)表示几分之几, 里边的数字表示把数据分成几份, 如果不够分, 优先参考最小分区.
       例如: 7条数据分成3份, 则最终结果为:  1, 1, 1    2, 2    3, 3

排序函数区别

1
2
row_number(), rank(), dense_rank()
-- 例如: 数据是100, 90, 90, 60, 则: row_number是: 1, 2, 3, 4,  rank: 1, 2, 2, 4,  dense_rank: 1, 2, 2, 3

与聚合函数结合

数据源我放到度盘了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--  细节: 如果写了partition by(表示分组): 则默认操作 组内所有的数据.
select
    cookieid,
    sum(pv) over (partition by cookieid)
from website_pv_info;
--  细节: 如果写了order by(表示排序):     则默认操作 组内第一行 至 当前行的数据.
select
    *,
    sum(pv) over (partition by cookieid order by createtime)
from website_pv_info;
--  上述的代码, 等价于如下的内容:
select
    *,
    sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row )
from website_pv_info;

--  需求: 统计每个cookieID的pv(访问量), 只统计: 当前行及 向前3行 向后1行
select
    *,
    sum(pv) over (partition by cookieid order by createtime rows between  3 preceding and 1 following)
from website_pv_info;

与排序函数结合

金典案例

1
2
3
4
with a1 as ( select *,
    row_number() over (partition by cookieid order by pv) as row4  -- 根据cookieid分组，按照pv进行排序
from website_pv_info )
select * from a1 where row4<=4;
1
2
3
4
5
with a1 as ( select
    *,
    ntile(3) over (partition by cookieid order by pv) as nt
from website_pv_info )
select * from a1 where nt=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--  需求: 根据点击量(pv)做排名, 组内排名.
--  这里的排序函数指的是: row_number(), rank(), dense_rank(), 它们都可以做排名, 不同的是, 对相同值的处理结果.
--  例如: 数据是100, 90, 90, 60, 则: row_number是: 1, 2, 3, 4,  rank: 1, 2, 2, 4,  dense_rank: 1, 2, 2, 3
select
       *,
       row_number() over (partition by cookieid order by pv desc) rn,
       rank() over (partition by cookieid order by pv desc) rk,
       dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info;

--  需求: 根据cookieID进行分组, 获取每组点击量最高的前4名数据, 这个就是经典的案例: 分组求TopN
--  Step1: 根据cookieID进行分组, 根据点击量进行排名.
select
       *,
       dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info where drk <= 4;        -- 报错.
--  细节: where只能筛选表中已经有的列(数据)
--  Step2: 把上述的查询结果当做一张表, 然后从中获取我们要的数据即可.
with t1 as (
    select
       *,
       dense_rank() over (partition by cookieid order by pv desc) drk
    from website_pv_info
)
select * from t1 where drk <= 4;

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


-- 需求: 按照cookieid分组, 按照点击量降序排列, 只要每组前三分之一的数据.
with t1 as (
    select
           *,
           ntile(3) over (partition by cookieid order by pv desc) nt
    from website_pv_info
)
select * from t1 where nt = 1;

select * from website_pv_info;

与其他函数结合

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--  1. LAG 用于统计窗口内往上第n行值
--  需求: 显示用户上一次的访问时间, 格式:  lag(字段, n, 默认值)  向上获取字段的第n个值, 如果没有写写默认值, 找不到就是null, 如果写了默认值, 找不到就用默认值.
select
       *,
       -- 向上1个, 找到就显示, 找不到就显示为 null
       lag(createtime) over(partition by cookieid order by createtime) `lag1`,
       -- 向上2个, 找到就显示, 找不到就用默认值: '2023-05-20 10:52:05'
       lag(createtime, 2, '2023-05-20 10:52:05') over(partition by cookieid order by createtime) `lag2`
from website_url_info;

--  根据cookieID分组, createtime升序排序, 获取当前行 向上2行的createtime列的值, 找不到就用默认值(夯哥)填充.

--  2. LEAD 用于统计窗口内往下第n行值
select
       *,
       lead(createtime) over(partition by cookieid order by createtime) `lead1`,
       lead(createtime, 2, '夯哥') over(partition by cookieid order by createtime) `lead2`
from website_url_info;

--  3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
select
       *,
       first_value(createtime) over(partition by cookieid order by createtime) `first_value`
from website_url_info;

--  4. LAST_VALUE  取分组内排序后，截止到当前行，最后一个值
select
       *,
       last_value(createtime) over(partition by cookieid order by createtime) `last_value`
from website_url_info;