Oracle常用的几个分析函数

hb0308513

贡献于2010-11-17

字数:3558 关键词: Oracle 数据库服务器 SQL

常用的几个分析函数 2007/06/16 功能测试用表: 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 二、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 eworm

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 15 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档