在
SELECT
查询语句中，可以使用
GROUP BY
子句来实现按照某一列或者几列进行分组，并可以再结合聚合函数对每个分组进行统计计算。同样，也可以使用同一个关键字，在同一语句中实现同时对不同分组进行统计，其结果即相当于将多个
GROUP BY
语句的结果进行
UNION ALL
后的结果。在下文中，将介绍使用
GROUPING SETS, ROLLUP
和
CUBE
实现完成这个目的。
SELECT <attributes> [, grouping_id(<attributes>) ] [, text_filter(dimensional_attribute) ]
FROM …
GROUP BY GROUPING SETS [BEST n] [LIMIT n] [OFFSET n]
[WITH SUBTOTAL] [WITH BALANCE] [WITH TOTAL]
[TEXT_FILTER <filterspec> [FILL UP [SORT MATCHES TO TOP]]]
[STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ‘value’]]
( <group>, …, <group> )
HAVING <having-filter>;
<group> := <attribute> |( <attribute>, …, <attribute> ) |(( <attribute>, …, <attribute> ) ORDER BY <attribute> [ASC] [DESC])
GROUPING SETS
可以使用
GROUPING SETS
来指定使用那些列的组合进行分组。
例如：
GROUPING SETS(A, B, (C,D))
即指分别按照列
A
分组，按照列
B
分组，按照列
C
、
D
分组，所得结果进行合并。
ROLLUP
和
CUBE
是
GROUPING SETS
的特殊形式。
ROLLUP
ROLLUP
举例：
ROLLUP(A,B,C)
相当于
GROUPING SETS((A,B,C),(A,B),A,())
，即指分别按照列
A,B,C
分组，按照列
A,B
分组，按照列
A
分组，和不分组四种情况，所得结果进行合并。
即，
ROLLUP
中
grouping set
的个数为：
n+
1。
CUBE
举例：
CUBE(A,B,C)
相当于
GROPING SETS((A,B,C),(A,B),(A,C),(B,C),A,B,C,())
，即指分别按照列
A,B,C
分组，按照列
A,B
分组，按照列
A,C
分组，按照列
B,C
分组，按照列
A
分组，按照列
B
分组，按照列
C
分组
,
和不分组八种情况，所得结果进行合并。
即，
CUBE
中
grouping set
的个数为：
1+C(n,1)+C(n,2)+…+C(n,n-1)+1=2
n
设计一个
SALES
表，进行使用举例。
create columntable sales ( id int primarykey, customer varchar(5), year int, product varchar(5), sales int );
insert into sales values(1, 'C1', 2009, 'P1', 100);
insert into sales values(2, 'C1', 2009, 'P2', 200);
insert into sales values(3, 'C1', 2010, 'P1', 50);
insert into sales values(4, 'C1', 2010, 'P2', 150);
insert into sales values(5, 'C2', 2009, 'P1', 200);
insert into sales values(6, 'C2', 2009, 'P2', 300);
insert into sales values(7, 'C2', 2010, 'P1', 100);
insert into sales values(8, 'C2', 2010, 'P2', 150);
select * from sales;
select customer, NULL, NULL, sum(sales) from sales groupby customer
union all
select NULL, year, NULL, sum(sales) from sales group by year
union all
select NULL, NULL, product, sum(sales) from sales groupby product;
select customer, year, sum(sales) from sales groupbygrouping sets ( (customer, year), (customer) )
union all
select NULL, NULL, sum(sales) from sales;
grouping_id()/grouping()
grouping_id()
为每个
grouping set
返回一个整数，用来区分不同的
grouping set
。
grouping()
函数也返回一个整数，参数只能为某一列的列名，表示是否使用了这个列用来分组，只能返回
0
或
1。
select customer, year, sum(sales),grouping_id(customer, year),grouping(customer),grouping(year) from sales
groupbyLU ROLP(customer, year);
select customer, year, sum(sales),grouping_id(customer, year),grouping(customer),grouping(year) from sales
groupby CUBE(customer, year);
注：以
CUBE
的例子来说明，
grouping_id()
中有
CUSTMOER
和
YEAR
两列。则相当于存在一个两位的二进制数，最低位代表
YEAR
，另一位代表
CUSTOMER
，如果某个
grouping set
按照本列分组则代表它的位置
1
，否则置
0
。最后转换为十进制返回。
BE
ST
BEST n
：
n
可以为正数、负数或零，指取每个
grouping set
中分组的数降序排列后的前
n
个
grouping set
，即负数为除去不分组的情况按升序排列的前
n
个
grouping set
，
0
则相当于未使用
BEST
关键字。
select customer, year, product, sum(sales)
from sales groupby cube BEST 3 ( customer, year, product );
LIMIT n [OFFSET m]
：限制每个
grouping set
中返回的分组数和偏移数。
select customer, year, product, sum(sales)
from sales
groupbygrouping sets LIMIT 2 OFFSET 1 ( (customer, year), (product) );
WITH SUBTOTAL/WITH BALANCE/WITH TOTAL
可以和
LIMIT OFFSET
配合使用，表示在每个
grouping set
中新增一个统计列。
WITH SUBTOTAL
表示在
LIMIT OFFSET
返回的部分使用统计函数，
WITH BALANCE
表示表示在
LIMIT OFFSET
未返回的部分使用统计函数，
WITH TOTAL
则表示使用统计函数的范围为整个
grouping set
，与是否使用
LIMIT OFFSET
无关。
select customer, year, product, sum(sales)
from sales groupbygrouping sets LIMIT 2 WITH SUBTOTAL WITH BALANCE WITH TOTAL( (customer, year), (product) );
TEXT_FILTER
TEXT_FILTER <filterspec>
，表示可以根据每列中存取的内容进行过滤。可以使用
*
和
?
作为通配符，可以使用
OR ,AND, NOT, AND NOT
这些逻辑操作符，空格相当于
OR
，必须大写，
–
相当于
NOT
。
select customer, year, product, sum(sales), text_filter(customer), text_filter(product)
from sales groupbygrouping sets TEXT_FILTER '*2' ( (customer, year), (product) );
FILL UP
与
TEXT_FILTER
结合使用，返回全部的分组，可以通过使用
text_filter()
函数来标识出满足过滤条件的内容。
select customer, year, product, sum(sales), text_filter(customer), text_filter(product)
from sales groupbygrouping sets TEXT_FILTER '*2' FILL UP ( (customer, year), (product) );
与
FILL UP
结合使用，将
满足条件的排在每个
grouping set
的最前面。
select customer, year, product, sum(sales), text_filter(customer), text_filter(product)
from sales groupbygrouping sets TEXT_FILTER '*2' FILL UP SORT MATCHES TO TOP ( (customer, year), (product));
STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ‘value’]
STRUCTURED RESULT
表示
将结果作为临时表返回，
WITH OVERVIEW
则会新加一个
overview
的结果表。使用
PREFIX
指定临时表的前缀，必须以
#
开头，即
local temporary
表，
session
结束即消失。默认为
#GN
：
0
是
overview
表
，
n
是每个
grouping sets
的结果，按照
grouping sets
中顺序排序，否则按照
best
的顺序
.
select customer, product, sum(sales)
from sales group by grouping sets structured result WITH OVERVIEW ( product, customer);
select * from "#GN0";
select * from "#GN1";
select * from "#GN2";
