Oracle Analysis Function (4) First/last ranking Query

The First/last function allows us to sort a dataset and process the first and last records of the result of the sort. After querying the first or last record, we need to apply an aggregate function to handle a particular column, in order to guarantee the uniqueness of the return result, because there may be more than one record in the first and the last in the ranking. You can use the First/last function to avoid a self connection or subquery, thereby improving processing efficiency.

Syntax format:

Aggregate_function KEEP (Dense_rank Last order by

expr [DESC | ASC] [NULLS {i | Last}]

[, expr [DESC | ASC] [NULLS {i | Last}] ...)
[Over Query_partitioning_clause]

An order clause can specify more than one field.

Example:

Used as a common aggregate function

Sql> SELECT prod_subcategory, MIN (prod_list_price)   
  2    KEEP (Dense_rank-A (Prod_min_price)) as Lp_ OF_LO_MINP,  
MIN (Prod_min_price) as LO_MINP,  
MAX (prod_list_price) KEEP (Dense_rank Last order by (Prod_min_ Price)  
 as LP_OF_HI_MINP,  
MAX (Prod_min_price) as HI_MINP from the products  
WHERE prod_category= ' electronics '
GROUP by Prod_subcategory;  3    4    5    6    7    8    
      
prod_subcategory               lp_of_lo_minp    lo_minp lp_of_hi_minp    HI_MINP  
----------------------------------------------------------------------------  
Game Consoles                         299.99     299.99        299.99     299.99 Home  
Audio        499.99     499.99 599.99 599.99  
y Box accessories                       7.99       7.99         20.99      20.99  
Y box Games                             7.99         7.99 29.99      29.99

Of course, first/last can also be used as an analytic function to

Sql> SELECT prod_id, Prod_list_price,  
  2      MIN (prod_list_price) KEEP (Dense_rank ) over  
       (PARTITION by (prod_subcategory) as LP_OF_LO_MINP,  
    MAX (prod_list_price) KEEP (Dense_rank Prod_min_price)  
       (PARTITION by (prod_subcategory)) as LP_OF_HI_MINP from the products  
WHERE prod_subcategory = ' Documentation ';  3    4    5    6    
      
   prod_id prod_list_price lp_of_lo_minp lp_of_hi_minp  
------------------------------ ---------------------           44.99         44.99         44.99           44.99         44.99  
        44.99           44.99         44.99         44.99           44.99         44.99         44.99-44.99 44.99         44.99           44.99         44.99         44.99

Just now, we found the first and last record, if we want to find the first N records or the next n records, how to do that? Don't worry, Oracle provides us with the Ntile function.

The Ntile function allows us to divide a partition into a number of buckets, the number of records in each buckets is equal (the difference is no more than 1), and Oracle assigns bucket numbers to each bucket.

Syntax format:

Ntile (expr) over ([Query_partition_clause] order_by_clause)

Example:

 sql> SELECT Calendar_month_desc as month, To_char (SUM (Amount_sold), 2 ' 9,999,999,999 ') sales$, Ntile (4) Over (order by SUM (Amount_sold)) as TILE4 from sales, products, customers, times, channels WHERE Sales.prod_id=produc ts.prod_id and sales.cust_id=customers.cust_id and sales.time_id=times.time_id and Sales.channel_id=channels.channel  _id and times.calendar_year=2000 and prod_category= ' Electronics ' GROUP by CALENDAR_MONTH_DESC;         3 4 5 6 7 8 MONTH sales$ TILE4--------------------------------2000-02 242,416 1 2000-01 257,286 1 2000-03 280,011 1 2000-06 315,9          51 2 2000-05 316,824 2 2000-04 318,106 2 2000-07 433,824 3 2000-08 477,833 3 2000-12 553,534 3 2000-10 652,225 4 2000-1         1 661,147 4 2000-09691,449 4 
 

The above example divides each record into 4 bucket, so we can get the first 25% records through the tile4=1, tile=4 to get the last 25% records.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/