3_SQL 11g (Advanced Queries_2)

Chapter 7: Advanced Queries(2)

■The Extended GROUP BY Clauses

・ROLLUP, which extends the GROUP BY clause to return a row containing a subtotal for each group of rows, plus a row containing a grand total for all the groups.

eg:

①

SQL> select division_id,sum(salary)

  2  from employees2

  3  group by division_id

  4  order by division_id;

DIV SUM(SALARY)

--- -----------

BUS     1610000

OPE     1320000

SAL     4936000

SUP     1015000

②Passing a Single Column to ROLLUP

SQL> select division_id,sum(salary)

  2  from employees2

  3  group by rollup(division_id)

  4  order by division_id;

DIV SUM(SALARY)

--- -----------

BUS     1610000

OPE     1320000

SAL     4936000

SUP     1015000

        8881000

③Passing Multiple Columns to ROLLUP

SQL> select division_id,job_id,sum(salary)

  2  from employees2

  3  group by rollup(division_id,job_id)

  4  order by division_id,job_id;

DIV JOB SUM(SALARY)

--- --- -----------

BUS MGR      530000

BUS PRE      800000

BUS WOR      280000

BUS         1610000

OPE ENG      245000

OPE MGR      805000

OPE WOR      270000

OPE         1320000

SAL MGR     4446000

SAL WOR      490000

SAL         4936000

SUP MGR      465000

SUP TEC      115000

SUP WOR      435000

SUP         1015000

            8881000

已选择16行。

④Changing the Position of Columns Passed to ROLLUP

  1  select job_id,division_id,sum(salary)

  2  from employees2

  3* group by rollup(job_id,division_id)

SQL> /

JOB DIV SUM(SALARY)

--- --- -----------

ENG OPE      245000

ENG          245000

MGR BUS      530000

MGR OPE      805000

MGR SAL     4446000

MGR SUP      465000

MGR         6246000

PRE BUS      800000

PRE          800000

TEC SUP      115000

TEC          115000

WOR BUS      280000

WOR OPE      270000

WOR SAL      490000

WOR SUP      435000

WOR         1475000

            8881000

已选择17行。

⑤Using Other Aggregate Functions with ROLLUP（Avg()....）

・CUBE, which extends the GROUP BY clause to return rows containing a subtotal for all

combinations of columns, plus a row containing the grand total.

SQL> l

  1  select division_id,job_id,sum(salary)

  2  from employees2

  3  group by cube(division_id,job_id)

  4  order by division_id,job_id

  5*

SQL> /

DIV JOB SUM(SALARY)

--- --- -----------

BUS MGR      530000

BUS PRE      800000

BUS WOR      280000

BUS         1610000

OPE ENG      245000

OPE MGR      805000

OPE WOR      270000

OPE         1320000

SAL MGR     4446000

SAL WOR      490000

SAL         4936000

SUP MGR      465000

SUP TEC      115000

SUP WOR      435000

SUP         1015000

    ENG      245000

    MGR     6246000

    PRE      800000

    TEC      115000

    WOR     1475000

            8881000

已选择21行。

SQL> select job_id,division_id,sum(salary)

  2  from employees2

  3  group by cube(job_id,division_id)

  4  order by job_id,division_id

  5  ;

JOB DIV SUM(SALARY)

--- --- -----------

ENG OPE      245000

ENG          245000

MGR BUS      530000

MGR OPE      805000

MGR SAL     4446000

MGR SUP      465000

MGR         6246000

PRE BUS      800000

PRE          800000

TEC SUP      115000

TEC          115000

WOR BUS      280000

WOR OPE      270000

WOR SAL      490000

WOR SUP      435000

WOR         1475000

    BUS     1610000

    OPE     1320000

    SAL     4936000

    SUP     1015000

            8881000

已选择21行。

SQL>

■GROUPING() Function

The GROUPING() function accepts a column and returns 0 or 1. GROUPING() returns 1 when the column value is null and returns 0 when the column value is non-null.

eg:

①Using GROUPING() with a Single Column in a ROLLUP

SQL> select division_id,sum(salary)

  2  from employees2

  3  group by rollup(division_id)

  4  order by division_id

  5  ;

DIV SUM(SALARY)

--- -----------

BUS     1610000

OPE     1320000

SAL     4936000

SUP     1015000

        8881000

SQL> select grouping(division_id),division_id,sum(salary)

  2  from employees2

  3  group by rollup(division_id)

  4  order by division_id;

GROUPING(DIVISION_ID) DIV SUM(SALARY)

--------------------- --- -----------

                    0 BUS     1610000

                    0 OPE     1320000

                    0 SAL     4936000

                    0 SUP     1015000

                    1         8881000

②Using CASE to Convert the Returned Value from GROUPING()

