网站导航
> 技术文档 > SQL数据清洗实用函数——以具体场景为例详细学习

SQL数据清洗实用函数——以具体场景为例详细学习

网友: 技术文档 2025-07-26 20:44:49

数据清洗（Data Cleaning）一直是数据分析中重要且不可缺少的一环，接下来我们从清洗一份有关纳什维尔房屋情况的数据集出发，学习在SQL中数据清洗处理不同情况的实用函数。

练习数据集：PortfolioProjects/Nashville Housing Data for Data Cleaning.xlsx at main · AlexTheAnalyst/PortfolioProjects

一、标准化销售时间格式Standarlize SaleDate Format

数据集中的销售时间格式是‘September 26, 2016’这样的，我们需要把它修改为标准的年月日格式：2016-09-26，这里介绍CONVERT函数。

1、CONVERT函数

将一个数据类型转换为另一个数据类型，常用于将字符串转为日期、整数等。

基本语法结构：

CONVERT(目标数据类型, 表达式, [样式])

--标准化销售时间格式Standarlize SaleDate FormatSELECT SaleDate,CONVERT(Date,SaleDate)From [SQL Portfolio].[dbo].[Nashville Housing Data for Data Cleaning]

这里就是将CONVERT(Date,SaleDate)放在SELECT语句中，然后格式得到修改，修改完毕我们更新一些数据库，使用UPDATE...SET...函数。

2、UPDATE...SET...

用于更新表中的已有数据，是数据清理中修改脏数据、填补缺失信息的常用语句。

基本语法：

UPDATE 表名
SET 列名 = 新值

UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data for Data Cleaning]SET SaleDate=CONVERT(Date,SaleDate)

新值直接将CONVERT语句复制就行。

二、将拥有相同值的空缺内容以有值内容填补

将ParcelID相同但空缺PropertyAdress的ID填充相同地址Populate PropertyAdress

首先我们找出数据中为空值的PropertyAddress，然后使用JOIN将相同ParcelID（=）但不同UniqueID（)连接，这里的重点是如果是空值的话就使用非空值的内容填充，用到ISNULL函数。

3、ISNULL函数

替换字段中的 NULL 值，避免分析时因 NULL 导致的问题。

基本语法：

ISNULL(表达式, 替代值)

SELECT PropertyAddressFROM [SQL Portfolio].[dbo].[Nashville Housing Data]where PropertyAddress is nullSELECT a.ParcelID,a.PropertyAddress,b.ParcelID,b.PropertyAddress,ISNULL(a.PropertyAddress,b.PropertyAddress)FROM [SQL Portfolio].[dbo].[Nashville Housing Data] aJOIN [SQL Portfolio].[dbo].[Nashville Housing Data] b ON a.ParcelID=b.ParcelID and a. [UniqueID ]b.[UniqueID ]WHERE a.PropertyAddress is nullUPDATE aSET PropertyAddress=ISNULL(a.PropertyAddress,b.PropertyAddress)FROM [SQL Portfolio].[dbo].[Nashville Housing Data] aJOIN [SQL Portfolio].[dbo].[Nashville Housing Data] b ON a.ParcelID=b.ParcelID and a. [UniqueID ]b.[UniqueID ]WHERE a.PropertyAddress is null

这里的ISNULL(a.PropertyAddress,b.PropertyAddress)意思就是以b的地址替换掉a的地址。

替换完成后再次使用UPDATE...SET...函数更新一下。

三、将以逗号为分隔符的长字符段PropertyAddress中的内容分隔成三个不同部分

数据集中的房产地址是这样的‘1808  FOX CHASE DR, GOODLETTSVILLE’，这里是用逗号分成了具体地址和城市两个部分，为了更强的阅读性，我们将数据分成两个部分，这里使用SUBSTRING函数和CHARINDEX函数。

4、SUBSTRING函数

从一个字符串中提取子字符串，适用于清洗地址、编码等复杂字段。

基本语法：SUBSTRING(字符串, 起始位置, 长度)

如从‘上海，中国’提取出上海：

