59.Oracle数据库SQL开发之 高级查询——使用扩展的GROUP BY子句

59.Oracle数据库SQL开发之 高级查询——使用扩展的GROUPBY子句

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847027

ROLLUP,是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及所有分组返回小计记录以及为所有分组返回总计记录。

         CUBE，也是GROUP子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

执行如下：

store@PDB1> select * from divisions;

 

DIV NAME

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

SAL Sales

OPE Operations

SUP Support

BUS Business

store@PDB1> SELECT * from jobs;

 

JOB NAME

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

WOR Worker

MGR Manager

ENG Engineer

TEC Technologist

PRE President

执行如下：

store@PDB1> select * from employees2 where rownum<= 5;

 

EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME      SALARY

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

           1 BUS PRE James      Smith          800000

           2 SAL MGR Ron        Johnson             350000

           3 SAL WOR Fred       Hobbs        140000

           4 SUP MGR Susan      Jones         200000

           5 SAL WOR Rob        Green       350000

1.  使用ROLLUP子句

将employee2表中的数据按division_id进行分组，并使用SUM()得到每一个division_id的工资总计：

store@PDB1> select division_id,sum(salary) fromemployees2 group by division_id order by division_id;

DIV SUM(SALARY)

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

BUS  1610000

OPE  1320000

SAL   4936000

SUP  1015000

1.1             向ROLLUP传递一列

store@PDB1> select division_id,sum(salary) fromemployees2 group by rollup(division_id) order by division_id;

 

DIV SUM(SALARY)

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

BUS  1610000

OPE  1320000

SAL   4936000

SUP  1015000

         8881000

最后多了一条记录，包含所有分组的工资总计。

如果需要以特定的顺序排列行，应该使用ORDER BY 子句。

1.2             向ROLLUP传递多列

store@PDB1> select division_id,job_id,sum(salary)from employees2 group by rollup(division_id,job_id) order bydivision_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 rows selected.

1.3             修改传递给ROLLUP的列的位置

store@PDB1> select job_id,division_id,sum(salary)from employees2 group by rollup(job_id,division_id) order byjob_id,division_id;

 

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 rows selected.

1.4             ROLLUP与其他的聚合函数一起使用

任何聚合函数都可以和ROLLUP一起使用。

store@PDB1> select division_id,job_id,avg(salary)from employees2 group by rollup(division_id,job_id) order bydivision_id,job_id;

 

DIV JOB AVG(SALARY)

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

BUS MGR 176666.667

BUS PRE     800000

BUS WOR     280000

BUS       322000

OPE ENG     245000

OPE MGR     201250

OPE WOR     135000

OPE  188571.429

SAL MGR 261529.412

SAL WOR     245000

SAL   259789.474

SUP MGR     232500

SUP TEC     115000

SUP WOR     145000

SUP  169166.667

          240027.027

2.  使用CUBE子句

使用CUBE子句对GROUP BY进行扩展，返回CUBE中所有列组合的小计信息，同时在最后显示总计信息。

store@PDB1> select division_id,job_id,sum(salary)from employees2 group by cube(division_id,job_id) 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

   ENG      245000

   MGR     6246000

   PRE      800000

   TEC      115000

   WOR     1475000

             8881000

 

21 rows selected.

改变division_id 和 job_id的顺序

store@PDB1> select job_id,division_id,sum(salary)from employees2 group by cube(job_id,division_id) order by job_id,division_id;

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 rows selected.

3.  使用GROUPING 函数

GROUPING函数可以接受一列，返回0或者1. 列值为空，那么GROUPING返回1.

GROUPING只能在使用ROLLUP或CUBE的查询中使用。

3.1         在ROLLUP中对单列使用GROUPING

store@PDB1> select division_id,sum(salary) fromemployees2 group by rollup(division_id) order by division_id;

 

DIV SUM(SALARY)

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

BUS  1610000

OPE  1320000

SAL   4936000

SUP  1015000

         8881000

最后一条记录的division_id列为空。可以用GROUPING函数来确定这一列是否为空。

store@PDB1> selectgrouping(division_id),division_id,sum(salary) from employees2 group byrollup(division_id) order by division_id;

GROUPING(DIVISION_ID) DIV SUM(SALARY)

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

                       0 BUS    1610000

                       0 OPE    1320000

                       0 SAL    4936000

                       0 SUP    1015000

                       1             8881000

3.2         使用CASE转换GROUPING的返回值

store@PDB1> select case grouping(division_id) when1 then 'all divisions'

    elsedivision_id

    end as div,

    sum(salary)

    fromemployees2 group by rollup(division_id) order by division_id;

DIV        SUM(SALARY)

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

BUS             1610000

OPE             1320000

SAL              4936000

SUP             1015000

all divisions         8881000

3.3         使用CASE和GROUPING转换多个列的值

store@PDB1> select casegrouping(division_id)

   when 1 then 'all divisions'

   else division_id

   end as div,

   case grouping(job_id)

   when 1 then 'all jobs'

   else job_id

   end as job,

   sum(salary)

  from employees2 group by rollup(division_id,job_id)

  order by division_id,job_id;

 

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 rows selected.

3.4         使用CUBE与GROUPING结合使用

store@PDB1> select case grouping(division_id) when1 then 'all divisions'

    elsedivision_id end as div,

    casegrouping(job_id)

    when 1 then'all jobs'

    else job_id

    end as job,

    sum(salary)from employees2

    group bycube(division_id,job_id)

    order bydivision_id,job_id;

 

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 rows selected.

3.5         使用GROUPINGS SETS子句

使用GROUPING SETS子句可以只返回小计记录。

例如：

store@PDB1> select division_id,job_id,sum(salary)from employees2 group by grouping sets( division_id,job_id)

    order bydivision_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 rows selected.

返回结果中只有division_id和job_id列的小计，而所有记录的工资总计记录并没有返回。

GROUPING SETS子句性能一般比CUBE好，尽可能使用GROUPINGSETS 少使用CUBE。

3.6         使用GROUPING_ID函数

使用GROUPING_ID函数借助HAVING子句对记录进行过滤。可以接受一列或多列。

当列值为空时，GROUPING返回1，当列值非空时返回0.

如下图1：

store@PDB1> select division_id,job_id,

    grouping(division_id)as div_grp,

   grouping(job_id) as job_grp,

   grouping_id(division_id,job_id) as grp_id,

    sum(salary)

    fromemployees2

    group bycube(division_id,job_id)

    order bydivision_id,job_id;

 

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 rows selected.

GROUPING_ID的一个用武之地在于使用HAVING子句过滤记录。HAVING子句可以将不包含小计或总计的记录出去。

store@PDB1> select division_id,job_id,grouping_id(division_id,job_id)as grp_id,

    sum(salary)from employees2 group by cube(division_id,job_id)

    havinggrouping_id(division_id,job_id) > 0 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 rows selected.

3.7         在GROUP BY子句中多次使用一个列

在GROUP BY子句中可以多次使用某个列，这样可以实现对数据的重新组织，或是按照不同的数据分组进行统计。

store@PDB1> select division_id,job_id,sum(salary)from employees2 group by division_id,rollup(division_id,job_id);

 

DIV JOB SUM(SALARY)

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

BUS MGR     530000

BUS PRE     800000

BUS WOR     280000

OPE ENG     245000

OPE MGR     805000

OPE WOR     270000

SAL MGR    4446000

SAL WOR     490000

SUP MGR     465000

SUP TEC     115000

SUP WOR     435000

BUS      1610000

OPE      1320000

SAL       4936000

SUP      1015000

BUS      1610000

OPE      1320000

SAL       4936000

SUP      1015000

 

19 rows selected.

最后四行记录出现重复，可以使用GROUP_ID来进行消除。

3.8         使用GROUP_ID函数

GROUP_ID函数用于消除GROUPBY子句返回的重复记录。GROUP_ID不接受任何参数。

store@PDB1> selectdivision_id,job_id,group_id(),sum(salary) from employees2 group bydivision_id,rollup(division_id,job_id);

 

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 rows selected.

使用HAVING子句消除重复记录，只返回GROUP_ID等于0的记录，如下：

store@PDB1> select division_id,job_id,group_id(),sum(salary)from employees2 group by division_id,rollup(division_id,job_id) havinggroup_id()=0;

 

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 rows selected.