Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause

T-SQL DML第7级:使用简单的GROUP BY子句汇总数据。

格雷戈里·拉森,2012/01/04

 

这篇文章是Stairway系列的一部分:通往T-SQL DML的阶梯。

通过使用SQLServer的Transact-SQL (T-SQL)方言，这个Stairway将为您提供如何使用SQL Server表数据的基本理解。DML是数据操纵语言，是处理数据的语言的方面。它包括语句选择、插入、更新和删除。这个Stairway将提供一些SQL语言的历史和一些关于集合理论的一般概念。每个级别都将建立在之前的级别上，所以当您完成时，您将很好地理解如何从SQL Server中选择和修改数据。

在Stairway6中，我向您展示了如何使用ORDERBY子句对数据进行排序。这使您可以根据单个或多个列对详细记录进行排序。如果您希望在特定记录中查看数据，那么详细的数据非常好，但是有时候您需要将详细的数据汇总到汇总值中。总结您可以使用GROUP BY子句完成的数据。

有两种类型的GROUPBY子句。一种被称为简单的GROUP BY子句，另一种则提供了简单的概括，称为general GROUP BY子句。这两种类型的主要区别是简单的GROUP BY只包含GROUP BY子句，而general GROUP BY子句包含其他操作符，如ROLLUP和CUBE。

在本文中，我将介绍如何使用简单的group BY子句对数据进行分组。在后续的一篇文章中，我将讨论更复杂的general GROUP BY子句。

 

简单的GROUP BY子句

 

使用simpleGROUP BY子句可以根据单个列、多个列或表达式聚合数据。根据GROUP BY子句中指定的列和/或表达式，只对每个惟一的值返回一个汇总行。当SQL Server通过子句处理一个组时，它通过惟一的列或表达式值对详细记录进行分组，然后根据选择列表中包含的聚合函数对每个集合进行总结。

为了更好地理解如何使用GROUPBY，让我们假设您有一个表，其中包含了不同商店的详细销售信息，您希望通过存储来总结总的销售额。您可以使用GROUP BY子句来聚合每个商店的总销售额。在本例中，您将分组的唯一列将是存储名称，而要聚合的列将是销售额。您的结果将显示每个惟一的存储名的一行，并且每个存储的行将包含该存储的销售额之和。

SQL Server对可以包含在GROUP BY查询的SELECT列表中的某些列有一些限制。 在GROUP BY查询的SELECT列表中指定的每个列都需要属于以下类别之一：
?GROUP BY子句中指定的列
?在GROUP BY子句中指定的表达式
?从聚合函数返回的值
如果某列不属于这些类别之一，那么在尝试运行GROUP BY查询时将会出现错误。 注意包含在GROUP BY子句中的列或表达式不需要位于选择列表中。
让我通过几个示例来帮助演示如何使用简单的GROUP BY子句获取汇总值。
用于探索简单GROUP BY子句的示例数据
为了演示如何使用简单的GROUP BY子句，我需要构建一些示例数据。 我提供了一个脚本来创建我的示例数据，以便您可以运行本文中提供的示例代码。 使用清单1中的脚本来构建和填充示例表。
清单1：创建示例数据的脚本
如果您查看清单1中的脚本，您会发现我创建了dbo.SalesTransaction表。 然后我在此表中插入了一些记录。 我将使用此表演示如何使用简单的GROUP BY子句来聚合数据。
按单列分组
使用清单1创建的示例表，这第一个示例将使用GROUP BY子句基于单个列汇总数据。 清单2中的例子总结了基于StoreName列的示例数据。
清单2：基于Single列的GROUP BY

当清单2中的代码针对我的样本表进行执行时，将返回报告1中的以下聚合行。

报告1：基于一个单独的列总结出一个样本

如果你回顾一下在报告1中的输出内容，你会发现只有一个行集合是storename的独特返回值.这个storestateaccount在每个记录中都是通过计算 toralsalesamount 子句（每个使用SUM功能的购买记录）来统计的。

多列分组

