oracle部分分析函数例子

最新推荐文章于 2024-10-10 16:42:38 发布

cuiqinxin1870

最新推荐文章于 2024-10-10 16:42:38 发布

阅读量69

点赞数

文章标签：数据库

oracle部分分析函数例子

可以从数据体会各函数之间的差别:

select * from emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 12/17/1980 800.00 20
7499 ALLEN SALESMAN 7698 2/20/1981 1600.00 300.00 30
7521 WARD SALESMAN 7698 2/22/1981 1250.00 500.00 30
7566 JONES MANAGER 7839 4/2/1981 2975.00 20
7654 MARTIN SALESMAN 7698 9/28/1981 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 5/1/1981 2850.00 30
7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
7839 KING PRESIDENT 11/17/1981 5000.00 10
7844 TURNER SALESMAN 7698 9/8/1981 1500.00 0.00 30
7900 JAMES CLERK 7698 12/3/1981 950.00 30
7902 FORD ANALYST 7566 12/3/1981 3000.00 20
7934 MILLER CLERK 7782 1/23/1982 1300.00 10
300 smith CLERK 7902 1/5/2007 2:38:53 PM 8000.00 4500.00 20

select ename,
sal,
hiredate,
first_value(ename)
over(order by hiredate asc rows 5 preceding) ename_prec,
first_value(hiredate) over(order by hiredate asc rows 5 preceding) hiredate_prec
from emp
order by hiredate asc
ENAME SAL HIREDATE ENAME_PREC HIREDATE_PREC
SMITH 800.00 12/17/1980 SMITH 12/17/1980
ALLEN 1600.00 2/20/1981 SMITH 12/17/1980
WARD 1250.00 2/22/1981 SMITH 12/17/1980
JONES 2975.00 4/2/1981 SMITH 12/17/1980
BLAKE 2850.00 5/1/1981 SMITH 12/17/1980
CLARK 2450.00 6/9/1981 SMITH 12/17/1980
TURNER 1500.00 9/8/1981 ALLEN 2/20/1981
MARTIN 1250.00 9/28/1981 WARD 2/22/1981
KING 5000.00 11/17/1981 JONES 4/2/1981
JAMES 950.00 12/3/1981 BLAKE 5/1/1981
FORD 3000.00 12/3/1981 CLARK 6/9/1981
MILLER 1300.00 1/23/1982 TURNER 9/8/1981
smith 8000.00 1/5/2007 2:38:53 PM MARTIN 9/28/1981

select ename,
sal,
hiredate,
first_value(ename)
over(order by hiredate desc rows 5 preceding) ename_prec,
first_value(hiredate) over(order by hiredate desc rows 5 preceding) hiredate_prec
from emp
order by hiredate asc
ENAME SAL HIREDATE ENAME_PREC HIREDATE_PREC
SMITH 800.00 12/17/1980 CLARK 6/9/1981
ALLEN 1600.00 2/20/1981 TURNER 9/8/1981
WARD 1250.00 2/22/1981 MARTIN 9/28/1981
JONES 2975.00 4/2/1981 KING 11/17/1981
BLAKE 2850.00 5/1/1981 JAMES 12/3/1981
CLARK 2450.00 6/9/1981 FORD 12/3/1981
TURNER 1500.00 9/8/1981 MILLER 1/23/1982
MARTIN 1250.00 9/28/1981 smith 1/5/2007 2:38:53 PM
KING 5000.00 11/17/1981 smith 1/5/2007 2:38:53 PM
JAMES 950.00 12/3/1981 smith 1/5/2007 2:38:53 PM
FORD 3000.00 12/3/1981 smith 1/5/2007 2:38:53 PM
MILLER 1300.00 1/23/1982 smith 1/5/2007 2:38:53 PM
smith 8000.00 1/5/2007 2:38:53 PM smith 1/5/2007 2:38:53 PM

select ename,
sal,
hiredate,
first_value(ename)
over(order by hiredate desc rows 5 preceding) ename_prec,
first_value(hiredate) over(order by hiredate desc rows 5 preceding) hiredate_prec
from emp
order by hiredate desc

