`
micheal19840929
  • 浏览: 167284 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

GROUP BY 子句

阅读更多

GROUP BY 子句
GROUP BY子句主要用于对WHERE中得到的结果进行分组,也就是说它是在WHERE子句之后执行,对经过WHERE筛选后的结果按照某些列进行分组,之后进行相应的处理工作。

Specify the GROUP BY clause if you want the database to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group.

当使用聚集函数的时候,除非对整个语句的查询结果集进行聚集运算,否则都要通过指定GROUP BY子句来确定是对某类结果集进行聚集运算。请看下面的例子:

 

17:11:10 SQL> select count(object_name) num from t;  --这里是对整个表进行count运算,不会出错。
   NUM
------
 50599
17:11:17 SQL> select count(object_name) num from t where STATUS='VALID'; --这里对返回来的所有结果进行count运算,不会出错。
  NUM
-----
50578
17:12:00 SQL> select owner,status,count(object_name) num from t; --这里是想对owner和status进行分组,计算出它们的count(object_name)值,没有使用group by来显示分组,出错。
select owner,status,count(object_name) num from t
       *
ERROR at line 1:
ORA-00937: not a single-group group function

17:13:36 SQL> select owner,status,count(object_name) num from t group by owner,status;
OWNER      STATUS          NUM
---------- ----------- -------
PUBLIC     VALID         19968
CTXSYS     VALID           339
..............................
NING       VALID             1
PUBLIC     INVALID          19
ORDSYS     VALID          1669
28 rows selected.
17:23:13 SQL> select owner,status,count(object_name) num from t group by owner,status,temporary; --这里多出了表t中的列temporary
OWNER      STATUS          NUM
---------- ------------ ------
WMSYS      VALID             1
SCOTT      INVALID           2
..............................
ORDSYS     VALID          1669
HR         VALID            34
IX         VALID            53
35 rows selected.
17:29:06 SQL> select owner,status,count(object_name) num from t group by owner; --GROUP BY子句的内容少了status列
select owner,status,count(object_name) num from t group by owner
             *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

可以看到,如果想对某一类结果集进行聚集运算,就必须通过GROUP BY来指定这类结果集,我们还可以看到以下两个特点:

1、 Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list.
2、 在SELECT列表中,除非是函数(主要是聚集函数)、常量以外,其他所有的列名或者表达式(在GROUP BY中必须也要是表达式的原样而不能是它的别名),都必须要在GROUP BY子句中出现。
3、 The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.

使用GROUP BY子句还有下面一些的限制:

1、 你不能在子句中使用LOB/VARRAYS/NESTED TABLE。
2、 子句中的表达式can be of any form. except scalar subquery expressions.
3、 如果GROUP BY中引用了对象类型列,则这个查询就不能使用并行。

HAVING 子句

Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE. If you omit this clause, then the database returns summary rows for all groups.这里的condition是HAVING子句指定的筛选条件。

HAVING子句依附于GROUP BY子句而出现,也就是它不能单独出现。

18:00:14 SQL> select owner,status s,count(object_name) num from t
18:00:43   2  group by owner,status,temporary having count(object_name)>500;
OWNER      S                NUM
---------- --------- ----------
SYS        VALID          23129
XDB        VALID            682
SYSMAN     VALID           1321
PUBLIC     VALID          19968
MDSYS      VALID            859
OLAPSYS    VALID            706
ORDSYS     VALID           1669
7 rows selected.
18:00:47 SQL> select owner,status s,count(object_name) num from t
18:01:58   2  group by owner,status,temporary having num>500; --可见这里不能使用别名
group by owner,status,temporary having num>500
                                       *
ERROR at line 2:
ORA-00904: "NUM": invalid identifier

GROUP BY子句扩展
1、自动汇总ROLLUP与CUBE扩展
我们先来看看ROLLUP的官方定义:

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, … 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function.这里simple_grouping_clause表示GROUP BY子句。

这句话可以这样理解,如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作(即GROUP BY(0))。即: ROLLUP操作字,对group by子句的各字段从右到左进行再聚合,逐个减少字段。ROLLUP常用于统计小计。
请看一个例子:

18:28:41 SQL> select * from t;
A      B             C
------ -------- ------
a      test1         1
a      test1         2
a      test2         1
a      test2         2
18:28:53 SQL> select a,b,c,sum(c) sc from t group by a,b,c order by 1;
A      B             C     SC
------ -------- ------ ------
a      test1         1      1
a      test1         2      2
a      test2         1      1
a      test2         2      2
18:28:57 SQL> select a,b,c,sum(c) sc from t group by rollup(a,b,c) order by 1;
A      B             C     SC
----- --------- ------ ------
a      test1         1      1
a      test1         2      2
a      test1                3  -- A
a      test2         1      1
a      test2         2      2
a      test2                3  -- A
a                           6  -- B
                            6  -- C
8 rows selected.

注意看上面最后一个选择的结果,它多了一些显示记录。很显然,这个A部分是通过对GROUP BY(A,B)而得到的,这个B部分是通过对GROUP BY(A),而C部分是通过GROUP BY(0)而来。如果要分别得到这些值,我们可以对等使用下面的方法得到:

18:35:25 SQL> select a,b,sum(c) sc from t group by a,b order by 1;
A      B            SC
------ -------- ------
a      test1         3
a      test2         3
18:35:30 SQL> select a,sum(c) sc from t group by a order by 1;
A            SC
------ --------
a             6
18:35:40 SQL> select sum(c) sc from t order by 1; --相当于GROUP BY(0)
    SC
------
     6

部分ROLLUP:你也可以使用Rollup包含有限的几个小计,其语法是:Group by A,Rollup(B,C)。这种情况下,group by 条款先计算rollup中的部分,然后加上非rollup的部分,故层次为(A,B,C)(A,B)(A)。这里去除A不看就是GROUP BY(B,C),GROUP BY(B),GROUP BY(0),刚好就是ROLLUP(B,C)部分,然后再加上A,就得到GROUP BY A,ROLLUP(B,C)。

20:53:26 SQL> select * from t;
A      B             C
------ -------- ------
a      test1         1
a      test1         2
a      test2         2
a      test2         1
20:54:21 SQL> select a,b,c,sum(c) sc from t group by a,rollup(b,c) order by 1;
A      B             C     SC
------ -------- ------ ------
a      test1         1      1
a      test1         2      2
a      test1                3
a      test2         1      1
a      test2         2      2
a      test2                3
a                           6
已选择7行。

从上面的讨论可以得到执行优先级是ROLLUP>GROUP BY,即先执行出ROLLUP中的内容,然后把GROUP BY中剩余的部分“拼接”上去。
下面来探讨一下CUBE的使用。
先看看CUBE的官方定义:

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the GROUP BY specification. It returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values. 这里simple_grouping_clause表示GROUP BY子句。

这个定义可以这样理解:如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作(即GROUP BY(0))。即:CUBE操作字,除完成ROLLUP的功能外,再对ROLLUP后的结果集从右到左再聚合,逐个减少字段。CUBE常用于需要产生交叉 报表的地方。
请看一个例子

18:42:01 SQL>  select a,b,c,sum(c) sc from t group by a,b,c order by 1;
A      B             C     SC
------ -------- ------ ------
a      test1         1      1
a      test1         2      2
a      test2         1      1
a      test2         2      2
18:42:11 SQL>  select a,b,c,sum(c) sc from t group by cube(a,b,c) order by 1;
A      B             C     SC
------ -------- ------ ------
a      test1         1      1
a      test1         2      2
a      test1                3  -- A
a      test2         1      1
a      test2         2      2
a      test2                3  -- A
a                    1      2  -- B
a                    2      4  -- B
a                           6  -- C
       test1         1      1  -- D
       test1         2      2  -- D
       test1                3  -- E
       test2         1      1  -- D
       test2         2      2  -- D
       test2                3  -- E
                     1      2  -- F
                     2      4  -- F
                            6  -- G
18 rows selected.

很显然,上面A部分是通过对GROUP BY(A,B)而得到,B部分是通过对GROUP BY(A,C)而得到,C部分是通过对GROUP BY(A)而得到,D部分是通过对GROUP BY(B,C)而得到,E部分是通过对GROUP BY(B)而得到,F部分是通过对GROUP BY(C)而得到,G部分是通过对GROUP BY(0)而得到。如果要分别得到这些值,我们可以对等使用下面的方法得到:

18:58:24 SQL> select a,b,sum(c) sc from t group by a,b order by 1,2;
A      B            SC
------ -------- ------
a      test1         3
a      test2         3
19:01:10 SQL>  select a,c,sum(c) sc from t group by a,c order by 1,2;
A             C     SC
------ -------- ------
a             1      2
a             2      4
19:01:28 SQL>  select a,sum(c) sc from t group by a order by 1;
A          SC
------ ------
a           6
19:01:41 SQL>  select b,c,sum(c) sc from t group by b,c order by 1,2;
B           C       SC
------ ------ --------
test1       1        1
test1       2        2
test2       1        1
test2       2        2
19:01:55 SQL>  select b,sum(c) sc from t group by b order by 1;
B                  SC
---------- ----------
test1               3
test2               3
19:02:04 SQL>  select c,sum(c) sc from t group by c order by 1;
     C     SC
------ ------
     1      2
     2      4
19:02:13 SQL>  select sum(c) sc from t;
    SC
------
     6

部分CUBE:CUBE也可以和ROLLUP一样,只包含GROUP BY子句中的部分列,其语法形式为:GROUP BY A,CUBE(B,C)。这种情况下,group by 条款先计算cube中的部分,然后加上非cube的部分,故层次为(A,B,C)(A,B)(A,C)(A)。这里去除A不看就是GROUP BY(B,C),GROUP BY(B), GROUP BY(C),GROUP BY(0),刚好就是COBE(B,C)部分,然后再加上A,就得到GROUP BY A,CUBE(B,C)。

20:53:26 SQL> select * from t;
     A B           C
------ ------ ------
     a test1       1
     a test1       2
     a test2       2
     a test2       1
20:54:30 SQL> select a,b,c,sum(c) sc from t group by a,cube(b,c) order by 1;
     A B           C      SC
------ ------ ------ -------
     a test1       1       1
     a test1       2       2
     a test1               3
     a test2       1       1
     a test2       2       2
     a test2               3
     a             1       2
     a             2       4
     a                     6
已选择9行。

从上面的讨论可以得到执行优先级是CUBE>GROUP BY,即先执行出CUBE中的内容,然后把GROUP BY中剩余的部分“拼接”上去。
由于CUBE是对ROLLUP从右到左进行再聚合,因此显然执行优先级是CUBE>ROLLUP,故得到执行优先级CUBE>ROLLUP>GROUP BY。
当它们符合起来使用的时候会产生很多情况,下面仅给个例子,不做详细说明:

21:20:05 SQL> select * from t;
A B       C   D    E
- ------ -- --- ----
a test1   1  11  111
a test1   2  11  111
a test2   2  22  222
a test2   1  22  222
21:20:08 SQL> select a,b,c,d,e,sum(c) sc from t group by a,rollup(b,c),cube(d,e);
A B       C    D    E  SC
- ------ -- ---- ---- ---
a test1   1   11  111   1
a test1   2   11  111   2
a test2   1   22  222   1
a test2   2   22  222   2
a test1       11  111   3
a             11  111   3
a test2       22  222   3
a             22  222   3
a test1   1   11        1
a test1   2   11        2
a test1       11        3
a             11        3
a test2   1   22        1
a test2   2   22        2
a test2       22        3
a             22        3
a test1           111   3
a                 111   3
a test2           222   3
a                 222   3
a test1   1             1
a test1   2             2
a test1                 3
a test2   1             1
a test2   2             2
a test2                 3
a                       6
a test1   1       111   1
a test1   2       111   2
a test2   1       222   1
a test2   2       222   2
已选择31行。

例子中GROUP BY A,ROLLUP(B,C),CUBE(D,E),可第一步分解CUBE为:

a,rollup(b,c),d,e
a,rollup(b,c),d
a,rollup(b,c),e
a,rollup(b,c)

第二步分解ROLLUP而得到最终所有情况为:

a,b,c,d,e
a,b,d,e
a,d,e
a,b,c,d
a,b,d
a,d
a,b,c,e
a,b,e
a,e
a,b,c
a,b
a

2、GROUPING SETS扩展
首先,我们还是来看下官方对GROUPING SETS的阐述:

GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. Oracle Database computes all groupings specified in the GROUPING SETS clause and combines the results of individual groupings with a UNION ALL operation.

也就是通过它,你就可以控制GROUP BY后面的内容。举个例子来说,如果GROUP BY A,GROUPING SETS(B,C),那么实际上就想当于GROUP BY A,B UNION ALL GROUP BY A,C。
请看下面的这个例子:

22:04:03 SQL> select * from t;
A B       C   D    E
- ------ -- --- ----
a test1   1  11  111
a test1   2  11  222
a test2   2  22  111
a test2   1  22  222
22:04:06 SQL> select a,b,c,d,e,sum(c) sc from t group by a,grouping sets((b,c),(d,e));
A B           C   D    E  SC
- ---------- -- --- ---- ---
a test1       2            2  -- F
a test1       1            1  -- F
a test2       2            2  -- F
a test2       1            1  -- F
a                22  111   2  -- G
a                11  222   2  -- G
a                22  222   1  -- G
a                11  111   1  -- G
已选择8行。
22:04:12 SQL> select a,b,c,d,e,sum(c) sc from t group by a,grouping sets(b,c);
select a,b,c,d,e,sum(c) sc from t group by a,grouping sets(b,c)
             *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

22:07:41 SQL> select a,b,c,d,e,sum(c) sc from t group by a,(b,c),(d,e);
A B       C          D          E         SC
- ------ -- ---------- ---------- ----------
a test2   2         22        111          2
a test1   2         11        222          2
a test1   1         11        111          1
a test2   1         22        222          1

由例子,可以很显然的看出,F部分是GROUP BY A,B,C的结果,G部分是GROUP BY A,D,E的结果,整个GROUP BY A GROUPING SETS((B,C),(D,E))就是GROUP BY A,B,C和GROUP BY A,D,E的UNION ALL的结果,注意UNION ALL会产生重复值,尽管本例没有重复值。
还是从例子,我们可以看出,如果整个GROUP BY后面的表达式没有全部包括SELECT列表中的表达式(除了常量和函数)是出错的,这和前面我们讨论的结果一致。
还是从例子(最后一个选择语句),可以得到GROUP BY子句后面的表达式组合的多样性,即你可以通过GROUPING SETS来随意组合GROUP BY中的表达式。看看官方说法:

Within the GROUP BY clause, you can combine expressions in various ways:
i、 To specify composite columns, group columns within parentheses so that the database treats them as a unit while computing ROLLUP or CUBE operations.
ii、 To specify concatenated grouping sets, separate multiple grouping sets, ROLLUP, and CUBE operations with commas so that the database combines them into a single GROUP BY clause. The result is a cross-product of groupings from each grouping set.

3、三个grouping函数扩展
三个grouping函数是为了更清楚的显示哪些行是再聚合的结果,因此它们都只支持GROUP BY ROLLUP/CUBE/GROUPING SETS()子句,它们有着“美化”这三个子句的效果。
GROUP_ID()

GROUP_ID distinguishes duplicate groups resulting from a GROUP BY specification. It returns an Oracle NUMBER to uniquely identify duplicate groups. If n duplicates exist for a particular grouping, then GROUP_ID returns numbers in the range 0 to n-1.

一句话,就是这个函数用来标记经过GROUP BY ROLLUP/CUBE筛选之后的重复记录的行数。
先看个简单的例子:

23:13:31 SQL> select job,comm,count(*) num,group_id() from emp group by job,rollup(comm,job) order by 1,2,num;
JOB        COMM  NUM GROUP_ID()
--------- ----- ---- ----------
ANALYST            2          0
ANALYST            2          0
ANALYST            2          1
CLERK              4          1
CLERK              4          0
CLERK              4          0
MANAGER            3          0
MANAGER            3          1
MANAGER            3          0
PRESIDENT          1          1
PRESIDENT          1          0
PRESIDENT          1          0
SALESMAN      0    2          1
SALESMAN      0    2          0
SALESMAN    300    2          1
SALESMAN    300    2          0
SALESMAN           4          0
已选择17行。

上面这个例子证明了GROUP_ID的含义,再请看下面这个例子

23:06:06 SQL> select * from t;
A B      C  D    E
- ------ - -- ----
a test1  1 11  111
a test1  2 11  111
23:06:42 SQL> select a,b,count(*) num,group_id() from t group by rollup(a,b);
A B       NUM GROUP_ID()
- ------ ---- ----------
a test1     2          0
a           2          0
            2          0
23:07:18 SQL> select a,b,count(*) num,group_id() from t group by rollup(a,b,d);
A B     NUM GROUP_ID()
- ----- --- ----------
a test1   2          0  -- 第一行
a test1   2          0  -- 第二行
a         2          0
          2          0

这里最后一个查询的前两行不是一摸一样吗?为什么在第二行的GROUP_ID()还是0而不是1呢?请注意看这里 group by rollup(a,b,d),多了个d列,由此可以知道:假设第一行是通过GROUP BY A,B,D而来,那么第二行就是通过GROUP BY A,B而来,这是两个不同的GROUP BY筛选条件了,即使得到同样的结果,GROUP_ID()也不认为它们是重复的。
因此,GROUP_ID()函数是标记相同的GROUP BY条件筛选出来的结果的重复值。

GROUPING(exp)

GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce superaggregate rows where the set of all values is represented by null.

也就是说,GROUPING函数是标记出除了exp而外的GROUP BY中表达式的被再聚合的行,即exp IS NULL并且是其他列被再聚合的行,而不管这种再聚合是ROLLUP还是CUBE。exp为NULL的时候,就标记为1,否则为0。
下面看个例子来说明这个问题:

23:33:11 SQL> select index_type,status,count(*) num,grouping(status) from test group by rollup(index_type,status);
INDEX_TYPE                  STATUS     NUM GROUPING(STATUS)
---------------------- -------- ----- ----------------
LOB                    N/A          1                0
LOB                    VALID      566                0
LOB                               567                1 * -- 第一行,因为status为null,故GROUPING(STATUS)标记为1
BITMAP                 N/A          7                0
BITMAP                 VALID        8                0
BITMAP                             15                1 *
DOMAIN                 VALID        1                0
DOMAIN                              1                1 *
NORMAL                 N/A         56                0
NORMAL                 VALID     1565                0
NORMAL                           1621                1 *
CLUSTER                VALID       10                0
CLUSTER                            10                1 *
IOT - TOP              VALID      114                0
IOT - TOP                         114                1 *
FUNCTION-BASED DOMAIN  VALID        1                0
FUNCTION-BASED DOMAIN               1                1 *
FUNCTION-BASED NORMAL  VALID       17                0
FUNCTION-BASED NORMAL              17                1 *
                                 2346                1 *
已选择20行。

注意看上面的加“*”部分,被标记为1的都是对除了status列外GROUP BY表达式中剩余部分,即IDNEX_TYPE列,进行再聚合的行。我以第一行做为讲例,它是通过GROUP BY INDEX_TYPE,即GROUP BY ROLLUP(INDEX_TYPE,STATUS)中GROUP BY (INDEX)。所有被标记为1的都是GROUP BY ROLLUP(INDEX_TYPE,STATUS)去除STATUS后GROUP BY ROLLUP(INDEX_TYPE)情况。
这里其实就是标记出INDEX_TYPE的小计。
我们来看相反的情况,把INDEX_TYPE列设置为要去除的列,看看结果:

23:36:33 SQL> select index_type,status,count(*) num,grouping(index_type) from test group by rollup(index_type,status);
INDEX_TYPE             STATUS       NUM GROUPING(INDEX_TYPE)
---------------------- ------- ---- --------------------
LOB                    N/A        1                    0
LOB                    VALID    566                    0
LOB                             567                    0
BITMAP                 N/A        7                    0
BITMAP                 VALID      8                    0
BITMAP                           15                    0
DOMAIN                 VALID      1                    0
DOMAIN                            1                    0
NORMAL                 N/A       56                    0
NORMAL                 VALID   1565                    0
NORMAL                         1621                    0
CLUSTER                VALID     10                    0
CLUSTER                          10                    0
IOT - TOP              VALID    114                    0
IOT - TOP                       114                    0
FUNCTION-BASED DOMAIN  VALID      1                    0
FUNCTION-BASED DOMAIN             1                    0
FUNCTION-BASED NORMAL  VALID     17                    0
FUNCTION-BASED NORMAL            17                    0
                               2346                    1 *
已选择20行。

完全正确,因为GROUP BY ROLLUP(INDEX_TYPE,STAUS)去除INDEX_TYPE而得到的GROUP BY ROLLUP(STATUS),它只要GROUP BY(0)这种情况。
下面我们看看CUBE,去除STATUS,即STATUS IS NULL 对INDEX_TYPE的再聚合情况

23:33:25 SQL> select index_type,status,count(*) num,grouping(status) from test group by cube(index_type,status);
INDEX_TYPE             STATUS    NUM GROUPING(STATUS)
---------------------- ------- ----- ----------------
                                2346                1
                       N/A        64                0
                       VALID    2282                0
LOB                              567                1
LOB                    N/A         1                0
LOB                    VALID     566                0
BITMAP                            15                1
BITMAP                 N/A         7                0
BITMAP                 VALID       8                0
DOMAIN                             1                1
DOMAIN                 VALID       1                0
NORMAL                          1621                1
NORMAL                 N/A        56                0
NORMAL                 VALID    1565                0
CLUSTER                           10                1
CLUSTER                VALID      10                0
IOT - TOP                        114                1
IOT - TOP              VALID     114                0
FUNCTION-BASED DOMAIN              1                1
FUNCTION-BASED DOMAIN  VALID       1                0
FUNCTION-BASED NORMAL             17                1
FUNCTION-BASED NORMAL  VALID      17                0
已选择22行。

下面是去除INDEX_TYPE,即INDEX_TYPE IS NULL对STATUS的再聚合情况

23:39:02 SQL> select index_type,status,count(*) num,grouping(index_type) from test group by cube(index_type,status);
INDEX_TYPE             STATUS     NUM GROUPING(INDEX_TYPE)
---------------------- ------- ------ --------------------
                                 2346                    1
                       N/A         64                    1
                       VALID     2282                    1
LOB                               567                    0
LOB                    N/A          1                    0
LOB                    VALID      566                    0
BITMAP                             15                    0
BITMAP                 N/A          7                    0
BITMAP                 VALID        8                    0
DOMAIN                              1                    0
DOMAIN                 VALID        1                    0
NORMAL                           1621                    0
NORMAL                 N/A         56                    0
NORMAL                 VALID     1565                    0
CLUSTER                            10                    0
CLUSTER                VALID       10                    0
IOT - TOP                         114                    0
IOT - TOP              VALID      114                    0
FUNCTION-BASED DOMAIN               1                    0
FUNCTION-BASED DOMAIN  VALID        1                    0
FUNCTION-BASED NORMAL              17                    0
FUNCTION-BASED NORMAL  VALID       17                    0
已选择22行。

下面是说明GROUPING函数的参数只能是一个表达式

23:39:19 SQL> select index_type,status,count(*) num,grouping(index_type,status) from test group by cube(index_type,status);
select index_type,status,count(*) num,grouping(index_type,status) from test group by cube(index_type,status)
                                      *
第 1 行出现错误:
ORA-00909: 参数个数无效

23:40:12 SQL> select index_type,status,count(*) num,grouping(status,index_type) from test group by cube(index_type,status);
select index_type,status,count(*) num,grouping(status,index_type) from test group by cube(index_type,status)
                                      *
第 1 行出现错误:
ORA-00909: 参数个数无效
通 过运用GROUPING(exp),就可以很简单的找出被再聚合的那些列的聚合结果,即GROUPING(exp)=1行,反应了GROUP BY子句中其它列的聚合结果。通俗的说法,即GROUPING(exp)=1所在的那一行,是GROUP BY子句中除了exp外其它部分的一个小计统计。具体请看上面的例子。

GROUPING_ID(exp[,…])

GROUPING_ID is functionally equivalent to taking the results of multiple GROUPING functions and concatenating them into a bit vector (a string of ones and zeros).
GROUPING_ID returns a number corresponding to the GROUPING bit vector associated
with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function.

它的含义是:
GROUPING_ID(exp1,exp2,…,expN)={GROUPING(exp1)||GROUPING(exp2)||…||GROUPING(expN)}变成十进制数,如:
如果GROUPING(A)=1,GROUPING(B)=0,GROUPING(C)=1,那么
GROUPING_ID(A,B,C) = [101]二进制 = 5,
GROUPING_ID(B,A,C) = [011]二进制 = 3.
因 此,你就可以很简单得通过GROUPING_ID函数来得到列的再聚合情况,如上面的GROUPING_ID(A,B,C) = 5,并且我们假设查询语句的GROUP BY子句是GROUP BY CUBE(A,B,C),那么它的含义就表示对A,C列进行了GROUPING结果,即B列进行的聚合小计(除了A,C以外的GROUP BY中的剩余列的聚合小计,这个是根据GROUPING函数的含义而来)。
我们来看个例子:

08:39:20 SQL> select * from t;
A B       C
- ------ --
a test1   1
a test1   2
a test2   1
a test2   2
08:39:26 SQL> select a,b,sum(c) sc from t group by rollup(a,b);
A B       SC
- ------ ---
a test1    3
a test2    3
a          6
           6
08:40:11 SQL> select a,b,sum(c) sc,grouping(a),grouping(b),grouping_id(a,b),grouping_id(b,a) from t group by rollup(a,b);
A B       SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B) GROUPING_ID(B,A)
- ------ --- ----------- ----------- ---------------- ----------------
a test1    3           0           0                0                0
a test2    3           0           0                0                0
a          6           0           1                1                2  #
           6           1           1                3                3

注意这里的加#部分,GROUPING_ID(B,A)=2,可推导出GROUPING(B)=1, GROUPING(A)=0,而查询语句中GROUP BY ROLLUP(A,B),这就说明这行是对A做了聚合小计(除B以外的GROUP BY子句中的列,A,做聚合小计)。
同样,也可以这样推导:GROUPING_ID(A,B)=1,可推导出GROUPING(A)=0, GROUPING(B)=1,而查询语句中GROUP BY ROLLUP(A,B),这就说明这行是对A做了聚合小计(除B以外的GROUP BY子句中的列,A,做聚合小计)。
看看CUBE的情况,根据GROUPING_ID的解释,也很明了。

08:40:48 SQL> select a,b,sum(c) sc,grouping(a),grouping(b),grouping_id(a,b),grouping_id(b,a) from t group by cube(a,b);
A B       SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B) GROUPING_ID(B,A)
- ------ --- ----------- ----------- ---------------- ----------------
           6           1           1                3                3
  test1    3           1           0                2                1
  test2    3           1           0                2                1
a          6           0           1                1                2
a test1    3           0           0                0                0
a test2    3           0           0                0                0
6 rows selected.

我们常将GROUPING_ID函数结合HAVING子句运用,把部署聚合小计的部分去除掉,如:

08:56:04 SQL> select a,b,sum(c) sc,grouping(a),grouping(b),grouping_id(a,b)
10:34:39   2  from t group by rollup(a,b) having grouping_id(a,b)>0;
A B   SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- -- --- ----------- ----------- ----------------
a      6           0           1                1
       6           1           1                3

这样就能很方便的查看聚合小计。
FAQ
我们可以通过灵活运用GROUPING SETS,ROLLUP,CUBE来实现显示不同层次的聚合。
下面来看这个例子,在这个句子中GROUPING SETS相当于ROLLUP:

10:34:40 SQL> select a,b,sum(c) sc,
10:36:30   2  grouping(a),grouping(b),grouping_id(a,b)
10:40:49   3  from t group by rollup(a,b);
A B       SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ --- ----------- ----------- ----------------
a test1    3           0           0                0
a test2    3           0           0                0
a          6           0           1                1
           6           1           1                3

10:40:50 SQL> select a,b,sum(c) sc,
10:40:53   2  grouping(a),grouping(b),grouping_id(a,b)
10:40:57   3  from t group by grouping sets((a,b),(a),null);
A B       SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ --- ----------- ----------- ----------------
a test1    3           0           0                0
a test2    3           0           0                0
a          6           0           1                1
           6           1           1                3

下面这个例子是GROUPING SETS相当于CUBE:

10:42:58 SQL> select a,b,sum(c) sc,
10:43:26     2         grouping(a),grouping(b),grouping_id(a,b)
10:43:26     3  from t

group by cube(a,b)
10:43:26     4  order by 1,2,3;
A B      SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ -- ----------- ----------- ----------------
a test1   3           0           0                0
a test2   3           0           0                0
a         6           0           1                1
  test1   3           1           0                2
  test2   3           1           0                2
          6           1           1                3
6 rows selected.

10:43:27 SQL>   select a,b,sum(c) sc,
10:43:34     2          grouping(a),grouping(b),grouping_id(a,b)
10:43:34     3    from t

group by grouping sets((a,b),(a),(b),())
10:43:34     4    order by 1,2,3;
A B      SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ -- ----------- ----------- ----------------
a test1   3           0           0                0
a test2   3           0           0                0
a         6           0           1                1
  test1   3           1           0                2
  test2   3           1           0                2
          6           1           1                3
6 rows selected.

这个里面需要说明的就是GROUPING SETS的最后一个表达式,即null和(),它是两种不同的表达方式,其实就是相当于做GROUP BY(0),请看操作例子:

10:46:32 SQL> select null,null,sum(c) sc,null,null,null
10:46:49   2  from t
10:46:49   3  order by 1,2,3;
N N  SC N N N
- - --- - - -
      6

参考文献
1、 official documents
2、 http://xsb.itpub.net/post/419/29159
3、 http://tomszrp.itpub.net/post/11835/64788

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/matrixzero/archive/2008/08/07/2782164.aspx

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics