ORACLE分析函数(四)-FIRST/LAST排名查询
日期：2014-05-16 浏览次数：20552 次
ORACLE分析函数(4)---FIRST/LAST排名查询
刚才，我们找出了第一条和最后一条记录，如果我们要找前n条记录或者后n条记录，该怎么做那？不要着急，oracle为我们提供了ntile函数。
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
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 ---
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
