29、SQL 高级应用：从子查询到数据集操作与高级计算

SQL 高级应用：从子查询到数据集操作与高级计算

1. 相关子查询与 NOT EXISTS 关键字

在 SQL 查询中，有时我们需要判断数据是否存在于相关表中，这时可以使用相关子查询和 NOT EXISTS 关键字。例如，在 Prestige Cars 数据模型里， Stock 表包含所有购买的车辆信息， SalesDetails 表包含所有售出的车辆信息。下面的 SQL 代码展示了相关子查询的使用：

(SELECT  *
FROM    SalesDetails SD
WHERE   ST.StockCode = SD.StockID);

这个查询的工作原理如下：
- 外层查询 ：连接 Make 、 Model 和 Stock 表，输出所需字段，并确保可以访问能与子查询共享的字段（如 StockCode 字段）。
- 内层查询 ：从 SalesDetails 表中选择所有数据，但由于这是一个相关子查询，它只查找 StockID 与外层查询的 StockCode 匹配的记录。

对于外层查询找到的每条记录，内层查询都会重新运行。如果 StockCode 和 StockID 字段匹配，外层查询的 WHERE 子句就会起作用，即 WHERE NOT EXISTS ，若两个表在某条记录上不匹配，该记录就会被查询返回。

使用 NOT EXISTS 函数时，需要注意以下要点：
- 相关子查询使用 NOT EXISTS 时，只关注子查询中数据的存在性，子查询实际选择的字段无关紧要。
- 这种相关子查询的运行速度可能比简单查询慢很多，因为内层查询要为外层查询的每条记录运行一次，处理大型数据集时可能需要较长时间。

以下是相关概念的总结表格：
| 概念 | 描述 |
| — | — |
| 相关子查询 | 允许将子查询与主查询关联，并通过将外层查询的每条记录与子查询的结果进行比较来过滤外层查询的数据。 |
| EXISTS | 测试与外层查询关联的子查询中是否存在记录。 |
| NOT EXISTS | 测试与外层查询关联的子查询中是否不存在记录。 |

2. 数据集操作

在实际的数据处理中，我们常常会遇到数据分散在多个结构相同的表中的情况，这时就需要进行数据集操作，主要包括以下几种方式：

2.1 使用 UNION 操作符从多个相同表中读取数据

当我们需要同时查询多个结构相同的表时，可以使用 UNION 操作符。例如，有三个销售信息表 Sales2015 、 Sales2016 和 Sales2017 ，它们包含相同的列且顺序相同，查询这三个表的 SQL 如下：

SELECT  MakeName, ModelName, CustomerName, 
CountryName
        ,Cost, RepairsCost, PartsCost, 
TransportInCost
        ,SalePrice, SaleDate
FROM    Sales2015
UNION 
SELECT  MakeName, ModelName, CustomerName, 
CountryName
        ,Cost, RepairsCost, PartsCost, 
TransportInCost
        ,SalePrice, SaleDate
FROM    Sales2016
UNION
SELECT  MakeName, ModelName, CustomerName, 
CountryName
        ,Cost, RepairsCost, PartsCost, 
TransportInCost
        ,SalePrice, SaleDate
FROM    Sales2017;

这个操作的工作原理是：SQL 允许用户在查询结构相同时，通过为每个表编写 SELECT 查询，然后使用 UNION 操作符合并这些查询，从而一次性从多个表中读取数据。这种方法在数据存储在多个相同结构表的场景中非常实用，例如数据来自外部供应商或原始关系数据分开存储的情况。

使用 UNION 操作符时，需要注意以下几点：
- 可以像在 SQL 查询中一样使用别名重命名 UNION 查询中的列，但只需在系列中的第一个查询中添加别名，其他查询会使用第一个查询的别名。
- 在 UNION 查询中，必须以相同的顺序选择每个单独查询中的列。
- 如果底层表的列不完全相同，可以在 SELECT 语句中用 NULL 替换缺失的列名。例如，如果 Sales2016 表中没有 TransportInCost 字段，可以这样写 SQL：

