32、SQL 数据排名、筛选与分段操作全解析

SQL 数据排名、筛选与分段操作全解析

在数据处理和分析中，SQL 是一种强大的工具，可用于对数据进行排名、筛选和分段等操作。以下将详细介绍如何使用 SQL 实现这些功能。

1. 数据排名与分组

在大多数情况下，我们可能会对数值字段进行数据排名，而使用文本字段对数据进行分区。不过，这并非绝对，也可以对数值字段进行分区，对字母字段进行排序。

1.1 创建多个排名组和子组

假设财务总监要求提供一份报告，显示每个客户购买的每种汽车品牌的销售利润排名。以下是实现该功能的 SQL 查询：

WITH
AllSalesProfit_CTE (CustomerName, MakeName, 
ModelName, SalePrice, ProfitPerModel)
AS
(
SELECT       CustomerName, MakeName, ModelName
             ,SalePrice
             ,((SalePrice - 
                 (Cost + NVL(RepairsCost,0) 
                  + PartsCost + TransportInCost))
                / SalePrice) * 100
FROM        SalesByCountry
)

SELECT       CustomerName, MakeName, ModelName, 
ProfitPerModel, SalePrice
 , RANK() OVER 
                      (PARTITION BY CustomerName, 
MakeName 
                       ORDER BY ProfitPerModel 
DESC) AS SalesImportance
FROM        AllSalesProfit_CTE
ORDER BY    CustomerName, MakeName, 
SalesImportance;

此代码分为两部分：
- CTE 部分 ：计算每辆售出汽车的净利润。
- 查询部分 ：使用 CTE 中的数据对输出进行分类。

CTE 有助于定义整体查询所需的关键数据，虽然不是必需的，但它可以帮助隔离核心数据，然后对其进行分类和分层。

运行该查询的结果展示了如何将 CTE 与 RANK() 函数结合使用，创建多个排名组和子组。

在使用 CTE 对数据集进行排名时，有以下注意事项：
- 可以使用派生表代替 CTE，这两种技术本质上是可互换的，选择哪种方法主要取决于个人偏好。
- 如果 CTE 内的查询没有列名，则必须在 CTE 名称后的括号中为输出字段添加名称，这些添加在括号内的元素称为 CTE 头。

2. 按排名项过滤数据

销售总监想要找出每种颜色的汽车中最畅销的品牌。以下 SQL 代码可以提供此信息：

SELECT      Color, MakeName
FROM
             (
             SELECT       DISTINCT MakeName, Color
                         ,RANK() OVER (PARTITION BY MakeName
                                      ORDER BY SalePrice DESC) 
                                           AS ColorRank
             FROM        allsales

             ) SQ
WHERE       ColorRank = 1
ORDER BY MakeName;

该查询的工作原理是将问题分解为两个部分：
- 派生表部分 ：连接所有必需的表，生成一个销售列表，显示汽车品牌、颜色、销售价格，并使用 RANK() 函数对每种品牌下的每种颜色的销售进行排名。
- 外部查询部分 ：返回每种品牌下排名第一的汽车的品牌和颜色。

运行该查询的结果展示了如何在子查询中对 RANK() 函数的结果进行过滤。

在使用派生表时，需要注意以下几点：
- 必须为派生表中使用的所有列添加别名，这是 SQL 的要求，不仅仅是因为使用 RANK() 函数的输出进行数据过滤。如果忘记添加别名，会收到错误消息。
- 也可以使用 CTE 代替派生表来对数据进行初始排名，具体方法可根据个人喜好选择。

3. 按严格排名顺序分类数据

管理层要求对 Prestige Cars 的销售数据进行更复杂的分析，找出五种最畅销颜色的汽车中销售额最高的前十款。以下是实现该功能的 SQL 查询：

