hive开窗函数大全

最新推荐文章于 2024-05-04 04:24:37 发布

原创

最新推荐文章于 2024-05-04 04:24:37 发布 · 1.8k 阅读

5 ·

CC 4.0 BY-SA版权

本文详细介绍了Hive中的窗口函数，包括sum、max、min、avg等聚合函数，以及row_number、ntile、rank、dense_rank等排名函数。还讲解了LAG、LEAD、FIRST_VALUE、LAST_VALUE等函数的使用，以及CUME_DIST和PERCENT_RANK在统计中的作用。此外，还探讨了GROUPING SETS、GROUPING__ID、CUBE和ROLLUP在分组聚合中的功能和差异。

-- 窗口函数sum,max,min,avg

SELECT 
cookieid,
createtime,
pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) AS pv3,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS pv4
FROM lxw1234;

-- 窗口函数row_number,ntile,rank,dense_rank(不支持rows between)
-- row_number分组排名，ntile切片,rank分组排名留空,dense_rank分组排名不留空

SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,--排名，分组内每条记录一个行号，无关心createtime是否相同
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,--表示对分组内的数据排序后切3片，多余的会加到第一片中
RANK() OVER(PARTITION BY cookieid ORDER BY