1、开窗函数(排序相关)
row_number ROW_NUMBER() 从1开始,按照顺序,生成分组内记录的序列
SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM test;
RANK 和 DENSE_RANK Rank() 生成数据项在分组中的排名,排名相等会在名字中留下空位, Dense_Rank() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位。
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM it_t1
WHERE cookieid = 'cookie1';
todo :https://www.studytime.xin/article/hive-knowledge-window-function.html
|