Hive--窗口函数--排序函数--row_number--dense_rank--rank--ntile

最新推荐文章于 2025-01-13 17:36:47 发布

原创最新推荐文章于 2025-01-13 17:36:47 发布 · 349 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

本文介绍SQL中的窗口函数如RANK(), DENSE_RANK(), ROW_NUMBER() 和 NTILE() 的使用方法，并通过实例展示如何解决常见的分组TopN问题及数据分桶统计。

功能：主要对数据分组排序之后，组内顺序标号。

适合场景：分组TopN问题。

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 website_pv_info;

--需求：找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
    cookieid,
    createtime,
    pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;

ntile函数：将分组排序之后的数据分成若干指定的部分，尽量平分，优先满足最小的桶，彼此最多不想差一个。

--把每个分组内的数据分为3桶
SELECT
    cookieid,
    createtime,
    pv,
    NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;

--需求：统计每个用户pv数最多的前3分之1天。
--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
SELECT * from
(SELECT
     cookieid,
     createtime,
     pv,
     NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
 FROM website_pv_info) tmp where rn =1;