超级会员免费看
SQL 复杂计算技巧：提升数据分析结果
在数据分析过程中，运用一些复杂的计算技巧可以显著提升分析结果的质量。本文将介绍一系列实用的 SQL 计算技巧，包括处理除零错误、避免重复子查询、去除计算中的小数、处理数值数据类型、创建金融计算等内容。
1. 计算数据集中每条记录的百分比
所有企业都喜欢稳定的客户，Prestige Cars 的 CEO 想知道每个客户的个人销售占比。可以使用以下 SQL 实现：
SELECT CustomerName
,FORMAT_NUMBER(
COUNT(CustomerName)
/ (SELECT COUNT(*)
FROM allsales)
, "0.00 %")
AS PercentageSalesPerCustomer
FROM allsales
GROUP BY CustomerName
ORDER BY CustomerName;
工作原理
：
- 在
AllSales
表中，按
CustomerName
字段分组，为每个客户生成一条记录。
- 选择客户名称和每个客户的记录数（使用
COUNT()
函数）。
- 创建一个子查询计算连接表集中的总记录数：
(SELECT COUNT(*) FROM allsales)
。
- 将每个客户的销售记录数除以总销售记录数，得到每个客户的个人销售占比，并格式化为百分比。
注意事项
：
在计算百分比时，如果需要将除法结果乘以 100，但由于数字是整数，结果仍不会显示小数。
2. 替换多个子查询
销售总监想查看 2017 年所有售出车辆的列表，包括每笔销售占该年销售总额的百分比以及与平均销售数字的差异。可以使用以下 SQL 实现：
SELECT MakeName, ModelName, SalePrice
,(SalePrice / CRX.TotalSales) * 100 AS PercentOfSales
,SalePrice - CRX.AverageSales AS DifferenceToAverage
FROM SalesByCountry
CROSS JOIN (SELECT SUM(SalePrice) AS TotalSales
,AVG(SalePrice) AS AverageSales
FROM SalesByCountry
WHERE YEAR(SaleDate) = 2017) AS CRX
WHERE YEAR(SaleDate) = 2017;
工作原理
：
- 首先，子查询计算 2017 年的总销售和平均销售：
(SELECT SUM(SalePrice) AS TotalSales
,AVG(SalePrice) AS AverageSales
FROM SalesByCountry
WHERE YEAR(SaleDate) = 2017) AS CRX
-
主查询从
SalesByCountry视图中提取 2017 年的车辆品牌、型号和销售价格。 -
使用
CROSS JOIN将主查询与子查询连接，确保每条记录都能使用子查询的输出，且连接不会过滤数据。 -
主查询的
SELECT子句扩展为使用交叉连接子查询的输出，计算每笔销售占总销售的百分比以及与平均销售价格的差异。
注意事项
：
- 子查询是标准的子查询，独立于主查询，若要限制子查询的输出，可能需要在主查询中应用相同的过滤条件。
-
CROSS JOIN
不需要
ON
子句，因为它会使用两个表或查询的所有字段。
- 在主查询的
SELECT
子句中引用子查询输出的字段时，需要使用子查询的别名（如本例中的
CRX
）。
3. 去除计算中的小数
如果交易高价值产品，可能不需要显示结果中的小数。财务总监想要一份显示车辆核心成本且无小数的列表，以避免四舍五入误差。可以使用以下 SQL 实现：
SELECT MakeName
,ModelName
,INT(Cost) AS Cost
,INT(RepairsCost) AS RepairsCost
,INT(PartsCost) AS PartsCost
,INT(SalePrice) AS SalePrice
FROM SalesByCountry;
工作原理
：
- 从
SalesByCountry
表中选择五个关键字段。
- 对包含数值的字段应用
INT()
函数，将其转换为整数，去除小数。
4. 数值数据类型
Databricks 提供了多种存储和处理数字的数值数据类型，各有其用途和限制，如下表所示：
| 数据类型 | 说明 |
| ---- | ---- |
| Tinyint | 1 字节无小数的整数值 |
| SmallInt | 2 字节无小数的整数值 |
| Int | 4 字节无小数的整数值 |
| Bigint | 8 字节无小数的整数值 |
| Decimal | 指定精度和小数位数（总允许数字和小数点后的数字） |
| Float | 4 字节单精度浮点数，可处理大数值，但存储可能不精确 |
| Double | 8 字节双精度浮点数，可处理巨大数值，但存储可能不精确 |
选择合适的数值数据类型可以减少数据在磁盘和内存中的占用空间，提高数据检索速度，对于数据库设计和维护非常重要。
5. 数值数据类型转换
可以使用一系列数值转换函数强制 Databricks 将一种数值数据类型转换为另一种。例如：
SELECT DOUBLE(RepairsCost) AS Repairs
FROM allsales
Databricks 提供的数值转换函数如下表所示：
| 函数 | 描述 |
| ---- | ---- |
| INT() | 将文本（尽可能）或数值转换为整数 |
| SMALLINT() | 将文本（尽可能）或数值转换为小整数 |
| TINYINT() | 将文本（尽可能）或数值转换为微小整数 |
| BIGINT() | 将文本（尽可能）或数值转换为大整数 |
| FLOAT() | 将文本（尽可能）或数值转换为单精度浮点数 |
| DOUBLE() | 将文本（尽可能）或数值转换为双精度浮点数 |
也可以使用
CAST()
函数进行转换，例如：
SELECT CAST(RepairsCost AS DOUBLE) AS Repairs
FROM allsales
CAST()
函数需要三个元素：要转换的数值字段名称、
AS
关键字和目标数据类型。
注意事项
：
- 当将数值数据转换为整数数据类型时，必须使用能够容纳最大转换值的整数类型。
- 只有在计算不需要完全精确时，才应转换为
Float
或
Real
数据类型，因为它们存储可能不精确，不适合商业或财务计算。
- 使用
CAST()
函数时，如果要转换的字段包含无法转换的数字，将返回
NULL
。
- 转换为
numeric
或
decimal
数据类型时，应指定总允许数字（包括小数）和小数位数。
6. 避免除零错误
财务部门想知道每辆车的零件成本与车辆成本的倍数关系。可以使用以下 SQL 避免除零错误：
SELECT ModelName
,Cost
,CASE
WHEN PartsCost != 0 THEN Cost / PartsCost
ELSE 0
END AS PartsCostMultiple
FROM allsales;
工作原理
：
- 查询连接
Stock
和
Make
表，获取车辆品牌和成本信息。
- 对
Cost
字段除以
PartsCost
字段进行计算。
- 使用
CASE
语句检查
PartsCost
字段的值，如果不为零，则进行除法计算；否则返回 0。
也可以使用
IFF()
函数处理除零错误，代码如下：
IFF(PartsCost != 0, Cost / PartsCost, 0 ) AS PartsCostMultiple
7. 使用模函数求除法余数
营销总监希望生成一份客户列表，这些客户将收到一封信，告知他们在下一次购买经典跑车时可获得特别客户折扣。她要求生成大约占客户群三分之一的列表。可以使用以下 SQL 实现：
SELECT CustomerID
FROM
(
SELECT CustomerID
,CustomerID % 3 AS ModuloOutput
,CASE
WHEN CustomerID % 3 = 1 THEN 'Winner'
ELSE NULL
END AS LuckyWinner
FROM Customer
) Rnd
WHERE LuckyWinner IS NOT NULL;
工作原理
：
- SQL 可以计算一个数除以另一个数的余数，这称为模运算，Databricks 中的模运算符为
%
。
- 定义一个包含数值的字段（如
CustomerID
），在字段名后添加模运算符和除数（如
CustomerID % 3
）。
- 添加逻辑判断，如果余数为 1，则标记该记录为“Winner”，并将逻辑选择作为子查询，最终只返回获胜记录。
注意事项
：
- 模运算符使用的数字可以是能被 Databricks 内部转换为数字的文本值。
- 计算中不一定需要显示模运算的输出。
8. 创建金融计算
财务总监希望将他的基本复利计算应用到 SQL 中，以便报告能显示未售出库存（“死库存”）给公司带来的成本。可以使用以下 SQL 实现：
SELECT
InitialCost
,MonthsSincePurchase
,(InitialCost * POWER(1 + (0.75 / 100), MonthsSincePurchase)) - InitialCost AS InterestCharge
,InitialCost * POWER(1 + (0.75 / 100), MonthsSincePurchase) AS TotalWithInterest
FROM
(
SELECT
DATEDIFF(MONTH, DateBought, SaleDate) AS MonthsSincePurchase
,(Cost + NVL(PartsCost, 0) + RepairsCost) AS InitialCost
此 SQL 不仅显示了利息费用，还显示了库存月数、成本价格以及包括利息在内的总成本。
综上所述，掌握这些 SQL 计算技巧可以帮助开发者更高效地处理数据分析任务，提高分析结果的准确性和可靠性。在实际应用中，根据具体需求选择合适的技巧和方法，能够更好地满足业务需求。
通过以上内容，我们可以清晰地看到这些 SQL 计算技巧在不同场景下的应用和优势。无论是处理复杂的数据分析任务，还是避免常见的计算错误，这些技巧都能发挥重要作用。希望本文能为你在 SQL 数据分析方面提供有价值的参考。
graph TD
A[开始] --> B[计算每条记录百分比]
B --> C[替换多个子查询]
C --> D[去除计算中小数]
D --> E[了解数值数据类型]
E --> F[进行数值数据类型转换]
F --> G[避免除零错误]
G --> H[使用模函数求余数]
H --> I[创建金融计算]
I --> J[结束]
以上是 SQL 复杂计算技巧的详细介绍，涵盖了从简单的百分比计算到复杂的金融计算等多个方面，希望能帮助你在数据分析中取得更好的效果。
SQL 复杂计算技巧：提升数据分析结果
9. 总结与应用建议
在实际的数据分析工作中，上述 SQL 计算技巧能发挥巨大的作用。以下是一些总结和应用建议：
-
选择合适的数据类型
：根据数据的范围和精度要求，选择合适的数值数据类型。例如，对于较小的整数，可以使用
Tinyint或SmallInt，以节省存储空间；对于需要精确计算的财务数据，应优先考虑Decimal类型。 -
避免重复计算
：当需要多次使用相同的计算结果时，可使用子查询或
CROSS JOIN来避免重复编写计算逻辑，提高代码的可读性和可维护性。 -
处理异常情况
：在进行除法运算时，务必考虑除零错误，可使用
CASE语句或IFF()函数进行处理，确保查询不会因异常情况而失败。 -
灵活运用函数
：合理使用
INT()、CAST()、MOD()等函数，实现数据类型转换、取整、求余数等操作，满足不同的业务需求。
10. 示例场景分析
为了更好地理解这些技巧的应用，下面通过一个具体的示例场景进行分析。
假设我们有一个汽车销售数据库，包含
SalesByCountry
表和
Customer
表，字段信息如下：
| 表名 | 字段名 | 说明 |
|---|---|---|
| SalesByCountry | MakeName | 汽车品牌 |
| ModelName | 汽车型号 | |
| SalePrice | 销售价格 | |
| Cost | 成本 | |
| RepairsCost | 维修成本 | |
| PartsCost | 零件成本 | |
| SaleDate | 销售日期 | |
| Customer | CustomerID | 客户 ID |
| CustomerName | 客户姓名 |
现在有以下业务需求：
- 计算每个客户的销售占比。
- 统计 2017 年各车型的销售百分比和与平均销售价格的差异。
- 显示车辆核心成本且无小数。
- 找出零件成本与车辆成本倍数关系，避免除零错误。
- 随机选取约三分之一的客户作为幸运客户。
- 计算未售出库存的复利成本。
针对这些需求，我们可以使用前面介绍的技巧编写相应的 SQL 代码：
-- 1. 计算每个客户的销售占比
SELECT
CustomerName,
FORMAT_NUMBER(
COUNT(CustomerName) / (SELECT COUNT(*) FROM allsales),
"0.00 %"
) AS PercentageSalesPerCustomer
FROM
allsales
GROUP BY
CustomerName
ORDER BY
CustomerName;
-- 2. 统计 2017 年各车型的销售百分比和与平均销售价格的差异
SELECT
MakeName,
ModelName,
SalePrice,
(SalePrice / CRX.TotalSales) * 100 AS PercentOfSales,
SalePrice - CRX.AverageSales AS DifferenceToAverage
FROM
SalesByCountry
CROSS JOIN
(
SELECT
SUM(SalePrice) AS TotalSales,
AVG(SalePrice) AS AverageSales
FROM
SalesByCountry
WHERE
YEAR(SaleDate) = 2017
) AS CRX
WHERE
YEAR(SaleDate) = 2017;
-- 3. 显示车辆核心成本且无小数
SELECT
MakeName,
ModelName,
INT(Cost) AS Cost,
INT(RepairsCost) AS RepairsCost,
INT(PartsCost) AS PartsCost,
INT(SalePrice) AS SalePrice
FROM
SalesByCountry;
-- 4. 找出零件成本与车辆成本倍数关系，避免除零错误
SELECT
ModelName,
Cost,
CASE
WHEN PartsCost != 0 THEN Cost / PartsCost
ELSE 0
END AS PartsCostMultiple
FROM
allsales;
-- 5. 随机选取约三分之一的客户作为幸运客户
SELECT
CustomerID
FROM
(
SELECT
CustomerID,
CustomerID % 3 AS ModuloOutput,
CASE
WHEN CustomerID % 3 = 1 THEN 'Winner'
ELSE NULL
END AS LuckyWinner
FROM
Customer
) Rnd
WHERE
LuckyWinner IS NOT NULL;
-- 6. 计算未售出库存的复利成本
SELECT
InitialCost,
MonthsSincePurchase,
(InitialCost * POWER(1 + (0.75 / 100), MonthsSincePurchase)) - InitialCost AS InterestCharge,
InitialCost * POWER(1 + (0.75 / 100), MonthsSincePurchase) AS TotalWithInterest
FROM
(
SELECT
DATEDIFF(MONTH, DateBought, SaleDate) AS MonthsSincePurchase,
(Cost + NVL(PartsCost, 0) + RepairsCost) AS InitialCost
FROM
SalesByCountry
);
11. 注意事项和常见错误
在使用这些 SQL 计算技巧时，还需要注意以下事项和常见错误：
-
数据类型转换错误
：在进行数据类型转换时，要确保目标数据类型能够容纳源数据的值，否则可能会出现错误。例如，将一个较大的整数转换为
SmallInt类型时，可能会导致数据溢出。 -
除零错误
：在进行除法运算时，一定要检查除数是否为零，否则会导致查询失败。可以使用
CASE语句或IFF()函数进行处理。 -
子查询性能问题
：过多使用子查询可能会影响查询性能，特别是在处理大量数据时。可以考虑使用
CROSS JOIN或其他优化方法来提高性能。 -
函数使用不当
：不同的函数有不同的用途和参数要求，使用时要仔细阅读文档，确保正确使用。例如，
CAST()函数在处理无法转换的数据时会返回NULL。
12. 未来发展趋势
随着数据分析需求的不断增长，SQL 作为一种强大的数据分析工具也在不断发展。未来，可能会出现以下发展趋势：
- 更强大的函数支持 ：数据库系统可能会提供更多的内置函数，以满足更复杂的计算需求，如机器学习相关的函数。
- 更好的性能优化 ：通过优化查询执行计划、索引技术等，提高 SQL 查询的性能，特别是在处理大数据集时。
- 与其他技术的融合 ：SQL 可能会与人工智能、机器学习等技术更加紧密地结合，实现更智能的数据分析和决策支持。
graph LR
A[业务需求] --> B[选择合适技巧]
B --> C[编写 SQL 代码]
C --> D[检查错误]
D --> E[优化性能]
E --> F[应用于实际业务]
总之，掌握 SQL 复杂计算技巧对于提升数据分析能力至关重要。通过合理运用这些技巧，可以更高效地处理数据，避免常见的错误，为业务决策提供有力支持。希望本文介绍的内容能帮助你在数据分析领域取得更好的成绩。
扫一扫
1281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
