欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle中常用的计算、统计类函数介绍

程序员文章站 2022-06-23 16:07:06
Oracle中常用的计算、统计类函数介绍 group by scott@DBHAWK>select deptno,sum(sal) from emp group...

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;