Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » OLAP 支持 » GROUP BY 子句扩展

 

GROUP BY GROUPING SETS

GROUPING SETS 子句是 SELECT 语句的 GROUP BY 子句的扩展。通过 GROUPING SETS 子句,您可采用多种方式对结果分组,而不必使用多个 SELECT 语句来实现这一目的。这就意味着,能够减少响应时间并提高性能。

例如,以下两条查询语句在语义上是等效的。不过,第二个查询通过使用 GROUP BY GROUPING SETS 子句能够更有效地定义分组条件。

使用多个 SELECT 语句的多个分组:

SELECT NULL, NULL, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
   UNION ALL
SELECT City, State, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY City, State
   UNION ALL
SELECT NULL, NULL, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY CompanyName;

使用 GROUPING SETS 的多个分组:

SELECT City, State, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) );

两种方法均产生相同的结果,如下所示:

City State CompanyName Cnt
1 (NULL) (NULL) (NULL) 8
2 (NULL) (NULL) Cooper Inc. 1
3 (NULL) (NULL) Westend Dealers 1
4 (NULL) (NULL) Toto's Active Wear 1
5 (NULL) (NULL) North Land Trading 1
6 (NULL) (NULL) The Ultimate 1
7 (NULL) (NULL) Molly's 1
8 (NULL) (NULL) Overland Army Navy 1
9 (NULL) (NULL) Out of Town Sports 1
10 'Pembroke' 'MB' (NULL) 4
11 'Petersburg' 'KS' (NULL) 1
12 'Drayton' 'KS' (NULL) 3

第 2-9 行是按照 CompanyName 分组生成的行,第 10-12 行是按照 City 和 State 的组合进行分组所生成的行,第 1 行是空分组集所表示的总计,它是使用一对成对的圆括号 () 指定的。空分组集表示 GROUP BY 输入中所有行的单个分区。

请注意 NULL 值如何在分组集中不使用的表达式中充当占位符,因为这些结果集必须可以组合。例如,第 2-9 行由查询 (CompanyName) 中的第二个分组集得到。因为分组集未将 City 或 State 作为表达式包含在内,所以对于第 2-9 行,City 和 State 的值中会含有占位符 NULL,而 CompanyName 中的值将含有在 CompanyName 中找到的明确值。

因为 NULL 用作占位符,所以很容易将占位符 NULL 与数据中找到的真正的 NULL 相混淆。为有助于将占位符 NULL 与 NULL 数据区分开来,请使用 GROUPING 函数。请参见使用 GROUPING 函数检测占位符 NULL

示例

下面的示例说明了如何使用 GROUPING SETS 定制从查询返回的结果,以及如何使用 ORDER BY 子句更好地组织这些结果。以下查询将按各年份 (Year) 中的季度 (Quarter) 返回订单总数以及各年份 (Year) 的总数。先按年份 (Year) 排序,再按季度 (Quarter) 排序可使结果更易于理解:

SELECT Year( OrderDate ) AS Year, 
        Quarter( OrderDate ) AS Quarter, 
        COUNT (*) AS Orders 
FROM SalesOrders
GROUP BY GROUPING SETS ( ( Year, Quarter ), ( Year ) )
ORDER BY Year, Quarter;

此查询会返回以下结果:

Year Quarter Orders
1 2000 (NULL) 380
2 2000 1 87
3 2000 2 77
4 2000 3 91
5 2000 4 125
6 2001 (NULL) 268
7 2001 1 139
8 2001 2 119
9 2001 3 10

第 1 行和第 6 行分别是 2000 年和 2001 年的订单数小计。第 2-5 行和第 7-9 行是小计行的详细信息行。也就是说,它们按年、按季度显示订单总数。

结果集中没有所有年份中所有季度的总计。要实现此目的,查询必须在 GROUPING SETS 说明中包括空分组说明 '()'。

指定空分组说明

如果在 GROUP BY 子句中使用空 GROUPING SETS 说明 '()',则会产生一个总计行,对结果中的所有项进行总计。使用总计行时,所有分组表达式的所有值均会包含占位符 NULL。可使用 GROUPING 函数将占位符 NULL 与计算行底层数据中的值后产生的实际 NULL 值区分开来。请参见使用 GROUPING 函数检测占位符 NULL

指定重复分组集

可在 GROUPING SETS 子句中指定重复分组说明。此时,SELECT 语句的结果将包含相同的行。

以下查询包括重复分组:

SELECT City, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY GROUPING SETS( ( City ), ( City ) );

此查询会返回以下结果。请注意,由于重复分组的原因,第 1-3 行与第 4-6 行相同:

City Cnt
1 'Drayton' 3
2 'Petersburg' 1
3 'Pembroke' 4
4 'Drayton' 3
5 'Petersburg' 1
6 'Pembroke' 4
实践良好的格式

GROUP BY GROUPING SETS 子句的分组语法的解释方式不同于简单的 GROUP BY 子句。例如,GROUP BY (X, Y) 返回按 X 和 Y 值的唯一组合进行分组的结果。而 GROUP BY GROUPING SETS (X, Y) 将指定两个单独的分组集,且这两个分组的结果会结合在一起。也就是说,结果将按 (X) 分组,然后合并到按 (Y) 分组的相同结果中。

为使格式良好,并在表达式复杂的情况下避免造成任何歧义,请在有可能出错的各种情况下,为说明中的每个单独分组集括上括号。例如,尽管以下两条语句都是正确的,且语义上等效,但建议采用第二种格式:

SELECT * FROM t GROUP BY GROUPING SETS ( X, Y );
SELECT * FROM t GROUP BY GROUPING SETS( ( X ), ( Y ) );