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
---------- ---------- --------- ---------- --------- ---------- ----- ------ --------- ------------