SELECT  MakeName, ModelName, CustomerName, 
CountryName, Cost, RepairsCost, PartsCost, NULL, 
SalePrice, SaleDate
FROM    Sales2015
UNION 
SELECT  MakeName, ModelName, CustomerName, 
CountryName ,Cost, RepairsCost, PartsCost, 
TransportInCost, SalePrice, SaleDate
FROM    Sales2016
UNION
SELECT  MakeName, ModelName, CustomerName, 
CountryName, Cost, RepairsCost, PartsCost, 
TransportInCost, SalePrice, SaleDate
FROM    Sales2017;

2.2 使用 INTERSECT 操作符隔离多个表中的相同数据

当需要比较不同时间段的相似元素时，可以使用 INTERSECT 操作符。例如，销售总监想要找出今年和去年都购买过的品牌，以下查询可以实现该需求：

SELECT     MK.MakeName
FROM       Make AS MK 
INNER JOIN Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Stock AS ST ON ST.ModelID = MD.ModelID
WHERE      YEAR(ST.DateBought) = 2015
INTERSECT
SELECT     MK.MakeName
FROM       Make AS MK 
INNER JOIN Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Stock AS ST ON ST.ModelID = MD.ModelID
WHERE      YEAR(ST.DateBought) = 2016;

这个查询的工作原理是：有两个单独的查询，第一个查询连接 Stock 、 Make 和 Model 表，筛选出 2015 年购买的车辆，只选择 MakeName 字段；第二个查询与第一个类似，但筛选出 2016 年购买的车辆。使用 INTERSECT 操作符将这两个查询组合起来，会运行两个查询并找出它们的共同结果，最终只输出这些共享结果。

使用 INTERSECT 操作符时，需要注意以下要点：
- 这两个查询在技术上不是子查询，它们处于同一级别，不需要放在括号内，但它们是完全独立的数据集。
- INTERSECT 查询会自动去除最终结果中的重复项。
- INTERSECT 查询的 SELECT 子句中必须有相同的字段且顺序相同，否则可能无法返回正确结果。
- INTERSECT 查询不限于两个查询，可以根据需要添加多个用 INTERSECT 关键字连接的查询。
- 为了隔离数据，不需要选择每个表中的所有字段，但选择的字段在所有源表中必须相同，记录才会被返回。

2.3 使用 EXCEPT 操作符隔离非相同记录

有时候，我们关注的是两个数据集之间不共享的数据。例如，CEO 要求列出今年售出但去年未售出的车辆模型，以下 SQL 可以实现该需求：

SELECT     MK.MakeName || ' ' || MD.ModelName AS 
MakeModel
FROM       Make AS MK 
INNER JOIN Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN SalesDetails SD ON ST.StockCode = 
SD.StockID
INNER JOIN Sales AS SA ON SA.SalesID = SD.SalesID
WHERE      YEAR(SA.SaleDate) = 2015
EXCEPT
SELECT     MK.MakeName || ' ' || MD.ModelName
FROM       Make AS MK 
INNER JOIN Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN SalesDetails SD ON ST.StockCode = 
SD.StockID
INNER JOIN Sales AS SA ON SA.SalesID = SD.SalesID
WHERE      YEAR(SA.SaleDate) = 2016;

这个查询的工作原理是：有两个查询，它们输出相同的字段集且顺序相同，使用 EXCEPT 操作符将两个查询的数据集进行比较，找出第一个查询中有但第二个查询中没有的元素，即今年售出但去年未售出的车辆模型。

使用 EXCEPT 操作符时，要注意两个查询必须完全独立，每个查询都有自己的 WHERE 子句，这样才能指定不同的数据集。

2.4 在子查询中连接多个相同表

如果需要对多个相同表连接后的数据进行筛选和处理，可以在子查询中使用 UNION 操作符。例如，CEO 要求列出所有三个表中销售给德国客户的品牌和型号，SQL 代码如下：