SELECT SUBSTRING(\'Shanghai, China\', 1, 8) AS City;

 5、CHARINDEX函数

查找某个子字符串在另一个字符串中的位置。配合SUBSTRING提取定界符前后的字段

基本语法：CHARINDEX(子字符串, 字符串)
如查找逗号在‘北京，中国’中的位置，这里会返回8：

SELECT CHARINDEX(\',\', \'Beijing, China\') AS CommaPosition;

 回到我们数据的处理，逻辑就是将房产地址从第一个字母开始，截取到逗号出现之前的字段就得到我们的具体地址，逗号出现之前（去掉逗号）的操作的对逗号所在的位置减一；

同样的，截取城市字段则从逗号出现之后（加一）的位置开始，到整个字符串长度结束，因为逗号后面就是城市。

--将以逗号为分隔符的长字符段PropertyAddress中的内容分隔成三个不同部分SELECT SUBSTRING(PropertyAddress, 1, CHARINDEX(\',\', PropertyAddress) - 1) AS Address, SUBSTRING(PropertyAddress, CHARINDEX(\',\', PropertyAddress)+ 1,LEN(PropertyAddress)) as AddressFROM [SQL Portfolio].[dbo].[Nashville Housing Data]WHERE PropertyAddress IS NOT NULL AND PropertyAddress  \'\' AND CHARINDEX(\',\', PropertyAddress) > 0

因为有些房产地址不是NULL而是空白的字符串所以要添加WHERE语句。

 ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data] ADD PropertySplitAddress NVARCHAR(255) UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data] SET PropertySplitAddress=SUBSTRING(PropertyAddress, 1, CHARINDEX(\',\', PropertyAddress) - 1) WHERE PropertyAddress IS NOT NULL AND CHARINDEX(\',\', PropertyAddress) > 0; ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data] ADD PropertySplitCity NVARCHAR(255) UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data] SET PropertySplitCity= SUBSTRING(PropertyAddress, CHARINDEX(\',\', PropertyAddress)+ 1,LEN(PropertyAddress)) SELECT *FROM [SQL Portfolio].[dbo].[Nashville Housing Data]

筛选出两个地址后我们用ALTER TABLE函数对表格进行修改，也就是添加具体地址和城市两个数据列。

6、ALTER TABLE函数

用于修改表结构，比如添加、删除列，修改列类型等。

--添加列ALTER TABLE 表名ADD 列名 数据类型;--删除列ALTER TABLE 表名DROP COLUMN 列名;--修改列ALTER TABLE 表名ALTER COLUMN 列名 新数据类型;

接下来就是添加新的数据列之后用 UPDATE...SET...更新，最后可以执行一下SELECT *查看数据是否有新增的数据列。

ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data]ADD PropertySplitAddress NVARCHAR(255)UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data]SET PropertySplitAddress=SUBSTRING(PropertyAddress, 1, CHARINDEX(\',\', PropertyAddress) - 1)WHERE PropertyAddress IS NOT NULL AND CHARINDEX(\',\', PropertyAddress) > 0;ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data]ADD PropertySplitCity NVARCHAR(255)UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data]SET PropertySplitCity= SUBSTRING(PropertyAddress, CHARINDEX(\',\', PropertyAddress)+ 1,LEN(PropertyAddress))

 

四、以更简单的方法对长字符段对OwnerAddress进行分隔

数据中房主的地址是这样的‘1808  FOX CHASE DR, GOODLETTSVILLE, TN‘，相比于刚刚使用的SUBSTRING和CHARINDEX，我们可以使用更简单的PARSENAME函数和REPLACE函数将房主地址按照地址、城市和州划分(Address,City,State)。

7、PARSENAME函数

用于按英文句号“.”分隔字符串，并提取指定部分，但最多只能提取4段，注意返回的顺序是逆序返回的！从后面开始数的

基本结构：

PARSENAME(\'字符串\', 索引部分)

如下面以句号分隔的四个部分提取倒数第二个部分：

SELECT PARSENAME(\'www.microsoft.com.cn\', 2) AS SecondPart; -- 返回 com

8、REPLACE函数 

用于替换字符串中指定子串。

REPLACE(字符串, 要替换的子串, 新子串)
如将字符串中的横杠-都改成空：

SELECT REPLACE(\'123-456-789\', \'-\', \'\') AS PhoneClean;

回到我们数据清洗，自然就简单啦!只需要在将房主信息中的逗号改为英文句号，再分别返回Address,City还有State

SELECT PARSENAME(REPLACE(OwnerAddress,\',\',\'.\'),3),PARSENAME(REPLACE(OwnerAddress,\',\',\'.\'),2),PARSENAME(REPLACE(OwnerAddress,\',\',\'.\'),1)FROM [SQL Portfolio].[dbo].[Nashville Housing Data]

再像上面一样增加新的三个数据列并且更新即可

ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data] ADD OwnerSplitAddress NVARCHAR(255)UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data] SET OwnerSplitAddress=PARSENAME(REPLACE(OwnerAddress,\',\',\'.\'),3)ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data] ADD OwnerSplitCity NVARCHAR(255)UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data] SET OwnerSplitCity= PARSENAME(REPLACE(OwnerAddress,\',\',\'.\'),2)ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data] ADD OwnerSplitState NVARCHAR(255)UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data] SET OwnerSplitState= PARSENAME(REPLACE(OwnerAddress,\',\',\'.\'),1)

 五、将SoldAsVacant里面的Y和N统一修改为Yes和No

数据列中的SoldAsVacant即’房屋在出售时是否为空置状态‘里面既有Y和N，也有Yes和No，这里我们少数服从多数将Y和N改为Yes和No

--使用DISTINCT和COUNT查询SoldAsVacant的情况SELECT DISTINCT(SoldAsVacant),Count(SoldAsVacant)FROM [SQL Portfolio].[dbo].[Nashville Housing Data]Group by SoldAsVacantorder by SoldAsVacant desc

 

9、CASE When函数

 用于实现类似“如果…则…”的条件判断逻辑，适用于分类、分组等

CASE WHEN 条件 THEN 结果1 WHEN 条件 THEN 结果2 ELSE 默认值END/END AS 列名称

 就是当=Y的时候就是Yes，=N到时候就是No，最后也是使用UPDATE...SET更新就行。

--将SoldAsVacant里面的Y和N统一修改为Yes和NoSELECT SoldAsVacant,CASE WHEN SoldAsVacant=\'Y\' THEN \'Yes\' WHEN SoldAsVacant=\'N\' THEN \'No\' ELSE SoldAsVacant ENDFROM [SQL Portfolio].[dbo].[Nashville Housing Data]UPDATE [SQL Portfolio].[dbo].[Nashville Housing Data]SET SoldAsVacant=CASE WHEN SoldAsVacant=\'Y\' THEN \'Yes\' WHEN SoldAsVacant=\'N\' THEN \'No\' ELSE SoldAsVacant END

六、删除表中的重复数据Remove Duplicates

一份到手的数据通常存在重复的数据，这是数据清洗中常见的问题，接下来我们使用窗口函数和CTE找出重复值个数，再使用DELETE进行删除。这里介绍一下ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)函数

10、ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)

用于先分组后排序，为分组后的每一行生成唯一编号，用于去重、排名、提取最新数据等。

基本结构：ROW_NUMBER() OVER(PARTITION BY 分组字段A ORDER BY 排序字段B)
是在 把数据按 A 分组后，按 B 排序，然后在每一组里从 1 开始编号。

在下面这段代码里面，分组的字段是多列，地块编号、房产地址、销售价格等都一样”，那其实就是把“重复数据”作为一组来看待，在DELETE前我们先查看rownum是2，表示重复项有 2 条，一条是“保留的”，一条是“多余的”。DELETE就是只删除重复的那几条（row_num > 1 的），保留第一条。

使用WITH...AS()的CTE格式对重复值进行删除。

--将表中重复的数据删除Remove DuplicatesWITH ROWNUMCTE AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY ParcelID, PropertyAddress, SalePrice, SaleDate, LegalReference ORDER BY UniqueID)row_numFROM [SQL Portfolio].[dbo].[Nashville Housing Data])DELETEFROM ROWNUMCTEWHERE row_num>1--order by PropertyAddress

六、删除不必要的数据Delete unused columns

数据清洗进行到尾声，目前我们已经将销售时间进行了标准化处理、填补了空值、对两个长字符串列使用两种方法进行了分隔形成更具体的可读性更高的新数据列、统一了Yes和No，还删除了表格中的重复值，现在我们对不需要使用的数据列进行删除。只需使用上述所讲的ALTER TABLE中的删除列DROP COLUMN函数即可。

--删除不必要的数据Delete unused columnsSELECT *FROM [SQL Portfolio].[dbo].[Nashville Housing Data]ALTER TABLE [SQL Portfolio].[dbo].[Nashville Housing Data]DROP COLUMN OwnerAddress,PropertyAddress,TAXDistrict,SaleDate

七、总结

回顾整个数据清洗过程，SQL 提供了大量灵活高效的函数和语句，帮助我们标准化格式、清理缺失值、拆分与合并字段、去除重复数据等。本文介绍的函数和语句包括：

  • 类型转换（CONVERT）

  • 更新与替换（UPDATE...SET、ISNULL、REPLACE）

  • 字符串处理（SUBSTRING、CHARINDEX、PARSENAME）

  • 条件逻辑判断（CASE WHEN）

  • 窗口函数与去重（ROW_NUMBER() OVER(PARTITION BY...ORDER BY...)）

  • 结构调整（ALTER TABLE、DROP COLUMN）

  • 数据删除（DELETE）

这些工具不仅可以提升数据清洗的效率，还能增强 SQL 脚本的可读性与可维护性。掌握它们，是每一位数据分析师、数据工程师乃至数据科学从业者必备的技能。 小伙伴们赶紧动手学习起来吧！

Thank you guys for reading!Hope you like my content , dont forget to hit a like and subscribe!

网络标签：

相关问题