#
第四十九章 SQL命令 GROUP BY
`SELECT`子句,它根据一个或多个列对查询的结果行进行分组。
# 大纲
```sql
SELECT ...
GROUP BY field {,field2}
```
## 参数
- `field` - 从其中检索数据的一个或多个字段。
单个字段名或以逗号分隔的字段名列表。
# 描述
`GROUP BY`是`SELECT`命令的一个子句。
可选的`GROUP BY`子句出现在`FROM`子句和可选的`WHERE`子句之后,可选的`HAVING`和`ORDER BY`子句之前。
`GROUP BY`子句接受查询的结果行,并根据一个或多个数据库列将它们分成单独的组。
当将`SELECT`与`GROUP BY`结合使用时,将为`GROUP BY`字段的每个不同值检索一行。
`GROUP BY`将`NULL`(没有指定值)字段作为一个独立的值组。
`GROUP BY`子句在概念上类似于 IRIS聚合函数扩展关键字`%FOREACH`,但是`GROUP BY`操作整个查询,而`%FOREACH`允许在子填充上选择聚合,而不限制整个查询填充。
`GROUP BY`可以在`INSERT`命令的`SELECT`子句中使用。
不能在`UPDATE`或`DELETE`命令中使用`GROUP BY`。
## 指定字段
`GROUP BY`子句最简单的形式指定单个字段,如`GROUP BY City`。
这将为每个惟一的`City`值选择任意一行。
还可以指定以逗号分隔的字段列表,将其组合值视为单个分组术语。
它为每个`City`和`Age`值的唯一组合选择任意一行。
因此,`GROUP BY City,Age`返回与`GROUP BY Age,City`相同的结果。
字段必须通过列名指定。
有效的字段值包括以下内容:列名(`GROUP BY City`);
`%ID`(返回所有行);
指定列名的标量函数(`GROUP BY ROUND(Age,-1)`);
指定列名的排序规则函数(`GROUP BY %EXACT(City)`)。
不能通过列别名指定字段;
尝试这样做会产生`SQLCODE -29`错误。
不能通过列号指定字段;
这被解释为一个文字并返回一行。
不能指定聚合字段;
尝试这样做将生成`SQLCODE -19`错误。
不能指定子查询;
这被解释为一个文字并返回一行。
`GROUP BY StreamField`操作流字段的`OID`,而不是它的实际数据。
因为所有流字段`oid`都是唯一的值,`GROUP BY`对实际的流字段重复数据值没有影响。
`GROUP BY StreamField`将流字段为`NULL`的记录数量减少为一条记录。
`GROUP BY`子句可以使用箭头语法(`- >`)操作符在非基表的表中指定字段。
例如:`GROUP BY Company->Name`。
在`GROUP BY`子句中指定一个字面值作为字段值返回`1`行;
返回哪一行是不确定的。
因此,指定`7`、`'Chicago'`、`''`、`0`或`NULL`都返回`1`行。
但是,如果在逗号分隔的列表中指定一个字面值作为字段值,则该字面值将被忽略,并且`GROUP BY`将为指定字段名的每个惟一组合选择任意一行。
## 具有GROUP BY和DISTINCT BY的聚合函数
在计算聚合函数之前应用`GROUP BY`子句。
在下面的示例中,`COUNT`聚合函数计算每个`GROUP BY`组中的行数:
```sql
SELECT Home_State,COUNT(Home_State)
FROM Sample.Person
GROUP BY Home_State
```
在计算聚合函数之后应用`DISTINCT BY`子句。
在下面的例子中,`COUNT`聚合函数计算整个表中的行数:
```sql
SELECT DISTINCT BY(Home_State) Home_State,COUNT(Home_State)
FROM Sample.Person
```
为了计算整个表的聚合函数,而不是`GROUP BY`组,可以指定一个选择项子查询:
```sql
SELECT Home_State,(SELECT COUNT(Home_State) FROM Sample.Person)
FROM Sample.Person
GROUP BY Home_State
```
当选择列表由聚合字段组成时,不应将`GROUP BY`子句与`DISTINCT`子句一起使用。
例如,下面的查询旨在返回共享相同`Home_State`的不同数量的人:
```sql
/* 此查询不应用DISTINCT关键字 */
/* 这里提供了一个警示的例子 */
SELECT DISTINCT COUNT(*) AS mynum
FROM Sample.Person
GROUP BY Home_State
ORDER BY mynum
```
这个查询没有返回预期的结果,因为它没有应用`DISTINCT`关键字。
要同时应用`DISTINCT`聚合和`GROUP BY`子句,请使用子查询,如下例所示:
```sql
SELECT DISTINCT *
FROM (SELECT COUNT(*) AS mynum
FROM Sample.Person
GROUP BY Home_State) AS Sub
ORDER BY Sub.mynum
```
此示例成功返回共享相同`Home_State`的不同人数。
例如,如果任何`Home_State`被8个人共享,查询返回8。
如果查询仅由聚合函数组成且不返回表中的任何数据,则返回`%ROWCOUNT=1`,并为聚合函数返回一个空字符串(或`0`)值。
例如:
```sql
SELECT AVG(Age) FROM Sample.Person WHERE Name %STARTSWITH 'ZZZZ'
```
但是,如果这种类型的查询包含`GROUP BY`子句,它将返回`%ROWCOUNT=0`,并且聚合函数值仍未定义。
## 飘絮,字母大小写和优化
本节描述`GROUP BY`如何处理只有字母大小写不同的数据值。
- 组合字母变体在一起(返回大写字母):
默认情况下,`GROUP By`根据创建字段时为其指定的排序规则将字符串值分组。
IRIS有一个默认的字符串排序规则,可以为每个名称空间设置;
所有名称空间的初始字符串排序规则默认值是`SQLUPPER`。
因此,除非另有说明,通常`GROUP BY`排序规则不区分大小写。
`GROUP BY`根据字段的大写字母排序规则,使用`SQLUPPER`排序规则对字段的值进行分组。
只有字母大小写不同的字段值被分组在一起。
分组字段值全部以大写字母返回。
这样做的性能优势在于允许`GROUP BY`为字段使用索引,而不是访问实际的字段值。
因此,只有在一个或多个选定字段的索引存在时才有意义。
它的结果是`group by`字段值全部以大写字母返回,即使实际数据值中没有一个都是大写字母。
- 组合字母大小写变体在一起(返回实际的字母大小写):
`GROUP BY`可以将字母大小写不同的值分组在一起,并使用实际的字段字母大小写值返回分组的字段值(随机选择)。
这样做的好处是返回的值是实际值,显示数据中至少一个值的字母大小写。
它的性能缺点是不能使用字段的索引。
可以通过对`select-item`字段应用`%EXACT`排序函数来为单个查询指定这个值。
- 不要将不同的字母组合在一起(返回实际的字母):
通过对`GROUP BY`字段应用`%EXACT`排序功能,`GROUP BY`可以对值进行区分大小写的分组。
这样做的好处是将每个字母变体作为一个单独的组返回。
它的性能缺点是不能使用字段的索引。
可以使用管理门户在系统范围内为包含`GROUP BY`子句的所有查询配置此行为。依次选择系统管理、配置、SQL和对象设置、SQL。查看和编辑`GROUP BY`和`DISTINCT`查询必须生成原始值复选框。默认情况下,此复选框未选中。此默认设置按字母值的大写排序规则对字母值进行分组。(此优化也适用于`DISTINCT`子句。)。
也可以使用`$SYSTEM.SQL.Util.SetOption()`方法快速区分选项在系统范围内设置此选项。要确定当前设置,请调用`$SYSTEM.SQL.CurrentSettings()`,它显示打开的不同优化设置;默认值为1。
此优化利用选定字段的索引。因此,只有在一个或多个选定字段存在索引时才有意义。它对存储在索引中的字段值进行排序;字母字符串以全部大写字母返回。可以设置此系统范围的选项,然后使用`%exact`排序规则函数为特定查询覆盖它以保留字母大小写。
以下示例显示了这些行为。这些示例假定`Sample.Person`包含具有`Home_City`字段的记录,该字段具有SQLUPPER排序规则,值为`‘New York’`和`‘New York’`:
```sql
SELECT Home_City FROM Sample.Person GROUP BY Home_City
/* 将Home_City值按其大写字母值组合在一起将以大写字母返回每个分组城市的名称。因此,返回‘NEW YORK’。
```
```sql
SELECT %EXACT(Home_City) FROM Sample.Person GROUP BY Home_City
/*将Home_City值按其大写字母值组合在一起将返回以原始字母大小写表示的分组城市的名称。因此,可以返回‘New York’或‘new York’,但不能同时返回两者。*/
```
```sql
SELECT Home_City FROM Sample.Person GROUP BY %EXACT(Home_City)
/*将Home_City值按其原始字母大小写组合在一起将返回每个分组的城市的名称(原始字母大小写)。因此,‘New York’和‘New York’都作为单独的组返回。*/
```
## %ROWID
指定`GROUP BY`子句会导致基于游标的嵌入式SQL查询不设置`%ROWID`变量。即使`GROUP BY`不限制返回的行数,也不设置`%ROWID`。下面的示例显示了这一点:
```java
ClassMethod GroupBy()
{
s %ROWID=999
&sql(
DECLARE EmpCursor11 CURSOR FOR
SELECT Name, Home_State
INTO :name,:state FROM Sample.Person
WHERE Home_State %STARTSWITH 'M'
GROUP BY Home_State
)
&sql(
OPEN EmpCursor11
)
q:(SQLCODE'=0)
FOR {
&sql(FETCH EmpCursor11)
q:SQLCODE
w !,"RowID: ",%ROWID," row count: ",%ROWCOUNT
w " Name=",name," State=",state
}
&sql(CLOSE EmpCursor11)
}
```
```java
DHC-APP>d ##class(PHA.TEST.SQLCommand).GroupBy()
RowID: 999 row count: 1 Name=O'Rielly,Chris H. State=MS
RowID: 999 row count: 2 Name=Orwell,John V. State=MT
RowID: 999 row count: 3 Name=Zevon,Heloisa O. State=MI
RowID: 999 row count: 4 Name=Kratzmann,Emily Z. State=MO
RowID: 999 row count: 5 Name=Hanson,George C. State=MD
RowID: 999 row count: 6 Name=Zucherro,Olga H. State=MN
RowID: 999 row count: 7 Name=Gallant,Thelma Q. State=MA
RowID: 999 row count: 8 Name=Xiang,Kirsten U. State=ME
```
查询行为的这种更改仅适用于基于游标的嵌入式`SQL SELECT`查询。动态`SQL SELECT`查询和非游标嵌入式`SQL SELECT`查询从未设置`%ROWID`。
## 事务提交的更改
包含`GROUP BY`子句的查询不支持`READ COMMITTED`隔离级别。在定义为`READ COMMITTED`的事务中,不带`GROUP BY`子句的`SELECT`语句仅返回已提交的数据修改;换句话说,它返回当前事务之前的数据状态。带有`GROUP BY`子句的`SELECT`语句返回所做的所有数据修改,无论它们是否已提交。
# 示例
下面的示例按名称的首字母对名称进行分组。它返回首字母、共享该首字母的姓名计数以及一个`Name`值的示例。名称使用其`SQLUPPER`排序规则进行分组,而不考虑实际值的字母大小写。请注意,名称`SELECT-ITEM`包含大写首字母;`%Exact`排序规则用于显示实际的`Name`值:
```sql
SELECT Name AS Initial,COUNT(Name) AS SameInitial,%EXACT(Name) AS Example
FROM Sample.Person GROUP BY %SQLUPPER(Name,2)
```