SELECT      Color, MakeAndModel, SalesImportance
FROM
(
SELECT       ST.Color, MakeName || ', ' || 
ModelName AS MakeAndModel
             , SalePrice
             , DENSE_RANK() OVER (ORDER BY SalePrice DESC) 
                           AS SalesImportance
FROM        allsales ST
INNER JOIN 
           (
            SELECT           Color, COUNT(*) AS 
NumberOfSales
            FROM             allsales

            GROUP BY         Color
            ORDER BY         NumberOfSales
            LIMIT 5
            ) CL
            ON CL.Color = ST.Color
) RK
WHERE       SalesImportance <= 10
ORDER BY    SalesImportance;

该查询的工作步骤如下：
- 筛选前五种颜色 ：首先，隔离出按数量计算的五种最畅销颜色，这一步限制了数据范围，只包括颜色属于这五种畅销颜色的车辆。
- 找出前十畅销款 ：然后，在颜色属于这五种畅销颜色的车辆中，找出销售额最高的前十款汽车。

此查询使用了嵌套派生表，具体操作如下：
1. 内层派生表 ：连接 Stock 和 SalesDetails 表，计算每种颜色的销售数量，选择前五种颜色。
2. 外层派生表 ：将内层派生表的结果与 allsales 表连接，计算每笔销售的排名。
3. 外部查询 ：选择排名在前十的销售记录。

注意，此 SQL 使用了 DENSE_RANK() 函数，与 RANK() 函数不同， DENSE_RANK() 函数在出现并列排名时不会跳过数字，而是顺序编号。

在处理此类复杂查询时，有以下要点：
- 可以将内层派生表嵌套在 CTE 中，然后使用 CTE 的输出进行最终查询，具体实现方式取决于个人偏好。
- 内层派生表可以在外部查询的 JOIN 子句或 WHERE 子句中使用，两种方法的结果相同。
- 不建议在外部派生表中使用 LIMIT 10 子句来获取前十的销售记录，因为它可能无法准确返回前十个值，只能返回指定数量的行。

4. 数据分段操作

在数据处理中，除了排名和筛选，还可以对数据进行分段操作，例如将数据分为十分位数、百分位数或五分位数等。

4.1 数据分为十分位数

销售总监希望将销售数据按销售价格分为十分位数，即每个组包含总数据集中十分之一的记录。以下是实现该功能的 SQL 查询：

SELECT       Color, MakeName || ', ' || ModelName 
AS MakeAndModel
             ,SalePrice
             ,NTILE(10) OVER (ORDER BY SalePrice DESC) AS 
SalesDecile
FROM        allsales
ORDER BY    SalesDecile, Color, MakeAndModel;

该查询的工作原理如下：
- 核心查询 ：这是一个简单的 SELECT 查询，连接必要的表并选择所需的字段。
- NTILE() 函数 ：该函数将数据集按指定的数量（这里是 10）进行分段，每个分段包含大致相同数量的记录。

运行该查询后，结果会显示每个记录所属的十分位数。

在使用 NTILE() 函数时，需要注意以下几点：
- 与 RANK() 和 DENSE_RANK() 函数类似， NTILE() 函数需要使用 OVER 关键字和 ORDER BY 子句来指定排序规则。
- NTILE() 函数还需要指定分段的数量，通过在函数后的括号中输入相应的数字来实现。
- NTILE() 函数也可以包含 PARTITION BY 子句，以进一步对数据进行分段，其工作方式与 RANK() 函数类似。

4.2 绘制百分位数的值

销售总监希望获取每个成本百分位数下车辆的总成本和相应的维修成本，以创建一个图表来显示汽车成本和维修成本之间的潜在相关性。以下是实现该功能的 SQL 查询：

WITH PercentileList_CTE
AS
(
SELECT     RepairsCost
          , Cost
          , NTILE(100) OVER (ORDER BY Cost DESC) AS Percentile
FROM      Stock
)
SELECT Percentile
          , SUM(Cost) AS TotalCostPerPercentile
          , SUM(RepairsCost) AS 
RepairsCostPerPercentile
              , SUM(RepairsCost) / SUM(Cost) AS 
RepairCostRatio
FROM      PercentileList_CTE
GROUP BY  Percentile
ORDER BY  RepairCostRatio DESC;

