oracle 1z007: Enhancements to the group by clause

group by及其高级函数

先看几个例子

 

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as system

test1表存放1-4个人销售业绩，如下：

SQL> Select * From test1    Order By Id ;
        ID NAME            SALES
---------- ---------- ----------
         1 Pataballa         100
         1 Pataballa         200
         1 Pataballa          50
         2 Lorentz            90
         2 Lorentz            20
         3 Diana             500
         4 Valli              40
         4 Valli              60
8 rows selected

 

1、Group By  后则是按照 ID,NAME进行分组压缩：

SQL> Select Id,Name ,Sum(sales) From test1   Group By  Id,Name Order By Id ;
        ID NAME       SUM(SALES)
---------- ---------- ----------
         1 Pataballa         350
         2 Lorentz           110
         3 Diana             500
         4 Valli             100

2、group by rollup( a,b)

是针对group by 后的结果，再按分组字段从右向左分别做 group by a、group by b ，最后再按 (a、b) 做group by ( a,b);

SQL> Select Id,Name ,Sum(sales) From test1   Group By rollup( Id,Name ) Order By Id ;
        ID NAME       SUM(SALES)
---------- ---------- ----------
         1 Pataballa         350
         1                   350
         2 Lorentz           110
         2                   110
         3 Diana             500
         3                   500
         4 Valli             100
         4                   100
                            1060
9 rows selected

分解如下：

先建个group by 的结果表：

SQL> Create Table test2 As Select Id,Name ,Sum(sales) sales From test1   Group By  Id,Name Order By Id ;
Table created

结果：
SQL> Select * From test2;
        ID NAME            SALES
---------- ---------- ----------
         1 Pataballa         350
         2 Lorentz           110
         3 Diana             500
         4 Valli             100

Group By rollup( Id,Name )则先按照name group by：

SQL> Select  Name ,Sum(sales)  From test2   Group By   Name  ;
NAME       SUM(SALES)
---------- ----------
Diana             500
Pataballa         350
Lorentz           110
Valli             100

 

Group By rollup( Id,Name )则再按照id group by：

SQL> Select  Id ,Sum(sales)  From test2   Group By   Id  ;
        ID SUM(SALES)
---------- ----------
         1        350
         2        110
         4        100
         3        500

 

别忘了最后的一个超集：

SQL> select sum(sales) from test2;
SUM(SALES)
----------
      1060

和起来的结果如下：

SQL> Select  null,Name ,Sum(sales)  From test2   Group By   Name  Union All
  2  Select  Id ,null,Sum(sales)  From test2   Group By   Id  Union All
  3  select null,null,sum(sales) from test2;
      NULL NAME       SUM(SALES)
---------- ---------- ----------
           Diana             500
           Pataballa         350
           Lorentz           110
           Valli             100
         1                   350
         2                   110
         4                   100
         3                   500
                            1060
9 rows selected

一个比较特殊的是：

SQL> Select Id,Name ,Sum(sales) From test1   Group By rollup(( Id,Name )) Order By Id ;
        ID NAME       SUM(SALES)
---------- ---------- ----------
         1 Pataballa         350
         2 Lorentz           110
         3 Diana             500
         4 Valli             100
                            1060
SQL>

其实就是针对（（id，name）） group by 然后再来个超集

以上就是 group by rullup函数。

 

3、 group by cube( a,b)

group by cube会针对group by rollup再对b做个group by；

SQL> Select Id,Name ,Sum(sales) sales From test1   Group By Cube( Id,Name);
        ID NAME            SALES
---------- ---------- ----------
                            1060
           Diana             500
           Valli             100
           Lorentz           110
           Pataballa         350
         1                   350
         1 Pataballa         350
         2                   110
         2 Lorentz           110
         3                   500
         3 Diana             500
         4                   100
         4 Valli             100
13 rows selected

上面的结果就是针对group by rollup 的结果再对Name做个group by；

4、grouping（id） 用来判断该行的聚合是否是该字段的聚合的结果，如果是则返回0，否则1；

SQL> Select Id,Name ,Sum(sales),Grouping(Id),Grouping(Name)   From test1   Group By  Id,Name Order By Id ;
        ID NAME       SUM(SALES) GROUPING(ID) GROUPING(NAME)
---------- ---------- ---------- ------------ --------------
         1 Pataballa         350            0              0
         2 Lorentz           110            0              0
         3 Diana             500            0              0
         4 Valli             100            0              0

上面说明返回的结果都是依照 id name 合计的；

SQL> Select Id,Name ,Sum(sales),Grouping(Id),Grouping(Name)     From test1   Group By Rollup( Id,Name);
        ID NAME       SUM(SALES) GROUPING(ID) GROUPING(NAME)
---------- ---------- ---------- ------------ --------------
         1 Pataballa         350            0              0          返回的结果都是依照 id name 合计的
         1                   350            0              1               返回的结果都是依照 id 合计的
         2 Lorentz           110            0              0          返回的结果都是依照 id name 合计的
         2                   110            0              1               返回的结果都是依照 id 合计的
         3 Diana             500            0              0           返回的结果都是依照 id name 合计的
         3                   500            0              1               返回的结果都是依照 id 合计的
         4 Valli             100            0              0              返回的结果都是依照 id name 合计的
         4                   100            0              1               返回的结果都是依照 id 合计的
                            1060            1              1               返回的结果都不是依照 id name 合计的
9 rows selected

 

下面还有几个例子：
SQL> Select Id,Name ,Sum(sales),Grouping(Id),Grouping(Name)     From test1   Group By Cube( Id,Name);
        ID NAME       SUM(SALES) GROUPING(ID) GROUPING(NAME)
---------- ---------- ---------- ------------ --------------
                            1060            1              1
           Diana             500            1              0
           Valli             100            1              0
           Lorentz           110            1              0
           Pataballa         350            1              0
         1                   350            0              1
         1 Pataballa         350            0              0
         2                   110            0              1
         2 Lorentz           110            0              0
         3                   500            0              1
         3 Diana             500            0              0
         4                   100            0              1
         4 Valli             100            0              0
13 rows selected
SQL> Select * From test2;
        ID NAME            SALES
---------- ---------- ----------
         1 Pataballa         350
         2 Lorentz           110
         3 Diana             500
         4 Valli             100
SQL> Select  Id , Sum(sales),Grouping(Id)   From test2   Group By   Id ;
        ID SUM(SALES) GROUPING(ID)
---------- ---------- ------------
         1        350            0
         2        110            0
         4        100            0
         3        500            0
SQL> Select  Name , Sum(sales) ,Grouping(Name)  From test2   Group By   Name ;
NAME       SUM(SALES) GROUPING(NAME)
---------- ---------- --------------
Diana             500              0
Pataballa         350              0
Lorentz           110              0
Valli             100              0
SQL>

5、grouping sets（id，name）分别针对 id和name做group by 然后 union all：

SQL> Select Id,Name ,Sum(sales)   From test1   Group By  Id,Name Order By Id ;
        ID NAME       SUM(SALES)
---------- ---------- ----------
         1 Pataballa         350
         2 Lorentz           110
         3 Diana             500
         4 Valli             100
SQL> Select Id,Name ,Sum(sales)   From test1  Group By Grouping sets(Id,Name) Order By Id ;
        ID NAME       SUM(SALES)
---------- ---------- ----------
         1                   350
         2                   110
         3                   500
         4                   100
           Valli             100
           Diana             500
           Pataballa         350
           Lorentz           110
8 rows selected