识别缺失值需使用is null、trim()=''或nullif函数统一处理null和空字符串；2. 处理缺失值可采用coalesce填充默认值、update设置统计值（如平均值）或删除缺失行；3. 检测异常值可通过范围检查（如年龄合法性）、格式匹配（如正则或like）、逻辑校验（如日期顺序）和频率分析；4. 修正异常值常用trim/upper等函数标准化、case语句条件替换、cast类型转换；5. 利用窗口函数（如row_number、avg over）实现分组填充和去重，结合cte提升复杂清洗逻辑的可读性；6. 组合使用nullif与coalesce可高效处理多类型缺失数据，配合索引优化确保清洗效率。整个数据清洗过程需结合业务规则迭代执行，确保数据准确完整。
SQL数据清洗，说白了，就是在数据库里把那些不规矩、不完整的、甚至是错得离谱的数据找出来，然后让它们变得规规矩矩、完整可用。这过程里，我们主要面对的就是缺失值和异常值。我的经验是，SQL本身提供了相当多的武器来应对这些挑战，从简单的过滤到复杂的窗口函数，都能派上用场。核心思路就是利用SQL的强大查询和更新能力，识别问题数据，然后根据业务规则进行修正或剔除。
处理SQL中的缺失与异常数据，通常可以归结为识别、分析和修正三个阶段。具体到SQL操作，我们主要依赖以下几种策略：
对于缺失数据（通常表现为NULL值或空字符串）：
IS NULL
IS NOT NULL
TRIM(column_name) = ''
LENGTH(TRIM(column_name)) = 0
LEN
COALESCE(column_name, default_value)
ISNULL(column_name, default_value)
UPDATE table_name SET column_name = default_value WHERE column_name IS NULL;
DELETE FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;
UPDATE table_name SET column_name = (SELECT AVG(column_name) FROM table_name WHERE column_name IS NOT NULL) WHERE column_name IS NULL;
对于异常数据（包括格式不一致、超出范围、逻辑错误等）：
WHERE numeric_column < min_value OR numeric_column > max_value
WHERE string_column NOT LIKE '%[0-9]%'
~
REGEXP
WHERE start_date > end_date
WHERE quantity < 0
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) < threshold;
UPDATE table_name SET string_column = TRIM(LOWER(string_column));
REPLACE(string_column, 'old_text', 'new_text')
CAST(string_column AS INT)
CONVERT(VARCHAR, date_column, 120)
CASE
UPDATE table_name
SET status = CASE
WHEN status = 'active ' THEN 'Active'
WHEN status = 'inactive' THEN 'Inactive'
ELSE status
END;在我看来，数据清洗不是一蹴而就的，它更像是一个迭代的过程，需要结合业务理解和数据探索。
识别SQL中的缺失值，说实话，比很多人想象的要复杂一点。我们通常认为的缺失值就是
NULL
''
' '
识别缺失值： 最直接的方式当然是
IS NULL
-- 查找所有订单金额为空的记录 SELECT * FROM Orders WHERE OrderAmount IS NULL;
但如果数据录入不规范，可能会有空字符串：
-- 查找所有客户名是空字符串或只有空格的记录 SELECT * FROM Customers WHERE TRIM(CustomerName) = '' OR CustomerName IS NULL; -- 或者更简洁地利用NULLIF，将空字符串转为NULL再判断 SELECT * FROM Customers WHERE NULLIF(TRIM(CustomerName), '') IS NULL;
NULLIF
NULL
NULL
处理缺失值： 处理方式的选择，很大程度上取决于数据的重要性和业务场景。
直接过滤掉： 这是最简单粗暴的方法，如果缺失值占比很小，或者你只关心完整数据，那么直接在查询中排除它们是效率最高的。
-- 只统计有有效订单金额的订单 SELECT SUM(OrderAmount) FROM Orders WHERE OrderAmount IS NOT NULL;
填充默认值： 当缺失值需要被某个固定值替代时，
COALESCE
NULL
-- 查询订单金额，如果为空则显示为0.00 SELECT OrderID, COALESCE(OrderAmount, 0.00) AS DisplayAmount FROM Orders; -- 更新表中NULL值为特定默认值 UPDATE Orders SET OrderAmount = 0.00 WHERE OrderAmount IS NULL;
对于字符串，可以填充为 'N/A' 或 '未知'：
UPDATE Products SET ProductDescription = 'N/A' WHERE NULLIF(TRIM(ProductDescription), '') IS NULL;
基于统计值填充： 比如用平均值、中位数来填充数值型缺失值，或者用出现频率最高的值来填充类别型缺失值。这种方式在SQL中实现起来会稍微复杂一些，通常需要子查询或窗口函数。
-- 假设我们要用产品的平均价格填充缺失价格 UPDATE Products SET Price = (SELECT AVG(Price) FROM Products WHERE Price IS NOT NULL) WHERE Price IS NULL;
但要注意，这种全局平均值填充可能不够精确。更高级的做法是按产品类别进行分组平均填充，这就要用到窗口函数了：
-- 假设按 ProductCategory 分组填充平均价格
WITH AvgPrices AS (
SELECT
ProductID,
Price,
ProductCategory,
AVG(Price) OVER (PARTITION BY ProductCategory) AS CategoryAvgPrice
FROM Products
)
UPDATE P
SET P.Price = AP.CategoryAvgPrice
FROM Products P
JOIN AvgPrices AP ON P.ProductID = AP.ProductID
WHERE P.Price IS NULL;这有点像在说：“如果这个产品的价格不知道，那就看看它同类产品的平均价格是多少，然后填上去。”这通常比简单地填一个全局平均值要合理得多。
异常数据，这东西真是让人头疼。它不像是
NULL
检测异常数据：
范围检测： 这是最常见的。比如，年龄不可能为负数，库存量不可能为负数，日期不可能在未来太远。
-- 查找年龄小于0或大于150的用户 SELECT UserID, Age FROM Users WHERE Age < 0 OR Age > 150; -- 查找订单日期在未来或发货日期早于订单日期的订单 SELECT OrderID, OrderDate, ShipDate FROM Orders WHERE OrderDate > GETDATE() OR ShipDate < OrderDate; -- GETDATE()是SQL Server，其他数据库可能用 NOW() 或 CURRENT_DATE
格式与类型检测： 有时候数字字段存了字母，或者日期格式五花八门。
-- 查找看起来不像有效电话号码的记录（假设电话号码都是数字） SELECT CustomerID, PhoneNumber FROM Customers WHERE PhoneNumber LIKE '%[^0-9]%'; -- 查找包含非数字字符的电话号码 -- 对于更复杂的模式，可能需要正则表达式（如果数据库支持） -- WHERE PhoneNumber ~ '[^0-9]' -- PostgreSQL示例
如果一个文本字段应该只有特定的几个值（比如 '男', '女'），那就可以这样：
-- 查找性别字段不规范的记录
SELECT UserID, Gender
FROM Users
WHERE Gender NOT IN ('男', '女', 'Male', 'Female'); -- 考虑到多种可能规范频率与离群值检测： 某些数值可能远远偏离平均水平，或者某个分类值出现频率极低，这可能就是录入错误。
-- 查找销售额远超平均水平的订单（简单离群点检测） SELECT OrderID, SaleAmount FROM Sales WHERE SaleAmount > (SELECT AVG(SaleAmount) * 3 FROM Sales); -- 销售额超过平均值3倍的订单
当然，更严谨的离群点检测会用到统计学方法，比如Z-score或IQR，但这些在纯SQL中实现起来会比较复杂，通常需要多步操作或结合编程语言。
修正异常数据：
标准化与格式统一：
TRIM
LOWER
UPPER
REPLACE
-- 统一产品名称大小写并去除前后空格 UPDATE Products SET ProductName = TRIM(UPPER(ProductName)); -- 将地址中的“路”统一替换为“路” UPDATE Addresses SET Street = REPLACE(Street, '大道', '路'); -- 假设“大道”是错的，应为“路”
条件修正：
CASE
-- 修正年龄异常值：将负数年龄设为NULL，过大年龄设为100
UPDATE Users
SET Age = CASE
WHEN Age < 0 THEN NULL
WHEN Age > 150 THEN 100
ELSE Age
END;
-- 修正不规范的性别表示
UPDATE Users
SET Gender = CASE
WHEN Gender IN ('M', 'man') THEN 'Male'
WHEN Gender IN ('F', 'woman') THEN 'Female'
ELSE Gender
END;类型转换与验证： 当数据类型不匹配时，
CAST
CONVERT
-- 尝试将一个字符串列转换为日期，如果转换失败（异常数据），则设为NULL UPDATE Transactions SET TransactionDate = TRY_CAST(TransactionDateString AS DATE); -- TRY_CAST是SQL Server特有，转换失败返回NULL -- 其他数据库可能需要更复杂的逻辑，例如先判断是否是有效日期格式
说实话，在SQL里处理所有异常情况有点像在玩拼图，你需要把各种函数和逻辑碎片拼起来，才能得到你想要的结果。有时候，我甚至觉得这种“修修补补”的工作比从头写一个新查询更有挑战性。
提升数据清洗效率，不仅仅是让查询跑得更快，更重要的是让清洗逻辑更清晰、更可维护。我发现，善用SQL的一些高级特性，能让这个过程变得优雅很多。
窗口函数： 这是我个人最喜欢也觉得最强大的工具之一。它能让你在分组内部进行计算，而不需要聚合整个组，这在数据清洗中尤其有用。
ROW_NUMBER()
-- 查找并保留每个CustomerID下最新的一条订单记录
WITH RankedOrders AS (
SELECT
OrderID,
CustomerID,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) as rn
FROM Orders
)
SELECT OrderID, CustomerID, OrderDate
FROM RankedOrders
WHERE rn = 1;这比
GROUP BY
MAX()
MIN()
-- 假设我们想用部门的平均工资来填充缺失的工资
UPDATE Employees
SET Salary = (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentID = Employees.DepartmentID AND e2.Salary IS NOT NULL
)
WHERE Salary IS NULL;
-- 使用窗口函数可以更简洁地在子查询中完成
WITH EmployeeSalaries AS (
SELECT
EmployeeID,
Salary,
DepartmentID,
AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgDeptSalary
FROM Employees
)
UPDATE E
SET E.Salary = ES.AvgDeptSalary
FROM Employees E
JOIN EmployeeSalaries ES ON E.EmployeeID = ES.EmployeeID
WHERE E.Salary IS NULL AND ES.AvgDeptSalary IS NOT NULL; -- 确保有平均值可供填充LAG()
LEAD()
公共表表达式 (CTEs - WITH
WITH CleanedNames AS (
SELECT
UserID,
TRIM(UPPER(FirstName)) AS CleanedFirstName,
TRIM(UPPER(LastName)) AS CleanedLastName
FROM Users
),
ValidatedAges AS (
SELECT
UserID,
CASE
WHEN Age BETWEEN 0 AND 120 THEN Age
ELSE NULL -- 将异常年龄设为NULL
END AS ValidAge
FROM Users
)
SELECT
CN.UserID,
CN.CleanedFirstName,
CN.CleanedLastName,
VA.ValidAge
FROM CleanedNames CN
JOIN ValidatedAges VA ON CN.UserID = VA.UserID;这样，每一步清洗的目的都非常明确，排查问题也方便很多。我经常用CTE来构建一个“中间数据集”，这样可以避免写一堆嵌套的子查询，那看起来简直是噩梦。
NULLIF
COALESCE
-- 将空字符串和只有空格的字符串都视为NULL，并用'未知'填充 SELECT COALESCE(NULLIF(TRIM(CustomerName), ''), '未知') AS NormalizedCustomerName FROM Customers;
这行代码，在我看来，就是数据清洗中的一个“小确幸”，因为它简洁而强大。
索引和查询优化： 虽然这不直接是“清洗”方法，但一个效率低下的清洗查询，在面对大数据量时会让人崩溃。确保你的
WHERE
JOIN
总的来说，SQL在数据清洗方面提供了相当多的灵活性和功能。关键在于理解你的数据，然后选择最适合的工具。这就像是修补一件旧家具，你不能指望一个锤子解决所有问题，你需要各种工具，并且知道什么时候用哪一个。
以上就是SQL数据清洗的实用方法：如何在SQL中处理缺失与异常数据的详细内容，更多请关注php中文网其它相关文章！
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
广告Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
122
收藏
