Ntile,row_number,rank and Dense_rank of hive analytic form functions

Continue to introduce several sequence functions:

NTILE. Row_number,rank and Dense_rank

Environmental information:
Hive Version number is Apache-hive-0.14.0-bin
Hadoop version number is hadoop-2.6.0
Tez version number is tez-0.7.0

Data:

p088888888888,2016-02-10,1

p088888888888,2016-02-11,3

p088888888888,2016-02-12,1

p088888888888,2016-02-13,9

p088888888888,2016-02-14,3

p088888888888,2016-02-15,12

p088888888888,2016-02-16,3

p066666666666,2016-02-10,6

p066666666666,2016-02-11,2

p066666666666,2016-02-12,1

p066666666666,2016-02-13,9

p066666666666,2016-02-14,2

p066666666666,2016-02-15,20

p066666666666,2016-02-16,2

Import the data into the hive table:

Load data local inpath '/home/hadoop/testhivedata/windows_func.txt ' overwrite into table windows_func;

NTILE
NTILE (n). Used to cut grouped data into n slices in order. Returns the current slice value
NTILE does not support rows between, for example NTILE (2) over (PARTITION by Polno ORDER by createtime rows Between 3 preceding and current ROW)
Suppose the slices are uneven. The distribution of the first slice is added by default.

SELECT

Polno,

Createtime,

Pnum,

NTILE (2) over (PARTITION by Polno ORDER by Createtime) as RN1,-- divide the data into groups within the 2 film

NTILE (3) over (PARTITION by Polno ORDER by Createtime) as RN2,-- divide the data into groups within the 3 film

NTILE (4) over (ORDER by Createtime) as Rn3-- divide all the data into 4 film

From Windows_func ORDER by Polno,createtime;

Results:

Polno createtime pnum rn1 rn2 rn3

P066666666666 2016-02-10 6 1 1 1

P066666666666 2016-02-11 2 1 1 1

P066666666666 2016-02-12 1 1 1 2

P066666666666 2016-02-13 9 1 2 2

P066666666666 2016-02-14 2 2 2 3

P066666666666 2016-02-15 20 2 3 3

P066666666666 2016-02-16 2 2 3 4

P088888888888 2016-02-10 1 1 1 1

P088888888888 2016-02-11 3 1 1 1

P088888888888 2016-02-12 1 1 1 2

P088888888888 2016-02-13 9 1 2 2

P088888888888 2016-02-14 3 2 2 3

P088888888888 2016-02-15 12 2 3 4

P088888888888 2016-02-16 3 2 3 4

For example, the top 1/3 days of the pnum number

--rn = 1 is the result of what we want.

SELECT Polno,

Createtime,

Pnum,

NTILE (3) over (PARTITION by Polno ORDER by Pnum DESC) as RN

From Windows_func;

Results:

Polno Createtime Pnum RN

P066666666666 2016-02-15 20 1

P066666666666 2016-02-13 9 1

P066666666666 2016-02-10 6 1

P066666666666 2016-02-16 2 2

P066666666666 2016-02-11 2 2

P066666666666 2016-02-14 2 3

P066666666666 2016-02-12 1 3

P088888888888 2016-02-15 12 1

P088888888888 2016-02-13 9 1

P088888888888 2016-02-16 3 1

P088888888888 2016-02-11 3 2

P088888888888 2016-02-14 3 2

P088888888888 2016-02-12 1 3

P088888888888 2016-02-10 1 3

Row_number

Row_number () – from 1 , the sequence of records within a group is generated , in accordance with the order
– For example, in descending order according to pnum . Generate Pnum rank within a group every day
Row_number () has many application scenarios. For example, get the first sort of records in a group, and so on.

SELECT Polno,

Createtime,

Pnum,

Row_number () over (PARTITION by Polno ORDER by pnum Desc) as RN

From Windows_func;

Results:

Polno Createtime Pnum RN

P066666666666 2016-02-15 20 1

P066666666666 2016-02-13 9 2

P066666666666 2016-02-10 6 3

P066666666666 2016-02-16 2 4

P066666666666 2016-02-11 2 5

P066666666666 2016-02-14 2 6

P066666666666 2016-02-12 1 7

P088888888888 2016-02-15 12 1

P088888888888 2016-02-13 9 2

P088888888888 2016-02-16 3 3

P088888888888 2016-02-11 3 4

P088888888888 2016-02-14 3 5

P088888888888 2016-02-12 1 6

P088888888888 2016-02-10 1 7

RANK and Dense_rank

-rank () The rank of the data item in the grouping, the rank equal will leave the vacancy in the position

-dense_rank () The rank of the data item in the grouping, the rank equal will not leave the vacancy in the rank

SELECT Polno,

Createtime,

Pnum,

RANK () over (PARTITION by Polno ORDER by pnum Desc) as RN1,

Dense_rank () over (PARTITION by Polno ORDER by pnum Desc) as RN2,

Row_number () over (PARTITION by Polno ORDER by Pnum DESC) as Rn3

From windows_func WHERE polno = ' P066666666666 ';

Results:

Polno createtime pnum rn1 rn2 rn3

P066666666666 2016-02-15 20 1 1 1

P066666666666 2016-02-13 9 2 2 2

P066666666666 2016-02-10 6 3 3 3

P066666666666 2016-02-11 2 4 4 4

P066666666666 2016-02-14 2 4 4 5

P066666666666 2016-02-16 2 4 4 6

P066666666666 2016-02-12 1 7 5 7

Ntile,row_number,rank and Dense_rank of hive analytic form functions