有时候当你想要得出一个你的数据需要被多列分组的报告。为了实现这个目的，你需要做的是添加额外的组数。当每个集合的值在group by子句中基于具体行SQL server的集合是独一无二的时候这个多列是特殊的。在列表3中我已经详述了在列表2中通过添加一个次级列到不group by子句中去。

列表3：基于单列的分组

当我用样本中的数据运行列表3中的代码，我得到的是报告2中的结果。

报告2：运行列表1的输出结果

在报告2中你可以了解storesalesamount 现在是storename 和salestypedesc等级的集合。同时也记录了返回的集合行不在已分类的基于列在group by的顺序。如果我想要归纳数据用于排列storename 的顺序那么我需要将一个在staement中的order by子句s包括进去。我会让你决定是否将order by加入列表3来返回在storenameorder中的已归纳数据。

在group by子句中使用公式

有时候你可能会需要将你的数据用某些其他的特殊的列或者列额集合来分组。打个比方，你可能想要总结你基于可变字符的列，或者只是一个日期，或者一个日期列的月份。

Sql server 允许你在groupby子句中指定公式来做到这点。一个公式可以是任何有效的、基于一个在具体记录中的列。为了证明如何去在group up子句中使用公式，请看列表4中的代码。

列表4：基于单列的分组

在列表4中，SELECT声明就是一个给数据分组的公式，在这种情况下是一个convert功能。如果你使用了一个公式在group by子集中，一个完全相同的公式也一定会用在SELECT列表中。CONVERT功能是salesdatetime列的解析，并且只返回列的数据部分。在GROUP BY中使用CONVERT子集允许我来总结基于属于不同Sale记录的具体数据。

报告3：基于表达式汇总数据时的输出
使用表达式允许您以编程方式识别您的详细数据的哪些部分将用于汇总数据。
HAVING子句
如果您使用GROUP BY子句汇总数据，则有可能不想返回所有汇总值。 相反，您可能只想返回聚合值的一个子集。 HAVING子句可用于选择性地标识要从GROUP BY摘要返回的聚合值。
通常当我们选择数据时，我们使用WHERE子句来限制返回的行。 唯一的问题是WHERE子句对行值进行操作，而对汇总值不起作用。 因此，WHERE子句无法使用由GROUP BY子句创建的聚合值。 但是，在GROUP BY子句后面添加HAVING子句可以指定条件以标识要返回的特定汇总值。 为了更好地理解这一点，让我给你提供几个例子。
使用HAVING子句的一个常见情况是在查看商店销售数据时，确定不符合特定销售配额的商店。 如果您想查找所有未达到最低销售额的商店，可以使用清单5中的代码来完成此操作。
清单5：使用HAVING子句限制结果集
在清单5中，我将结果集限制为聚合TotalSalesAmount小于1000.00的商店。 在我这里的小例子中，您会发现StoreName中的“计算机书籍和软件”是唯一没有达到$ 1000.00销售配额数量的商店。
HAVING子句可用于未汇总的列。 如果要限制根据GROUP BY子句中使用的任何一列的特定值返回的行，您也可以这样做，清单6演示了这一点
清单6：根据GROUP BY列限制结果集
在清单6中，我只想查看商店名称中具有“Outlet”或“Books”的商店的汇总数据。此示例还说明您可以在HAVING子句中拥有多个条件。 另一种思考WHERE和HAVING之间区别的方法是WHERE子句在聚合数据之前过滤掉数据行，而HAVING子句在应用GROUP BY之后过滤掉聚合行。
用简单的GROUP BY子句汇总数据
在本文中，我向您展示了如何使用简单的GROUP BY子句来汇总数据。 我讨论了如何使用单列，多列以及GROUP BY子句中的表达式来汇总详细数据。 通过使用我已经演示的内容，您现在应该可以构建一个简单的GROUP BY子句来汇总数据，并可以使用HAVING过滤汇总数据。
在我的下一篇文章中，我将扩展我对GROUP BY子句的讨论。 在这篇后续文章中，我将向您展示如何使用CUBE和ROLLUP操作符来生成其他汇总数据，例如小计和总计值。