Hive函数：CUME_DIST,PERCENT_RANK

参考自：大数据田地http://lxw1234.com/archives/2015/04/185.htm

数据准备：

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
 
CREATE EXTERNAL TABLE test_data (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile location '/user/jc_rc_ftp/test_data';
 
 
hive> select * from test_data;
+-----------------+-------------------+----------------+--+
| test_data.dept  | test_data.userid  | test_data.sal  |
+-----------------+-------------------+----------------+--+
| d1              | user1             | 1000           |
| d1              | user2             | 2000           |
| d1              | user3             | 3000           |
| d2              | user4             | 4000           |
| d2              | user5             | 5000           |
+-----------------+-------------------+----------------+--+

CUME_DIST

–CUME_DIST 小于等于当前值的行数/分组内总行数
–比如，统计小于等于当前薪水的人数，所占总人数的比例

SELECT 
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM test_data;
+-------+---------+-------+------+---------------------+--+
| dept  | userid  |  sal  | rn1  |         rn2         |
+-------+---------+-------+------+---------------------+--+
| d1    | user1   | 1000  | 0.2  | 0.3333333333333333  |
| d1    | user2   | 2000  | 0.4  | 0.6666666666666666  |
| d1    | user3   | 3000  | 0.6  | 1.0                 |
| d2    | user4   | 4000  | 0.8  | 0.5                 |
| d2    | user5   | 5000  | 1.0  | 1.0                 |
+-------+---------+-------+------+---------------------+--+
rn1: 没有partition,所有数据均为1组，总行数为5，
     第一行：小于等于1000的行数为1，因此，1/5=0.2
     第三行：小于等于3000的行数为3，因此，3/5=0.6
rn2: 按照部门分组，dpet=d1的行数为3,
     第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

PERCENT_RANK

–PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
应用场景不了解，可能在一些特殊算法的实现中可以用到吧。

SELECT 
dept,
userid,
sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内
RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分组内总行数
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM test_data;
+-------+---------+-------+-------+-------+-------+------+--+
| dept  | userid  |  sal  |  rn1  | rn11  | rn12  | rn2  |
+-------+---------+-------+-------+-------+-------+------+--+
| d1    | user1   | 1000  | 0.0   | 1     | 5     | 0.0  |
| d1    | user2   | 2000  | 0.25  | 2     | 5     | 0.5  |
| d1    | user3   | 3000  | 0.5   | 3     | 5     | 1.0  |
| d2    | user4   | 4000  | 0.75  | 4     | 5     | 0.0  |
| d2    | user5   | 5000  | 1.0   | 5     | 5     | 1.0  |
+-------+---------+-------+-------+-------+-------+------+--+

这样只要排序字段为null,就会放在最后，而不会影响排序结果

rn1: rn1 = (rn11-1) / (rn12-1) 
       第一行,(1-1)/(5-1)=0/4=0
       第二行,(2-1)/(5-1)=1/4=0.25
       第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分组，
     dept=d1的总行数为3
     第一行，(1-1)/(3-1)=0
     第三行，(3-1)/(3-1)=1