分析函数之排名统计

IDLE > conn scott/tiger

Connected.

SCOTT:159@hongye > select * from emp;

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

例子1、rank 和 和 dense_rank 函数

rank 函数统计排名，会跳过重复的排名，比如有2个第一名，那么下一个就是第三名了

dense_rank函数则不会跳过，如果有2个第一名，其下一个依然还是第2名。

SCOTT:159@hongye > select deptno,sum(sal),rank() over (order by sum(sal)) as rank,dense_rank() over (order by sum(sal)) as dense_rank from emp group by deptno;

    DEPTNO   SUM(SAL)       RANK DENSE_RANK

---------- ---------- ---------- ----------

        10       8750          1          1

        30       9400          2          2

        20      10875          3          3

排序的顺序（升序或者降序）也是可以控制的

SCOTT:159@hongye > select deptno,sum(sal),rank() over (order by sum(sal) desc) as rank,dense_rank() over (order by sum(sal)) as dense_rank from emp group by deptno;

    DEPTNO   SUM(SAL)       RANK DENSE_RANK

---------- ---------- ---------- ----------

        10       8750          3          1

        30       9400          2          2

        20      10875          1          3

例子2、cume_dist 和 percent_rank 函数

cume_dist 返回该条记录在分组中的位置，比如1/3、2/3、1

percent_rank 返回的是一条记录在分组红的百分比排名，从0开始，比如0、0.5、1

SCOTT:159@hongye > select deptno,sum(sal),cume_dist() over(order by sum(sal)) as cume_dist,percent_rank() over (order by sum(sal)) as per_rank from emp group by deptno;

    DEPTNO   SUM(SAL)  CUME_DIST   PER_RANK

---------- ---------- ---------- ----------

        10       8750 .333333333          0

        30       9400 .666666667         .5

        20      10875          1          1

在emp表中增加一个部门继续后续的实验。

SCOTT:159@hongye > create table t as select empno,ename,job,mgr,sal,deptno from emp;

Table created.

SCOTT:159@hongye > insert into t values(1111,'HONGYE','MANAGER',7839,10000,40);

1 row created.

SCOTT:159@hongye > insert into t values(2222,'DBA','ANALYST',1111,8888,40);

1 row created.

SCOTT:159@hongye > select * from t;

     EMPNO ENAME      JOB              MGR        SAL     DEPTNO

---------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902        800         20

      7499 ALLEN      SALESMAN        7698       1600         30

      7521 WARD       SALESMAN        7698       1250         30

      7566 JONES      MANAGER         7839       2975         20

      7654 MARTIN     SALESMAN        7698       1250         30

      7698 BLAKE      MANAGER         7839       2850         30

      7782 CLARK      MANAGER         7839       2450         10

      7788 SCOTT      ANALYST         7566       3000         20

      7839 KING       PRESIDENT                  5000         10

      7844 TURNER     SALESMAN        7698       1500         30

      7876 ADAMS      CLERK           7788       1100         20

      7900 JAMES      CLERK           7698        950         30

      7902 FORD       ANALYST         7566       3000         20

      7934 MILLER     CLERK           7782       1300         10

      1111 HONGYE     MANAGER         7839      10000         40

      2222 DBA        ANALYST         1111       8888         40

16 rows selected.

例子3、ntile 函数

ntile(buckets)，将所在的分组再分成 bucket 个片段，计算每个片段的排名

如下：2分片

SCOTT:159@hongye > select deptno,sum(sal),ntile(2) over(order by sum(sal)) as ntile from t group by deptno;

    DEPTNO   SUM(SAL)      NTILE

---------- ---------- ----------

        10       8750          1

        30       9400          1

        20      10875          2

        40      18888          2

如下：3分片

SCOTT:159@hongye > select deptno,sum(sal),ntile(3) over(order by sum(sal)) as ntile from t group by deptno;

    DEPTNO   SUM(SAL)      NTILE

---------- ---------- ----------

        10       8750          1

        30       9400          1

        20      10875          2

        40      18888          3

如下：4分片

SCOTT:159@hongye > select deptno,sum(sal),ntile(4) over(order by sum(sal)) as ntile from t group by deptno;

    DEPTNO   SUM(SAL)      NTILE

---------- ---------- ----------

        10       8750          1

        30       9400          2

        20      10875          3

        40      18888          4

例子4、使用row_number函数

row_number 函数从1开始，为每个分组记录返回一个递增的数字

SCOTT:159@hongye > select deptno,sum(sal),row_number() over(order by sum(sal)) as ntile from t group by deptno;

    DEPTNO   SUM(SAL)      NTILE

---------- ---------- ----------

        10       8750          1

        30       9400          2

        20      10875          3

        40      18888          4

如下：倒序的 row_number

SCOTT:159@hongye > select deptno,sum(sal),row_number() over(order by sum(sal) desc) as ntile from t group by deptno;

    DEPTNO   SUM(SAL)      NTILE

---------- ---------- ----------

        40      18888          1

        20      10875          2

        30       9400          3

        10       8750          4

SCOTT:159@hongye > 

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

转载于:http://blog.itpub.net/24465008/viewspace-690006/