【Hive】窗口函数
窗口函数介绍:
概述:
窗口函数指的是 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
排序函数区别
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
与聚合函数结合
数据源我放到度盘了
-- 细节: 如果写了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;
与排序函数结合
金典案例
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;
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;
-- 需求: 根据点击量(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. 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;