ENAME SAL HIREDATE ENAME_PREC HIREDATE_PREC
smith 8000.00 1/5/2007 2:38:53 PM smith 1/5/2007 2:38:53 PM
MILLER 1300.00 1/23/1982 smith 1/5/2007 2:38:53 PM
FORD 3000.00 12/3/1981 smith 1/5/2007 2:38:53 PM
JAMES 950.00 12/3/1981 smith 1/5/2007 2:38:53 PM
KING 5000.00 11/17/1981 smith 1/5/2007 2:38:53 PM
MARTIN 1250.00 9/28/1981 smith 1/5/2007 2:38:53 PM
TURNER 1500.00 9/8/1981 MILLER 1/23/1982
CLARK 2450.00 6/9/1981 FORD 12/3/1981
BLAKE 2850.00 5/1/1981 JAMES 12/3/1981
JONES 2975.00 4/2/1981 KING 11/17/1981
WARD 1250.00 2/22/1981 MARTIN 9/28/1981
ALLEN 1600.00 2/20/1981 TURNER 9/8/1981
SMITH 800.00 12/17/1980 CLARK 6/9/1981

select ename,
hiredate,
sal,
avg(sal) over(order by hiredate asc rows 5 preceding) avg_5_before,
count(*) over(order by hiredate asc rows 5 preceding) obs_before,
avg(sal) over(order by hiredate desc rows 5 preceding) avg_5_after,
count(*) over(order by hiredate desc rows 5 preceding) obs_after
from emp
order by hiredate

ENAME HIREDATE SAL AVG_5_BEFORE OBS_BEFORE AVG_5_AFTER OBS_AFTER
SMITH 12/17/1980 800.00 800 1 1987.5 6
ALLEN 2/20/1981 1600.00 1200 2 2104.16666666667 6
WARD 2/22/1981 1250.00 1216.66666666667 3 2045.83333333333 6
JONES 4/2/1981 2975.00 1656.25 4 2670.83333333333 6
BLAKE 5/1/1981 2850.00 1895 5 2333.33333333333 6
CLARK 6/9/1981 2450.00 1987.5 6 2358.33333333333 6
TURNER 9/8/1981 1500.00 2104.16666666667 6 2166.66666666667 6
MARTIN 9/28/1981 1250.00 2045.83333333333 6 3250 6
KING 11/17/1981 5000.00 2670.83333333333 6 3650 5
JAMES 12/3/1981 950.00 2333.33333333333 6 3312.5 4
FORD 12/3/1981 3000.00 2358.33333333333 6 4100 3
MILLER 1/23/1982 1300.00 2166.66666666667 6 4650 2
smith 1/5/2007 2:38:53 PM 8000.00 3250 6 8000 1

select ename, hiredate, sal, RATIO_TO_REPORT(sal) over()
from emp
order by sal

ENAME HIREDATE SAL RATIO_TO_REPORT(SAL)OVER()
SMITH 12/17/1980 800.00 0.0242976461655277
JAMES 12/3/1981 950.00 0.0288534548215642
WARD 2/22/1981 1250.00 0.0379650721336371
MARTIN 9/28/1981 1250.00 0.0379650721336371
MILLER 1/23/1982 1300.00 0.0394836750189825
TURNER 9/8/1981 1500.00 0.0455580865603645
ALLEN 2/20/1981 1600.00 0.0485952923310554
CLARK 6/9/1981 2450.00 0.0744115413819286
BLAKE 5/1/1981 2850.00 0.0865603644646925
JONES 4/2/1981 2975.00 0.0903568716780562
FORD 12/3/1981 3000.00 0.0911161731207289
KING 11/17/1981 5000.00 0.151860288534548
smith 1/5/2007 2:38:53 PM 8000.00 0.242976461655277

select ename, hiredate, sal, ntile(4) over(order by sal asc)
from emp
order by sal
ENAME HIREDATE SAL NTILE(4)OVER(ORDERBYSALASC)
SMITH 12/17/1980 800.00 1
JAMES 12/3/1981 950.00 1
WARD 2/22/1981 1250.00 1
MARTIN 9/28/1981 1250.00 1
MILLER 1/23/1982 1300.00 2
TURNER 9/8/1981 1500.00 2
ALLEN 2/20/1981 1600.00 2
CLARK 6/9/1981 2450.00 3
BLAKE 5/1/1981 2850.00 3
JONES 4/2/1981 2975.00 3
FORD 12/3/1981 3000.00 4
KING 11/17/1981 5000.00 4
smith 1/5/2007 2:38:53 PM 8000.00 4

