实用SQL函数清单——教你用SQL清理数据

疫情的流行教会了我们很多清洁方面的知识。让我们继续把清理技巧用在我们的数据上！

本文将带你了解SQL的一些基本指令，并解释它的详细用法。如果你想了解更多数据分析相关内容，可以阅读以下这些文章：
手把手教你用Python创建SQL数据库！
六条鲜为人知的SQL技巧，帮你每月省下100小时！
数据分析新工具MindsDB–用SQL预测用户流失
DS数据科学家和DA数据分析师：要学习什么不同内容？

CAST

数据类型（Data Type）为数据库提供了信息，通过数据类型，我们可以知道要如何使用列中的信息，以及能对这些数据执行哪些操作。例如， COUNT函数就用于任何的数据类型，但SUM函数就只适用于数字类型的数据。

通常，在我们导入或创建数据库时，数字和日期会被视为字符串。而这限制了我们可以对它们执行的函数。为了改善这一点，我们可以这样修改数据类型：

CAST(column_name AS data_type)

或者：

column_name :: data_type

TRIM

有时，我们会遇到需要从字段中删除某些字符的情况。 TRIM函数可以用于删除字符串开头和结尾的字符。举个例子：

SELECT column,TRIM(position 'characters' FROM column) FROM Tablename

TRIM函数需要 3 个参数。首先是位置（position） ，是要从开头（’leading’）、结尾（’trailing’）还是两者（’both’）中删除字符。然后，你要指定要修剪的所有字符。单引号中包含的任何字符都会被从字符串的开头、结尾或两侧删除。最后一个参数是列名（column name）。

LEFT/RIGHT

使用这个函数，可以从字符串的左侧/右侧检索指定长度的字符,并将其显示为单独的字符串。

用法：

LEFT(column, number_of_characters)

SUBSTR

LEFT和RIGHT函数都会创建指定长度的子字符串，但它们只能从现有字符串的一侧开始。如果你想从字符串的中间开始，可以用SUBSTR 函数。方法如下：

SUBSTR(column, starting_character_position, number_of_characters) POSITION/ STRPOS

用POSITION和STRPOS函数，需要首先给到一个子字符串，它们可以返回这个子字符串首次出现在目标字符串中的位置（从左数）。

POSITION( substring IN column )

或者：

STRPOS( column , substring )

CACONCATST

我们还可以用CONCAT函数把多个列中的字符组合到一起。只需按照你想连接的值排序，并用逗号分隔它们：

CONCAT ( col1,col2,col3…..)

你也可以把固定编码值（Hardcoded Values）放在单引号内来连接，如下所示：

CONCAT( Numerator , '/' , Denominator )

CONCAT的目标也可以通过||来实现。

col1 || col2 || col3 ………

日期分析（Parsing Dates）

如果日期导入的时候是字符串格式，我们当然可以用CAST把它们转换为日期数据类型。但这只能在它用 SQL 可识别的格式时才有效。那如果日期是其他格式，比如 MM/DD/YYYY的时候怎么办？我们首先要把日期转换为SQL可接受格式的字符串，然后再进行转换。例如：

(SUBSTR(date, 7, 4) || ‘-’ || LEFT(date, 2) || ‘-’ || SUBSTR(date, 4, 2))::date AS cleaned_date

EXTRACT

很多时候，我们只需要使用日期的特定部分，比如本月的销售额，今年的入学人数等等。在这种情况下，我们用extract从日期列中提取信息：

EXTRACT('year' FROM cleaned_date) AS year,
EXTRACT('month' FROM cleaned_date) AS month,
EXTRACT('day' FROM cleaned_date) AS day,
EXTRACT('hour' FROM cleaned_date) AS hour,
EXTRACT('minute' FROM cleaned_date) AS minute, 
EXTRACT('second' FROM cleaned_date) AS second, 
EXTRACT('decade' FROM cleaned_date) AS decade,
EXTRACT('dow' FROM cleaned_date) AS day_of_week

NOW

SQL 提供了很多的函数来检索当前日期、时间和时间戳。有个有趣的小知识：它们可以在没有 FROM 的情况下打印出结果。

SELECT CURRENT_DATE AS date,CURRENT_TIME AS time,CURRENT_TIMESTAMP AS timestamp,LOCALTIME AS localtime,LOCALTIMESTAMP AS localtimestamp,CURRENT_TIME AT TIME ZONE 'PST' AS time_pst,NOW() AS now

处理缺失值

数据中最常见的问题就是缺失值。有很多方法可以解决这个问题，这里列出了一些：

• 删除缺失值所在的行
• 把缺失值替换为均值/中值/众数
• 分配一个独特的类别
• 预测缺失值

本文中，我们先把重点放在第一和第三点上。

Not Null

当我们想从结果中删除有空字段的行时，我们使用 NOT NULL 函数，它只会打印指定列中有特定值的行。

SLELCT *FROM TablenameWHERE column NOT NULL

COALESCE

COALESCE函数可以将 NULL 值替换为特定值，从而让它们不会妨碍进一步的分析。

COALESCE(column, new value)

以上就是本文介绍的几个十分有用的SQL数据清理函数，你有没有用过这些？哪些对你来说比较常用？欢迎在文章下方分享！

祝你数据清洁愉快！✨你还可以订阅我们的YouTube频道，观看大量数据科学相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/

原文作者：Aakriti Sharma
翻译作者：Jiawei Tong
美工编辑：过儿
校对审稿：Jiawei Tong
原文链接：https://medium.com/@aakriti.sharma18/data-cleaning-with-sql-eaab6d29d007