面向开发者的 SQL： 数据处理
开发者从海量数据中提取正确信息的指南。了解如何将数据浓缩成有用的信息，同时确信您没有遗漏任何数据。
引言
在我上一篇关于基本数据检索的文章中，我分享过软件开发者有时会被要求从 SQL 数据库中检索数据。这些文章旨在帮助那些开发者。本文特别关注数据处理。如果一家公司拥有数百万条数据，但却不知道这些数据意味着什么，那么这些数据就毫无用处。本指南将帮助您了解如何处理这些大量数据，同时确保您获得的数据是准确的。查询大量数据时最糟糕的事情就是出现一点点偏差。您可能要等到为时已晚才能发现。本文将帮助您了解在运行查询之前，如何确保获得正确的信息。
注释
我在编写本文时使用了 Microsoft 提供的 AdventureWorks 数据库。以下所有查询都可以在该数据库上运行。我提供了一个工具，可以为您运行每个查询。T-SQL 测试平台的*.exe*可执行文件和源代码都已提供给您。您可以直接运行我的示例查询，也可以使用此工具测试您自己的查询。我在 Microsoft SQL Server 2008 R2 上测试了所有内容，但我相信所有包含的命令都可以与 Microsoft SQL Server 2005 兼容，除非另有说明。
作为我惯例免责声明的一部分，我想说明一点，本文的范围不包括讨论从代码调用 SQL 查询与在服务器上执行存储过程的优缺点。我个人认为，如果您有能力这样做，编写存储过程是最佳选择，但这并非总是一种选择。无论如何，本文都不是讨论这些内容的场所。
现在我们已经处理完了所有行政事宜，下面进入正题。
使用 SQL 函数处理数据
可以对数据集执行许多函数。众所周知的函数包括 SUM、COUNT、AVG 等。虽然这些函数可能易于理解，但您需要了解一些关于这些函数工作方式的内容，以免做出代价高昂但错误的假设。
COUNT 运算符经常用于确定特定数据集中的行数（或项数）。例如，如果我们想知道 Person.Contact 表中有多少条记录，我们可以运行以下查询：
SELECT COUNT(*) AS RecordCount
FROM Person.Contact
RecordCount
-----------
19972
(1 row(s) affected)
完美。只需一个简单的查询，我就知道了表中有多少行。在深入探讨 COUNT 的功能之前，我想退一步看看这个特定的查询。这种获取表中记录数的方法非常普遍。然而，有一种替代方法速度更快。如果您需要查找特定表中的记录数，请使用以下查询：
SELECT rows AS RecordCount
FROM sysindexes
WHERE id = OBJECT_ID('Person.Contact') AND indid < 2
RecordCount
-----------
19972
(1 row(s) affected)
这两种查询的速度差异令人难以置信。在我的机器上，COUNT(*) 方法比这种方法慢 25 倍。这是一个巨大的区别，尤其是在您每天多次执行该语句的情况下。这个 sysindexes 表存储着每个表中所有索引的信息。第一个索引 ID（indid）表示主键，它始终索引表中的每一条记录。因此，我们可以提取它索引的记录数，从而找出表中记录的数量。
回到 COUNT 语句，我们也可以对特定列使用 COUNT。这似乎是计算记录的更好方法，因为它只捕获一列，而不是令人讨厌的星号选择。我们都知道不应该使用 SELECT *，所以不使用 COUNT(*) 似乎也说得通，对吧？实际上不是。对一列使用 COUNT 而不是星号实际上有不同的目的。在 COUNT 中使用列名会计算该列中的非 NULL 条目。 如果您对不允许 NULL 的列使用 COUNT，您不会注意到结果数量的差异。然而，请观察以下示例：
SELECT COUNT(MiddleName) AS RecordCount
FROM Person.Contact
RecordCount
-----------
11473
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
请注意，由于我是在 SQL 查询窗口中运行此查询，因此它实际上警告了我这个小细节。如果您在代码中运行此命令，将不会收到漂亮的警告消息来提示您考虑您正在做什么。此查询的最终结果是，我们“丢失”了大约 8,500 条记录。这很重要。然而，更糟糕的是，如果您只丢失了几条记录。如果 40% 的记录不存在，您可能会注意到，但如果 0.1% 的记录丢失了，您会注意到吗？这充分说明了为什么在使用每个命令之前都需要了解其所有功能。
我相信您现在一定在想 SUM 和 AVG 等其他 SQL 函数语句。事实是，它们的工作方式相同。当您只看 COUNT 函数时，您的第一反应可能是这是一个错误。然而，当您结合 SUM（和其他函数）来看时，这是非常有意义的。NULL 不是数字。事实上，NULL 是任何事物的缺失。它是一个真空。它不等于零或空字符串。它意味着“值的缺失”。因此，它不应包含在平均值中。相反，这些行会被跳过。如果您不喜欢这一点，下一节将告诉您如何将 NULL 值转换为默认值。
使用 COALESCE 转换 NULL 值
有时您会希望将 NULL 值视为特定值。例如，如果要求学生为一个分数提交三份作业，您可能希望在截止日期过后，将任何缺交的作业视为零分。您可能不想在记录中填入零，因为这表示一项已完成但未获得任何学分的作业（这与缺交的作业不同）。在查询数据时，您可以使用 COALESCE 函数将这些条目转换为默认值。例如，在我们上面关于使用 COUNT(MiddleName) 方法计算行数的陈述中，我们发现有很多中间名具有 NULL 值。为了演示（这只是为了展示如何使用 COALESCE 关键字），我们可以像这样修改查询：
SELECT COUNT(COALESCE(MiddleName,'')) AS RecordCount
FROM Person.Contact
RecordCount
-----------
19972
(1 row(s) affected)
请注意，我们现在又获得了总记录数。这是因为我们使用了 COALESCE 关键字，并将 MiddleName 列中的所有 NULL 条目转换为空字符串。如果我们使用它来转换分数，我们会填入零而不是空字符串。当您将结果发送到与 NULL 值不兼容的系统时，COALESCE 也会非常有用。您可以对所有允许 NULL 值的列使用 COALESCE，以便生成的记录集不包含 NULL。
COALESCE 运算符可以用于比仅仅删除 NULL 值更有趣的应用程序。如下所示，我们可以使用它根据 NULL 值所代表的含义来填充列中的新值。我们甚至可以使用 COALESCE 将两个（或更多）列合并为一个，如果我们知道只有一个列有值（或者我们只想要找到的第一个值）。
使用 GROUP BY 折叠数据
从数据集中提取有意义信息的最常见方法之一是将其折叠。例如，我们不查看篮球运动员投出的每一次罚球的结果。相反，我们查看该球员的命中率。当查看一个表中多个球员时，您将使用 GROUP BY 语句将每个球员的记录分组在一起，然后计算投进的次数，并除以总投篮次数。这将为我们计算每个球员的命中率。
GROUP BY 语句按 GROUP BY 语句中指定的每个列对记录进行分组。然后，它可以对这些分组的记录执行聚合函数。例如，在这里我按电子邮件推广（Email Promotion）对 Contact 记录进行了分组，然后计算每个组中的记录数：
SELECT EmailPromotion, COUNT(*) AS RecordCount
FROM Person.Contact
GROUP BY EmailPromotion
EmailPromotion RecordCount
-------------- -----------
0 11158
1 5044
2 3770
(3 row(s) affected)
此查询可用于找出每个电子邮件推广有多少人。虽然这是 GROUP BY 的简单用法，但它确实突显了我们正确构建此命令所需的基本结构。使用 GROUP BY 时，您需要将所有非聚合列包含在 GROUP BY 语句中。您放入此语句的每一列都将是分组依据。因此，如果您只在此部分放置姓氏，它将按每个姓氏进行分组。您的数据库中可能有 15 个 Smith。如果您将名字和姓氏放入 GROUP BY 部分，它将按两者的组合进行分组，所以可能有 12 个姓氏为 Smith 的条目。很少会把大量列放入 GROUP BY 部分，因为这会使匹配数量非常少。唯一的例外是当您要查找重复项时。例如，如果您想通过组合名字和姓氏来创建用户名，您可能首先运行一个查询来查看有多少重复项。您可以通过将名字和姓氏列都放入 GROUP BY 并进行记录计数来做到这一点。然后，您可以按计数降序排序，以找到任何给定姓名的最大重复次数。以下是此类查询语句的示例：
SELECT FirstName,LastName, COUNT(*) AS Duplicates
FROM Person.Contact
GROUP BY FirstName,LastName
ORDER BY Duplicates DESC
FirstName LastName Duplicates
------------------------------ --------------------------------------- ----------
Laura Norman 5
Kim Ralls 4
Jean Trenary 4
Sheela Word 4
…
(19516 row(s) affected)
请注意，我们使用了 ORDER BY 进行降序排序，以便将重复项放在顶部。此查询返回了大量记录，其中大部分不是重复项。我们可以通过使用 TOP 命令限制查询来解决这个问题，但这并不能真正满足我们的需求。例如，假设我们只想找到有四次或更多重复项的记录。逻辑会告诉我们使用 WHERE 语句。问题在于 WHERE 语句作用于每一条记录，而不是聚合数据。我见过一些人通过嵌套查询来解决这个问题，而外层查询带有 WHERE 语句。这可行，但并不必要。相反，我们应该使用 HAVING 关键字。此关键字对结果行执行过滤操作，而不是对原始行执行过滤。以下是如何在查询中使用它的示例：
SELECT FirstName,LastName, COUNT(*) AS Duplicates
FROM Person.Contact
WHERE LastName = 'Miller' OR LastName = 'Martin'
GROUP BY FirstName,LastName
HAVING COUNT(*) > 3
ORDER BY Duplicates DESC
FirstName LastName Duplicates
------------------------------- -------------------------------------- -----------
Benjamin Martin 4
Mindy Martin 4
Dylan Miller 4
Frank Miller 4
(4 row(s) affected)
请注意，HAVING 语句不识别 SELECT 语句中的别名，因此我们需要在 HAVING 语句中也放置表达式。实际上，如果我们不想，我们甚至不需要在 SELECT 语句中包含表达式。例如，我们可能只想找出那些有四次或更多重复项的人，但我们不关心他们有多少记录。在这种情况下，我们将省略重复项列，但会在 HAVING 部分保留该语句。另请注意，我包含了一个 WHERE 语句只是为了表明可以这样做。请记住，WHERE 作用于每个单独的记录，而 HAVING 作用于结果行数据。
使用 WITH ROLLUP 命令获取小计
使用 GROUP BY 语句为您开启了数据汇总之路。一旦您对 GROUP BY 的使用有了扎实的理解，下一个命令将使生活变得更好。WITH ROLLUP 命令将允许您在查询中获取小计。例如，假设您有很多采购订单，每个订单都有多个项目。您的老板会让您提供一份报告，说明每个采购订单的总金额，但您还需要提供每个采购订单上每种产品的总支出摘要（假设采购订单上可能有多个行引用同一产品）。一种方法是按采购订单号和产品号进行 GROUP BY。您可以对产品成本列进行 SUM。这会为您提供每个采购订单每个项目的总计，但不会为您提供每个采购订单的总计。这就是 WITH ROLLUP 命令的用武之地。它将为您提供每个部分的汇总以及总的汇总。让我们看看这会是什么样子：
SELECT PurchaseOrderID ,
ProductID ,
SUM(LineTotal) AS Total
FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderID < 5
GROUP BY PurchaseOrderID,ProductID WITH ROLLUP
PurchaseOrderID ProductID Total
--------------- ----------- ---------------------
1 1 201.04
1 NULL 201.04
2 359 135.36
2 360 136.7415
2 NULL 272.1015
3 530 8847.30
3 NULL 8847.30
4 4 171.0765
4 NULL 171.0765
NULL NULL 9491.518
(10 row(s) affected)
如果我们在没有 WITH ROLLUP 命令的情况下运行此语句，它将提供一个类似的列表，但不包括包含 NULL 值的行。这是一个简单的例子，但它显示了 WITH ROLLUP 命令的工作方式。每个包含 NULL 值的行都是一个部分汇总。查看第五行（PurchaseOrderID 2 ProductID NULL）。这是整个采购订单的摘要。因此，Total 列是前面两个小计的总和。底部，在两列中都包含 NULL 的行是总计行。Total 列中的值是整个查询的总和。正如您所看到的，当您需要获取汇总信息时，这是一个有价值的命令。您可以将其与 AVG、SUM、COUNT 等任何命令一起使用。请注意，GROUP BY 语句中的列顺序很重要。 我将 PurchaseOrderID 列放在前面，以便获得每个采购订单的总计。如果我将 ProductID 放在前面，我将获得每个产品的总计，这可能不是我想要的。
使用 WITH CUBE 命令进行高级小计
一旦您理解了 WITH ROLLUP 命令，您就可以使用 WITH CUBE 命令更进一步。此命令允许您从各个方面获取汇总。例如，在我们上面的示例中，我们想要每个采购订单的总金额以及每个采购订单下每个项目的总和。但是，如果我们还想查看我们如何销售每种产品呢？如果您已经设置了带有 WITH ROLLUP 命令的查询，只需将其更改为 WITH CUBE 即可。这是一个例子：
SELECT PurchaseOrderID ,
ProductID ,
SUM(LineTotal) AS Total
FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderID < 5
GROUP BY PurchaseOrderID,ProductID WITH CUBE
PurchaseOrderID ProductID Total
--------------- ----------- ---------------------
1 1 201.04
NULL 1 201.04
4 4 171.0765
NULL 4 171.0765
2 359 135.36
NULL 359 135.36
2 360 136.7415
NULL 360 136.7415
3 530 8847.30
NULL 530 8847.30
NULL NULL 9491.518
1 NULL 201.04
2 NULL 272.1015
3 NULL 8847.30
4 NULL 171.0765
(15 row(s) affected)
如果您将其与之前的查询结果进行比较，您会看到五个额外的行。除了我们已经拥有的内容之外，我们还得到了每个 Product ID 的小计。顺序有点不同，每个 ProductID 的总计混在我们的列表中，而每个采购订单的总计放在底部。如果顺序对您很重要，您可以更改 GROUP BY 部分中给出的顺序，因为它不会影响提供的数据，只会影响显示顺序（与 WITH ROLLUP 不同，后者顺序很重要）。WITH CUBE 命令允许我们获取每列的汇总数据。但请谨慎使用，因为如果您在 GROUP BY 部分尝试处理太多列，它可能会产生大量开销。
使用 GROUPING 关键字识别汇总行
一旦您开始熟悉 WITH ROLLUP 和 WITH CUBE 的使用，您可能会想知道除了 NULL 值之外，是否有方法可以识别汇总行。SQL 配备了 GROUPING 关键字，它允许我们识别 WITH ROLLUP 或 WITH CUBE 命令生成的每个汇总行。以下是如何使用它的示例：
SELECT PurchaseOrderID ,
ProductID ,
SUM(LineTotal) AS Total ,
GROUPING(PurchaseOrderID) AS PurchaseOrderGrouping,
GROUPING(ProductID) AS ProductGrouping
FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderID < 5
GROUP BY PurchaseOrderID,ProductID WITH CUBE
PurchaseOrderID ProductID Total PurchaseOrderGrouping ProductGrouping
--------------- ----------- --------------------- --------------------- ---------------
1 1 201.04 0 0
NULL 1 201.04 1 0
4 4 171.0765 0 0
NULL 4 171.0765 1 0
2 359 135.36 0 0
NULL 359 135.36 1 0
2 360 136.7415 0 0
NULL 360 136.7415 1 0
3 530 8847.30 0 0
NULL 530 8847.30 1 0
NULL NULL 9491.518 1 1
1 NULL 201.04 0 1
2 NULL 272.1015 0 1
3 NULL 8847.30 0 1
4 NULL 171.0765 0 1
(15 row(s) affected)
正如您所见，如果某个项目是小计或总计，GROUPING 命令会在该列中放入一个 1。如果记录是由 GROUP BY 语句创建的，它会放入一个 0。我使用了别名来命名这两个新列，以便于引用。请注意，如果您使用 HAVING 语句，现在可以根据这些值过滤此查询。
使用 COALESCE 关键字识别汇总行
我们已经介绍了 COALESCE 命令的工作原理。正如我们所知，COALESCE 给出了它包含的值列表中第一个非 null 的值。在前面的示例中，我们使用 COALESCE 简单地清理了一个 NULL 值并用一个常量替换它，作为提供默认值的方法。然而，我们可以在 COALESCE 中进行更高级的语句。让我通过示例向您展示我的意思，然后我会解释我做了什么。
SELECT COALESCE(CAST(PurchaseOrderID AS NVARCHAR(10)),_
'Product Total: ' + CAST(ProductID AS NVARCHAR(10)), 'Grand Total') AS PurchaseOrder,
COALESCE(CAST(ProductID AS NVARCHAR(10)),'PO Total: ' + _
CAST(PurchaseOrderID AS NVARCHAR(10)), '') AS Product ,
SUM(LineTotal) AS Total
FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderID < 5
GROUP BY PurchaseOrderID,ProductID WITH CUBE
PurchaseOrder Product Total
------------------------- -------------------- ---------------------
1 1 201.04
Product Total: 1 1 201.04
4 4 171.0765
Product Total: 4 4 171.0765
2 359 135.36
Product Total: 359 359 135.36
2 360 136.7415
Product Total: 360 360 136.7415
3 530 8847.30
Product Total: 530 530 8847.30
Grand Total 9491.518
1 PO Total: 1 201.04
2 PO Total: 2 272.1015
3 PO Total: 3 8847.30
4 PO Total: 4 171.0765
(15 row(s) affected)
首先要注意的是，这有点复杂。因为列是 int 类型，我必须将值 CAST 为 nvarchar 类型，否则语句会报错。这使得查询看起来很难看，但性能与使用 GROUPING 关键字的语句没有区别（我多次检查过，因为我难以置信）。
抛开它看起来吓人这一事实，这个查询没有什么复杂的。基本上，我使用了一个包含三个选项的 COALESCE 语句。第一个选项是字段本身。但是，如果它是 NULL，我们就知道我们正在获取另一个列的总计。在这种情况下，我将一个 静态字符串 与另一个列值合并，以获得总计行。但是，如果另一个列也是 NULL，这意味着我们正处于总计行。在这种情况下，我根据我所在的列交替执行操作。对于第一列，我将其标记为总计行。第二列我只放了一个空 字符串，因为该行已被指定为总计行。唯一真正需要记住的复杂之处是，如果您在一个列中发现 NULL 值，这意味着正在对另一列进行汇总（而不是当前列）。
结论
在本文中，我们学习了如何将数据压缩并汇总成有意义的信息。我们涵盖了大量围绕 GROUP BY 语句的信息，以及如何有效地使用它，远超我们通常看到的那样简单的方法。关于 SQL 还有很多我尚未涵盖的内容。我的计划是继续满足软件开发者在编写 T-SQL 方面的需求。希望您从本文中有所收获。我感谢您的建设性反馈，并期待您对如何改进本文的意见。
历史
- 2011 年 1 月 22 日：初始版本