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;