ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析函数(NTIL的使用和first/last的使用)

分析函数(NTIL的使用和first/last的使用)

原创 Linux操作系统 作者:alert.an 时间:2009-12-08 10:39:15 0 删除 编辑
分析函数(NTIL的使用和first/last的使用)

########################
NTIL的使用
########################

有时我们会收到类似下面这样的需求:找出订单总额排名前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)就可以了。


########################
first/last的使用
########################
---------------------------------
准备环境:
---------------------------------

create table test tablespace test
as
with an_test as
(select rownum r1 from dba_objects where rownum<100)
select a.r1 r1,b.r1 r2
from an_test a,an_test b
where a.r1<=b.r1;


sys@MYORACLE> select r1,count(*) from test group by r1 order by 1;

        R1   COUNT(*)
---------- ----------
         1         99
         2         98
         3         97
         4         96
         5         95
         6         94
         7         93
         8         92
         9         91
        10         90
        11         89
        12         88
        13         87
        14         86
        15         85
        16         84
        17         83
        18         82
        19         81
        20         80
        21         79
        22         78
        23         77
        24         76
        25         75
        26         74
        27         73
        28         72
        29         71
        30         70
        31         69
        32         68
        33         67
        34         66
        35         65
        36         64
        37         63
        38         62
        39         61
        40         60
        41         59
        42         58
        43         57
        44         56
        45         55
        46         54
        47         53
        48         52
        49         51
        50         50
        51         49
        52         48
        53         47
        54         46
        55         45
        56         44
        57         43
        58         42
        59         41
        60         40
        61         39
        62         38
        63         37
        64         36
        65         35
        66         34
        67         33
        68         32
        69         31
        70         30
        71         29
        72         28
        73         27
        74         26
        75         25
        76         24
        77         23
        78         22
        79         21
        80         20
        81         19
        82         18
        83         17
        84         16
        85         15
        86         14
        87         13
        88         12
        89         11
        90         10
        91          9
        92          8
        93          7
        94          6
        95          5
        96          4
        97          3
        98          2
        99          1

99 rows selected.

sys@MYORACLE>


---------------------------------
分析函数first和last的使用:
---------------------------------

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

幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话:
sys@MYORACLE>
select min(r1) keep(dense_rank  first order by sum(r2) desc  ) first,
min(r1) keep(dense_rank  last order by sum(r2) desc ) last
  3  from test group by r1;

     FIRST       LAST
---------- ----------
         1         99

sys@MYORACLE>


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

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

首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢?
SQL>select keep(dense_rank  first order by sum(r2) desc) first
  2  from test
  3  group by r1;
select keep(dense_rank  first order by sum(r2) desc) first
                        *
ERROR at line 1:
ORA-00907: missing right parenthesis


接下来看看第2个问题:keep是干什么用的?从上面的结果我们已经知道Oracle对排名的结果只“保留”1条数据,这就是keep的作用。告诉Oracle只保留符合keep条件的记录。
sys@MYORACLE>select min(r1) (dense_rank  first order by sum(r2) desc) first
           2  from test
           3  group by r1;
select min(r1) (dense_rank  first order by sum(r2) desc) first
               *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


那么什么才是符合条件的记录呢?这就是第3个问题了。dense_rank是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。
sys@MYORACLE>select min(r1) keep(dense_rank  order by sum(r2) desc) first
          2  from test
          3  group by r1;
select min(r1) keep(dense_rank  order by sum(r2) desc) first
                                *
ERROR at line 1:
ORA-30492: One of FIRST or LAST keywords is expected.


第4个问题:如果我们把dense_rank换成rank呢?
sys@MYORACLE>select min(r1) keep(rank first order by sum(r2) desc) first
          2  from test
          3  group by r1;
select min(r1) keep(rank first order by sum(r2) desc) first
                    *
ERROR at line 1:
ORA-02000: missing DENSE_RANK keyword

所以first/last的语法规则是非常严谨的, 缺一不可。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/123747/viewspace-621922/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-08-08

  • 博文量
    11
  • 访问量
    13669