Using Grouping_id() in aggregate.-CSDN博客

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"