How to use Row Number window function in Hive

In view of how to use the Row Number window function in Hive, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Row_number

Earlier, when we introduced the window function, we talked about the use scenario of the window function, and we also gave it a name to distinguish between the general window function and the special window function. Today we will take a look at the window function related to sorting. Because it is a window function, and we say it is used to sort, we can probably guess that it is used to sort the data in the window.

In fact, with regard to sorting, we have also introduced the common sorting methods such as order by,sort by, Hive syntax, and why there are window functions for sorting, because the previous order by,sort by can sort but can not return the sorted value (rank). If you have used mysql, you will know how to write stored procedures or use custom variables to complete this function. The same is true for row number. You can return the value of the corresponding sort order according to our custom sorting rules.

So we think that row_number is a window sorting function, but hive does not provide a non-window sorting function, but we said earlier that if there is no partition by in the definition of a window, then the entire data input is regarded as a window, then in this case we can also use the window sorting function to complete global sorting.

test data

Here is a test data id,dept,salary, and then we use this test data to learn our window sorting function.

1, sales, 10000 2, sales, 14000 3, sales, 10000 4, back-end, 20000 5, back-end, 25000 6, back-end, 32000 7 create table ods_num_window (id string, dept string, salary int) ROW FORMAT DELIMITED FIELDS TERMINATED BY',',' LOAD DATA LOCAL INPATH'/ Users/liuwenqiang/workspace/hive/number.txt' OVERWRITE INTO TABLE ods_num_window

Learn row_number from examples

The employees of each department are sorted according to the descending order of wages.

Select *, row_number () over (partition by dept order by salary desc) as rn from ods_num_window

We see that each department has its own first place, and it is obvious that the ranking takes place within each department.

All employees are sorted in descending order of wages.

Select *, row_number () over (order by salary desc) as rn from ods_num_window

When we do not define the partition by clause, all our data is placed in a window, and our sorting is the global sort. In fact, if you take a closer look at the section on the first acquaintance of our Hive grammar window function, you will know that partition by actually defines a child window. If there is no child window, it is a window. If all the data is placed in one window, it is global sorting.

Take the top two salaries of each department

This is a very common scenario for the use of the row_number () function top-N. In fact, if you take a closer look at our section on the first acquaintance of the window function of Hive grammar, you will know that partition by actually defines a child window, and the top-N here is essentially the top-N of a child window.

Select * from (select *, row_number () over (partition by dept order by salary desc) as rn from ods_num_window) tmp where rn