ITPub博客

首页 > IT基础架构 > 网络安全 > Using Grouping_id() in aggregate.

Using Grouping_id() in aggregate.

原创 网络安全 作者:jametong 时间:2005-03-28 18:41:09 0 删除 编辑
You Asked (Jump to Tom's latest followup)
Hi Tom
I never really understood the usage of GROUPING_ID function in OTN.I heard it
avoids using multiple GROUPING functions.Can you please illustrate with a small
example
Thanks
and we said...
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:18735507471922#18844335353167
You use grouping_id with "grouping sets" or group by cube/rollup -- it tells you 
the level of aggregation.

Consider, we'll show grouping sets, group by rollup and group by cube to see
what you get:

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.

ops$tkyte@ORA9IR2> select deptno, job, sum(sal),
2 grouping_id(deptno) deptno_g,
3 grouping_id(job) job_g,
4 case when grouping_id(deptno)||grouping_id(job) = '00'
5 then 'Dtl both'
6 when grouping_id(deptno)||grouping_id(job) = '10'
7 then 'Agg over deptno'
8 when grouping_id(deptno)||grouping_id(job) = '01'
9 then 'Agg over job'
10 when grouping_id(deptno)||grouping_id(job) = '11'
11 then 'Agg over both'
12 end what
13 from emp
14 group by grouping sets( (deptno), (job) )
15 /

DEPTNO JOB SUM(SAL) DEPTNO_G JOB_G WHAT
------ --------- ---------- ---------- ---------- ---------------
10 8750 0 1 Agg over job
20 10875 0 1 Agg over job
30 9400 0 1 Agg over job
ANALYST 6000 1 0 Agg over deptno
CLERK 4150 1 0 Agg over deptno
MANAGER 8275 1 0 Agg over deptno
PRESIDENT 5000 1 0 Agg over deptno
SALESMAN 5600 1 0 Agg over deptno

8 rows selected.

We asked for group bys only on DEPTNO and then only on JOB. So, that was like
running the query:

select deptno, null, sum(sal) from emp group by deptno
union all
select null, job, sum(sal) from emp group by job;

in one query.... -- grouping_id tells us what level of detail we have on each
row


ops$tkyte@ORA9IR2> select deptno, job, sum(sal),
2 grouping_id(deptno) deptno_g,
3 grouping_id(job) job_g,
4 case when grouping_id(deptno)||grouping_id(job) = '00'
5 then 'Dtl both'
6 when grouping_id(deptno)||grouping_id(job) = '10'
7 then 'Agg over deptno'
8 when grouping_id(deptno)||grouping_id(job) = '01'
9 then 'Agg over job'
10 when grouping_id(deptno)||grouping_id(job) = '11'
11 then 'Agg over both'
12 end what
13 from emp
14 group by rollup( deptno, job )
15 /

DEPTNO JOB SUM(SAL) DEPTNO_G JOB_G WHAT
------ --------- ---------- ---------- ---------- ---------------
10 CLERK 1300 0 0 Dtl both
10 MANAGER 2450 0 0 Dtl both
10 PRESIDENT 5000 0 0 Dtl both
10 8750 0 1 Agg over job
20 CLERK 1900 0 0 Dtl both
20 ANALYST 6000 0 0 Dtl both
20 MANAGER 2975 0 0 Dtl both
20 10875 0 1 Agg over job
30 CLERK 950 0 0 Dtl both
30 MANAGER 2850 0 0 Dtl both
30 SALESMAN 5600 0 0 Dtl both
30 9400 0 1 Agg over job
29025 1 1 Agg over both

13 rows selected.

Rollup is sort of like a running total report -- and grouping id tells us when
the rollups happened. So the data is sorted by deptno, job and we have
subtotals by job (agg over job) and by deptno, job (agg over both) along with
the details by deptno/job

cube is similar but you get all possible aggregations:



ops$tkyte@ORA9IR2> select deptno, job, sum(sal),
2 grouping_id(deptno) deptno_g,
3 grouping_id(job) job_g,
4 case when grouping_id(deptno)||grouping_id(job) = '00'
5 then 'Dtl both'
6 when grouping_id(deptno)||grouping_id(job) = '10'
7 then 'Agg over deptno'
8 when grouping_id(deptno)||grouping_id(job) = '01'
9 then 'Agg over job'
10 when grouping_id(deptno)||grouping_id(job) = '11'
11 then 'Agg over both'
12 end what
13 from emp
14 group by cube( deptno, job )
15 /

DEPTNO JOB SUM(SAL) DEPTNO_G JOB_G WHAT
------ --------- ---------- ---------- ---------- ---------------
29025 1 1 Agg over both
CLERK 4150 1 0 Agg over deptno
ANALYST 6000 1 0 Agg over deptno
MANAGER 8275 1 0 Agg over deptno
SALESMAN 5600 1 0 Agg over deptno
PRESIDENT 5000 1 0 Agg over deptno
10 8750 0 1 Agg over job
10 CLERK 1300 0 0 Dtl both
10 MANAGER 2450 0 0 Dtl both
10 PRESIDENT 5000 0 0 Dtl both
20 10875 0 1 Agg over job
20 CLERK 1900 0 0 Dtl both
20 ANALYST 6000 0 0 Dtl both
20 MANAGER 2975 0 0 Dtl both
30 9400 0 1 Agg over job
30 CLERK 950 0 0 Dtl both
30 MANAGER 2850 0 0 Dtl both
30 SALESMAN 5600 0 0 Dtl both

18 rows selected.


so, grouping id doesn't avoid multiple grouping functions (grouping SETS
does). but grouping id plays an important role in seeing what data is "what"

[@more@]

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

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280599