LAG.LEAD .RATIO_TO_REPORT 分析函數的用法

LAG(EXPRESSION,,)

SQL> with a as (select 1 id,'a' name from dual
2 union
3 select 2 id,'b' name from dual
4 union
5 select 3 id,'c' name from dual
6 union
7 select 4 id,'d' name from dual
8 union
9 select 5 id,'e' name from dual
10 )
11 select id,name,lag(id,1,'')over(order by name) from a
12 /

ID NAME LAG(ID,1,'')OVER(ORDERBYNAME)
---------- ---- -----------------------------
1 a
2 b 1
3 c 2
4 d 3
5 e 4

LEAD(EXPRESION,,)

SQL> with a as (select 1 id,'a' name from dual
2 union
3 select 2 id,'b' name from dual
4 union
5 select 3 id,'c' name from dual
6 union
7 select 4 id,'d' name from dual
8 union
9 select 5 id,'e' name from dual
10 )
11 select id,name,lead(id,1,'')over(order by name) from a
12 /

ID NAME LEAD(ID,1,'')OVER(ORDERBYNAME)
---------- ---- ------------------------------
1 a 2
2 b 3
3 c 4
4 d 5
5 e

 

RATIO_TO_REPORT(col) over ([partition_clause]) 本函数计算本行col列值在该分组序列sum(col)中所占比率

with a as (select 1 a from dual
           union all
           select 1 a from dual
           union  all
           select 1 a from dual
           union all
           select 2 a from dual
           union all
           select 3 a from dual
           union all
           select 4 a from dual
           union all
           select 4 a from dual
           union all
           select 5 a from dual
           )
select a, ratio_to_report(a)over(partition by a) b from a
order by a;  --a為1 時 1/3,a為2 時 1/1,a為3 時 1/1,a為4 時 1/2,a為5 時 1/1

select a, ratio_to_report(a)over() b from a
order by a; --a/SUM(A)

select a, ratio_to_report(COUNT(a))over() b from a
GROUP BY A    --用分組后的COUNT(A)/總的COUNT(A)
order by a;

 

--group by 函数的扩展使用

with a as (select 1 a,11 b,10 c from dual
             union
             select 2 a,11 b,20 c from dual
             union
             select 3 a,22 b,30 c from dual
             union
             select 4 a,22 b,40 c from dual
             )
/*select b,c,sum(c) c   --ROLLUP是GROUP BY子句的扩展，它是为每一个分组(B和C)返回一条合计记录，并为全部分组返回总计
from a
group by rollup (b,c)   */ 
       
/*select b,c,sum(c) c   --計算每一欄(B)分組之SUM;每一欄(C)分組之SUM,最后再總的SUM
from a
group by cube (b,c) */

/*select b,c,sum(c) c   --GROUPING SETS子句来限制只返回小计记录(A的小計,B的小計,A有11,22兩個,B有4個,所以總共6個)
from a
group by GROUPING SETS (C,B)*/

/*select b,c,sum(c) c1,GROUPING(B) B1,GROUPING(C) C2   --先是按B,C進行分組求和,然后按b分組后的SUM
from a
group by rollup (b,c)*/
--GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING（）的列值为空时它返回1，当非空时返回0；

/*select grouping_id(A,B) C,GROUPING(A) C1,GROUPING(B) C2,
sum(c)
from A
group by rollup(A,B)*/
--GROUPING_ID（）函数可接受一列或多列，它返回GROUPING位向量的十进制值。
GROUPING(A)     GROUPING(B)        GROUPING_ID(A,B)
0                0                   0
0                1                   1
1                0                   2
1                1                   3

select a,DECODE(GROUPING_ID(A,B),2,'小計',3,'總計',B) SUM,
sum(c)
from A
group by rollup(b,A)

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

转载于:http://blog.itpub.net/10768286/viewspace-83495/