SQL> edit;

已写入 file afiedt.buf

  1  select

  2    case grouping(division_id)

  3      when 1 then 'All divisions'

  4      else division_id

  5    end as div,

  6    sum(salary)

  7  from employees2

  8  group by rollup(division_id)

  9* order by division_id

SQL> /

DIV           SUM(SALARY)

------------- -----------

BUS               1610000

OPE               1320000

SAL               4936000

SUP               1015000

All divisions     8881000

③Using CASE and GROUPING() to Convert Multiple Column Values

SQL> l

  1  select

  2     case grouping(division_id)

  3        when 1 then 'All divisions'

  4        else division_id

  5     end as div,

  6     case grouping(job_id)

  7        when 1 then 'All jobs'

  8        else job_id

  9     end as job,

 10     sum(salary)

 11  from employees2

 12  group by rollup(division_id,job_id)

 13* order by division_id,job_id

SQL> /

DIV           JOB      SUM(SALARY)

------------- -------- -----------

BUS           MGR           530000

BUS           PRE           800000

BUS           WOR           280000

BUS           All jobs     1610000

OPE           ENG           245000

OPE           MGR           805000

OPE           WOR           270000

OPE           All jobs     1320000

SAL           MGR          4446000

SAL           WOR           490000

SAL           All jobs     4936000

SUP           MGR           465000

SUP           TEC           115000

SUP           WOR           435000

SUP           All jobs     1015000

All divisions All jobs     8881000

已选择16行。

④Using GROUPING() with CUBE

SQL> edit;

已写入 file afiedt.buf

  1  select

  2      case grouping(division_id)

  3         when 1 then 'All divisions'

  4         else division_id

  5      end as div,

  6      case grouping(job_id)

  7         when 1 then 'All jobs'

  8          else job_id

  9      end as job,sum(salary)

 10  from employees2

 11  group by cube(division_id,job_id)

 12* order by division_id,job_id

SQL> /

DIV           JOB      SUM(SALARY)

------------- -------- -----------

BUS           MGR           530000

BUS           PRE           800000

BUS           WOR           280000

BUS           All jobs     1610000

OPE           ENG           245000

OPE           MGR           805000

OPE           WOR           270000

OPE           All jobs     1320000

SAL           MGR          4446000

SAL           WOR           490000

SAL           All jobs     4936000

SUP           MGR           465000

SUP           TEC           115000

SUP           WOR           435000

SUP           All jobs     1015000

All divisions ENG           245000

All divisions MGR          6246000

All divisions PRE           800000

All divisions TEC           115000

All divisions WOR          1475000

All divisions All jobs     8881000

已选择21行。

SQL>

■GROUPING SETS Clause

You use the GROUPING SETS clause to get just the subtotal rows.

the totalfor all salaries is not returned.

eg:

SQL> select division_id,job_id,sum(salary)

  2  from employees2

  3  group by grouping sets(division_id,job_id)

  4  order by division_id,job_id;

DIV JOB SUM(SALARY)

--- --- -----------

BUS         1610000

OPE         1320000

SAL         4936000

SUP         1015000

    ENG      245000

    MGR     6246000

    PRE      800000

    TEC      115000

    WOR     1475000

已选择9行。

TIP：The GROUPING SETS clause typically offers better performance than

CUBE. Therefore, you should use GROUPING SETS rather than CUBE　wherever possible.

■GROUPING_ID() Function

You can use the GROUPING_ID() function to filter rows using a HAVING clause to exclude rows

that don’t contain a subtotal or total. The GROUPING_ID() function accepts one or more columns

and returns the decimal equivalent of the GROUPING bit vector.

non-null non-null 00 0
non-null null 01 1
null non-null 10 2
null null 11 3

eg：

SQL> edit

已写入 file afiedt.buf

  1  select

  2    division_id,job_id,

  3    grouping(division_id) as div_grp

  4    ,grouping(job_id) as job_grp,

  5    grouping_id(division_id,job_id) as grp_id,

  6    sum(salary)

  7  from employees2

  8  group by cube(division_id,job_id)

  9* order by division_id,job_id

 10  ;

DIV JOB    DIV_GRP    JOB_GRP     GRP_ID SUM(SALARY)

--- --- ---------- ---------- ---------- -----------

BUS MGR          0          0          0      530000

BUS PRE          0          0          0      800000

BUS WOR          0          0          0      280000

BUS              0          1          1     1610000

OPE ENG          0          0          0      245000

OPE MGR          0          0          0      805000

OPE WOR          0          0          0      270000

OPE              0          1          1     1320000

SAL MGR          0          0          0     4446000

SAL WOR          0          0          0      490000

SAL              0          1          1     4936000

