05 -数据分析与SQL- Lesson5 SQL数据清理

最新推荐文章于 2019-05-02 21:23:03 发布

Peter Huang的培训教案

最新推荐文章于 2019-05-02 21:23:03 发布

阅读量270

点赞数

分类专栏： Udacity SQL学习指导文章标签：培训教学

本文链接：https://blog.csdn.net/huangsheng2/article/details/86561851

版权

Udacity SQL学习指导专栏收录该内容

10 篇文章 1 订阅

订阅专栏

05 数据分析与SQL Lesson5 SQL数据清理

1.视频：SQL 数据清理简介

2.视频：LEFT 与 RIGHT

LEFT 从起点（或左侧）开始，从特定列中的每行获取一定数量的字符。正如此处看到的，你可以使用 LEFT(phone_number, 3) 获取电话号码中的前三位。
RIGHT 从末尾（或右侧）开始，从特定列中的每行获取一定数量的字符。正如此处看到的，你可以使用 RIGHT(phone_number, 8) 获取电话号码的最后 8 位。
LENGTH 提供了特定列每行的字符数。这里，我们可以使用 LENGTH(phone_number) 得出每个电话号码的长度。

对于公司名字的研究

4.解决方案：LEFT 与 RIGHT

在 accounts 表格中，有一个列存储的是每个公司的网站。最后三个数字表示他们使用的是什么类型的网址。此处给出了扩展（和价格）列表。请获取这些扩展并得出 accounts 表格中每个网址类型的存在数量。

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

对于公司名称（甚至名称的第一个字母）的作用存在颇多争议 - https://www.entrepreneur.com/article/237643 。请从 accounts 表格中获取每个公司名称的第一个字母，看看以每个字母（数字）开头的公司名称分布情况。

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

使用 accounts 表格和 CASE 语句创建两个群组：一个是以数字开头的公司名称群组，另一个是以字母开头的公司名称群组。以字母开头的公司名称所占的比例是多少？
- （为了可读性，缩进有所调整）

SELECT SUM(num) nums, SUM(letter) letters
FROM (SELECT name, 
    CASE WHEN LEFT(UPPER(name), 1) IN ('0','1','2','3','4','5','6','7','8','9') 
    THEN 1 ELSE 0 END AS num, 
    CASE WHEN LEFT(UPPER(name), 1) IN ('0','1','2','3','4','5','6','7','8','9') 
    THEN 0 ELSE 1 END AS letter
    FROM accounts) t1;

结论：有 350 个公司名称以字母开头，1 个公司以数字开头。因此有 350/351 的公司名称以字母开头，即百分比是 99.7%。

元音是指 a、e、i、o 和 u。有多少比例的公司名称以元音开头，以其他音节开头的公司名称百分比是多少？

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;

有 80 的公司名称以元音开头，271 的公司以其他音节开头。元音的比例是 80/351，或 22.8%。因此，有 77.2% 的公司名称没有以元音开头。

5.视频: POSITION、STRPOS 和 SUBSTR

POSITION: Provides the position of a sting counting from the left 获取字符和列，并提供该字符在每行的索引。第一个位置的索引在 SQL 中是 1。如果你之前学习了其他编程语言，就会发现很多语言的索引是从 0 开始。这里，你发现可以使用 POSITION(’,’ IN city_state) 获取逗号的索引。

PS: STRPOS 和 POSITION 的功能与定义相同：
STRPOS: Provides the position of a string counting from the left
在使用上有小区别，掌握一种就好

**需要注意：**STRPOS 和 POSITION 对于搜索的内容都是大小写敏感的，根据场景可能需要用 LOWER 和 UPPER 统一搜索内容的大小写。（LOWER and UPPER: Used to lowercase of capitalize all characters of a string.)
在这里插入图片描述
注：观察下右下角 city 的输出，多了一个“，”。要解决这个问题需要在最后的 LEFT 函数后面减1。（课程视频最后没有显示出来）

7.解决方案：POSITION、STRPOS 和

使用 STRPOS 的方法

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

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

使用 POSITION 的方法

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

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

PS：SQL中的注释方法

单行注释：–
多行注释：/…/

8.视频：CONCAT

CONCAT

CONCAT是组合字符串的函数（之前的EXCEL中也有，专门处理字符串）（CONCAT: Combines Values From Serveral columns into one column.)
CONCAT(first_name, ' ', last_name)

||（管道 Piping）

也可以用 || 将多个字符串链接起来:
first_name || ' ' || last_name

10.解决方案：CONCAT

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;

注意其中的 name 是指的 company name（在accounts中是存为name）

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;

注意区别是最后用了个REPLACE(name, ' ', '')将空格替换为空，所以结果中@后面的地址就没有带空格的了。
具体文档和其他扩展可以参考：PostgreSQL字符操作

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)
-- 下面最后我加了个 password 列名便于识别
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), ' ', '') AS password
FROM t1;

11.视频：CAST

在处理数据的时候，有时时间是以文本的方式提供的，为了能够处理，需要使用CAST函数进行转换，需要记住SQL的时间格式是：YYYY-MM-DD。（CAST: Allows us to change columns from one data type to another.）

这里重点看这3个语句：

使用DATA_PART拆分文字为数字DATE_PART('month', TO_DATE(month, 'month'))
使用CAST转换格式为日期CAST(date_column AS DATE)
也可以使用两个冒号完成date_column::DATE

另外还要注意阅读最后的“专家提示”，要点如下：

LEFT、RIGHT 和 TRIM 都仅用来选择特定的字符串元素。也就是说经过这3个函数处理以后，输出就会变为字符。
TRIM 函数的功能是去掉字符串开始和结束位置的空格。
链接的文档和上节的相同，如果要在SQL处理复杂的文字，请扩展阅读。

13.解决方案：CAST

Q1

SELECT *
FROM sf_crime_data
LIMIT 10;

观察输出就可以回答Q3，这里的时间格式是不对的。

Q4

SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2)) new_date
FROM sf_crime_data;

这里SUBSTR(date, 7, 4)的意识时说，对于data这个数据，从第7个开始，数4个数，就得到年了。后面的意思一样，得出月和年，并用-连接起来。为什么要连接？因为只有这样才符合SQL的日期格式，才能用下面的::DATE来进行字符格式转换到日期格式的操作。

14.视频：COALESCE

COALESCE可以用来处理NULL值。（COALESCE: Returns the first non-null value passed for each row.)这点在COUNT的时候会有区别（COUNT不计算空值）。

16.解决方案：COALESCE

Q1

SELECT *
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Q2

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;

Q3

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, o.standard_qty, o.gloss_qty, o.poster_qty, o.total, o.standard_amt_usd, o.gloss_amt_usd, o.poster_amt_usd, o.total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

Q4

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;

此处练习需要细心，并且理解COALESCE、LEFT JOIN等的位置信息是怎么实现的。

Peter Huang的培训教案

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
05 -数据分析与SQL- Lesson5 SQL数据清理

05 数据分析与SQL Lesson5 SQL数据清理文章目录05 数据分析与SQL Lesson5 SQL数据清理1.视频：SQL 数据清理简介2.视频：LEFT 与 RIGHT4.解决方案：LEFT 与 RIGHT5.视频: POSITION、STRPOS 和 SUBSTR7.解决方案：POSITION、STRPOS 和使用 STRPOS 的方法使用 POSITION 的方法8.视频：CON...
复制链接

扫一扫