Oracle中常用的计算、统计类函数介绍
group by
scott@DBHAWK>select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
rank ( ) over (partition by … order by … )
scott@DBHAWK>select t.*,rank() over(partition by deptno order by sal desc) RANK from emp t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RANK
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10 1
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 1
7566 JONES MANAGER 7839 02-APR-81 2975 20 3
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 4
7369 SMITH CLERK 7902 17-DEC-80 800 20 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 2
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 3
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4
8888 MARK HAWKER 7698 28-SEP-81 1250 1400 30 4
7900 JAMES CLERK 7698 03-DEC-81 950 30 7
dense_rank ( ) over (partition by … order by … )
scott@DBHAWK>select t.*,dense_rank() over(partition by deptno order by sal desc) DENSERANK from emp t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DENSERANK
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10 1
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 1
7566 JONES MANAGER 7839 02-APR-81 2975 20 2
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 3
7369 SMITH CLERK 7902 17-DEC-80 800 20 4
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 2
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 3
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4
8888 MARK HAWKER 7698 28-SEP-81 1250 1400 30 4
7900 JAMES CLERK 7698 03-DEC-81 950 30 5
row_number () over (partition by … order by … )
scott@DBHAWK>select t.*,row_number () over (partition by deptno order by sal desc)ROWNUMBER from emp t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUMBER
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10 1
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 2
7566 JONES MANAGER 7839 02-APR-81 2975 20 3
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 4
7369 SMITH CLERK 7902 17-DEC-80 800 20 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 2
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 3
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 5
8888 MARK HAWKER 7698 28-SEP-81 1250 1400 30 6
7900 JAMES CLERK 7698 03-DEC-81 950 30 7
min ( ) over (partition by … )
max ( ) over (partition by … )
scott@DBHAWK>select t.*,min(sal) over(partition by deptno)min_sal ,max(sal) over(partition by deptno)max_sal from emp t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MIN_SAL MAX_SAL
---------- ---------- --------- ---------- --------- ---------- ----- ------ --------- ------------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 1300 5000
7934 MILLER CLERK 7782 23-JAN-82 1300 10 1300 5000
7839 KING PRESIDENT 17-NOV-81 5000 10 1300 5000
7566 JONES MANAGER 7839 02-APR-81 2975 20 800 3000
7369 SMITH CLERK 7902 17-DEC-80 800 20 800 3000
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 800 3000
7902 FORD ANALYST 7566 03-DEC-81 3000 20 800 3000
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 800 3000
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 950 2850
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 950 2850
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 950 2850
7900 JAMES CLERK 7698 03-DEC-81 950 30 950 2850
8888 MARK HAWKER 7698 28-SEP-81 1250 1400 30 950 2850
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 950 2850
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 950 2850
lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。
lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。
select t.*,
lead(sal, 1, 0) over(partition by deptno order by sal) lead_sal,
lag(sal, 1, 0) over(partition by deptno order by sal) lag_sal,
nvl(lead(sal) over(partition by deptno order by sal) - sal,
0) diff_lead_sal,
nvl(sal - lag(sal) over(partition by deptno order by sal), 0) diff_lag_sal
from emp t;
扩展使用
select t.*,
first_value(sal) over(partition by deptno) first_sal,
last_value(sal) over(partition by deptno) last_sal,
sum(sal) over(partition by deptno) sum_sal,
avg(sal) over(partition by deptno) avg_sal,
count(sal) over(partition by deptno) count_num,
row_number() over(partition by deptno order by sal) row_num
from emp t;