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

Heim

Datenbank

MySQL-Tutorial

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

Jun 07, 2016 pm 02:51 PM

hive 函数分析窗口

问题导读： 1.NTILE作用是什么？ 2.按照pv降序排列，生成分组内每天的pv名次可使用哪个窗口函数？ 3.RANK 和 DENSE_RANK作用是什么？接上篇：Hive分析窗口函数（一）SUM,AVG,MIN,MAX 本文中介绍前几个序列函数，NTILE,ROW_NUMBER,RANK,DENSE_RANK，下面会一

问题导读：
1.NTILE作用是什么？
2.按照pv降序排列，生成分组内每天的pv名次可使用哪个窗口函数？
3.RANK 和 DENSE_RANK作用是什么？

接上篇：Hive分析窗口函数（一）SUM,AVG,MIN,MAX

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

注意：序列函数不支持WINDOW子句。（什么是WINDOW子句，Hive分析窗口函数（一）SUM,AVG,MIN,MAX)

数据准备：

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&gt; 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

Nach dem Login kopieren

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

Nach dem Login kopieren

比如，统计一个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

Nach dem Login kopieren

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

Nach dem Login kopieren

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: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。

Nach dem Login kopieren

Erklärung dieser Website

Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn