31、SQL高级计算与数据分段分类技巧

SQL高级计算与数据分段分类技巧

在数据处理和分析领域，SQL是一种强大的工具。本文将介绍一些SQL中的高级计算技巧以及数据分段分类的方法，帮助你更好地处理和分析数据。

1. 复杂计算：复合利息计算

在进行复合利息计算时，可以使用子查询来简化分析和编码。以下是一个示例代码：

FROM         allsales
WHERE        DATEDIFF(MONTH, DateBought, 
SaleDate) > 2
) SRC;

这个查询分为内查询和外查询两部分：
- 内查询 ：计算车辆的总成本，包括购买成本、备件成本和维修成本，并确定购买日期和销售日期之间的月数。使用 Stock 、 SalesDetails 和 Sales 表获取所需信息，通过 JOIN 操作将数据关联起来，并使用 WHERE 子句过滤出购买和销售时间间隔超过两个月的车辆。
- 外查询 ：显示内查询隔离出的两个字段（ InitialCost 和 MonthsSincePurchase ），并使用这两个字段作为核心元素计算复合利息。

复合利息的计算公式为：

CI = K * (1 + P/100)t

在SQL中，使用 POWER() 函数来实现指数运算，具体代码如下：

InitialCost * POWER(1 + (0.75 / 100)

最后，从复合利息中减去初始成本，得到实际利息金额，这样可以同时看到总成本和利息。

技巧和陷阱 ：虽然可以不使用子查询编写这样的查询，但子查询能带来更好的简化和更高的清晰度，值得额外的努力。

2. 使用计数表生成连续数字列表

当需要计算车辆的直线折旧时，可以使用计数表来生成连续的数字列表。以下是示例代码：

SELECT                  RowNo AS PeriodNumber
                        ,Cost
                        ,Cost / 5 AS 
StraightLineDepreciation
                        ,Cost - ((Cost / 5) * 
RowNo) AS RemainingValue
FROM                  allsales
CROSS JOIN

(
    SELECT 1 AS RowNo
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
) Tally
WHERE  StockCode = 'A2C3B95E-3005-4840-8CE3-
A7BC5F9CFB5F';

工作原理 ：
1. 计数表是一个包含一系列连续数字的记录集，通常从1开始，没有间隔。
2. 在这个例子中，使用 UNION ALL 操作创建一个包含5行的计数表。
3. 通过 CROSS JOIN 将计数表与 allsales 表关联起来，从而生成一个包含多个记录的表格。
4. 计算每年的折旧金额，即车辆成本除以年数。
5. 计算剩余价值，即车辆成本减去每年的累计折旧金额。

技巧和陷阱 ：
- 如果需要在折旧计算中包含残值成本，可以在每次使用 Cost 字段时减去残值成本。
- 计数表可以在许多情况下解决看似复杂的SQL问题，虽然不常用，但在某些情况下是有价值的资源。
- 可以使用 JOIN 代替 CROSS JOIN 。

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

当需要从数据集中生成完全随机的样本输出时，可以使用 TABLESAMPLE 函数。以下是示例代码：

SELECT       *
FROM         allsales TABLESAMPLE (5 PERCENT); 

工作原理 ：
1. TABLESAMPLE 函数将输出减少为查询中定义的原始表或表的完全随机样本。
2. 扩展 FROM 子句的步骤如下：
- 输入 FROM 关键字。
- 添加 TABLESAMPLE 关键字。
- 在括号中，添加所需的百分比样本，后跟 PERCENTAGE 关键字。

技巧和陷阱 ：
- 可以将 TABLESAMPLE 应用于 FROM 子句中使用的任何表，即使有多个表使用不同的连接类型。
- 可以使用 TABLESAMPLE 返回完全随机的行数。

4. 处理数字存储为文本的源数据

当源数据中的数字存储为文本时，可以使用 TRY_CAST() 函数将文本转换为数字。以下是示例代码：

SELECT       CountryName
            ,MakeName
            ,TRY_CAST(SalePrice AS NUMERIC(20,4)) 
AS SalePriceConverted
FROM        SalesText;

工作原理 ：
1. TRY_CAST() 函数尝试将一种数据类型转换为另一种指定的数据类型。
2. 在这个例子中，将 SalePrice 字段从文本转换为 NUMERIC(20,4) 类型。

技巧和陷阱 ： TRY_CAST() 函数可以帮助查询正常工作，但不能解决数据的底层问题。当遇到复杂数据导致SQL出错时，最好让IT专业人员进行数据转换。

5. SQL关键字总结

以下是本文中介绍的一些SQL关键字及其描述：
| 关键字 | 描述 |
| — | — |
| INT() | 用于在可能的情况下转换数据类型，可以将一种数字数据类型转换为另一种数字数据类型，或将实际上是数字的字符串转换为数字数据类型。 |
| TINYINT() | 将字符串或数字转换为 tinyint 数据类型。 |
| SMALLINT() | 将字符串或数字转换为 smallint 数据类型。 |
| BIGINT() | 将字符串或数字转换为 bigint 数据类型。 |
| FLOAT() | 将字符串或数字转换为 float 数据类型。 |
| DOUBLE() | 将字符串或数字转换为 double 数据类型。 |
| % (modulo) | 模函数，返回一个值除以另一个值后的余数。 |
| POWER() | 数学函数，用于将一个值提升到另一个值的幂。 |
| TABLESAMPLE | 从源表中返回记录的随机样本。 |
| TRY_CAST() | 尝试将一种数据类型转换为另一种指定的数据类型。 |

数据分段分类技巧

6. 按排名组织数据

在分析数据时，按排名组织数据可以帮助我们更好地了解数据的相对重要性。例如，销售总监想要一份显示2018年销售情况及其相对价值的列表，可以使用以下SQL：

SELECT       CustomerName
            ,MakeName || ', ' || ModelName AS 
MakeAndModel
            ,SalePrice
            ,RANK() OVER (ORDER BY SalePrice DESC) 
                   AS SalesImportance
FROM        allsales
WHERE       YEAR(SaleDate) = 2018
ORDER BY    SalesImportance;

工作原理 ：
1. 使用 allsales 表避免连接多个源表。
2. 添加 WHERE 子句过滤出2018年的销售数据。
3. 在 SELECT 子句中添加所需的字段。
4. 使用 RANK() 函数对销售数据进行排名，根据销售价格降序排列。
5. 使用 ORDER BY 子句按排名对数据进行排序。

技巧和陷阱 ：
- RANK() 函数是窗口函数的一种，用于将数据分组为行子集。
- 窗口函数中的 ORDER BY 子句与标准SQL的 ORDER BY 子句类似，但只影响窗口函数内的数据。
- 可以为 RANK() 函数的结果列添加别名，以避免在 ORDER BY 子句中重复完整的计算。
- 添加 RANK() 函数会自动对输出数据进行排序。

7. 创建多个排名组

当需要对产品销售进行分类，以了解哪些产品销售最好时，可以创建多个排名组。例如，CEO想要一份显示2017年按品牌排名的销售报告，可以使用以下SQL：

SELECT       MakeName || ', ' || ModelName AS 
MakeAndModel
            , SalePrice
 , RANK() OVER (PARTITION BY MakeName 
                          ORDER BY SalePrice DESC) AS 
SalesImportance
FROM        allsales
WHERE       YEAR(SaleDate) = 2017
ORDER BY    MakeName, SalesImportance;

工作原理 ：
1. 核心仍然是 RANK() 函数。
2. 添加 PARTITION BY 子句，按车辆品牌将数据划分为多个子组。
3. 在每个子组内，使用 ORDER BY 子句按销售价格对记录进行排名。

技巧和陷阱 ：
- 当数据排名出现平局时，Databricks会给平局的行相同的排名，并使下一个最低记录的排名降低两个或更多位置。

通过以上这些SQL技巧，你可以更高效地进行复杂计算和数据分段分类，从而更好地分析和处理数据。希望这些内容对你有所帮助！

SQL高级计算与数据分段分类技巧

8. 创建多个排名组和子组

在更复杂的数据分析场景中，可能需要创建多个排名组和子组。例如，要对不同品牌下不同车型的销售情况进行详细排名。假设我们有一个销售数据集，包含客户姓名、车辆品牌、车型、销售价格等信息，我们可以使用以下 SQL 来实现：

SELECT 
    MakeName,
    ModelName,
    SalePrice,
    RANK() OVER (PARTITION BY MakeName, ModelName ORDER BY SalePrice DESC) AS ModelSalesRank,
    RANK() OVER (PARTITION BY MakeName ORDER BY SalePrice DESC) AS MakeSalesRank
FROM 
    allsales
WHERE 
    YEAR(SaleDate) = 2019
ORDER BY 
    MakeName, ModelName, ModelSalesRank;

工作原理 ：
1. 首先使用 PARTITION BY 子句按品牌和车型将数据划分为多个子组。在每个品牌 - 车型子组内，使用 RANK() 函数根据销售价格降序对记录进行排名，得到 ModelSalesRank 。
2. 同时，按品牌再次使用 PARTITION BY 子句，对每个品牌下的所有车型记录根据销售价格降序排名，得到 MakeSalesRank 。
3. 最后使用 ORDER BY 子句按品牌、车型和车型销售排名对结果进行排序。

技巧和陷阱 ：
- 随着 PARTITION BY 子句中字段的增加，数据分组会更细，排名也会更精确，但同时也会增加查询的复杂度和计算量。
- 要注意排名的计算逻辑，确保符合实际业务需求。

9. 按排名项目过滤数据

当我们只需要关注排名靠前或靠后的部分数据时，可以按排名项目过滤数据。例如，我们只想要获取每个品牌下销售价格排名前 3 的车型信息，可使用以下 SQL：

WITH RankedSales AS (
    SELECT 
        MakeName,
        ModelName,
        SalePrice,
        RANK() OVER (PARTITION BY MakeName ORDER BY SalePrice DESC) AS SalesRank
    FROM 
        allsales
    WHERE 
        YEAR(SaleDate) = 2020
)
SELECT 
    MakeName,
    ModelName,
    SalePrice,
    SalesRank
FROM 
    RankedSales
WHERE 
    SalesRank <= 3
ORDER BY 
    MakeName, SalesRank;

工作原理 ：
1. 使用 WITH 子句创建一个名为 RankedSales 的临时结果集，在这个结果集中，按品牌对销售数据进行排名。
2. 从 RankedSales 中筛选出排名小于等于 3 的记录。
3. 最后按品牌和排名对结果进行排序。

技巧和陷阱 ：
- 使用 WITH 子句可以使查询结构更清晰，便于理解和维护。
- 要根据实际需求调整排名的筛选条件。

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

在某些情况下，需要按严格的排名顺序对数据进行分类。例如，将销售数据分为三个等级：高、中、低。可以使用以下 SQL：

SELECT 
    CustomerName,
    MakeAndModel,
    SalePrice,
    CASE 
        WHEN RANK() OVER (ORDER BY SalePrice DESC) <= (SELECT COUNT(*) * 0.2 FROM allsales) THEN '高'
        WHEN RANK() OVER (ORDER BY SalePrice DESC) <= (SELECT COUNT(*) * 0.7 FROM allsales) THEN '中'
        ELSE '低'
    END AS SalesLevel
FROM 
    (SELECT 
        CustomerName,
        MakeName || ', ' || ModelName AS MakeAndModel,
        SalePrice
    FROM 
        allsales
    WHERE 
        YEAR(SaleDate) = 2021
    ) SubQuery
ORDER BY 
    SalePrice DESC;

工作原理 ：
1. 首先在子查询中筛选出 2021 年的销售数据。
2. 使用 RANK() 函数对销售价格进行排名。
3. 使用 CASE 语句根据排名将销售数据分为高、中、低三个等级。
4. 最后按销售价格降序对结果进行排序。

技巧和陷阱 ：
- CASE 语句的条件判断顺序很重要，要确保逻辑正确。
- 计算排名比例时，要根据实际数据量和业务需求进行调整。

11. 将数据分段为十分位数

将数据分段为十分位数可以帮助我们更好地了解数据的分布情况。以下是一个将销售价格数据分段为十分位数的 SQL 示例：

SELECT 
    CustomerName,
    MakeAndModel,
    SalePrice,
    NTILE(10) OVER (ORDER BY SalePrice) AS Decile
FROM 
    (SELECT 
        CustomerName,
        MakeName || ', ' || ModelName AS MakeAndModel,
        SalePrice
    FROM 
        allsales
    WHERE 
        YEAR(SaleDate) = 2022
    ) SubQuery
ORDER BY 
    Decile, SalePrice;

工作原理 ：
1. 在子查询中筛选出 2022 年的销售数据。
2. 使用 NTILE(10) 函数将销售价格数据分为 10 个相等的部分，每个部分对应一个十分位数。
3. 最后按十分位数和销售价格对结果进行排序。

技巧和陷阱 ：
- NTILE() 函数可能会导致部分组的记录数不完全相等，特别是当记录数不能被指定的分段数整除时。
- 要根据实际需求调整分段数。

12. 绘制百分位数的值

绘制百分位数的值可以直观地展示数据的分布特征。例如，我们要获取销售价格的 25%、50% 和 75% 百分位数的值，可以使用以下 SQL：

SELECT 
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY SalePrice) OVER () AS P25,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SalePrice) OVER () AS P50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SalePrice) OVER () AS P75
FROM 
    allsales
WHERE 
    YEAR(SaleDate) = 2023;

工作原理 ：
- 使用 PERCENTILE_CONT() 函数计算指定百分位数的值。 WITHIN GROUP (ORDER BY SalePrice) 指定按销售价格排序， OVER () 表示对整个数据集进行计算。

技巧和陷阱 ：
- PERCENTILE_CONT() 函数计算的是连续百分位数，可能会返回非整数值。
- 要确保数据集中有足够的记录来准确计算百分位数。

13. 从特定五分位数中提取数据

从特定五分位数中提取数据可以帮助我们聚焦于数据的特定部分。例如，我们要获取销售价格处于第 3 个五分位数的数据，可使用以下 SQL：

WITH QuintiledSales AS (
    SELECT 
        CustomerName,
        MakeAndModel,
        SalePrice,
        NTILE(5) OVER (ORDER BY SalePrice) AS Quintile
    FROM 
        (SELECT 
            CustomerName,
            MakeName || ', ' || ModelName AS MakeAndModel,
            SalePrice
        FROM 
            allsales
        WHERE 
            YEAR(SaleDate) = 2024
        ) SubQuery
)
SELECT 
    *
FROM 
    QuintiledSales
WHERE 
    Quintile = 3
ORDER BY 
    SalePrice;

工作原理 ：
1. 使用 WITH 子句创建一个名为 QuintiledSales 的临时结果集，在其中使用 NTILE(5) 函数将销售价格数据分为 5 个相等的部分，得到每个记录的五分位数。
2. 从临时结果集中筛选出五分位数为 3 的记录。
3. 最后按销售价格对结果进行排序。

技巧和陷阱 ：
- 与 NTILE() 函数相关的注意事项同样适用于这里，如部分组记录数可能不相等。
- 要根据实际需求选择正确的五分位数。

14. 显示中位数的值

显示中位数的值可以帮助我们了解数据的中间水平。以下是一个显示销售价格中位数的 SQL 示例：

SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SalePrice) OVER () AS Median
FROM 
    allsales
WHERE 
    YEAR(SaleDate) = 2025;

工作原理 ：
- 使用 PERCENTILE_CONT(0.5) 函数计算销售价格的中位数。

技巧和陷阱 ：
- 同计算百分位数时一样，要确保数据集中有足够的记录来准确计算中位数。

总结

功能	关键字/函数	示例代码
复合利息计算	POWER()	InitialCost * POWER(1 + (0.75 / 100))
生成连续数字列表	计数表（ UNION ALL ）	SELECT 1 AS RowNo UNION ALL SELECT 2 UNION ALL ...
生成随机样本输出	TABLESAMPLE	SELECT * FROM allsales TABLESAMPLE (5 PERCENT);
文本转数字	TRY_CAST()	SELECT TRY_CAST(SalePrice AS NUMERIC(20,4)) FROM SalesText;
数据排名	RANK()	SELECT RANK() OVER (ORDER BY SalePrice DESC) FROM allsales;
数据分段	NTILE()	SELECT NTILE(10) OVER (ORDER BY SalePrice) FROM allsales;
百分位数计算	PERCENTILE_CONT()	SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SalePrice) OVER () FROM allsales;

通过掌握这些 SQL 高级计算和数据分段分类技巧，我们可以更深入地分析数据，挖掘数据中的有价值信息，为决策提供有力支持。希望大家在实际应用中能够灵活运用这些技巧，提升数据分析的效率和质量。

下面是一个简单的 mermaid 流程图，展示了按排名组织数据的基本流程：

graph TD;
    A[开始] --> B[选择数据集];
    B --> C[添加过滤条件];
    C --> D[使用 RANK() 函数排名];
    D --> E[按排名排序结果];
    E --> F[输出结果];
    F --> G[结束];

在实际使用这些技巧时，要根据具体的业务需求和数据特点进行灵活调整，同时注意避免一些常见的陷阱，以确保查询结果的准确性和可靠性。