# Oracle分析函数-2

[@more@]

--CCUBE

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

 DEPTNO ENAME SAL 1 0 AUDIT\$ 150 2 0 DBMS_STANDARD 650 3 0 FLOAT 600 … … … 19 0 V_\$SESSION_EVENT 1000 20 0 V_\$SPPARAMETER 850 21 0 11300 22 1 INTEGER 601 23 1 I_AUDIT 151 … …. … 39 1 V\$SESSION_EVENT 1001 40 1 V\$SPPARAMETER 851 41 1 V_\$SESSION_OBJECT_CACHE 1051 42 1 11320 43 AUDIT\$ 150 44 DBMS_STANDARD 650 57 JOB\$ 201 58 JOBSEQ 200 59 KOPM\$ 350 … … … 78 V_\$OPTION 900 79 V_\$PGA_TARGET_ADVICE_HISTOGRAM 700 80 V_\$SESSION_EVENT 1000 81 V_\$SESSION_OBJECT_CACHE 1051 82 V_\$SPPARAMETER 850 83 22620

/*--------理解cube

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

group by cube( a, b, c)

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

group by grouping sets(

( a, b, c ),

( a, b ), ( a ), ( b, c ),

( b ), ( a, c ), ( c ),

() )

*/

--DGROUPING

/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,

select decode(grouping(id),1,'all id',id) id,

decode(grouping(area),1,'all area',to_char(area)) area,

decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,

sum(score) score

from hyf_students

group by cube(id,area,stu_type)

order by id,area,stu_type;

Ø OVER函数

1. rank,dense_rank,row_number函数的介绍

select deptno,
ename,
sal,
rank() over(partition by deptno order by sal) seq1,
dense_rank() over(partition by deptno order by sal) seq2,
row_number() over(partition by deptno order by sal) seq3
from hyf_t
where deptno <
2;

DEPTNO

ENAME

SAL

SEQ1

SEQ2

SEQ3

0

I_CDEF1

50

1

1

1

0

AUDIT\$

100

2

2

2

0

TRUSTED_LIST\$

100

2

2

3

0

JOBSEQ

200

4

3

4

0

I_TYPE2

300

5

4

5

1

I_CDEF2

51

1

1

1

1

PROPS\$

101

2

2

2

1

I_AUDIT

• 博文量
127
• 访问量
821838