ORACLE分析函数(4)---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> 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%的记录。


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值