转： 聚合函数的扩展使用

1.ROLLUP子句

     ROLLUP是GROUP BY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。下面举例说明：

EG：

select * from employees2;

EMPLOYEE_ID DIVISI JOB_ID 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

          6 SUP    WOR    Jane                 Brown                    200000

          7 SUP    MGR    John                 Grey                     265000

          8 SUP    WOR    Jean                 Blue                     110000

          9 SUP    WOR    Henry                Heyson                   125000

         10 OPE    MGR    Kevin                Black                    225000

         11 OPE    MGR    Keith                Long                     165000

         12 OPE    WOR    Frank                Howard                   125000

         13 OPE    WOR    Doreen               Penn                     145000

         14 BUS    MGR    Mark                 Smith                    155000

         15 BUS    MGR    Jill                 Jones                    175000

         16 OPE    ENG    Megan                Craig                    245000

         17 SUP    TEC    Matthew              Brant                    115000

         18 OPE    MGR    Tony                 Clerke                   200000

         19 BUS    MGR    Tanya                Conway                   200000

         20 OPE    MGR    Terry                Cliff                    215000

         21 SAL    MGR    Steve                Green                    275000

         22 SAL    MGR    Roy                  Red                      375000

         23 SAL    MGR    Sandra               Smith                    335000

         24 SAL    MGR    Gail                 Silver                   225000

         25 SAL    MGR    Gerald               Gold                     245000

         26 SAL    MGR    Eileen               Lane                     235000

         27 SAL    MGR    Doreen               Upton                    235000

         28 SAL    MGR    Jack                 Ewing                    235000

         29 SAL    MGR    Paul                 Owens                    245000

         30 SAL    MGR    Melanie              York                     255000

         31 SAL    MGR    Tracy                Yellow                   225000

         32 SAL    MGR    Sarah                White                    235000

         33 SAL    MGR    Terry                Iron                     225000

         34 SAL    MGR    Christine            Brown                    247000

         35 SAL    MGR    John                 Brown                    249000

         36 SAL    MGR    Kelvin               Trenton                  255000

         37 BUS    WOR    Damon                Jones                    280000

 

已选择37行。

首先我们来看单独的GROUP BY语句：

select division_id,SUM(salary) from employees2

group by division_id;

DIVISI SUM(SALARY)

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

BUS        1610000

OPE        1320000

SAL        4936000

SUP        1015000

接下来是扩展了的ROLLUP的使用：

select division_id,SUM(salary) from employees2

group by ROLLUP(division_id);

DIVISI SUM(SALARY)

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

BUS        1610000

OPE        1320000

SAL        4936000

SUP        1015000

           8881000

再来看个例子：

select division_id,job_id,SUM(salary)

 from employees2

  group by ROLLUP(division_id,job_id);

DIVISI JOB_ID 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

小结：扩展了一个rollup子句带来了分组的一个合计。

2.CUBE子句

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

EG：

select division_id,job_id,SUM(salary)

 from employees2

  group by CUBE(division_id,job_id)

   order by division_id;

DIVISI JOB_ID 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

结：CUBE这里的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示次分组字段的合计信息（job_id）

3.GROUPING()函数与ROLLUP、CUBE的结合使用

GROUPING（）可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。（注意：GROUPING只能在ROLLUP、CUBE查询中使用。还可以结合DECODE（）函数来注释使用更好）

现在我们把上面的一个例子改一下;

select GROUPING(division_id), division_id,SUM(salary)

 from employees2

  group by ROLLUP(division_id);

 

GROUPING(DIVISION_ID) DIVISI SUM(SALARY)

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

                    0 BUS        1610000

                    0 OPE        1320000

                    0 SAL        4936000

                    0 SUP        1015000

                    1            8881000

下面再用DECODE（）函数来转换下更好：

select DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,

 division_id,SUM(salary)

  from employees2

   group by ROLLUP(division_id);

DIV                        DIVISI SUM(SALARY)

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

BUS                        BUS        1610000

OPE                        OPE        1320000

SAL                        SAL        4936000

SUP                        SUP        1015000

All divisions                         8881000

哈哈，这样看下更清晰明了

再举例CUBE与GROUPING（）结合的使用

select

 DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,

 DECODE(GROUPING(job_id),1,'All jobs',job_id) AS job,

 SUM(salary)

  from employees2

   group by CUBE(division_id,job_id)

   order by division_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行。

2）下面使用GROUPING SETS子句来限制只返回小计记录。例如下：

select division_id,job_id,SUM(salary)

 from employees2

  group by GROUPING SETS(division_id,job_id);

DIVISI JOB_ID SUM(SALARY)

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

BUS               1610000

OPE               1320000

SAL               4936000

SUP               1015000

       ENG         245000

       MGR        6246000

       PRE         800000

       TEC         115000

       WOR        1475000

 

已选择9行。

4.GROUPING_ID（）函数的使用

GROUPING_ID（）函数可接受一列或多列，它返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是按照顺序对每一列调用GROUPING（）函数的结果组合起来。它的作用是借助HAVING子句对记录进行过滤，将不包含小计或总计的记录除去。

GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING（）的列值为空时它返回1，当非空时返回0；

比如：division_id,job_id两列都为非空，GROUPING（）都返回0。将这两列的值组合起来，形成一个位向量00，十进制为0。即，当division_id,job_id都非空时，GROUPING_ID（）返回0。（这里要特别注意division_id与job_id两列的顺序）下面举个例子：

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)

 from employees2

  group by CUBE(division_id,job_id)

  order by division_id;

 

DIVISI JOB_ID    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行。

下面是一个使用GROUPING_ID过滤不包含小计或总计的记录的例子：

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)

  having grouping_id(division_id,job_id)>0

  order by division_id;

DIVISI JOB_ID     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

好了这次对聚合函数GROUP BY的扩展使用到此，下次对分析函数做下总结