SUP MGR          0          0          0      465000

SUP TEC          0          0          0      115000

SUP WOR          0          0          0      435000

SUP              0          1          1     1015000

    ENG          1          0          2      245000

    MGR          1          0          2     6246000

    PRE          1          0          2      800000

    TEC          1          0          2      115000

    WOR          1          0          2     1475000

                 1          1          3     8881000

已选择21行。

SQL> select

  2    division_id,job_id,

  3    grouping_id(division_id,job_id) as grp_id,

  4    sum(salary)

  5  from employees2

  6  group by cube(division_id,job_id)

  7  having grouping_id(division_id,job_id)>0

  8  order by division_id,job_id;

DIV JOB     GRP_ID SUM(SALARY)

--- --- ---------- -----------

BUS              1     1610000

OPE              1     1320000

SAL              1     4936000

SUP              1     1015000

    ENG          2      245000

    MGR          2     6246000

    PRE          2      800000

    TEC          2      115000

    WOR          2     1475000

                 3     8881000

已选择10行。

■GROUP_ID() Function

・You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BYclause. GROUP_ID() doesn’t accept any parameters. If n duplicates exist for a particulargrouping, GROUP_ID returns numbers in the range 0 to n – 1.

・Using a Column Multiple Times in a GROUP BY Clause

SQL> edit;

已写入 file afiedt.buf

  1  select division_id,job_id,group_id(),sum(salary)

  2  from employees2

  3* group by division_id,rollup(division_id,job_id)

SQL> /

DIV JOB GROUP_ID() SUM(SALARY)

--- --- ---------- -----------

BUS MGR          0      530000

BUS PRE          0      800000

BUS WOR          0      280000

OPE ENG          0      245000

OPE MGR          0      805000

OPE WOR          0      270000

SAL MGR          0     4446000

SAL WOR          0      490000

SUP MGR          0      465000

SUP TEC          0      115000

SUP WOR          0      435000

BUS              0     1610000

OPE              0     1320000

SAL              0     4936000

SUP              0     1015000

BUS              1     1610000

OPE              1     1320000

SAL              1     4936000

SUP              1     1015000

已选择19行。

・

SQL> edit

已写入 file afiedt.buf

  1  select division_id,job_id,group_id(),sum(salary)

  2  from employees2

  3  group by division_id,rollup(division_id,job_id)

  4* having group_id()=0

SQL> /

DIV JOB GROUP_ID() SUM(SALARY)

--- --- ---------- -----------

BUS MGR          0      530000

BUS PRE          0      800000

BUS WOR          0      280000

OPE ENG          0      245000

OPE MGR          0      805000

OPE WOR          0      270000

SAL MGR          0     4446000

SAL WOR          0      490000

SUP MGR          0      465000

SUP TEC          0      115000

SUP WOR          0      435000

BUS              0     1610000

OPE              0     1320000

SAL              0     4936000

SUP              0     1015000

已选择15行。

※

Compare this with the normal ROLLUP as in: 

   GROUP BY ROLLUP(a, b, c)

 which would be 

   GROUP BY a, b, c UNION ALL

   GROUP BY a, b UNION ALL

   GROUP BY a UNION ALL

   GROUP BY ().

※

 Similarly, 

   GROUP BY CUBE((a, b), c) 

 would be equivalent to

   GROUP BY a, b, c UNION ALL

   GROUP BY a, b UNION ALL

   GROUP BY c UNION ALL

   GROUP By ()

※※

The following table shows grouping sets specification and equivalent GROUP BY specification.

GROUPING SETS Satements Equivalent GROUP BY Statements

GROUP BY a UNION ALL

GROUP BY GROUPING SETS(a,b,c) GROUP BY b UNION ALL

GROUP BY c

GROUP BY GROUPING SETS(a,b,(b,c)) GROUP BY a UNION ALL

(The GROUPING SETS expression has a GROUP BY b UNION ALL

composite column) GROUP BY b,c

GROUP BY GROUPING SETS((a,b,c)) GROUP BY a,b,c

GROUP BY a UNION ALL

GROUP BY GROUPING SETS(a,(b),()) GROUP BY b UNION ALL

GROPY BY ()

GROUP BY GROUPING SETS(a,ROLLUP(b,c)) GROUP BY a UNION ALL

(The GROUPING SETS expression has a GROUP BY ROLLUP(b,c)

composite column)

※※

※※CUBE and ROLLUP can be thought of as grouping sets with very specific semantics. The following equivalencies show this fact: 

CUBE (a,b,c)   GROUPING SETS //2的3次方

is equivalent to   ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c)())

ROLLUP(a,b,c) GROUPING SETS

is equivalent to ((a,b,c),(a,b),(a),())

■Using the Analytic Functions

246~