来自 计算机教程 2020-05-02 08:14 的文章
当前位置: 美洲杯冠军竞猜 > 计算机教程 > 正文

Oracle 分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

排列函数。这些排列函数提供了定义一个集合,然后根据某种排序方式对这个集合内的元素进行排列的能力,下面以scott用户的emp表为例来说明rank over partition如何使用。

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序 而这个值就表示每组内部排序后的顺序编号 RANK() 类似,不过RANK 排序的时候跟派名次一样,可以并列2个第一名之后 是第3名 LAG 表示 分组排序后 ,组内后面一条记录减前面一条记录的差,第一条可返回 NULL BTW: EXPERT ONE ON ONE 上讲的最详细,还有很多相关特性,文档看起来比较费劲 row_number()和rownum差不多,功能更强一点 rank()是跳跃排序,有两个第二名时接下来就是第四名 dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。 相比之下row_number是没有重复值的 lag(arg1,arg2,arg3): arg1是从其他行返回的表达式 arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,时一个往回检索以前的行的数目。 arg3是在arg2表示的数目超出了分组的范围时返回的值。 SQL set pagesize 100; SQL select rownum from emp; ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 已选择14行。 已用时间: 00: 00: 00.10 SQL select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno; DEPTNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSAL) ---------- --------------------------------------------- 10 1 2 3 20 1 2 3 4 5 30 1 2 3 4 5 6 已选择14行。 已用时间: 00: 00: 00.41 SQL select deptno,rank() over (partition by deptno order by sal) from emp order by deptno; DEPTNO RANK()OVER(PARTITIONBYDEPTNOORDERBYSAL) ---------- --------------------------------------- 10 1 2 3 20 1 2 3 4 4 30 1 2 2 4 5 6 已选择14行。 已用时间: 00: 00: 00.21 SQL select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno; DEPTNO DENSE_RANK()OVER(PARTITIONBYDEPTNOORDERBYSAL) ---------- --------------------------------------------- 10 1 2 3 20 1 2 3 4 4 30 1 2 2 3 4 5 已选择14行。 已用时间: 00: 00: 00.20 SQL select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno; DEPTNO ENAME SAL LAG(ENAME, ---------- ---------- ---------- ---------- 10 CLARK 2450 KING 5000 CLARK MILLER 1300 KING 20 ADAMS 1100 FORD 3000 ADAMS JONES 2975 FORD SCOTT 3000 JONES SMITH 800 SCOTT 30 ALLEN 1600 BLAKE 2850 ALLEN JAMES 950 BLAKE MARTIN 1250 JAMES TURNER 1500 MARTIN WARD 1250 TURNER 已选择14行。 已用时间: 00: 00: 00.31 SQL select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p order by deptno; DEPTNO ENAME SAL LAG(ENAME, ---------- ---------- ---------- ---------- 10 CLARK 2450 example KING 5000 example MILLER 1300 CLARK 20 ADAMS 1100 example FORD 3000 example JONES 2975 ADAMS SCOTT 3000 FORD SMITH 800 JONES 30 ALLEN 1600 example BLAKE 2850 example JAMES 950 ALLEN MARTIN 1250 BLAKE TURNER 1500 JAMES WARD 1250 MARTIN 已选择14行。

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

其实从上面三个例子当中,不难看出over(partition by ... order by ...)的整体概念,我理解是

结果如下:

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

结果如下:

篮球世界杯冠军竞猜,row_number()函数则是按照顺序依次使用,相当于我们普通查询里的rownum值

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

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

1)查询员工薪水并连续求和

3)如下:

本文由美洲杯冠军竞猜发布于计算机教程,转载请注明出处:Oracle 分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

关键词: