Oracle分析函式-1

學習筆記

[@more@]

Ø e建立基礎資料

create table hyf_t as
select object_name ename,mod(object_id,50) deptno, created fired_date, object_id sal
from all_objects where rownum<=200

create index hyf_t on hyf_t(deptno,fired_date,ename)

Ø GROUP函式

A、GROUPING SETS

select deptno, ename, sum(sal) sal
from hyf_t

where deptno <2
group by grouping sets((deptno, ename), deptno)
order by deptno

DEPTNO	ENAME	SAL
0	AUDIT$	150
0	DBMS_STANDARD	650
0	FLOAT	600
0	V_$PGA_TARGET_ADVICE_HISTOGRAM	700
0	V_$SESSION_EVENT	1000
0	V_$SPPARAMETER	850
0		11300
1	INTEGER	601
1	I_AUDIT	151
1	V$OPTION	901
1	V$PGA_TARGET_ADVICE_HISTOGRAM	701
1	V$SESSION_EVENT	1001
1	V$SPPARAMETER	851
1	V_$SESSION_OBJECT_CACHE	1051
1		11320

/*--------理解grouping sets

select a, b, c, sum( d ) from t

group by grouping sets ( a, b, c )

等效於

select * from (

select a, null, null, sum( d ) from t group by a

union all

select null, b, null, sum( d ) from t group by b

union all

select null, null, c, sum( d ) from t group by c

)

*/

--B、ROLLUP

select deptno, ename, sum(sal) sal
from hyf_t
where deptno < 2
group by rollup(deptno, ename)
order by deptno

deptno	ename	sal
0	AUDIT$	150
0	DBMS_STANDARD	650
	… …
0	V_$SESSION_EVENT	1000
0	V_$SPPARAMETER	850
0		11300
1	INTEGER	601
	… …
1	I_AUDIT	151
1	V_$SESSION_OBJECT_CACHE	1051
1		11320
		22620

/*--------理解rollup

select a, b, c, sum( d )

from t

group by rollup(a, b, c);

等效於

select * from (

select a, b, c, sum( d ) from t group by a, b, c

union all

select a, b, null, sum( d ) from t group by a, b

union all

select a, null, null, sum( d ) from t group by a

union all

select null, null, null, sum( d ) from t

)

*/