Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what is the difference between grouping and ranking rank,row_number,dense_rank". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Grouping for ranking

I believe that many users who use Mysql must be afraid of the need for grouping and ranking.

But it can be easily implemented in hive or oracle.

Using the window function: rank () over () / row_number () over () / dense_rank () over () function can be easily completed.

Window function

We can understand that we first select the fields that need to be analyzed, and then analyze them through the window function.

So the result of the window function must be one-to-one, even if you use the grouping operation in the window to produce the same value.

Example select user_id,user_type,sales,-- Analysis fields of window function RANK () over (partition by user_type order by sales desc) as rank, ROW_NUMBER () over (partition by user_type order by sales desc) as row_number, DENSE_RANK () over (partition by user_type order by sales desc) as dense_rankfrom order_detail

According to the understanding of the window function, let's look at the above example

The fields to be analyzed are user_type,sales, and the execution process select these fields (select may not be accurate, but you need to get these fields for analysis first)

Using window function to analyze over () is a window function. Partition by and order by are usually written in over () to group and sort the analyzed data.

The analysis strategy should be defined before the over () function, such as rank (), row_number (), dense_rank () are different analysis strategies.

Results user_iduser_typesalesrankrow_numberdense_rankwutongnew6111qishilinew5222lilisinew5232wangernew3443zhangsanew2554qibaqiunew1665liiunew1675liweiold3111wangshiold2222lisiold1333

As in the above table, rank () / row_number () / dense_rank () are all ranked.

When dealing with the same ranking, rank () carries out the sequential ranking according to the number of data of the same ranking.

Dense_rank () will not postpone ranking when dealing with the same ranking.

Row_number (), such as the function name, is just the number of rows that are ranked.

Support for aggregate functions after HIVE2.10 the over () function supports SELECT rank () OVER (ORDER BY sum (b)) FROM tableGROUP BY a

The above equals to

SELECT sum_b rank () OVER (ORDER BY sum_b) FROM (SELECT SUM (b) AS sum_b FROM table GROUP BY a) t; "what is the difference between grouping and ranking rank,row_number,dense_rank" is introduced here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.