http://blog.csdn.net/rokii/article/details/6429145
排列（rank ()）函数。这些排列函数提供了定义一个集合（使用 PARTITION 子句），然后根据某种排序方式对这个集合内的元素进行排列的能力，下面以scott用户的emp表为例来说明rank over partition如何使用
1）查询员工薪水并连续求和
select deptno,ename,sal,
sum(sal)over (order by ename) sum1, /*表示连续求和*/
sum(sal)over () sum2, /*相当于求和sum(sal)*/
100* round(sal/sum(sal)over (),4) "bal%"
from emp
结果如下：
DEPTNO ENAME SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
DEPTNO ENAME SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
2）如下：
select deptno,ename,sal,
sum(sal)over (partition by deptno order by ename) sum1,/*表示按部门号分氏，按姓名排序并连续求和*/
sum(sal)over (partition by deptno) sum2,/*表示部门分区，求和*/
sum(sal)over (partition by deptno order by sal) sum3,/*按部门分区，按薪水排序并连续求和*/
100* round(sal/sum(sal)over (),4) "bal%"
from emp
结果如下：
DEPTNO ENAME SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 CLARK 2450 2450 8750 3750 8.44
10 KING 5000 7450 8750 8750 17.23
10 MILLER 1300 8750 8750 1300 4.48
20 ADAMS 1100 1100 10875 1900 3.79
20 FORD 3000 4100 10875 10875 10.34
20 JONES 2975 7075 10875 4875 10.25
20 SCOTT 3000 10075 10875 10875 10.34
20 SMITH 800 10875 10875 800 2.76
30 ALLEN 1600 1600 9400 6550 5.51
30 BLAKE 2850 4450 9400 9400 9.82
30 JAMES 950 5400 9400 950 3.27
DEPTNO ENAME SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
30 MARTIN 1250 6650 9400 3450 4.31
30 TURNER 1500 8150 9400 4950 5.17
30 WARD 1250 9400 9400 3450 4.31
3）如下：
select empno,deptno,sal,
sum(sal)over (partition by deptno) "deptSum",/*按部门分区，并求和*/
rank ()over (partition by deptno order by sal desc nulls last) rank , /*按部门分区，按薪水排序并计算序号*/
dense_rank()over (partition by deptno order by sal desc nulls last) d_rank,
row_number()over (partition by deptno order by sal desc nulls last) row_rank
from emp
注：
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值
其实从上面三个例子当中，不难看出over (partition by ... order by ...)的整体概念，我理解是
partition by :按照指字的字段分区，如果没有则针对全体数据
order by :按照指定字段进行连续操作（如求和(sum),排序(rank ()等)，如果没有指定，就相当于对指定分区集合内的数据进行整体sum操作
http://keke-wanwei.iteye.com/blog/138632
首先,要了解rank在英语的意思:等级.也就是说这是一个给数据确定等级的函数.
以销售为例,有地区,年,月,销售员,销售额,记录这五个字段.我们可以按地区,年,月,销售额对销售员进行排序,这样对销售员来说就相当于有一个等级概念了,第一名就是销售最高的......,如果我们要找出每个地区,年,月,销售额的前三名销售员.SQL如何写?
- SELECT area_code, YEAR, MONTH, saleroom,saler
- RANK () OVER
- (PARTITION BY area_code,year ,month ORDER BY area_code,year,month,saleroom ) RANK
- FROM t_sale
现在RANK 就是1,2,3,3,3,6,有了这个字段,就很容易得到前三名的销售员了.
新问题:销售额50000块在深圳,2007年5月能排到第几?
上面这个SQL就可以搞定了.要注意的是,Rank()里的参数必须为常数,或常值表达式,里面参数的个数,类型也要和order by后字段的类型相对应.
上面就是Rank函数的两个用法.另外还有一个dense_rank(),它的用法和rank()一样,只是计算等级的方式不同.例如上面的
1,2,3,3,3,6.用dense_rank() 就是1,2,3,3,3,4.
http://blog.csdn.net/maqinqin/article/details/3320247
若不是这次使用oracle lead，我还不会发现，原来还有这么好用的东西。
oracle 统计分析函数 lead
语法结构：
lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)
参数说明：
value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。
offset 偏移，应该是很熟悉的数学概念了，或者是相对偏移，表格来开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。 如果不提供这个参数，就是默认为1.
default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null
over 可以简单地翻译为在什么。。。的基础之上
query_partition_clause 分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。
Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc
解释示例：
SQL> select * from test_value;
MONS JJR CJL CJJE
---------- ---------- ---------- ----------
200801 LZF 250 1999
200802 LZF 200 2000
200803 LZF 300 1000
200804 LZF 23 189
200805 LZF 356 456
200806 LZF 100 200
200807 LZF 600 700
200808 LZF 23 123
200809 LZF 400 500
9 rows selected
SQL>
SQL> select rownum 序号,Mons,cjl cjl_01,
2 lead(cjl,1) over (order by mons desc) cjl_02,
3 lead(cjl,2) over (order by mons desc) cjl_03,
4 lead(cjl,3) over (order by mons desc) cjl_04,
5 lead(cjl,4) over (order by mons desc) cjl_05,
6 lead(cjl,5) over (order by mons desc) cjl_06,
7 lead(cjl,6) over (order by mons desc) cjl_07,
8 lead(cjl,7) over (order by mons desc) cjl_08,
9 lead(cjl,8) over (order by mons desc) cjl_09
10 from test_value
序号 MONS CJL_01 CJL_02 CJL_03 CJL_04 CJL_05 CJL_06 CJL_07 CJL_08 CJL_09
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
9 200809 400 23 600 100 356 23 300 200 250
8 200808 23 600 100 356 23 300 200 250
7 200807 600 100 356 23 300 200 250
6 200806 100 356 23 300 200 250
5 200805 356 23 300 200 250
4 200804 23 300 200 250
3 200803 300 200 250
2 200802 200 250
1 200801 250
实践使用示例：
select id,lead(id,1)over(partition by call_req_id order by call_req_id ,id),type,analyst,time_stamp from act_log order by call_req_id ,id
扫一扫
1215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
