Hive Analytics Functions - rank() vs dense_rank() vs percent_rank() vs row_umber() vs cume_dist()

RANK - Rank of each row within partition of result set.

DENSE_RANK - Mostly, similar to RANK. But, there will be no gaps in ranking.

PERCENT_RANK - Relative Rank of row within group of rows.

ROW_NUMBER - Sequential number of row within partition of a result set.

CUME_DIST - For row r, the number of rows with value lower than or equal to value of r , divided by number of rows evaluated in partition.


Practice -

hive> create table test (v string ) row format delimited fields terminated by ',';

hive> alter table test add columns (t string);

hive> load data local inpath '/root/test' overwrite into table test;

test data in local looks like below -

a,1
a,2
a,3
a,1
a,2
b,1
c,1
c,2
d,1
e,1

Execute below query and analyze the result -

hive> select v, t, rank() over (partition by v ), dense_rank() over (partition by v  ), row_number() over (partition by v  ), percent_rank()over (partition by v  ), cume_dist() over (partition by v  ) from test;

a       2       1       1       1       0.0     1.0

a       1       1       1       2       0.0     1.0

a       3       1       1       3       0.0     1.0

a       2       1       1       4       0.0     1.0

a       1       1       1       5       0.0     1.0

b       1       1       1       1       0.0     1.0

c       2       1       1       1       0.0     1.0

c       1       1       1       2       0.0     1.0

d       1       1       1       1       0.0     1.0

e       1       1       1       1       0.0     1.0

Note - Output of rank(), dense_rank() is same. Also, percent_rank() & cume_dist() results same output for each row in entire data set.

Above, example only clears us functioning of row_number() as each row within partition is given a sequential number. 

But, this example will also help us to understand that there is something wrong and we will hence forth proceed to correct results.

By the way, we can also execute analytics functions with empty  over clause. For example - 

hive> select v, t, rank() over ( ), dense_rank() over (  ), row_number() over ( ), percent_rank()over (), cume_dist() over (  ) from test;

e       1       1       1       1       0.0     1.0

d       1       1       1       2       0.0     1.0

c       2       1       1       3       0.0     1.0

c       1       1       1       4       0.0     1.0

b       1       1       1       5       0.0     1.0

a       2       1       1       6       0.0     1.0

a       1       1       1       7       0.0     1.0

a       3       1       1       8       0.0     1.0

a       2       1       1       9       0.0     1.0

a       1       1       1       10      0.0     1.0

What happened above is pretty explanatory. But, to hint you see row_number() column results.

Now, Lets execute below query - 

hive> select v, t, rank() over (partition by v order by t), dense_rank() over (partition by v  order by t), row_number() over (partition by v  order by t), percent_rank()over (partition by v  order by t), cume_dist() over (partition by v  order by t) from test;

a       1       1       1       1       0.0     0.4

a       1       1       1       2       0.0     0.4

a       2       3       2       3       0.5     0.8

a       2       3       2       4       0.5     0.8

a       3       5       3       5       1.0     1.0

b       1       1       1       1       0.0     1.0

c       1       1       1       1       0.0     0.5

c       2       2       2       2       1.0     1.0

d       1       1       1       1       0.0     1.0

e       1       1       1       1       0.0     1.0

• In above results see that same rows are given same rank(). But rank = 2 is missing.
• Whereas, in dense_rank() there is no missing sequential number.
• Row_number() works fine for us.
• percent_rank() actually gives us relative rank
• cume_dist() finds distance of each row within result set.