SQL数据清理及准备 – 看这一篇就够了

SQL数据清理及准备 – 看这一篇就够了

在数据分析中,最好在分析之前确保您的数据是干净的,因为脏数据(dirty data)会导致结果不可靠,且具有误导性。如果你想了解更多关于数据分析的相关内容,可以阅读以下这些文章:
数据分析在Supply Chain方向有哪些应用?
顺利拿到数据分析师OFFER的作品集长什么样?
Excel小能手们,如何让自己数据分析及汇报的能力再上一层楼?
一篇文章带你了解探索性数据分析

为清理和准备数据进行分析所采取的步骤:

  • 检查重复数据并将其删除
  • 删除多余的空格
  • 在需要时分离或组合单元格中的值
  • 检查某些列中的值是否在适当的范围内
  • 检查异常值
  • 纠正拼写或输入错误的数据
  • 向数据中添加新的相关行
  • 如果缺少初始条件,则检查空值并根据其他条件检索记录。
  • 检查值是否遵循准确的模式。

从表中查找/删除重复数据:查找重复值的一种方法是按列的计数对数据进行分组,理想情况下该列应包含唯一值,然后使用HAVING子句返回计数大于1的所有记录。

请参见下面的示例:这里我按transaction_id列分组,因为理想情况下它应该包含唯一值。

SELECT transaction_id, COUNT(transaction_id)
FROM Transaction__Table
GROUP BY transaction_id
HAVING COUNT(transaction_id) > 1;

结果显示,id为7397和7400的交易详情有重复。

如果对表中的重复记录视而不见,分析结果将受损。

接下来,删除重复项:

SELECT *,
ROW_NUMBER() OVER(PARTITION BY transaction_id
ORDER BY transaction_id) AS Duplicate_row
FROM Transaction__Table
WITH CTE AS
(SELECT *,
ROW_NUMBER() OVER(PARTITION BY transaction_id
ORDER BY transaction_id) AS Duplicate_rows
FROM Transaction__Table)
DELETE FROM CTE WHERE Duplicate_rows >1;

现在重复值消失了。

确保数据类型准确:另一个重要步骤是确保使用的数据格式正确。如果在表中以文本类型存储了数值型数据,则无法对其执行计算。

显示错误信息,因为对其执行算术运算的变量存储为了nvarchar。所以必须将其转换为数值型数据。

使用ALTER TABLE、ALTER COLUMN语句做到这一点。

ALTER TABLE Transaction__Table
ALTER COLUMN list_price float

删除多余的空格:当查询每个品牌的平均价格时,注意到品牌列第4行中的品牌“So lex”。我已经确认没有“So lex”这样的名字,而是“Solex”。

为了解决这个问题,必须消除“So lex”中的空格。所以使用SELECT REPLACE子句来消除空格:

SELECT REPLACE(‘So lex’, ‘ ‘, ‘’)
Then I ran a nested query to Update the brand column:
UPDATE Transaction__Table
SET brand = (SELECT REPLACE(‘So lex’, ‘ ‘, ‘’))
WHERE brand = ‘So lex’;

再次运行平均价格查询以查看差异。

使用TRIM()函数也可以消除不需要的空格,但这只会删除开头和结尾的空格。LTRIM和RTRIM用于指定要删除的空格是开头的空格还是结尾的空格。

检查值范围:使用LENGTH和WHERE子句来确保transaction_id列中的值是四个字符长度。

SELECT *
FROM Transaction__Table
WHERE LEN(transaction_id) <> 4;

返回了字符长度为7的一行数据,已确认正确的transaction_id是“7405”,因此继续使用UPDATE子句修复此问题。

UPDATE Transaction__Table
SET transaction_id = 7405
WHERE transaction_id = 7405675;

检查正则表达式:进一步检查以确保某些值遵循正则表达式。使用 WHERE LIKE子句来执行此操作。

SELECT *
FROM Transaction__Table
WHERE transaction_id NOT LIKE ‘7%’;

检查缺失值:使用IS NULL运算符来检查相关列中的缺失值。

SELECT *
FROM Transaction__Table
WHERE transaction_id IS NULL;

transaction_id列中没有空值,继续检查customer_id列中的空值。

这将返回2行空值。我可以决定删除具有空值的行,但这将被忽略,因为它不会影响分析。结合名字和姓氏列来创建一个唯一值来识别每个客户,确保每个客户的名字和姓氏都是唯一的。

SELECT first_name, last_name
FROM Transaction__Table
GROUP BY first_name, last_name
HAVING COUNT(*)>1;

这表明名字和姓氏列仅包含唯一值。

因此,继续合并first和last列,以创建一个称为full name的唯一列。

此代码返回我想要的表。现在已为数据分析做好准备,因此将此查询结果导出到所需的BI工具。

清理SQL中的数据是必要的,尤其是在处理非常大的数据集时。大多数功能也可以用Excel来执行。然而,使用Excel处理大数据会减慢这个过程。作为一名数据分析师,无论您多么精通 Microsoft Excel,当然这也是一个很好的工具,您都必须提高SQL技能,以减缓一些压力和节省时间。你还可以订阅我们的YouTube频道,观看大量大数据行业相关公开课:https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ;在LinkedIn上关注我们,扩展你的人际网络!https://www.linkedin.com/company/dataapplab/

原文作者:Kelechi Ogbogu
翻译作者:明慧
美工编辑:过儿
校对审稿:Miya
原文链接:https://medium.com/@kelechiogbogu/data-cleaning-and-preparation-with-sql-f5f7e539808