Oracle Development Analysis Function (Rank, Dense_rank, Row_number) _oracle

First, use rownum for record ranking:

In the previous "Introduction to Oracle Development analysis function over", we understand the basic application of analytic function, now we consider the following questions:

① to rank all customers by total order
② rank by region and customer order totals
③ find the top 13 customers in order total
④ find customers with the highest and lowest order totals
⑤ find the top 25% of the total order

According to the idea of the first article, we can only do statistics on the data of each group, if you need to rank, then simply add rownum to the line? If the facts are as simple as they might be, let's practice them.

"1" Test environment:

Copy Code code as follows:

sql> desc User_order;

Name Null? Type
----------------------------------------- -------- ----------------------------
region_id Number (2)
CUSTOMER_ID Number (2)
Customer_sales number

"2" test data:

Copy Code code as follows:

Sql> SELECT * from User_order order by Customer_sales;

region_id customer_id Customer_sales
---------- ----------- --------------
5 1 151162
10 29 903383
6 7 971585
10 28 986964
9 21 1020541
9 22 1036146
8 16 1068467
6 8 1141638
5 3 1161286
5 5 1169926
8 19 1174421
7 12 1182275
7 11 1190421
6 10 1196748
6 9 1208959
10 30 1216858
5 2 1224992
9 24 1224992
9 1224992
8 18 1253840
7 15 1255591
7 13 1310434
10 27 1322747
8 20 1413722
6 6 1788836
10 26 1808949
5 4 1878275
7 14 1929774
8 17 1944281
9 25 2232703

Rows selected.

Note that there are 3 records of the total order amount is the same. If we need to sift through the top 12 customers now, what would be the consequences if we use rownum?

Copy Code code as follows:

Sql> Select RowNum, t.*
From (SELECT *
From User_order
ORDER BY customer_sales Desc) t
where RowNum <= 12
ORDER BY customer_sales Desc;

RowNum region_id customer_id Customer_sales
---------- ---------- ----------- --------------
1 9 25 2232703
2 8 17 1944281
3 7 14 1929774
4 5 4 1878275
5 10 26 1808949
6 6 6 1788836
7 8 20 1413722
8 10 27 1322747
9 7 13 1310434
10 7 15 1255591
11 8 18 1253840
5 2 1224992

Rows selected.

Obviously, if we simply sort by rownum, we're missing another two records (refer to the results above).

Second, the use of analytic functions to rank records:

For the above scenario, Oracle provides 3 analytic functions from 8i: Rand,dense_rank,row_number to solve such problems, let's take a look at the role of these 3 analytic functions and the difference between each other:

The Rank,dense_rank,row_number function produces a natural number from 1 to n for each record, and the value of n may be less than or equal to the total number of records. The only difference between these 3 functions is the ranking strategy when the same data is encountered.

①row_number:

The Row_number function returns a unique value, and when the same data is encountered, the rankings are incremented in the order in which they are recorded in the recordset.

②dense_rank:
The Dense_rank function returns a unique value, except that when the same data is encountered, all the same data are ranked the same at this time.

③rank:
The Rank function returns a unique value, except when the same data is encountered, when all the same data is ranked the same, and the rank is vacated between the last same record and the ranking of the next different record.

This kind of introduction is a bit difficult to understand, let's illustrate it by example, the following example illustrates the different ranking strategies for 3 different functions when encountering the same data:

Copy Code code as follows:

Sql> Select region_id, customer_id, sum (customer_sales) Total,
Rank () Over (order by sum (customer_sales) desc) Rank,
Dense_rank () over (order by sum (customer_sales) desc) Dense_rank,
Row_number () over (order by sum (customer_sales) desc) Row_number
From User_order
Group by region_id, customer_id;

region_id customer_id Total RANK dense_rank row_number
---------- ----------- ---------- ---------- ---------- ----------

8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 of
9 24 1224992 12 12 -
10 30 1216858 of

Rows selected.

Notice the green highlights above, which show 3 different ranking strategies in a lively way:

① for the first record, 3 functions are ranked the same: 12

② when the second record is the same, rank and Dense_rank still give the same rank of 12, while row_number increments by 13, and so on to the same record in the third article.

③ when ranking to the next different record, you can see that the rank function vacated the rank of 13,14 between 12 and 15 because the 2 rankings are actually accounted for by the same second to third record. And Dense_rank is incremented sequentially. Row_number functions are also sequential increments

Comparing the above 3 different strategies, we will choose the time according to the customer's needs to decide:

① if the customer only needs to specify the number of records, then the use of Row_number is the simplest, but there is a risk of missing records

② If the customer wants all records to reach the rank level, then rank or Dense_rank is a good choice. As for choosing which one to look at the customer's needs, choose Dense_rank or get the biggest record

Third, use analytic functions to rank records in groups:

The ranking above is ranked by the total order, now with further: if it is the total number of orders for each region to rank it? This means another group operation: grouping records by region and ranking them. Thanks to Oracle's support, all we have to do is add a group clause to the top of the over function: partition by region_id.

Copy Code code as follows:

Sql> Select region_id, customer_id,
SUM (customer_sales) Total,
Rank () Over ( partition by region_id
ORDER by sum (customer_sales) desc) rank,
Dense_rank () Over ( partition by region_id
ORDER by sum (customer_sales) desc) Dense_rank,
Row_number () Over ( partition by region_id
ORDER by sum (customer_sales) desc) row_number
From User_order
Group by region_id, customer_id;

region_id customer_id Total RANK dense_rank row_number
---------- ----------- ---------- ---------- ---------- ----------
5 4 1878275 1 1 1
5 2 1224992 2 2 2
5 5 1169926 3 3 3
6 6 1788836 1 1 1
6 9 1208959 2 2 2
6 10 1196748 3 3 3

Rows selected.

Now we see that the rankings will be based on all regions, not all regions! The role of the Partition by clause in the permutation function is to divide a result set into sections so that the permutation function can be applied to each subset.

We have already solved 2 of the 5 issues we mentioned earlier (1th, 2), and the remaining 3 questions (Top/bottom N,first/last, ntile) will be explained in the next article.

The above is Oracle rank, Dense_rank, row_number all the functions of the use of all content, hope to give you a reference, but also hope that we support the cloud habitat community.