oracle (Rank,Lag,Aggregate_function,First/Last)

功能测试用表: v_test(id int,group_id varchar2(10),name varchar2(10),num int)

ID	GROUP_ID	NAME	NUM
1	001	ok	13
2	001	yes	25
3	001	no	25
4	001	hi	25
5	001	no	17
6	001	no	34
7	002	no	26
8	002	oh	19

 

一、Rank（）Over（Partiton by … Order By …Nulls First/Last）

Dense_rank （）Over（Partiton by … Order By …Nulls First/Last）

Row_number （）Over（Partiton by … Order By …Nulls First/Last）

 

SQL语句：

-------------------------------------------------------------------------------------------------------

select id,group_id,name,num,
       rank()over(partition by group_id order by name asc nulls last) rk,
       dense_rank()over(partition by group_id order by name asc nulls first) dr,
       row_number()over(partition by group_id order by name asc nulls last) rn
from v_test;

-------------------------------------------------------------------------------------------------------

结果：

ID	GROUP_ID	NAME	NUM	RK	DR	RN
4	001	hi	25	1	1	1
3	001	no	25	2	2	2
5	001	no	17	2	2	3
6	001	no	34	2	2	4
1	001	ok	13	5	3	5
2	001	yes	25	6	4	6
7	002	no	26	1	1	1
8	002	oh	19	2	2	2

 

rang()涵数主要用于排序，并给出序号

dense_rank()：功能同rank()一样，区别在于，rank()对于排序并的数据给予相同序号，接下来的数据序号直接跳中跃，dense_rank()则不是，比如数据：1，2，2，4，5，6.。。。。这是rank()的形式

                                                       1，2，2，3，4，5，。。。。这是dense_rank()的形式

                                                       1，2，3，4，5，6.。。。。。这是row_number()涵数形式

row_number()涵数则是按照顺序依次使用，相当于我们普通查询里的rownum值

 

 

 

 

二、Lag（…，…）Over（Partiton by … Order By …Nulls First/Last）

Lead（…，…）Over（Partiton by … Order By …Nulls First/Last）

 

SQL语句：

-------------------------------------------------------------------------------------------------------

select id,group_id,name,num,
       lag(name,1)over(partition by group_id order by name asc nulls last) lg,
       lead(name,1)over(partition by group_id order by name asc nulls first) ld
from v_test;

-------------------------------------------------------------------------------------------------------

结果：

ID	GROUP_ID	NAME	NUM	LG	LD
4	001	hi	25		no
3	001	no	25	hi	no
5	001	no	17	no	no
6	001	no	34	no	ok
1	001	ok	13	no	yes
2	001	yes	25	ok
7	002	no	26		oh
8	002	oh	19	no

 

三、Aggregate_function：MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV.

Aggregate_function（…）Over（Partition by … Order By …Nulls First/Last）

 

SQL语句：

-------------------------------------------------------------------------------------------------------

select id,group_id,name,num,
       min(num)over(partition by group_id order by id asc Nulls First ) mn,
       max(num)over(partition by group_id order by id asc Nulls First) mx,
       avg(num)over(partition by group_id order by id asc Nulls First) ag,
       sum(num)over(partition by group_id order by id asc Nulls First) sm
from v_test;

-------------------------------------------------------------------------------------------------------

结果：

ID	GROUP_ID	NAME	NUM	MN	MX	AG	SM
1	001	ok	13	13	13	13	13
2	001	yes	25	13	25	19	38
3	001	no	25	13	25	21	63
4	001	hi	25	13	25	22	88
5	001	no	17	13	25	21	105
6	001	no	34	13	34	23.1666666666667	139
7	002	no	26	26	26	26	26
8	002	oh	19	19	26	22.5	45

 

四、First/Last

Aggregate_function（…） Keep（Dense_rank First/Last Order By …Nulls First/Last） 

Over （Partition by …）

说明：DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank ("olympic rank"). 

SQL语句：

-------------------------------------------------------------------------------------------------------

select id,group_id,name,num,
    min(num)keep(dense_rank first order by id)over(partition by group_id ) mnf,
    max(num)keep(dense_rank last order by id)over(partition by group_id ) mxl
from v_test;

-------------------------------------------------------------------------------------------------------

结果：

ID	GROUP_ID	NAME	NUM	MNF	MXL
1	001	ok	13	13	34
2	001	yes	25	13	34
3	001	no	25	13	34
4	001	hi	25	13	34
5	001	no	17	13	34
6	001	no	34	13	34
7	002	no	26	26	19
8	002	oh	19	26	19

注释：指定First(Last)是指在数据排序后，取排序结果中第一条（最后一条）记录。但是由于指定排序的栏位值可能相等，并导致排序后的序号相同，所以还需要使用聚合函数确定到底取用哪一条记录作为结果集返回。

-------------------------------------------------------------------------------------------------------

select id,group_id,name,num,
    min(num)keep(dense_rank first order by name)over(partition by group_id ) mnf,
    max(num)keep(dense_rank last order by name)over(partition by group_id ) mxl
from v_test where name='no';

-------------------------------------------------------------------------------------------------------

结果：

ID	GROUP_ID	NAME	NUM	MNF	MXL
3	001	no	25	17	34
5	001	no	17	17	34
6	001	no	34	17	34
7	002	no	26	26	26

<!--EndFragment-->