该查询分为两个阶段：
- CTE 阶段 ：使用 NTILE() 函数将每个销售分配到一个百分位数，并给出每个销售的维修成本。
- 查询阶段 ：对 CTE 的输出进行聚合，计算每个百分位数的总成本和总维修成本，并计算维修成本比率。

运行该查询后，可以得到每个百分位数下的总成本、总维修成本和维修成本比率。

在处理此类查询时，需要注意以下几点：
- Databricks 可以将数据导出为电子表格可打开的格式，也可以将许多应用程序直接连接到 Databricks 数据库。可以使用 Databricks 笔记本优化查询，然后将查询复制到用于显示和分析数据的应用程序中。
- 在将大型结果集从 Databricks 导出到其他应用程序时要小心，因为结果集可能包含数百万条记录，可能会超出目标工作站或电子表格的容量。

4.3 从特定五分位数中提取数据

销售总监希望了解占销售 20% - 40% 的客户的销售细节，找出该五分位数中最畅销的三个品牌。以下是实现该功能的 SQL 查询：

WITH Top20PercentSales_CTE
AS
(
SELECT       SalesDetailsID, MakeName, ModelName, 
SalePrice
             ,NTILE(5) OVER (ORDER BY SalePrice DESC) 
                  AS SalesQuintile
FROM        allsales
)
SELECT      MakeName, ModelName, SalePrice
FROM        Top20PercentSales_CTE CTE
WHERE       MakeName IN (
                         SELECT   MakeName
                         FROM     
Top20PercentSales_CTE
                         WHERE    SalesQuintile = 2
                         GROUP BY MakeName
                         ORDER BY SUM(SalePrice) 
DESC
                         LIMIT 3
                        )   
ORDER BY   SalePrice DESC;

该查询的工作原理如下：
- CTE 部分 ：使用 NTILE() 函数计算每个销售的五分位数。
- 外部查询部分 ：使用子查询找出第二五分位数中最畅销的三个品牌，然后输出这些品牌的销售细节。

运行该查询后，可以得到所需的销售细节。

在处理此类查询时，需要注意以下几点：
- CTE 被使用了两次，关键技术是先定义五分位数，然后从数据集中选择特定的五分位数。由于 CTE 已经连接了列出品牌、型号和销售价格所需的表，因此可以在主查询中重复使用，避免重写所有 SQL 代码。
- 可以根据需要隔离十分位数、百分位数或任何数据段，只需在 NTILE() 函数的括号中输入相应的数字即可。例如，要隔离十分位数，可以使用 NTILE(10) OVER (ORDER BY SalePrice DESC) AS SalesDecile 。

通过以上介绍，我们可以看到 SQL 在数据排名、筛选和分段方面的强大功能。合理运用这些技术，可以更好地分析和理解数据，为企业决策提供有力支持。

以下是一个简单的 mermaid 流程图，展示了数据分段操作的一般流程：

graph TD;
    A[选择数据] --> B[定义分段方式];
    B --> C[应用分段函数];
    C --> D[输出分段结果];

通过以上内容，我们详细介绍了如何使用 SQL 进行数据排名、筛选和分段操作，以及在操作过程中需要注意的事项。希望这些内容对您有所帮助。

SQL 数据排名、筛选与分段操作全解析

5. 操作总结与对比

为了更清晰地理解不同操作的特点和适用场景，我们对前面介绍的几种 SQL 操作进行总结和对比，如下表所示：
| 操作类型 | 主要功能 | 关键函数 | 注意事项 |
| ---- | ---- | ---- | ---- |
| 数据排名与分组 | 对数据按指定字段进行排名和分组 | RANK() 、 DENSE_RANK() | 可使用 CTE 或派生表；CTE 无列名时需添加 CTE 头 |
| 按排名项过滤数据 | 根据排名结果筛选数据 | RANK() | 派生表列需添加别名；可使用 CTE 替代派生表 |
| 按严格排名顺序分类数据 | 找出特定条件下排名靠前的数据 | DENSE_RANK() | 可嵌套派生表或使用 CTE；避免使用 LIMIT 取排名数据 |
| 数据分段操作 | 将数据分为不同的段，如十分位数、百分位数、五分位数 | NTILE() | 可包含 PARTITION BY 子句；注意数据导出问题 |

