返回顶部

【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;
posted @ 2023-06-08 22:47  FlowersandBoys  阅读(58)  评论(0编辑  收藏  举报