oracle分析函数(3)

【2】找出每个区域订单总额排名前3的大客户：

SQL >   select   *
    2      from  ( select  region_id,
    3                  customer_id,
    4                   sum (customer_sales) cust_total,
    5                   sum ( sum (customer_sales))  over (partition  by  region_id) reg_total,
    6                  rank() over (partition  by  region_id
                                order   by   sum (customer_sales)  desc  NULLS LAST) rank
    7              from  user_order
    8             group   by  region_id, customer_id)
    9     where rank  <=   3 ;

  REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK
-- -------- ----------- ---------- ---------- ----------
           5             4      1878275      5585641            1
           5             2      1224992      5585641            2
           5             5      1169926      5585641            3
           6             6      1788836      6307766            1
           6             9      1208959      6307766            2
           6            10      1196748      6307766            3
           7            14      1929774      6868495            1
           7            13      1310434      6868495            2
           7            15      1255591      6868495            3
           8            17      1944281      6854731            1
           8            20      1413722      6854731            2
           8            18      1253840      6854731            3
           9            25      2232703      6739374            1
           9            23      1224992      6739374            2
           9            24      1224992      6739374            2
          10            26      1808949      6238901            1
          10            27      1322747      6238901            2
          10            30      1216858      6238901            3

18  rows selected.

三、First/Last排名查询：

想象一下下面的情形：找出订单总额最多、最少的客户。按照前面我们学到的知识，这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名，第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦？因为Rank函数只告诉我们排名的结果，却无法自动替我们从中筛选结果。

幸好Oracle为我们在排列函数之外提供了两个额外的函数：first、last函数，专门用来解决这种问题。还是用实例说话：

SQL >   select   min(customer_id)
    2          keep (dense_rank first  order   by   sum (customer_sales)  desc ) first,
    3           min (customer_id)
    4          keep (dense_rank last  order   by   sum (customer_sales)  desc )  last
    5      from  user_order
    6     group   by  customer_id;

      FIRST       LAST
-- -------- ----------
          31            1

这里有几个看起来比较疑惑的地方：

①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能换成rank吗？

首先解答一下第一个问题：min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢？

SQL >   select  keep (dense_rank first  order   by   sum (customer_sales)  desc ) first,
    2              keep (dense_rank last  order   by   sum (customer_sales)  desc ) last
    3      from  user_order
    4     group   by  customer_id;
select  keep (dense_rank first  order   by   sum (customer_sales)  desc ) first,
                          *
ERROR at line  1 :
ORA - 00907 : missing  right  parenthesis

接下来看看第2个问题：keep是干什么用的？从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。告诉Oracle只保留符合keep条件的记录。

那么什么才是符合条件的记录呢？这就是第3个问题了。dense_rank是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。

第4个问题：如果我们把dense_rank换成rank呢？

SQL >   select   min (region_id)
    2           keep(rank first  order   by   sum (customer_sales)  desc ) first,
    3           min (region_id)
    4           keep(rank last  order   by   sum (customer_sales)  desc ) last
    5      from  user_order
    6     group   by  region_id;
select   min (region_id)
*
ERROR at line  1 :
ORA - 02000 : missing DENSE_RANK

四、按层次查询：


现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N，第一个，最后一个记录。有时我们会收到类似下面这样的需求：找出订单总额排名前1/5的客户。

很熟悉是不？我们马上会想到第二点中提到的方法，可是rank函数只为我们做好了排名，并不知道每个排名在总排名中的相对位置，这时候就引入了另外一个分析函数NTile，下面我们就以上面的需求为例来讲解一下：

SQL >   select  region_id,
    2          customer_id,
    3          ntile( 5 )  over ( order   by   sum (customer_sales)  desc ) til
    4      from  user_order
    5     group   by  region_id, customer_id;

  REGION_ID CUSTOMER_ID       TILE
-- -------- ----------- ----------
          10            31            1
           9            25            1
          10            26            1
           6             6             1         
           8            18            2
           5             2             2
           9            23            3
           6             9             3
           7            11            3
           5             3             4
           6             8             4
           8            16            4
           6             7             5
          10            29            5
           5             1             5

Ntil函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。

参考资料：《Mastering Oracle SQL》(By Alan Beaulieu , Sanjay Mishra  O'Reilly June 2004  0-596-00632-2)
博客：http://www.blogjava.net/pengpenglin/archive/2008/06/25/210536.html