ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析函数之排名统计

分析函数之排名统计

原创 Linux操作系统 作者:红叶DBA 时间:2011-03-20 09:55:45 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 创建数据库
请登录后发表评论 登录
全部评论

注册时间:2010-08-19

  • 博文量
    54
  • 访问量
    70910