识别缺失值需使用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
来筛选出含有或不含有NULL值的行。对于空字符串，则用
TRIM(column_name) = ''
或
LENGTH(TRIM(column_name)) = 0
(不同数据库函数可能略有差异，如SQL Server的
LEN
)。处理：填充默认值： 使用
COALESCE(column_name, default_value)
或
ISNULL(column_name, default_value)
（SQL Server特有）在查询时替换NULL。如果需要持久化到表中，则使用
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 max_value
。格式检查：
WHERE string_column NOT LIKE '%[0-9]%'
（检查非数字字符），或者使用正则表达式函数（如PostgreSQL的
~
或 MySQL的
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_nameSET status = CASE WHEN status = 'active ' THEN 'Active' WHEN status = 'inactive' THEN 'Inactive' ELSE statusEND;
隔离或标记： 不直接修正，而是将异常数据标记出来，或将其移动到单独的“问题数据”表中进行后续分析。
在我看来，数据清洗不是一蹴而就的，它更像是一个迭代的过程，需要结合业务理解和数据探索。
如何识别和处理SQL中的缺失值？
识别SQL中的缺失值，说实话，比很多人想象的要复杂一点。我们通常认为的缺失值就是
NULL
，但很多时候，空字符串
''
或者只有空格的字符串
' '
也代表着信息缺失。我习惯把它们都当作缺失值来处理，毕竟它们都不能提供有效信息。
识别缺失值：最直接的方式当然是
IS NULL
。
-- 查找所有订单金额为空的记录SELECT *FROM OrdersWHERE OrderAmount IS NULL;
但如果数据录入不规范，可能会有空字符串：
-- 查找所有客户名是空字符串或只有空格的记录SELECT *FROM CustomersWHERE TRIM(CustomerName) = '' OR CustomerName IS NULL;-- 或者更简洁地利用NULLIF，将空字符串转为NULL再判断SELECT *FROM CustomersWHERE NULLIF(TRIM(CustomerName), '') IS NULL;
NULLIF
这个函数非常实用，它会在两个表达式相等时返回
NULL
，否则返回第一个表达式。这对于统一处理空字符串和
NULL
非常有效。
处理缺失值：处理方式的选择，很大程度上取决于数据的重要性和业务场景。
直接过滤掉： 这是最简单粗暴的方法，如果缺失值占比很小，或者你只关心完整数据，那么直接在查询中排除它们是效率最高的。
-- 只统计有有效订单金额的订单SELECT SUM(OrderAmount)FROM OrdersWHERE OrderAmount IS NOT NULL;
填充默认值： 当缺失值需要被某个固定值替代时，
COALESCE
是我的首选。它会返回参数列表中第一个非
NULL
的表达式。
-- 查询订单金额，如果为空则显示为0.00SELECT OrderID, COALESCE(OrderAmount, 0.00) AS DisplayAmountFROM Orders;-- 更新表中NULL值为特定默认值UPDATE OrdersSET OrderAmount = 0.00WHERE OrderAmount IS NULL;
对于字符串，可以填充为 ‘N/A’ 或 ‘未知’：
UPDATE ProductsSET ProductDescription = 'N/A'WHERE NULLIF(TRIM(ProductDescription), '') IS NULL;
基于统计值填充： 比如用平均值、中位数来填充数值型缺失值，或者用出现频率最高的值来填充类别型缺失值。这种方式在SQL中实现起来会稍微复杂一些，通常需要子查询或窗口函数。
-- 假设我们要用产品的平均价格填充缺失价格UPDATE ProductsSET 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 PSET P.Price = AP.CategoryAvgPriceFROM Products PJOIN AvgPrices AP ON P.ProductID = AP.ProductIDWHERE P.Price IS NULL;
这有点像在说：“如果这个产品的价格不知道，那就看看它同类产品的平均价格是多少，然后填上去。”这通常比简单地填一个全局平均值要合理得多。
针对异常数据，SQL有哪些实用的检测与修正技巧？
异常数据，这东西真是让人头疼。它不像是
NULL
那样一眼就能看出来，它可能只是格式不对，或者值超出了常理。检测异常数据，我觉得更像是在数据里玩“大家来找茬”。
检测异常数据：
黑色全屏自适应的H5模板
黑色全屏自适应的H5模板HTML5的设计目的是为了在移动设备上支持多媒体。新的语法特征被引进以支持这一点，如video、audio和canvas 标记。HTML5还引进了新的功能，可以真正改变用户与文档的交互方式，包括：新的解析规则增强了灵活性淘汰过时的或冗余的属性一个HTML5文档到另一个文档间的拖放功能多用途互联网邮件扩展（MIME）和协议处理程序注册在SQL数据库中存
56 查看详情 
范围检测： 这是最常见的。比如，年龄不可能为负数，库存量不可能为负数，日期不可能在未来太远。
-- 查找年龄小于0或大于150的用户SELECT UserID, AgeFROM UsersWHERE Age 150;-- 查找订单日期在未来或发货日期早于订单日期的订单SELECT OrderID, OrderDate, ShipDateFROM OrdersWHERE OrderDate > GETDATE() OR ShipDate < OrderDate; -- GETDATE()是SQL Server，其他数据库可能用 NOW() 或 CURRENT_DATE
格式与类型检测： 有时候数字字段存了字母，或者日期格式五花八门。
-- 查找看起来不像有效电话号码的记录（假设电话号码都是数字）SELECT CustomerID, PhoneNumberFROM CustomersWHERE PhoneNumber LIKE '%[^0-9]%'; -- 查找包含非数字字符的电话号码-- 对于更复杂的模式，可能需要正则表达式（如果数据库支持）-- WHERE PhoneNumber ~ '[^0-9]' -- PostgreSQL示例
如果一个文本字段应该只有特定的几个值（比如 ‘男’, ‘女’），那就可以这样：
-- 查找性别字段不规范的记录SELECT UserID, GenderFROM UsersWHERE Gender NOT IN ('男', '女', 'Male', 'Female'); -- 考虑到多种可能规范
频率与离群值检测： 某些数值可能远远偏离平均水平，或者某个分类值出现频率极低，这可能就是录入错误。
-- 查找销售额远超平均水平的订单（简单离群点检测）SELECT OrderID, SaleAmountFROM SalesWHERE SaleAmount > (SELECT AVG(SaleAmount) * 3 FROM Sales); -- 销售额超过平均值3倍的订单
当然，更严谨的离群点检测会用到统计学方法，比如Z-score或IQR，但这些在纯SQL中实现起来会比较复杂，通常需要多步操作或结合编程语言。
修正异常数据：
标准化与格式统一：
TRIM
,
LOWER
,
UPPER
,
REPLACE
是我的老朋友。
-- 统一产品名称大小写并去除前后空格UPDATE ProductsSET ProductName = TRIM(UPPER(ProductName));-- 将地址中的“路”统一替换为“路”UPDATE AddressesSET Street = REPLACE(Street, '大道', '路'); -- 假设“大道”是错的，应为“路”
条件修正：
CASE
语句是修正异常数据的瑞士军刀，它能根据不同的条件执行不同的更新逻辑。
-- 修正年龄异常值：将负数年龄设为NULL，过大年龄设为100UPDATE UsersSET Age = CASE WHEN Age 150 THEN 100 ELSE AgeEND;-- 修正不规范的性别表示UPDATE UsersSET Gender = CASE WHEN Gender IN ('M', 'man') THEN 'Male' WHEN Gender IN ('F', 'woman') THEN 'Female' ELSE GenderEND;
类型转换与验证： 当数据类型不匹配时，
CAST
或
CONVERT
就派上用场了。
-- 尝试将一个字符串列转换为日期，如果转换失败（异常数据），则设为NULLUPDATE TransactionsSET TransactionDate = TRY_CAST(TransactionDateString AS DATE); -- TRY_CAST是SQL Server特有，转换失败返回NULL-- 其他数据库可能需要更复杂的逻辑，例如先判断是否是有效日期格式
说实话，在SQL里处理所有异常情况有点像在玩拼图，你需要把各种函数和逻辑碎片拼起来，才能得到你想要的结果。有时候，我甚至觉得这种“修修补补”的工作比从头写一个新查询更有挑战性。
如何利用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, OrderDateFROM RankedOrdersWHERE rn = 1;
这比
GROUP BY
配合
MAX()
或
MIN()
更灵活，因为你可以保留整行数据。
组内填充： 之前提到的按类别平均值填充，窗口函数就能很自然地实现。
-- 假设我们想用部门的平均工资来填充缺失的工资UPDATE EmployeesSET 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 ESET E.Salary = ES.AvgDeptSalaryFROM Employees EJOIN EmployeeSalaries ES ON E.EmployeeID = ES.EmployeeIDWHERE E.Salary IS NULL AND ES.AvgDeptSalary IS NOT NULL; -- 确保有平均值可供填充
序列检查：
LAG()
和
LEAD()
可以比较当前行与前一行或后一行的数据，这在检查时间序列数据（比如连续的异常值）时非常有用。
公共表表达式 (CTEs –
WITH
子句)：当清洗逻辑变得复杂时，CTEs 能极大地提高SQL的可读性和可维护性。你可以把复杂的清洗步骤拆分成多个逻辑块，每个块处理一部分数据，然后像乐高积木一样组合起来。
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.ValidAgeFROM CleanedNames CNJOIN ValidatedAges VA ON CN.UserID = VA.UserID;
这样，每一步清洗的目的都非常明确，排查问题也方便很多。我经常用CTE来构建一个“中间数据集”，这样可以避免写一堆嵌套的子查询，那看起来简直是噩梦。
NULLIF
和
COALESCE
的组合拳：这两个函数一起用，能非常优雅地处理多种形式的缺失。
-- 将空字符串和只有空格的字符串都视为NULL，并用'未知'填充SELECT COALESCE(NULLIF(TRIM(CustomerName), ''), '未知') AS NormalizedCustomerNameFROM Customers;
这行代码，在我看来，就是数据清洗中的一个“小确幸”，因为它简洁而强大。
索引和查询优化：虽然这不直接是“清洗”方法，但一个效率低下的清洗查询，在面对大数据量时会让人崩溃。确保你的
WHERE
子句和
JOIN
条件涉及的列有合适的索引。有时候，将复杂的清洗逻辑分步执行，或者在ETL工具中完成部分清洗，反而比在一个巨型SQL查询中完成所有事情更高效。
总的来说，SQL在数据清洗方面提供了相当多的灵活性和功能。关键在于理解你的数据，然后选择最适合的工具。这就像是修补一件旧家具，你不能指望一个锤子解决所有问题，你需要各种工具，并且知道什么时候用哪一个。
以上就是SQL数据清洗的实用方法：如何在SQL中处理缺失与异常数据的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/972466.html
微信扫一扫 
支付宝扫一扫 
