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

ORACLE分析函数(4)---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 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 TILE4FROM sales, products, customers, times, channelsWHERE 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          12000-01         257,286          12000-03         280,011          12000-06         315,951          22000-05         316,824          22000-04         318,106          22000-07         433,824          32000-08         477,833          32000-12         553,534          32000-10         652,225          42000-11         661,147          42000-09         691,449          4

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