Oracle 分析函数
SQL> select ename,sal,deptno,round(avg(sal) over ( partition by deptno order by deptno),2) as avg_sl from emp;
ENAME SAL DEPTNO AVG_SAL
---------- ---------- ---------- ----------
CLARK 2450 10 2916.67
KING 5000 10 2916.67
MILLER 1300 10 2916.67
SMITH 800 20 2175
ADAMS 1100 20 2175
FORD 3000 20 2175
SCOTT 3000 20 2175
JONES 2975 20 2175
ALLEN 1600 30 1566.67
BLAKE 2850 30 1566.67
MARTIN 1250 30 1566.67
JAMES 950 30 1566.67
TURNER 1500 30 1566.67
WARD 1250 30 1566.67
ENAME
|
SAL
|
GREATER_NUM
|
SMITH
|
800
|
0
|
JAMES
|
950
|
0
|
ADAMS
|
1100
|
0
|
WARD
|
1250
|
2
|
MARTIN
|
1250
|
2
|
MILLER
|
1300
|
2
|
TURNER
|
1500
|
1
|
ALLEN
|
1600
|
1
|
CLARK
|
2450
|
0
|
BLAKE
|
2850
|
0
|
JONES
|
2975
|
2
|
SCOTT
|
3000
|
2
|
FORD
|
3000
|
2
|
KING
|
5000
|
0
|
SQL> select ename,sal,count(ename) over (order by sal range between 100 preceding and 100 following)-1 as all_row
2 from emp;
ENAME SAL ALL_ROW
---------- ---------- ----------
SMITH 800 0
JAMES 950 0
ADAMS 1100 0
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 1
ALLEN 1600 1
CLARK 2450 0
BLAKE 2850 0
JONES 2975 2
SCOTT 3000 2
FORD 3000 2
KING 5000 0
2 from t2;
(select ename,sal,deptno,dense_rank() over (partitionby deptno orderby sal desc ) as seq_num
from emp) a
where seq_num<=3
ENAME
|
SAL
|
deptno
|
KING
|
5000
|
10
|
CLARK
|
2450
|
10
|
MILLER
|
1300
|
10
|
SCOTT
|
3000
|
20
|
FORD
|
3000
|
20
|
JONES
|
2975
|
20
|
ADAMS
|
1100
|
20
|
BLAKE
|
2850
|
30
|
ALLEN
|
1600
|
30
|
TURNER
|
1500
|
30
|
first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal) asfirst,
from emp;
ADAMS
|
1100
|
20
|
SQL> select ename,sal,deptno,row_number() over (partition by deptno order by sal desc) as sorts
2 from emp;
ENAME SAL DEPTNO SORTS
---------- ---------- ---------- ----------
KING 5000 10 1
CLARK 2450 10 2
MILLER 1300 10 3
SCOTT 3000 20 1
FORD 3000 20 2
JONES 2975 20 3
ADAMS 1100 20 4
SMITH 800 20 5
BLAKE 2850 30 1
ALLEN 1600 30 2
TURNER 1500 30 3
WARD 1250 30 4
MARTIN 1250 30 5
JAMES 950 30 6
已选择14行。
分页语句里使用row_number() over:
ENAME
|
DEPTNO
|
SORTNO
|
KING
|
10
|
1
|
CLARK
|
10
|
2
|
MILLER
|
10
|
3
|
SCOTT
|
20
|
1
|
FORD
|
20
|
1
|
JONES
|
20
|
2
|
ADAMS
|
20
|
3
|
BLAKE
|
30
|
1
|
ALLEN
|
30
|
2
|
TURNER
|
30
|
3
|
DEPTNO
|
HIGHEST
|
SEC_HIGHEST
|
THIRD_HIGHEST
|
10
|
KING
|
CLARK
|
MILLER
|
20
|
FORD
|
JONES
|
ADAMS
|
30
|
BLAKE
|
ALLEN
|
TURNER
|
min(decode(seq_num,2,ename,null)) as sec_highest,
min(decode(seq_num,3,ename,null)) as third_highest from
(select ename,deptno,seq_num from
(select ename,deptno,dense_rank() over (partitionby deptno orderby sal desc ) as seq_num
from emp) a
where seq_num<=3) b
groupby b.deptno