Oracle Development Analysis Functions (Top/bottom N, First/last, Ntile) _oracle

One, with a null value of the arrangement:

In the previous "Oracle Development Analysis Function (Rank, Dense_rank, Row_number)" article, we have learned how to arrange for a batch of records, grouped. What if the sorted data contains null values?

Copy Code code as follows:

Sql> Select region_id, customer_id,
SUM (customer_sales) Cust_sales,
SUM (SUM (customer_sales)) over (partition by region_id) Ran_total,
Rank () over (partition by region_id
ORDER by sum (customer_sales) desc) Rank
From User_order
Group by region_id, customer_id;

region_id customer_id cust_sales ran_total RANK
---------- ----------- ---------- ---------- ----------
10 31 6238901 1
10 26 1808949 6238901 2
10 27 1322747 6238901 3
10 30 1216858 6238901 4
10 28 986964 6238901 5
10 29 903383 6238901 6

We see here is a record of the Cust_total field value is null, but actually in the first place! Obviously it's not reasonable. So let's readjust our ranking strategy and look at the following statement:

Copy Code code as follows:

Sql> Select region_id, customer_id,
SUM (customer_sales) Cust_total,
SUM (SUM (customer_sales)) over (partition by region_id) Reg_total,
Rank () over (partition by region_id
ORDER by sum (customer_sales) desc NULLSlast) Rank
From User_order
Group by region_id, customer_id;

region_id customer_id cust_total reg_total RANK
---------- ----------- ---------- ---------- ----------
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
10 28 986964 6238901 4
10 29 903383 6238901 5
10 31 6238901 6

Green highlights, NULLS Last/first told Oracle to let null value rank last after first.

Note is nulls, not null.

Second, Top/bottom N query:

In the day-to-day work of production, we often encounter such a query: Find the top 5 order customers, find the top 10 sales people, and so on. Now this is a very simple question for us. Let's use a practical example to illustrate the following:

"1" to find out the top 3 of all orders total number of major customers:

Copy Code code as follows:

Sql> SELECT *
From (select region_id,
CUSTOMER_ID,
SUM (customer_sales) Cust_total,
Rank () over (order by sum (customer_sales) desc NULLS last) rank
From User_order
Group by region_id, customer_id)
where rank <= 3;

region_id customer_id cust_total RANK
---------- ----------- ---------- ----------
9 25 2232703 1
8 17 1944281 2
7 14 1929774 3

Sql>

"2" to find out the top 3 of the total orders in each region of the major customers:

Copy Code code as follows:

Sql> SELECT *
From (select region_id,
CUSTOMER_ID,
SUM (customer_sales) Cust_total,
SUM (SUM (customer_sales)) over (partition by region_id) Reg_total,
Rank () Over ( Partition by region_id
ORDER by sum (customer_sales) desc NULLS last) rank
From User_order
Group by region_id, customer_id)
where rank <= 3;

region_id customer_id cust_total reg_total RANK
---------- ----------- ---------- ---------- ----------
5 4 1878275 5585641 1
5 2 1224992 5585641 2
5 5 1169926 5585641 3
6 6 1788836 6307766 1
6 9 1208959 6307766 2
6 10 1196748 6307766 3
7 14 1929774 6868495 1
7 13 1310434 6868495 2
7 15 1255591 6868495 3
8 17 1944281 6854731 1
8 20 1413722 6854731 2
8 18 1253840 6854731 3
9 25 2232703 6739374 1
9 23 1224992 6739374 2
9 24 1224992 6739374 2
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3

Rows selected.

Third, first/last ranking query:

Imagine the following scenario: Find the customer with the most total orders and the fewest. According to the knowledge we learned earlier, this requires at least 2 queries. The first query is sorted in descending order totals to get the first place, and the second query in ascending order totals to get the last one. Is it annoying? Because the rank function only tells us the results of the rankings, it doesn't automatically filter the results for us.

Fortunately, Oracle provides us with two additional functions outside of the permutation function: first and last functions, which are specifically designed to solve this problem. Or use the example to speak:

Copy Code code as follows:

sql> Select min (customer_id)
Keep (Dense_rank-A-sum (customer_sales) desc)
min (customer_id)
Keep (Dense_rank Last order by sum (customer_sales) desc) Last
From User_order
Group BY CUSTOMER_ID;

The last
---------- ----------
31 1

Here are a few of the more confusing places to look:

① Why do you use the Min function here?
②keep, what does this thing do?
What does ③fist/last do?
What's the difference between ④dense_rank and Dense_rank (), can you change to rank?

First answer the first question: the function of the Min function is to be used to guarantee the return of a unique record when there are multiple first/last cases. What would be the consequences if we removed them?

Copy Code code as follows:

Sql> Select Keep (Dense_rank-I by sum (customer_sales) desc)
Keep (Dense_rank Last order by sum (customer_sales) desc) Last
From User_order
Group BY CUSTOMER_ID;
Select Keep (Dense_rank (customer_sales) desc)
*

ERROR at line 1:
Ora-00907:missing Right Parenthesis

Now let's look at the 2nd question: What is keep for? From the results above we already know that Oracle's ranking results are only "retained" 2 data, which is the role of Keep. Tell Oracle to keep only records that meet the keep criteria.

So what is the qualifying record? That's the 3rd question. Dense_rank is a policy that tells Oracle to arrange, and First/last tells the criteria for the final filter.

4th question: What if we change Dense_rank to rank?

Copy Code code as follows:

sql> Select min (region_id)
Keep (rank order by sum (customer_sales) desc)
Min (region_id)
Keep (rank last order by sum (customer_sales) desc) Last
From User_order
Group BY region_id;
Select min (region_id)
*

ERROR at line 1:
Ora-02000:missing Dense_rank

Four, according to the level of inquiry:

Now we've seen how to get Top/bottom N, first, and last record through Oracle's analytic functions. Sometimes we receive a demand similar to the following: Find the top 1/5 of the total order.

Very familiar, isn't it? We will immediately think of the method mentioned in the 2nd, but the rank function only for us to do the ranking, do not know each ranking in the overall ranking of the relative position, this time introduced another analytic function ntile, below we take the above requirements as an example to explain:

Copy Code code as follows:

Sql> Select region_id,
CUSTOMER_ID,
Ntile (5) over (order by sum (customer_sales) desc) til
From User_order
Group by region_id, customer_id;

region_id customer_id TILE
---------- ----------- ----------
10 31 1
9 25 1
10 26 1
6 6 1
8 18 2
5 2 2
9 23 3
6 9 3
7 11 3
5 3 4
6 8 4
8 16 4
6 7 5
10 29 5
5 1 5

The Ntil function calculates the proportions of each record in the recordset, and we see that all of the records are divided into 5 levels, so if we only need the first 1/5 records, we need to intercept the tile value of 1. If we need the top 25% record (that is, 1/4) then we just need to set Ntile (4).

The above is Oracle in the first few, after several, the most, at least, and by the level of all the contents of the query, I hope to give you a reference, but also hope that we support the cloud habitat community.