SELECT  MakeName, ModelName
FROM
(
   SELECT  MakeName, ModelName, CustomerName, 
CountryName, Cost
   ,RepairsCost, PartsCost, TransportInCost, 
SalePrice, SaleDate
   FROM    Sales2015
 UNION 
   SELECT  MakeName, ModelName, CustomerName, 
CountryName, Cost
   ,RepairsCost, PartsCost, TransportInCost, 
SalePrice, SaleDate
   FROM    Sales2016
 UNION
   SELECT  MakeName, ModelName, CustomerName, 
CountryName, Cost
   ,RepairsCost, PartsCost, TransportInCost, 
SalePrice, SaleDate
   FROM    Sales2017
) SQ
WHERE   CountryName = 'Germany';

这个操作的工作原理是：将 UNION 查询包裹在一个外层查询中，将其视为一个派生表，这样可以方便地选择所需字段，使用单个 WHERE 子句进行数据过滤，甚至可以根据需要对数据进行分组。

使用这种方法时，需要注意：
- 虽然可以对 UNION 查询中的每个查询应用相同的 SELECT 和 WHERE 子句，但将 UNION 查询转换为派生表并对组合数据进行过滤会更简单，也更不容易出错。
- 扩展查询以包含派生表是代码复用的一个很好的例子，只需进行小的调整就能使之前创建的 SQL 更适用，因此开发者应保存这些代码片段，以备后续使用。

以下是数据集操作相关概念的总结表格：
| 概念 | 描述 |
| — | — |
| UNION | 从多个结构相同的表或视图中提取数据，并去除重复项。 |
| UNION ALL | 从多个结构相同的表或视图中提取数据，并保留重复项。 |
| INTERSECT | 返回两个表或视图中共同的数据子集。 |
| EXCEPT | 返回一个表或视图中存在但另一个表或视图中不存在的数据子集。 |

通过以上介绍，我们了解了 SQL 中相关子查询、 NOT EXISTS 关键字以及各种数据集操作的使用方法和注意事项，这些技巧可以帮助我们更高效地处理和分析数据。

3. SQL 高级计算

在 SQL 中，除了基本的数学运算，还可以进行更高级的计算。但要进行这些计算，需要更深入地理解 SQL 如何处理数字。

3.1 计算数据集中每条记录的百分比

在某些情况下，我们需要计算数据集中每条记录所占的百分比。例如，在销售数据中，计算每个产品的销售额占总销售额的百分比。具体操作步骤如下：
1. 首先，计算总销售额：

SELECT SUM(SalePrice) AS TotalSale
FROM Sales;

2. 然后，计算每个产品的销售额占总销售额的百分比：

SELECT 
    ProductName, 
    SalePrice, 
    (SalePrice / (SELECT SUM(SalePrice) FROM Sales)) * 100 AS Percentage
FROM Sales;

3.2 替换多个子查询

当查询中存在多个子查询时，可能会影响查询性能。可以通过连接表的方式来替换多个子查询。例如，原本使用子查询获取每个客户的订单数量和总订单金额，现在可以通过连接 Customers 表和 Orders 表来实现：

SELECT 
    C.CustomerName, 
    COUNT(O.OrderID) AS OrderCount, 
    SUM(O.OrderAmount) AS TotalOrderAmount
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerName;

3.3 去除计算中的小数

在某些计算中，可能不需要小数部分。可以使用 FLOOR 或 ROUND 函数来去除小数。例如，将产品价格向下取整：

SELECT 
    ProductName, 
    FLOOR(Price) AS RoundedPrice
FROM Products;

或者将价格四舍五入到整数：

SELECT 
    ProductName, 
    ROUND(Price) AS RoundedPrice
FROM Products;

3.4 理解数值数据类型

在进行高级计算时，选择合适的数值数据类型非常重要。常见的数值数据类型有 INT 、 FLOAT 、 DECIMAL 等。以下是它们的特点和适用场景：
| 数据类型 | 描述 | 适用场景 |
| — | — | — |
| INT | 用于存储整数，占用空间小，计算速度快 | 存储不需要小数的整数，如数量、年龄等 |
| FLOAT | 用于存储浮点数，精度有限 | 对精度要求不高的计算，如近似值计算 |
| DECIMAL | 用于存储精确的小数，可指定精度和小数位数 | 对精度要求高的计算，如货币计算 |

