- 17 Dec, 2021
- 0 Comments
- 2 Mins Read
rank,dense_rank and row_number in Analytical Functions Hive
sample data
Load sample data to hive table
CREATE EXTERNAL TABLE IF NOT EXISTS users.emp
(id int,
name STRING,
department string,
salary STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'hdfs://localhost:9000/user/hive/input/users/'
TBLPROPERTIES("skip.header.line.count"="1");
RANK() Function
select name,department,salary,rank() over (partition by department order by salary desc) as salary_rank from users.emp; name department salary salary_rank Warren Rumble Accounting 60000 1 Warren Rumble Accounting 60000 1 Warren Rumble Accounting 60000 1 Rosella Tomaszek Accounting 50000 4 Nike Lighton Accounting 50000 4 Rosella Tomaszek Accounting 50000 4 Nike Lighton Accounting 50000 4 Rosella Tomaszek Accounting 50000 4 Nike Lighton Accounting 50000 4 Sherri Eckh Accounting 20000 10 Sherri Eckh Accounting 20000 10 Sherri Eckh Accounting 20000 10 Waylin Knoton Accounting 10000 13 Waylin Knoton Accounting 10000 13 Waylin Knoton Accounting 10000 13 Vanni Sandall Marketing 50000 1 Vanni Sandall Marketing 50000 1 Vanni Sandall Marketing 50000 1 Lettie Diloway Marketing 30000 4 Lemar Speck Marketing 30000 4 Lemar Speck Marketing 30000 4 Lettie Diloway Marketing 30000 4 Othilia Timmons Marketing 30000 4 Lemar Speck Marketing 30000 4 Lettie Diloway Marketing 30000 4 Othilia Timmons Marketing 30000 4 Othilia Timmons Marketing 30000 4 Thurston Doumenc Marketing 20000 13 Thurston Doumenc Marketing 20000 13 Thurston Doumenc Marketing 20000 13 Sagar Product Management 50000 1 Sagar Product Management 50000 1 Steffane Davenport Product Management 50000 1 Sagar Product Management 50000 1 Steffane Davenport Product Management 50000 1 Steffane Davenport Product Management 50000 1 Ankush Product Management 20000 7 Ankush Product Management 20000 7 Ankush Product Management 20000 7 Time taken: 20.325 seconds, Fetched: 39 row(s) hive>
DENSE_RANK() Function
select name,department,salary,dense_rank() over (partition by department order by salary desc) as salary_rank from users.emp; name department salary salary_rank Warren Rumble Accounting 60000 1 Warren Rumble Accounting 60000 1 Warren Rumble Accounting 60000 1 Rosella Tomaszek Accounting 50000 2 Nike Lighton Accounting 50000 2 Rosella Tomaszek Accounting 50000 2 Nike Lighton Accounting 50000 2 Rosella Tomaszek Accounting 50000 2 Nike Lighton Accounting 50000 2 Sherri Eckh Accounting 20000 3 Sherri Eckh Accounting 20000 3 Sherri Eckh Accounting 20000 3 Waylin Knoton Accounting 10000 4 Waylin Knoton Accounting 10000 4 Waylin Knoton Accounting 10000 4 Vanni Sandall Marketing 50000 1 Vanni Sandall Marketing 50000 1 Vanni Sandall Marketing 50000 1 Lettie Diloway Marketing 30000 2 Lemar Speck Marketing 30000 2 Lemar Speck Marketing 30000 2 Lettie Diloway Marketing 30000 2 Othilia Timmons Marketing 30000 2 Lemar Speck Marketing 30000 2 Lettie Diloway Marketing 30000 2 Othilia Timmons Marketing 30000 2 Othilia Timmons Marketing 30000 2 Thurston Doumenc Marketing 20000 3 Thurston Doumenc Marketing 20000 3 Thurston Doumenc Marketing 20000 3 Sagar Product Management 50000 1 Sagar Product Management 50000 1 Steffane Davenport Product Management 50000 1 Sagar Product Management 50000 1 Steffane Davenport Product Management 50000 1 Steffane Davenport Product Management 50000 1 Ankush Product Management 20000 2 Ankush Product Management 20000 2 Ankush Product Management 20000 2 Time taken: 20.13 seconds, Fetched: 39 row(s) hive>
ROW_NUMBER()
select name,department,salary,row_number() over (partition by department order by salary desc) as salary_rank from users.emp; name department salary salary_rank Warren Rumble Accounting 60000 1 Warren Rumble Accounting 60000 2 Warren Rumble Accounting 60000 3 Rosella Tomaszek Accounting 50000 4 Nike Lighton Accounting 50000 5 Rosella Tomaszek Accounting 50000 6 Nike Lighton Accounting 50000 7 Rosella Tomaszek Accounting 50000 8 Nike Lighton Accounting 50000 9 Sherri Eckh Accounting 20000 10 Sherri Eckh Accounting 20000 11 Sherri Eckh Accounting 20000 12 Waylin Knoton Accounting 10000 13 Waylin Knoton Accounting 10000 14 Waylin Knoton Accounting 10000 15 Vanni Sandall Marketing 50000 1 Vanni Sandall Marketing 50000 2 Vanni Sandall Marketing 50000 3 Lettie Diloway Marketing 30000 4 Lemar Speck Marketing 30000 5 Lemar Speck Marketing 30000 6 Lettie Diloway Marketing 30000 7 Othilia Timmons Marketing 30000 8 Lemar Speck Marketing 30000 9 Lettie Diloway Marketing 30000 10 Othilia Timmons Marketing 30000 11 Othilia Timmons Marketing 30000 12 Thurston Doumenc Marketing 20000 13 Thurston Doumenc Marketing 20000 14 Thurston Doumenc Marketing 20000 15 Sagar Product Management 50000 1 Sagar Product Management 50000 2 Steffane Davenport Product Management 50000 3 Sagar Product Management 50000 4 Steffane Davenport Product Management 50000 5 Steffane Davenport Product Management 50000 6 Ankush Product Management 20000 7 Ankush Product Management 20000 8 Ankush Product Management 20000 9 Time taken: 22.099 seconds, Fetched: 39 row(s)