select ename, hiredate, sal, PERCENT_RANK() over(order by sal)
from emp
order by sal
ENAME HIREDATE SAL PERCENT_RANK()OVER(ORDERBYSAL)
SMITH 12/17/1980 800.00 0
JAMES 12/3/1981 950.00 0.0833333333333333
WARD 2/22/1981 1250.00 0.166666666666667
MARTIN 9/28/1981 1250.00 0.166666666666667
MILLER 1/23/1982 1300.00 0.333333333333333
TURNER 9/8/1981 1500.00 0.416666666666667
ALLEN 2/20/1981 1600.00 0.5
CLARK 6/9/1981 2450.00 0.583333333333333
BLAKE 5/1/1981 2850.00 0.666666666666667
JONES 4/2/1981 2975.00 0.75
FORD 12/3/1981 3000.00 0.833333333333333
KING 11/17/1981 5000.00 0.916666666666667
smith 1/5/2007 2:38:53 PM 8000.00 1

select ename,
deptno,
hiredate,
sal,
PERCENT_RANK() over(partition by deptno order by sal)
from emp
order by deptno

ENAME DEPTNO HIREDATE SAL PERCENT_RANK()OVER(PARTITIONBY
MILLER 10 1/23/1982 1300.00 0
CLARK 10 6/9/1981 2450.00 0.5
KING 10 11/17/1981 5000.00 1
SMITH 20 12/17/1980 800.00 0
JONES 20 4/2/1981 2975.00 0.333333333333333
FORD 20 12/3/1981 3000.00 0.666666666666667
smith 20 1/5/2007 2:38:53 PM 8000.00 1
JAMES 30 12/3/1981 950.00 0
MARTIN 30 9/28/1981 1250.00 0.2
WARD 30 2/22/1981 1250.00 0.2
TURNER 30 9/8/1981 1500.00 0.6
ALLEN 30 2/20/1981 1600.00 0.8
BLAKE 30 5/1/1981 2850.00 1

select ename,
deptno,
hiredate,
sal,
CUME_DIST() over(partition by deptno order by sal)
from emp
order by deptno

ENAME DEPTNO HIREDATE SAL CUME_DIST()OVER(PARTITIONBYDEP
MILLER 10 1/23/1982 1300.00 0.333333333333333
CLARK 10 6/9/1981 2450.00 0.666666666666667
KING 10 11/17/1981 5000.00 1
SMITH 20 12/17/1980 800.00 0.25
JONES 20 4/2/1981 2975.00 0.5
FORD 20 12/3/1981 3000.00 0.75
smith 20 1/5/2007 2:38:53 PM 8000.00 1
JAMES 30 12/3/1981 950.00 0.166666666666667
MARTIN 30 9/28/1981 1250.00 0.5
WARD 30 2/22/1981 1250.00 0.5
TURNER 30 9/8/1981 1500.00 0.666666666666667
ALLEN 30 2/20/1981 1600.00 0.833333333333333
BLAKE 30 5/1/1981 2850.00 1

select ename,
deptno,
hiredate,
sal,
RANK() over(partition by deptno order by sal)
from emp
order by deptno

ENAME DEPTNO HIREDATE SAL RANK()OVER(PARTITIONBYDEPTNOOR
MILLER 10 1/23/1982 1300.00 1
CLARK 10 6/9/1981 2450.00 2
KING 10 11/17/1981 5000.00 3
SMITH 20 12/17/1980 800.00 1
JONES 20 4/2/1981 2975.00 2
FORD 20 12/3/1981 3000.00 3
smith 20 1/5/2007 2:38:53 PM 8000.00 4
JAMES 30 12/3/1981 950.00 1
MARTIN 30 9/28/1981 1250.00 2
WARD 30 2/22/1981 1250.00 2
TURNER 30 9/8/1981 1500.00 4
ALLEN 30 2/20/1981 1600.00 5
BLAKE 30 5/1/1981 2850.00 6

select ename,
deptno,
hiredate,
sal,
dense_RANK() over(partition by deptno order by sal)
from emp
order by deptno

ENAME DEPTNO HIREDATE SAL DENSE_RANK()OVER(PARTITIONBYDE
MILLER 10 1/23/1982 1300.00 1
CLARK 10 6/9/1981 2450.00 2
KING 10 11/17/1981 5000.00 3
SMITH 20 12/17/1980 800.00 1
JONES 20 4/2/1981 2975.00 2
FORD 20 12/3/1981 3000.00 3
smith 20 1/5/2007 2:38:53 PM 8000.00 4
JAMES 30 12/3/1981 950.00 1
MARTIN 30 9/28/1981 1250.00 2
WARD 30 2/22/1981 1250.00 2
TURNER 30 9/8/1981 1500.00 3
ALLEN 30 2/20/1981 1600.00 4
BLAKE 30 5/1/1981 2850.00 5

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/36779/viewspace-896419/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/36779/viewspace-896419/