6. 实际应用案例分析

下面通过一个实际案例，综合运用上述 SQL 操作来解决复杂的数据处理问题。

假设我们有一个汽车销售数据库，包含 Sales 表（记录销售信息，如销售 ID、客户 ID、汽车品牌、型号、销售价格等）和 Customers 表（记录客户信息，如客户 ID、客户姓名、客户类型等）。现在需要找出每个客户类型中购买金额排名前三的客户及其购买信息。

以下是实现该功能的 SQL 查询：

WITH CustomerSales_CTE
AS
(
SELECT       C.CustomerType, C.CustomerName, SUM(S.SalePrice) AS TotalPurchase
             ,RANK() OVER (PARTITION BY C.CustomerType ORDER BY SUM(S.SalePrice) DESC) AS CustomerRank
FROM        Customers C
JOIN        Sales S ON C.CustomerID = S.CustomerID
GROUP BY    C.CustomerType, C.CustomerName
)
SELECT      CustomerType, CustomerName, TotalPurchase
FROM        CustomerSales_CTE
WHERE       CustomerRank <= 3
ORDER BY    CustomerType, CustomerRank;

该查询的工作流程如下：
1. CTE 部分 ：
- 连接 Customers 表和 Sales 表，根据客户类型和客户姓名分组。
- 计算每个客户的总购买金额。
- 使用 RANK() 函数对每个客户类型内的客户按总购买金额进行排名。
2. 外部查询部分 ：
- 从 CTE 中筛选出排名在前三的客户记录。
- 按客户类型和客户排名排序输出结果。

以下是该查询的 mermaid 流程图：

graph TD;
    A[连接 Customers 和 Sales 表] --> B[按客户类型和姓名分组];
    B --> C[计算总购买金额];
    C --> D[使用 RANK() 函数排名];
    D --> E[筛选排名前三的记录];
    E --> F[按客户类型和排名排序输出];

7. 性能优化建议

在处理复杂的 SQL 查询时，性能优化是非常重要的。以下是一些性能优化的建议：
- 合理使用索引 ：在经常用于 WHERE 子句、 JOIN 条件和 ORDER BY 子句的字段上创建索引，可以加快查询速度。
- 避免全表扫描 ：尽量使用索引来减少全表扫描的情况，特别是在处理大型数据集时。
- 优化子查询和派生表 ：如果子查询或派生表的结果集较大，可以考虑将其转换为 CTE 或使用临时表，以提高查询性能。
- 减少数据传输 ：只选择需要的字段，避免选择不必要的列，减少数据传输量。

8. 总结

通过本文的介绍，我们详细了解了 SQL 在数据排名、筛选和分段方面的操作方法，包括使用 RANK() 、 DENSE_RANK() 和 NTILE() 等函数，以及 CTE 和派生表的应用。同时，我们还通过实际案例展示了如何综合运用这些操作来解决复杂的数据处理问题，并给出了性能优化的建议。

在实际应用中，我们可以根据具体的业务需求选择合适的操作方法，合理运用 SQL 技术来分析和处理数据，为企业决策提供有力支持。希望本文的内容对您在 SQL 数据处理方面有所帮助。

以下是一个简单的列表，总结了本文的重点内容：
1. 数据排名与分组：使用 RANK() 或 DENSE_RANK() 函数，可结合 CTE 或派生表。
2. 按排名项过滤数据：在子查询中使用 RANK() 函数进行排名，然后根据排名结果筛选数据。
3. 按严格排名顺序分类数据：使用 DENSE_RANK() 函数，可嵌套派生表或使用 CTE。
4. 数据分段操作：使用 NTILE() 函数将数据分为不同的段。
5. 实际应用案例：综合运用上述操作解决复杂数据处理问题。
6. 性能优化建议：合理使用索引、避免全表扫描等。