- 浏览: 166287 次
- 性别:
- 来自: 广州
-
文章分类
最新评论
-
兰斯洛特1987：
顶!!!!谢谢分享.最近我也在研究这玩意...
Java语言的Hook实现 -
pu02203：
我把Confidant.jar, 丢进去eclipse, 里面 ...
重磅推出诛仙辅助软件第二波：Java版按键精灵 -
泣血端午：
Calculagraph 这是哪个类啊？
A星算法 -
haitaohehe：
我使用的是jstl1.0 可是在使用<c:set va ...
JSTL1.0和JSTL1.1的区别 -
micheal19840929：
学习楼主，我也测试一下~看看兼容性吧。lanlanzhilia ...
手机版飞鸽传书：无线牵
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
发表评论
-
Oracle自用指令
2010-04-21 15:49 674【修改列】 alter table table_name ... -
SQLPLUS命令大全
2010-04-02 10:43 669转自：http://imain.iteye.com/blog/ ... -
Truncate和Delete区别及Truncate使用说明
2010-03-30 11:23 839Truncate和Delete有以下几点区别1、Truncat ... -
Oracle show parameter 命令的小技巧
2010-03-23 18:49 1622今天无意中发现了一个O ... -
恒等查询条件改变执行计划——SQL优化之Everything is Possible
2010-03-23 14:36 1173有的时候开发人员为了方便会在WHERE语句后面添加一个1=1， ... -
oracle 无order by , 默认排序问题
2010-03-17 19:49 1944Oracle会根据具体的数据块的存储返回记录.oracle数据 ... -
Oracle常用命令
2009-12-08 14:27 831第一章:日志管理 1.forcing log s ... -
Oracle数据类型
2009-12-08 11:00 807在ORACLE8中定义了：标量(Scalar)、复合(CO ...
相关推荐
在数据库查询语言SQL中，Group By子句是一种常用的聚合查询工具，它可以将数据集分成不同的组，对每组数据执行聚合函数操作，比如计算每个组的平均值、总和、最大值、最小值等。当与Select语句结合使用时，Group By...
SQL语言中GROUP BY子句的应用剖析 GROUP BY子句是SQL语言中一个强大的工具，用于对数据进行分组并进行汇总统计。通过对GROUP BY子句的执行过程分析，阐述了使用GROUP BY子句对数据进行分组的意义，并进一步剖析了...
GROUP BY 子句有多种形式，本文将通过实例来说明 GROUP BY 子句的三种样式：GROUP BY、GROUP BY ROLLUP、GROUP BY CUBE、GROUP BY GROUPING SETS。 1. GROUP BY 语句 GROUP BY 语句是最基本的分组语句，它可以根据...
Transact-SQL语言是基于ANSI SQL-92标准扩展而来的，但是，据分析，在某些特定情况下，Microsoft SQL Server 2000中的查询优化器生成的执行计划可能会出现错误，特别是当涉及到group by子句的操作时。 分析小组异常...
在Oracle数据库中，GROUP BY子句是SQL查询中用于对数据进行分组的关键部分，它允许我们基于一个或多个列对结果集进行聚合操作。在"ORACLE学习笔记系列（15）使用扩展的 GROUP BY 子句"这篇博文中，作者深入探讨了...
数据库管理与应用 5.3.4使用GROUP BY子句对数据进行分组汇总.pdf 学习资料 复习资料 教学资源
Linux运维-运维课程d2-MySQL基本SQL语句（下）-15-GROUP BY子句之统计函数.mp4
00574 Oracle公司内部数据库培训资料-Les17_对 GROUP BY 子句的扩展(PPT 27页).ppt
Linux运维-运维课程d2-MySQL基本SQL语句（下）-16-GROUP BY子句之分组原理与实
以上知识点虽然受到文件内容提供的限制，但还是根据标题、描述、标签和部分内容进行了综合和深入分析，以提供关于SQL Server中GROUP BY与COMPUTE BY子句的比较探讨，并扩展到相关的数据处理和数据库应用知识。
* 在使用 group by 子句时，select 子句中的任意一列要么包含在聚合函数中，要么必须在 group by 子句中出现过。 例如，从 teacher 表中查询各个系男、女教师的人数，可以使用以下语句： select dname, tsex, ...
在数据库查询语言 SQL（Structured Query Language）中，`GROUP BY` 和 `ORDER BY` 是两个非常重要的子句，它们可以帮助我们更高效地管理和展示数据。 #### GROUP BY 子句 `GROUP BY` 主要用于将数据表中的数据...
1. select 语句中所有列必须出现在 GROUP BY 子句中，或者使用聚合函数。 2. GROUP BY 子句中不能使用聚合函数。 3. 如果select 语句中有多个列，需要使用聚合函数来计算每组的值。 常用的聚合函数有： 1. AVG：...
要了解GROUP BY子句考虑的EMPLOYEE_TBL的的表具有以下记录： mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+--...
标题 "ORA-00979 不是group by 表达式" 提及的是一个在使用SQL查询时常见的错误，通常出现在尝试执行聚合操作（如COUNT、SUM、AVG等）而没有正确指定GROUP BY子句的情况下。在SQL中，当你在SELECT语句中包含非聚合...
在7.10 GROUP BY Restrictions这一部分，作者深入探讨了在使用GROUP BY子句时的一些限制和注意事项，这对于理解和编写高效、正确的SQL查询至关重要。 1. GROUP BY基础：GROUP BY子句用于将数据集按照一个或多个列...
数据库查询语句执行顺序与编写顺序...WHERE子句用于过滤数据，GROUP BY子句用于分组数据，HAVING子句用于筛选分组后的数据，ORDER BY子句用于排序数据。索引可以提高查询速度，但需要根据实际情况选择是否使用索引。
MySQL作为广泛使用的数据库管理系统，提供了强大的数据分组功能，其中GROUP BY子句是实现这一功能的关键。本文将深入探讨如何使用GROUP BY进行数据分组，包括其基本概念、语法结构、应用场景以及最佳实践。 GROUP BY...