ORACLE分析函数---FIRST/LAST排名查询

first/last函数允许我们对某数据集进行排序，并对排序结果的第一条记录和最后一条记录进行处理。在查询出第一条或者最后一条记录后，我们需要应用一个聚合函数来处理特定列，这是为了保证返回结果的唯一性，因为排名第一的记录和排名最后的记录可能会存在多个。使用first/last函数可以避免自连接或者子查询，因此可以提高处理效率。

语法格式：

aggregate_function KEEP ( DENSE_RANK LAST ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]

order子句可以指定多个字段。

示例：

作为普通的聚合函数使用

[sql] 
SQL> SELECT prod_subcategory, MIN(prod_list_price)   
  2    KEEP (DENSE_RANK FIRST ORDER BY (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 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

当然，first/last 也可以作为分析函数来使用

[sql] 
SQL> SELECT prod_id, prod_list_price,  
  2      MIN(prod_list_price) KEEP (DENSE_RANK FIRST ORDER BY (prod_min_price))  
       OVER(PARTITION BY (prod_subcategory)) AS LP_OF_LO_MINP,  
    MAX(prod_list_price) KEEP (DENSE_RANK LAST ORDER BY (prod_min_price))  
       OVER(PARTITION BY (prod_subcategory)) AS LP_OF_HI_MINP  
FROM products WHERE prod_subcategory = 'Documentation';  3    4    5    6    
  
   PROD_ID PROD_LIST_PRICE LP_OF_LO_MINP LP_OF_HI_MINP  
---------- --------------- ------------- -------------  
        40           44.99         44.99         44.99  
        41           44.99         44.99         44.99  
        45           44.99         44.99         44.99  
        43           44.99         44.99         44.99  
        44           44.99         44.99         44.99  
        42           44.99         44.99         44.99

刚才，我们找出了第一条和最后一条记录，如果我们要找前n条记录或者后n条记录，该怎么做那？不要着急，oracle为我们提供了ntile函数。

ntile函数允许我们将一个分区划分为一定数量的buckets，每个buckets中的记录数量是相等的（差额不超过1），oracle会为每个bucket赋值一个bucket number。

语法格式：

NTILE (expr) OVER ([query_partition_clause] order_by_clause)

示例：

        
[sql] 
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=products.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,951          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-11         661,147          4  
2000-09         691,449          4

上面的示例，将每条记录划分到4个bucket中，因此我们可以通过tile4=1来获取前25%的记录，通过tile=4来获取后25%的记录。