Chinaunix首页 | 论坛 | 博客
  • 博客访问: 980948
  • 博文数量: 358
  • 博客积分: 8185
  • 博客等级: 中将
  • 技术积分: 3751
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-15 16:27
个人简介

The views and opinions expressed all for my own,only for study and test, not reflect the views of Any Company and its affiliates.

文章分类

全部博文(358)

文章存档

2012年(8)

2011年(18)

2010年(50)

2009年(218)

2008年(64)

我的朋友

分类: Oracle

2009-07-13 21:36:33

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~
阅读(522) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~