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

最新推荐文章于 2022-09-06 17:18:18 发布

iteye_1485

最新推荐文章于 2022-09-06 17:18:18 发布

阅读量223

点赞数

文章标签：数据库

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> 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> 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> 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%的记录。