Hive 窗口函数及其搭配分析函数

Hive 窗口函数及其搭配分析函数

①SUM(),AVG(),MIN(),MAX(),COUNT()

②NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值

    注1：如果切片不均匀，默认增加第一个切片的分布
   注2：NTILE不支持ROWS BETWEEN

③RANK 序号跳跃、dense_rank序号连续、row_number不重复连续序号

④CUME_DIST 返回小于等于当前值的行数/分组内总行数

⑤PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1

⑥LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

⑦LEAD(col,n,DEFAULT)  用于统计窗口内往下第n行值

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

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

+OVER(PARTITION BY ORDER BY  ROWS BETWEEN  AND ROWS BETWEEN )

ROWS BETWEEN 也叫做window子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点

例子：

select table.website
    ,table.view_time
    ,table.pv
    ,SUM(table.pv) over (partition by table.website order by table.view_time) as sum_pv_1 --默认情况
    ,SUM(table.pv) over (partition by table.website order by table.view_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_pv_2 --表示从起点到当前行
    ,SUM(table.pv) over (partition by table.website) as sum_pv_3 --表示窗口内所有行
    ,SUM(table.pv) over (partition by table.website order by table.view_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as sum_pv_4 --表示起点到终点
    ,SUM(table.pv) over (partition by table.website order by table.view_time ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) as sum_pv_5 --表示前2行到后面1行
from (
select 
split(pv_table,',')[0] as website
,split(pv_table,',')[1] as view_time
,split(pv_table,',')[2] as pv
from
(
    select
    concat('www.baidu.com,2020-12-02,1',
    '#www.beke.com,2020-12-05,2',
    '#www.pdd.com,2020-12-07,2',
    '#www.jd.com,2020-12-01,8',
    '#www.beke.com,2020-12-04,4',
    '#www.jd.com,2020-12-01,6',
    '#www.tencent.com,2020-12-06,4') as pv_data
) website_pv
lateral view explode(split(website_pv.pv_data,'#')) pv_t as pv_table
) table