【用于数据分析的 SQL（五）】【SQL 数据清理】

上一篇：【用于数据分析的 SQL（四）】【SQL 子槽和临时表】

一、 前言

清洁数据是数据分析的重要组成部分，学习如何使用SQL执行数据清理。

二、 LEFT、RIGHT和LENGTH

左从开头（或左侧）开始，在指定列中为每行提取指定的字符数。可以拉一个电话号码的前三位数字使用左（phone_number，3）。

右从末尾（或右侧）开始，在指定列中为每行提取指定的字符数。可以拉一个电话号码的最后八位数字使用右（phone_number，8）。

长度为指定列的每行提供字符数。可以用它来获得每个电话号码的长度为长度（phone_number）。

1.在帐目表中，有一个列，用于为每家公司保留网站。最后三位数字指定他们使用的网址类型。此处提供了扩展（和定价）列表。提取这些扩展并提供帐户表中每个网站类型中有多少个。

SELECT RIGHT(website, 3) AS domain, COUNT(*) num_companies
FROM accounts
GROUP BY 1
ORDER BY 2 DESC;

2.使用帐户表提取每个公司名称的第一个字母，以查看以每个字母（或数字）开头的公司名称的分布情况。

SELECT LEFT(UPPER(name), 1) AS first_letter, COUNT(*) num_companies
FROM accounts
GROUP BY 1
ORDER BY 2 DESC;

3.将元音视为"，"，"“和”"公司名称以元音开头的比例是多大比例的，其他名称的起点是哪个百分比？

SELECT SUM(vowels) vowels, SUM(other) other
FROM (SELECT name, CASE WHEN LEFT(UPPER(name), 1) IN ('A','E','I','O','U') 
                        THEN 1 ELSE 0 END AS vowels, 
          CASE WHEN LEFT(UPPER(name), 1) IN ('A','E','I','O','U') 
                       THEN 0 ELSE 1 END AS other
         FROM accounts) t1;

三、 POSITION和STRPOS

POSITION需要一个字符和一个列，并提供该字符为每行的索引。第一位置的指数在 SQL 中为 1。可以拉逗号的索引作为位置（’，’，city_state）。

STRPOS提供与位置相同的结果，但实现这些结果的语法不同：STRPOS（city_state，’，’）。

POSITION和STRPOS大小写敏感

1.使用该表创建第一和姓氏列，其中显示第一和姓氏。

SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name, 
RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name
FROM accounts;

2.现在看看你是否能对桌面上的每个代表做同样的事情。再次提供名字和姓氏列。

SELECT LEFT(name, STRPOS(name, ' ') -1 ) first_name, 
       RIGHT(name, LENGTH(name) - STRPOS(name, ' ')) last_name
FROM sales_reps;

四、 CONCAT和 ||

CONCAT（first_name," ",last_name）或 first_name || ’ ’ || last_name

1.表中的每个公司都希望为每个公司创建一个电子邮件地址。电子邮件地址应该是primary_poc姓氏primary_poc公司名称的第一个名称。accountsprimary_poc.@.com

WITH t1 AS (
 SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
 FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com')
FROM t1;

2.创建一个电子邮件地址，将工作，通过删除帐户中的所有空间

WITH t1 AS (
 SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
 FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', REPLACE(name, ' ', ''), '.com')
FROM  t1;

3.创建初始密码，他们将在第一次登录后更改该密码。第一个密码是名字的第一个字母（小写字母），然后是他们名字的最后一个字母（小写字母），他们姓氏的第一个字母（小写字母），他们姓氏的最后一个字母（小写字母），他们名字中的字母数，姓氏中的字母数，然后他们合作的公司的名称

WITH t1 AS (
 SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
 FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com'), LEFT(LOWER(first_name), 1) || RIGHT(LOWER(first_name), 1) || LEFT(LOWER(last_name), 1) || RIGHT(LOWER(last_name), 1) || LENGTH(first_name) || LENGTH(last_name) || REPLACE(UPPER(name), ' ', '')
FROM t1;

五、 COALESCE

一般来说，COALESCE返回每行传递的第一个非 NULL 值。

1.使用COALESCE填写列，其中填写表的NULL值

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, o.*
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

2.使用COALESCE填写每个qty和usd列，其中表中为0

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, COALESCE(o.standard_qty, 0) standard_qty, COALESCE(o.gloss_qty,0) gloss_qty, COALESCE(o.poster_qty,0) poster_qty, COALESCE(o.total,0) total, COALESCE(o.standard_amt_usd,0) standard_amt_usd, COALESCE(o.gloss_amt_usd,0) gloss_amt_usd, COALESCE(o.poster_amt_usd,0) poster_amt_usd, COALESCE(o.total_amt_usd,0) total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;