Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK

来源：互联网发布：androis中的js调试编辑：程序博客网时间：2024/04/27 23:58

转载地址：

lxw的大数据田地 » Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK

本文中介绍前几个序列函数，NTILE,ROW_NUMBER,RANK,DENSE_RANK，下面会一一解释各自的用途。

Hive版本为 apache-hive-0.13.1

注意：序列函数不支持WINDOW子句。（什么是WINDOW子句，点此查看前面的文章）

数据准备：

    cookie1,2015-04-10,1    cookie1,2015-04-11,5    cookie1,2015-04-12,7    cookie1,2015-04-13,3    cookie1,2015-04-14,2    cookie1,2015-04-15,4    cookie1,2015-04-16,4    cookie2,2015-04-10,2    cookie2,2015-04-11,3    cookie2,2015-04-12,5    cookie2,2015-04-13,6    cookie2,2015-04-14,3    cookie2,2015-04-15,9    cookie2,2015-04-16,7         CREATE EXTERNAL TABLE lxw1234 (    cookieid string,    createtime string,   --day     pv INT    ) ROW FORMAT DELIMITED     FIELDS TERMINATED BY ','     stored as textfile location '/tmp/lxw11/';         DESC lxw1234;    cookieid                STRING     createtime              STRING     pv INT          hive> select * from lxw1234;    OK    cookie1 2015-04-10      1    cookie1 2015-04-11      5    cookie1 2015-04-12      7    cookie1 2015-04-13      3    cookie1 2015-04-14      2    cookie1 2015-04-15      4    cookie1 2015-04-16      4    cookie2 2015-04-10      2    cookie2 2015-04-11      3    cookie2 2015-04-12      5    cookie2 2015-04-13      6    cookie2 2015-04-14      3    cookie2 2015-04-15      9    cookie2 2015-04-16      7

NTILE

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布

    SELECT     cookieid,    createtime,    pv,    NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,--分组内将数据分成2片    NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,  --分组内将数据分成3片    NTILE(4) OVER(ORDER BY createtime) AS rn3        --将所有数据分成4片    FROM lxw1234     ORDER BY cookieid,createtime;         cookieid day           pv       rn1     rn2     rn3    -------------------------------------------------    cookie1 2015-04-10      1       1       1       1    cookie1 2015-04-11      5       1       1       1    cookie1 2015-04-12      7       1       1       2    cookie1 2015-04-13      3       1       2       2    cookie1 2015-04-14      2       2       2       3    cookie1 2015-04-15      4       2       3       3    cookie1 2015-04-16      4       2       3       4    cookie2 2015-04-10      2       1       1       1    cookie2 2015-04-11      3       1       1       1    cookie2 2015-04-12      5       1       1       2    cookie2 2015-04-13      6       1       2       2    cookie2 2015-04-14      3       2       2       3    cookie2 2015-04-15      9       2       3       4    cookie2 2015-04-16      7       2       3       4

–比如，统计一个cookie，pv数最多的前1/3的天

    SELECT     cookieid,    createtime,    pv,    NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn     FROM lxw1234;         --rn = 1 的记录，就是我们想要的结果         cookieid day           pv       rn    ----------------------------------    cookie1 2015-04-12      7       1    cookie1 2015-04-11      5       1    cookie1 2015-04-15      4       1    cookie1 2015-04-16      4       2    cookie1 2015-04-13      3       2    cookie1 2015-04-14      2       3    cookie1 2015-04-10      1       3    cookie2 2015-04-15      9       1    cookie2 2015-04-16      7       1    cookie2 2015-04-13      6       1    cookie2 2015-04-12      5       2    cookie2 2015-04-14      3       2    cookie2 2015-04-11      3       3    cookie2 2015-04-10      2       3

ROW_NUMBER

ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
–比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。

    SELECT     cookieid,    createtime,    pv,    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn     FROM lxw1234;         cookieid day           pv       rn    -------------------------------------------     cookie1 2015-04-12      7       1    cookie1 2015-04-11      5       2    cookie1 2015-04-15      4       3    cookie1 2015-04-16      4       4    cookie1 2015-04-13      3       5    cookie1 2015-04-14      2       6    cookie1 2015-04-10      1       7    cookie2 2015-04-15      9       1    cookie2 2015-04-16      7       2    cookie2 2015-04-13      6       3    cookie2 2015-04-12      5       4    cookie2 2015-04-14      3       5    cookie2 2015-04-11      3       6    cookie2 2015-04-10      2       7

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 lxw1234     WHERE cookieid = 'cookie1';         cookieid day           pv       rn1     rn2     rn3     --------------------------------------------------     cookie1 2015-04-12      7       1       1       1    cookie1 2015-04-11      5       2       2       2    cookie1 2015-04-15      4       3       3       3    cookie1 2015-04-16      4       3       3       4    cookie1 2015-04-13      3       5       4       5    cookie1 2015-04-14      2       6       5       6    cookie1 2015-04-10      1       7       6       7         rn1: 15号和16号并列第3, 13号排第5    rn2: 15号和16号并列第3, 13号排第4    rn3: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。

0 0