三、常见分析函数详解

create table t(    bill_month varchar2(12) ,    area_code number,    net_type varchar(2),    local_fare number );

insert into t values('200405',5761,'G', 7393344.04); insert into t values('200405',5761,'J', 5667089.85); insert into t values('200405',5762,'G', 6315075.96); insert into t values('200405',5762,'J', 6328716.15); insert into t values('200405',5763,'G', 8861742.59); insert into t values('200405',5763,'J', 7788036.32); insert into t values('200405',5764,'G', 6028670.45); insert into t values('200405',5764,'J', 6459121.49); insert into t values('200405',5765,'G', 13156065.77); insert into t values('200405',5765,'J', 11901671.70); insert into t values('200406',5761,'G', 7614587.96); insert into t values('200406',5761,'J', 5704343.05); insert into t values('200406',5762,'G', 6556992.60); insert into t values('200406',5762,'J', 6238068.05); insert into t values('200406',5763,'G', 9130055.46); insert into t values('200406',5763,'J', 7990460.25); insert into t values('200406',5764,'G', 6387706.01); insert into t values('200406',5764,'J', 6907481.66); insert into t values('200406',5765,'G', 13562968.81); insert into t values('200406',5765,'J', 12495492.50); insert into t values('200407',5761,'G', 7987050.65); insert into t values('200407',5761,'J', 5723215.28); insert into t values('200407',5762,'G', 6833096.68); insert into t values('200407',5762,'J', 6391201.44); insert into t values('200407',5763,'G', 9410815.91); insert into t values('200407',5763,'J', 8076677.41); insert into t values('200407',5764,'G', 6456433.23); insert into t values('200407',5764,'J', 6987660.53); insert into t values('200407',5765,'G', 14000101.20); insert into t values('200407',5765,'J', 12301780.20); insert into t values('200408',5761,'G', 8085170.84); insert into t values('200408',5761,'J', 6050611.37); insert into t values('200408',5762,'G', 6854584.22); insert into t values('200408',5762,'J', 6521884.50); insert into t values('200408',5763,'G', 9468707.65); insert into t values('200408',5763,'J', 8460049.43); insert into t values('200408',5764,'G', 6587559.23); insert into t values('200408',5764,'J', 7342135.86); insert into t values('200408',5765,'G', 14450586.63); insert into t values('200408',5765,'J', 12680052.38); commit;

SELECT BILL_MONTH,        AREA_CODE,       SUM(LOCAL_FARE) LOCAL_FARE,        FIRST_VALUE(AREA_CODE)        OVER(PARTITION BY BILL_MONTH             ORDER BY SUM(LOCAL_FARE) DESC             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL,        LAST_VALUE(AREA_CODE)        OVER(PARTITION BY BILL_MONTH             ORDER BY SUM(LOCAL_FARE) DESC             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL   FROM T  GROUP BY BILL_MONTH, AREA_CODE  ORDER BY BILL_MONTH

rank，dense_rank，row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略
①row_number：
row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增
②dense_rank：
dense_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的
③rank：
rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名

1、rank()值相同时排名相同，其后排名跳跃不连续

SELECT *   FROM (SELECT DEPTNO,                RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,                ENAME,               SAL          FROM SCOTT.EMP)  WHERE RW <= 4;

2、dense_rank()值相同时排名相同，其后排名连续不跳跃

SELECT *   FROM (SELECT DEPTNO,                DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,                ENAME,               SAL          FROM SCOTT.EMP)  WHERE RW <= 4;

3、row_number()值相同时排名不相等，其后排名连续不跳跃

SELECT *   FROM (SELECT DEPTNO,                ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,                ENAME,               SAL          FROM SCOTT.EMP)  WHERE RW <= 4;

lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)

SQL> select *  from kkk;

ID NAME
---------- --------------------
1 1name
2 2name
3 3name
4 4name
5 5name
SQL> select id,name,lag(name,1,0) over(order by id) from kkk;

ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- ----------------------------
1 1name                0
2 2name                1name
3 3name                2name
4 4name                3name
5 5name                4name

SQL> select id,name,lead(name,1,0) over(order by id) from kkk;

ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
1 1name                2name
2 2name                3name
3 3name                4name
4 4name                5name
5 5name                0

SQL> select id,name,lead(name,2,0) over(order by id) from kkk;
ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
1 1name                3name
2 2name                4name
3 3name                5name
4 4name                0
5 5name                0
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;

ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')
---------- -------------------- ------------------------------
1 1name                2name
2 2name                3name
3 3name                4name
4 4name                5name
5 5name                linjiqin

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

1)、group by rollup(a, b, c)：

2)、group by cube(a, b, c)：

1、生成演示数据：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ds_trade

SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS

SQL> create table scott.t as select * from dba_indexes;

Table created

SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott

SQL>

2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;

3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C)：

sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);

4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C)：

sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);

SELECT AREA_CODE,        BILL_MONTH,       LOCAL_FARE,       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum",        AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg",        MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max",        MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min"   FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE           FROM T          GROUP BY T.AREA_CODE, T.BILL_MONTH)

SELECT AREA_CODE,        BILL_MONTH,       LOCAL_FARE,       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY BILL_MONTH ASC) "last_sum_value"   FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE           FROM T          GROUP BY T.AREA_CODE, T.BILL_MONTH)  ORDER BY AREA_CODE, BILL_MONTH

--------------------------------------------------------------------------
Blog：http://www.cnblogs.com/linjiqin/
J2EE、Android、Linux、Oracle QQ交流群：142463980、158560018(满)

posted on 2012-04-06 15:43  Ruthless  阅读(10518)  评论(0编辑  收藏