Oracle Analytic function Series Top/bottom N, min () Keep First/last, ntile: Ranking null processing, query first, last __ static function

Directory
===============================================
1. The arrangement with null value
2.top/bottom N Query
3.first/last ranking Query
4. Query by Level

One, with a null value of the arrangement:

In the previous article on Oracle development topics: Analytic functions 2 (Rank, Dense_rank, row_number), we have learned how to arrange and group records in a full arrangement. What if the sorted data contains null values.

Sql> Select region_id, customer_id,
2SUM (customer_sales) Cust_sales,
3SUM (SUM (customer_sales)) over (partition by region_id) Ran_total,
4Rank () over (partition by region_id
5ORDER by sum (customer_sales) desc) rank
6From User_order
7Group by region_id, customer_id;

region_id customer_id cust_sales ran_total RANK
---------- ----------- ---------- ---------- ----------
Ten to62389011
TenNum180894962389012
Ten -132274762389013
Ten -121685862389014
Ten -98696462389015
Ten -90338362389016

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

Sql> Select region_id, customer_id,
2SUM (customer_sales) Cust_total,
3SUM (SUM (customer_sales)) over (partition by region_id) Reg_total,
4Rank () over (partition by region_id
ORDER by sum (customer_sales) descNULLS Last) rank
5From User_order
6Group by region_id, customer_id;

region_id customer_id cust_total reg_total RANK
---------- ----------- ---------- ---------- ----------
TenNum180894962389011
Ten -132274762389012
Ten -1216858 62389013
Ten -98696462389014
Ten -9033836238901 5
Ten to62389016

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:

Sql> SELECT *
Sql> from (select region_id,
Sql> customer_id,
Sql> sum (customer_sales) Cust_total,
Sql> rank (customer_sales) desc NULLS last) rank
Sql> from User_order
Sql> GROUP by region_id, customer_id)
sql> where rank <=3;

region_id customer_id cust_total RANK
---------- ----------- ---------- ----------
9 -22327031
8 -1944281 2
7 -19297743

Sql>

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

Sql> SELECT *
2From (select region_id,
3CUSTOMER_ID,
4SUM (customer_sales) Cust_total,
5SUM (SUM (customer_sales)) over (partition by region_id) Reg_total,
6Rank () over (partition by region_id
ORDER by sum (customer_sales) desc NULLS last) rank
7From User_order
8Group by region_id, customer_id)
9where Rank <=3;

region_id customer_id cust_total reg_total RANK
---------- ----------- ---------- ---------- ----------
5 4187827555856411
52122499255856412
5 5116992655856413
66178883663077661
6 9120895963077662
6Ten119674863077663
7 -192977468684951
7 of131043468684952
7 -125559168684953
8 -19442816854731