oracle ntite函数,分析函数（NTIL的使用和first/last的使用）

分析函数(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/，如需转载，请注明出处，否则将追究法律责任。