分析函式之排名統計

紅葉DBA發表於2011-03-20
函式
IDLE > conn scott/tiger
Connected.

SCOTT:159@hongye > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章