3.5 数值数据类型的转换

有时候，需要将一种数值数据类型转换为另一种。可以使用 CAST 或 CONVERT 函数来实现。例如，将 FLOAT 类型的价格转换为 DECIMAL 类型：

SELECT 
    ProductName, 
    CAST(Price AS DECIMAL(10, 2)) AS ConvertedPrice
FROM Products;

3.6 避免除零错误

在进行除法运算时，需要注意避免除零错误。可以使用 CASE 语句来检查除数是否为零。例如，计算每个产品的利润率：

SELECT 
    ProductName, 
    CASE 
        WHEN Cost = 0 THEN 0
        ELSE ((SalePrice - Cost) / Cost) * 100
    END AS ProfitMargin
FROM Products;

3.7 使用模函数求除法的余数

在 SQL 中，可以使用 MOD 函数来求除法的余数。例如，找出所有能被 3 整除的产品 ID：

SELECT 
    ProductID
FROM Products
WHERE MOD(ProductID, 3) = 0;

3.8 创建财务计算

在财务分析中，可能需要进行一些复杂的计算，如计算净现值（NPV）、内部收益率（IRR）等。虽然 SQL 本身没有直接提供这些函数，但可以通过编写自定义函数或使用数据库的扩展功能来实现。例如，计算简单的利息：

SELECT 
    Principal, 
    InterestRate, 
    Term, 
    Principal * InterestRate * Term AS Interest
FROM Loans;

3.9 使用计数表生成连续的数字列表

计数表是一个包含连续数字的表，可以用于生成连续的数字列表。例如，生成从 1 到 10 的数字列表：

SELECT Number
FROM TallyTable
WHERE Number BETWEEN 1 AND 10;

3.10 从数据集中生成完全随机的样本输出

有时候，需要从数据集中随机抽取样本。可以使用 RAND() 函数来实现。例如，从 Customers 表中随机抽取 10 个客户：

SELECT TOP 10 *
FROM Customers
ORDER BY RAND();

3.11 处理源数据中数字以文本形式存储的情况

在实际数据中，可能会遇到数字以文本形式存储的情况。可以使用 CAST 或 CONVERT 函数将文本转换为数字。例如，将文本形式的价格转换为数字：

SELECT 
    ProductName, 
    CAST(PriceText AS DECIMAL(10, 2)) AS Price
FROM Products;

总结

通过本文的介绍，我们学习了 SQL 中的相关子查询、 NOT EXISTS 关键字、各种数据集操作以及高级计算的方法和技巧。相关子查询和 NOT EXISTS 关键字可以帮助我们判断数据是否存在于相关表中； UNION 、 INTERSECT 、 EXCEPT 操作符可以用于处理多个相同结构表的数据；而高级计算则可以让我们进行更复杂的数学运算，如计算百分比、避免除零错误等。掌握这些知识，可以帮助我们更高效地处理和分析数据，解决实际工作中的各种问题。在实际应用中，我们需要根据具体的需求选择合适的方法和技巧，并注意各种操作的注意事项，以确保查询的正确性和性能。

下面是一个简单的流程图，展示了 SQL 高级应用的主要步骤：

graph LR
    A[数据查询与分析] --> B[相关子查询与NOT EXISTS]
    A --> C[数据集操作]
    A --> D[高级计算]
    B --> B1[外层查询连接表]
    B --> B2[内层查询匹配记录]
    C --> C1[UNION提取数据]
    C --> C2[INTERSECT找出共同数据]
    C --> C3[EXCEPT隔离非相同记录]
    C --> C4[子查询连接表并过滤]
    D --> D1[计算百分比]
    D --> D2[替换子查询]
    D --> D3[去除小数]
    D --> D4[处理数据类型]
    D --> D5[避免除零错误]
    D --> D6[其他高级计算]

通过这个流程图，我们可以更清晰地看到 SQL 高级应用的整体结构和主要步骤，有助于我们更好地理解和应用这些知识。