【课程笔记】《Udacity数据分析（入门）》「纳米学位」——第

2020-02-26 本文已影响0人 Tensor麻麻麻

第3部分：数据分析入门

笔记有点乱，仅作为学习记录作为参考

数据分析入门 - 总共分为10部分：

数据分析过程
数据分析过程 - 案例研究1
数据分析过程 - 案例研究2
数据分析的编程工作流
项目：探索数据集
基本SQL
SQL JOIN
SQL 聚合
SQL字查询和临时表格
SQL 数据清理

一、数据分析过程

1、数据分析过程概述
提问——整理数据——探索性数据分析——得出结论——传达结果
包：Numpy 、Pandas 、 Matplotlib

1、阅读csv文件：
head()是一个有用的功能，可以在数据框上调用，用于显示前几行
一般显示前五行
df.tail()返回最后几行，但是也可以指定你希望返回的行数

也可以是别的数字

header = 定义表头

index_col =

索引 ➡️可单独用于进行多种操作，例如解析日期、填充空值、跳行等。

查看数据信息

df.shape
df.dtype

image.png

df.describe()

每列数据的有效描述性统计

描述信息

df.loc[] / df.iloc[]

两个基本没有区别，使用区别就是一个用名称索引，一个用数字索引。如下图：
下边两种用法答案是一样的⬇️

iloc与loc的区别

.to_csv()

保存新的csv文件

保存csv

df.isnull().any(axis = 0)   #查看是否有缺失值

df_08.isnull().any(axis = 0).sum()  #缺失值的数量

df.info()

处理数据

1、数据缺失：空值填充——以填充平均数为例

nean = df['某一列'].mean()     #求平均数
df['某一列'] = df['某一列'].fillna(mean)     #填充平均数 重新赋值变量

数据缺失处理

2、数据冗余：duplicated函数查看冗余行有哪些
🌟冗余数据整行完全一样

df.duplicated()  #查看冗余
sum(df.duplicated())   #计算一下有多少冗余
df.drop_duplicates(inplace = True)  去除冗余

🌟冗余数据不是整行相同，需根据列进行筛选，比如：‘ID’，‘name’等......

3、数据类型错误
不同情况比较多，需要开始什么数据类型，直接在google搜转换方法即可

4、重命名列

重命名某个单独的列.png

从名称中移除

更改名称、保存

2、数据分析过程：案例研究1

问题一：导入数据，发现格式有问题，是分隔符用了分号，需要单独定义一下分隔符；

定义分隔符
问题二：求唯一值&唯一值都是什么？

唯一值

unique与nunique的区别

问题三：用pandas画图

直方图
柱形图
饼图
散点图

#读取数据和导入包
import pandas as pd
import numpy as np
%matplotlib inline

#画直方图
df.hist(figsize = (20,20));  

#或者使用plot
df['列名'].plot(kind = 'hist',figsize = (20,20))
hist——直方图
bar——柱形图
pie——饼图
#hist 改为其他词语，使用方法同上（注意最后的分号；）

散点图

#绘制散点图等变量之间的关系图——注意最后的分号；
pd.plotting.scatter_matrix(df,figsize(20,20))；

#绘制散点图
df.plot(x = '自变量'，y = '因变量'，kind= ‘scatter’);

#绘制箱线图
df['列名'].plot(kind = 'box');

Groupby

#groupby函数的使用——根据quality列进行求平均值
df.groupby('quality').mean()

groupby函数的结合使用

# groupby函数根据quality和color列进行求平均值
df.groupby(['quality','color']).mean()

对酸度进行分级并创建新列

query

# selecting malignant records in cancer data
df_m = df[df['diagnosis'] == 'M']
df_m = df.query('diagnosis == "M"')

# selecting records of people making over $50K
df_a = df[df['income'] == ' >50K']
df_a = df.query('income == " >50K"')

看数据的相关度？？
先获取中位数，中位数下边的数据求平均数，中位数上边的数据求平均数。

导入的包都有哪些？

#导入函数包
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

matplotlib画图

/
/

3、数据分析过程：案例研究2

删除列

# 从 2008 数据集中丢弃列
df_08.drop(['Stnd', 'Underhood ID', 'FE Calc Appr', 'Unadj Cmb MPG'], axis=1, inplace=True)

# 确认更改
df_08.head(1)

重命名列（修改）

# 将销售区域重命名为特定区域
df_08.rename(columns = {'Sales Area':'Cert Region'})

# 确认更改
df_08.head(1)

重命名列：修改列名(下划线代替空格)

# 在 2008 数据集中用下划线和小写标签代替空格
df_08.rename(columns=lambda x: x.strip().lower().replace(" ", "_"), inplace=True)

# 确认更改
df_08.head(1)


# 确认 2008 和 2018 数据集的列标签相同
df_08.columns == df_18.columns

或者
# 确定所有的列标签都相同，如下所示
(df_08.columns == df_18.columns).all()

drop 函数

df[df.isnull()]  #返回的是个true或false的Series对象（掩码对象），进而筛选出我们需要的特定数据。
df[df.notnull()]

df.dropna()     #将所有含有nan项的row删除
df.dropna(axis=1,thresh=3)  #将在列的方向上三个为NaN的项删除
df.dropna(how='ALL')        #将全部项都是nan的row删除

检查列的数量值

# 检查 2008 cyl 列的值数量
df_08['cyl'].value_counts()

七、SQL JOIN

SQL JOIN 用法

还是拿这张图片来说：

PK 在每个表格中与第一列相关。PK 表示主键。每个表格都存在主键，它是每行的值都唯一的列。
外键 (FK)：外键是另一个表格中的主键

ERD

重命名——别名

下边两种表达是完全一样的；在表格名称后边加一个空格写上简写即可

1
2

练习JIOIN

1、为与 name=Walmart 相关的所有 web_events 创建一个表格。表格应该包含三列：primary_poc、事件时间和每个事件的channel（渠道）。此外，你可以选择添加第四列，确保仅选中了 Walmart 事件。

SELECT a.primary_poc, w.occurred_at, w.channel, a.name
FROM web_events w
JOIN accounts a
ON w.account_id = a.id
WHERE a.name = 'Walmart';

2、为每个 sales_rep 对应的region以及相关的accounts 创建一个表格，最终表格应该包含三列：区域名称、销售代表名称，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region_name,s.name sales_reps_name ,a.name accounts_name
FROM accounts a
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id
ORDER BY accounts_name

3、提供每个订单的每个region，以及 account和 unit price (total_amt_usd/total)。最终表格应该包含三列：区域名称、客户名称和订单单价。少数几个客户的总订单数为 0，因此我除以的是 (total + 0.01) 以确保不会除以 0。

SELECT r.name region_name,a.name accounts_name,o.total_amt_usd/(o.total+0.001) unit_price #加0.001是因为个别客户的total是0，0不能被除。
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id

JOIN :INNER JOIN & OUTER JOIN

INNER JOIN
模板：

SELECT 需要的列 ，用逗号隔开
FROM 外键的表格名称
JOIN 主键的表格名称
ON 外键表格.列名 = 主键表格.列名

因为INNER JONIN只能处理两个表格都有的数据，如果哪一列某个行并不是两个表都有，就需要用到OUTER JOIN
OUTER JOIN

LEFT �JOIN
RIGHT JOIN
FULL OUTER JOIN
模板

SELECT 需要的列 ，用逗号隔开
FROM 外键的表格名称
LEFT/RIGHT JOIN 主键的表格名称
ON 外键表格.列名 = 主键表格.列名

练习JOIN最后的检测

1、为每个sales_rep对应的region以及相关的accounts创建一个表格，这次仅针对 Midwest 区域。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region_name,s.name sales_reps_name ,a.name accounts_name
FROM accounts a
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id
AND r.name = 'Midwest'

4 、提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。为了避免除以 0 个订单，这里可以在分母上加上 0.01，即：(total_amt_usd/(total+0.01))。

SELECT r.name region_name,a.name accounts_name,total_amt_usd/(total+0.001) danjia
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id
AND o.standard_qty >100

5、提供每个订单的区域名称，客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对standard_qty超过 100 且poster_qty超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最低的单价在最之前排序。为了避免除以 0 个订单，这里可以在分母上加上 0.01，即：(total_amt_usd/(total+0.01))。


SELECT r.name region_name,a.name accounts_name,total_amt_usd/(total+0.001) danjia
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id
AND o.standard_qty >100
AND o.poster_qty >50
ORDER BY danjia;

6、
/提供每个订单的区域名称，客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对standard_qty超过 100 且poster_qty超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最高的单价在最之前排序。为了避免除以 0 个订单，这里可以在分母上加上 0.01，即：(total_amt_usd/(total+0.01))。/

SELECT r.name region_name,a.name accounts_name,total_amt_usd/(total+0.001) danjia
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id
AND o.standard_qty >100
AND o.poster_qty >50
ORDER BY danjia DESC

7 、
account id 为 1001 的客户使用了哪些不同的channel。最终表格应该包含 2 列：account和不同的channel。你可以尝试使用 SELECT DISTINCT 使结果仅显示唯一的值。

SELECT DISTINCT a.name, w.channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
WHERE a.id = '1001';

8、找出发生在 2015 年的所有订单。最终表格应该包含 4 列：occurred_at、account name、order total 和 order total_amt_usd。

SELECT w.occurred_at, a.name, o.total, o.total_amt_usd
FROM accounts a
JOIN orders o
ON o.account_id = a.id
JOIN web_events w
ON a.id = w.account_id
WHERE w.occurred_at BETWEEN '01-01-2015' AND '01-01-2016'
ORDER BY w.occurred_at DESC;

八、SQL 聚合

NULL
在 WHERE 条件中表示 NULL 时，我们写成 IS NULL 或 IS NOT NULL。我们不使用 =，因为 NULL 在 SQL 中不属于值。但是它是数据的一个属性。
COUNT
计算表格中的行数

SELECT COUNT(*)
FROM accounts

SUM
SUM 函数将忽略 NULL 值，计算这一列所有行的值

SUM函数

练习：
MIN & MAX
MIN 和 MAX 聚合函数也会忽略 NULL 值——从功能上来说，MIN 和 MAX 与 COUNT 相似，它们都可以用在非数字列上。MIN 将返回最小的数字、最早的日期或按字母表排序的最之前的非数字值，具体取决于列类型。MAX 则正好相反，返回的是最大的数字、最近的日期，或与“Z”最接近（按字母表顺序排列）的非数字值。

MAX MIN

AVG（平均值）
AVG 返回的是数据的平均值，即列中所有的值之和除以列中值的数量。该聚合函数同样会忽略分子和分母中的 NULL 值。
GROUP BY
GROUP BY 可以用来在数据子集中聚合数据。例如，不同客户、不同区域或
不同销售代表分组；
SELECT 语句中的任何一列如果不在聚合函数中，则必须在 GROUP BY 条件中；
GROUP BY 始终在 WHERE 和 ORDER BY 之间。
ORDER BY 有点像电子表格软件中的 SORT。

GROUP BY 函数

练习

/*
1、哪个客户（按照名称）下的订单最早？你的答案应该包含订单的客户名称和日期。
*/
SELECT a.name,w.occurred_at
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
ORDER BY occurred_at
LIMIT 1

/*

2、算出每个客户的总销售额（单位是美元）。答案应该包括两列：每个公司的订单总销售额（单位是美元）以及公司名称。
*/
SELECT a.name,
    SUM(total_amt_usd) AS total_usd
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY a.name

/*
3、最近的 web_event 是通过哪个渠道发生的，与此 web_event 相关的客户是哪个？你的查询应该仅返回三个值：日期、渠道和客户名称。
*/

SELECT a.name,w.occurred_at occurred,w.channel
FROM web_events w
JOIN accounts a
ON w.account_id = a.id
ORDER BY occurred DESC
LIMIT 1

/*
4、算出 web_events 中每种渠道的次数。最终表格应该有两列：渠道和渠道的使用次数。
*/
SELECT w.channel,COUNT(channel)
FROM web_events w
GROUP BY w.channel

/*
5、与最早的 web_event 相关的主要联系人是谁？
*/
SELECT a.primary_poc
FROM web_events w
JOIN accounts a
ON w.account_id = a.id
ORDER BY occurred_at
LIMIT 1

/*
6、每个客户所下的最小订单是什么（以总金额（美元）为准）。答案只需两列：客户名称和总金额（美元）。从最小金额到最大金额排序。
*/
SELECT a.name,MIN(o.total_amt_usd) usd
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY a.name
ORDER BY usd

/*
7、算出每个区域的销售代表人数。最早表格应该包含两列：区域和 sales_reps 数量。从最少到最多的代表人数排序。
*/
SELECT r.name,COUNT(s.id) AS  sales_reps_count
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
GROUP BY r.name
ORDER BY sales_reps_count

ORDEY 进阶知识
- 你可以同时按照多列分组，正如此处所显示的那样。这样经常可以在大量不同的细分中更好地获得聚合结果。
- ORDER BY 条件中列出的列顺序有区别。你是从左到右让列排序。
- 和 ORDER BY 一样，你可以在 GROUP BY 条件中用数字替换列名称。

练习题

/*1 、对于每个客户，确定他们在订单中购买的每种纸张的平均数额。结果应该有四列：客户名称一列，每种纸张类型的平均数额一列。*/
SELECT a.name,
    AVG(o.standard_qty) AS avg_s,
    AVG(o.gloss_qty) AS avg_g,
    AVG(o.poster_qty) AS avg_p
FROM orders o
JOIN accounts a 
ON o.account_id = a.id
GROUP BY a.name

/*对于每个客户，确定在每个订单中针对每个纸张类型的平均消费数额。结果应该有四列：客户名称一列，每种纸张类型的平均消费数额一列。*/
SELECT a.name,
    AVG(o.standard_amt_usd) AS avg_s,
    AVG(o.gloss_amt_usd) AS avg_g,
    AVG(o.poster_amt_usd) AS avg_p
FROM orders o
JOIN accounts a 
ON o.account_id = a.id
GROUP BY a.name

/*确定在 web_events 表格中每个销售代表使用特定渠道的次数。最终表格应该有三列：销售代表的名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。*/

SELECT a.primary_poc,w.channel,COUNT(w.channel)AS count_a
FROM web_events w
JOIN accounts a
ON w.account_id = a.id
GROUP BY a.primary_poc,w.channel
ORDER BY count_a DESC

/*确定在 web_events 表格中针对每个地区特定渠道的使用次数。最终表格应该有三列：区域名称、渠道和发生次数。按照最高的发生次数在最上面对表格排序。*/
SELECT r.name,w.channel,COUNT(w.id) AS count_w
FROM web_events w
JOIN accounts a
ON w.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
JOIN region r
ON s.region_id = r.id
GROUP BY r.name,w.channel
ORDER BY count_w DESC

以上为GROUP BY 部分

DISTINCT
可以将 DISTINCT 看做仅返回特定列的唯一值的函数。
- 练习

/*使用 DISTINCT 检查是否有任何客户与多个区域相关联？*/
SELECT DISTINCT a.id, r.id, a.name, r.name
FROM accounts a
JOIN sales_reps s
ON s.id = a.sales_rep_id
JOIN region r
ON r.id = s.region_id;
and

SELECT DISTINCT id, name
FROM accounts;

得到的行数相同，所以没有与多个区域相关联的客户

HAVING
HAVING 是过滤被聚合的查询的 “整洁”方式，但是通常采用子查询的方式来实现。本质上，只要你想对通过聚合创建的查询中的元素执行 WHERE 条件，就需要使用 HAVING。
HAVING一般是跟在GROUP BY 后边的
HAVING 的用法

练习

有多少位销售代表需要管理超过 5 个客户？
/*
SELECT s.id, s.name, COUNT(*) num_accounts
FROM accounts a
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.id, s.name
HAVING COUNT(*) > 5
ORDER BY num_accounts;*/

/*有多少个客户具有超过 20 个订单？*/
SELECT a.id,a.name,COUNT(*) number
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
GROUP BY 1,2
HAVING COUNT(*)>20
ORDER BY number

/*哪个客户的订单最多？*/
SELECT a.id, a.name, COUNT(*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY num_orders DESC
LIMIT 1;

/* 有多少个客户在所有订单上消费的总额超过了 30,000 美元？*/
SELECT a.name aname,SUM(o.total_amt_usd) num
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY aname
HAVING SUM(o.total_amt_usd)>30000
ORDER BY num

/* 哪个客户消费的最多？
*/
SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY total_spent DESC
LIMIT 1;

/* 哪个客户使用 facebook 作为与消费者沟通的渠道超过 6 次？
*/
SELECT a.id, a.name, w.channel,COUNT(*) count_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.id, a.name,w.channel
HAVING  w.channel='facebook' AND COUNT(*) > 6 
ORDER BY count_channel

哪个渠道是客户常用的

SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.id, a.name, w.channel
ORDER BY use_of_channel DESC
LIMIT 10;

以上是HAVING部分

DATE函数
- DATE_TRUNC函数：DATE_TRUNC 使你能够将日期截取到日期时间列的特定部分。常见的截取依据包括日期、月份和年份。
- DATE_PART函数： DATE_PART 可以用来获取日期的特定部分，但是注意获取 month 或 dow （day of week）意味着无法让年份按顺序排列。而是按照特定的部分分组，无论它们属于哪个年份。
  
  DATE
练习DATE

/*Parch & Posey 在哪一年的总销售额最高？数据集中的所有年份保持均匀分布吗？*/
SELECT DATE_PART('year',o.occurred_at) AS happen_year,SUM(o.total_amt_usd) num_usd
FROM orders o
JOIN accounts a
ON o.account_id= a.id
JOIN web_events w
ON w.account_id = a.id
GROUP BY DATE_PART('year',o.occurred_at)
ORDER BY num_usd DESC

答案：
SELECT DATE_PART('year', occurred_at) ord_year,  SUM(total_amt_usd) total_spent
FROM orders
GROUP BY 1
ORDER BY 2 DESC;


/*Walmart 在哪一年的哪一个月在铜版纸上的消费最多？
*/

SELECT DATE_TRUNC('month',o.occurred_at) AS happen_month,a.name,SUM(o.gloss_amt_usd) glo_num
FROM orders o
JOIN accounts a
ON o.account_id= a.id
WHERE a.name='Walmart' 
GROUP BY DATE_TRUNC('month',o.occurred_at),a.name 
ORDER BY glo_num DESC
LIMIT 1

答案：
SELECT DATE_TRUNC('month', o.occurred_at) ord_date, SUM(o.gloss_amt_usd) tot_spent
FROM orders o 
JOIN accounts a
ON a.id = o.account_id
WHERE a.name = 'Walmart'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

以上是DATE

CASE
- CASE 语句始终位于 SELECT 条件中。
- CASE 必须包含以下几个部分：WHEN、THEN 和 END。ELSE 是可选组成部分，用来包含不符合上述任一 CASE 条件的情况。
- 你可以在 WHEN 和 THEN 之间使用任何条件运算符编写任何条件语句（例如 WHERE），包括使用 AND 和 OR 连接多个条件语句。
- 你可以再次包含多个 WHEN 语句以及 ELSE 语句，以便处理任何未处理的条件。

CASE用法示例

练习

/*
我们想要根据相关的消费量了解三组不同的客户。最高的一组是终身价值（所有订单的总销售额）大于 200,000 美元的客户。第二组是在 200,000 到 100,000 美元之间的客户。最低的一组是低于 under 100,000 美元的客户。请提供一个表格，其中包含与每个客户相关的级别。你应该提供客户的名称、所有订单的总销售额和级别。消费最高的客户列在最上面。
*/
SELECT a.name aname,
        SUM(total_amt_usd) money,
        CASE WHEN SUM(total_amt_usd)>200000 THEN 'uper'
        WHEN SUM(total_amt_usd)>=100000 AND SUM(total_amt_usd)<=200000 THEN 'minddle'
        WHEN SUM(total_amt_usd)<100000 THEN 'under'
        END AS jibie
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY a.name
ORDER BY money DESC

/*
现在我们想要执行和第一个问题相似的计算过程，但是我们想要获取在 2016 年和 2017 年客户的总消费数额。级别和上一个问题保持一样。消费最高的客户列在最上面。
*/
SELECT DATE_PART('year',occurred_at) AS occ_time,
a.name aname,
        SUM(total_amt_usd) money,
        CASE WHEN SUM(total_amt_usd)>200000 THEN 'uper'
        WHEN SUM(total_amt_usd)>=100000 AND SUM(total_amt_usd)<=200000 THEN 'minddle'
        WHEN SUM(total_amt_usd)<100000 THEN 'under'
        END AS jibie
FROM orders o
JOIN accounts a
ON o.account_id = a.id
WHERE DATE_PART('year',occurred_at) >=2016
GROUP BY a.name,occ_time
ORDER BY money DESC

/*
我们想要找出绩效最高的销售代表，也就是有超过 200 个订单的销售代表。创建一个包含以下列的表格：销售代表名称、订单总量和标为 top 或 not 的列（取决于是否拥有超过 200 个订单）。销售量最高的销售代表列在最上面。
*/
SELECT s.name,COUNT(o.id),
        CASE WHEN COUNT(o.id)>200 THEN 'top'
         ELSE 'minddle'
         END AS jibie_yeji
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
GROUP BY s.name
ORDER BY count DESC
#值得注意的是，上述语句假定每个名称是唯一的，好几次都是这么假定的。否则需要根据名称和 ID 拆分表格。



/*
之前的问题没有考虑中间水平的销售代表或销售额。管理层决定也要看看这些数据。我们想要找出绩效很高的销售代表，也就是有超过 200 个订单或总销售额超过 750000 美元的销售代表。中间级别是指有超过 150 个订单或销售额超过 500000 美元的销售代表。创建一个包含以下列的表格：销售代表名称、总订单量、所有订单的总销售额，以及标为 top、middle 或 low 的列（取决于上述条件）。在最终表格中将销售额最高的销售代表列在最上面。根据上述标准，你可能会见到几个表现很差的销售代表！
*/
SELECT s.name,COUNT(o.id),SUM(o.total_amt_usd),
        CASE WHEN COUNT(o.id)>200 THEN 'top'
        WHEN SUM(o.total_amt_usd) > 750000 THEN 'top'
        WHEN COUNT(o.id) <= 200 AND COUNT(o.id) >= 150 THEN 'minddle'
       WHEN SUM(o.total_amt_usd) <= 750000 AND SUM(o.total_amt_usd) >= 500000  THEN 'minddle'
        WHEN COUNT(o.id) < 150 OR SUM(o.total_amt_usd) < 500000  THEN 'minddle' END AS jibie_yeji
FROM orders o
JOIN accounts a
ON o.account_id = a.id
JOIN sales_reps s
ON a.sales_rep_id = s.id
GROUP BY s.name
ORDER BY sum DESC

* SQL字查询和临时表格

这节课将重点讲解以下三项内容：

子查询
表格表达式
持久衍生表格
1、子查询
子查询

2、子查询（第二部分）

第一个子查询的结果是一个表格，如果子查询只返回一个值，则可以在逻辑语句中使用该值，例如 WHERE、HAVING，甚至 SELECT，该值可以嵌套在 CASE 语句中。

在条件语句中编写子查询时，不能包含别名（子查询会被当做单个值（或者对于 IN 情况是一组值），而不是一个表格。）
如果我们返回了整个列，则需要使用 IN 来执行逻辑参数。如果我们要返回整个表格，则必须为该表格使用别名，并对整个表格执行其他逻辑。
查找和最早订单同一月份的订单：
练习子查询

/*1、提供每个区域销售额 (total_amt_usd) 最高的销售代表的姓名。*/
 SELECT   sale_name,region_name,max_sale
 FROM 
   (SELECT region_name region_names,MAX(sale_all) AS max_sale
   FROM 
       (SELECT s.name sale_name,r.name region_name,SUM(o.total_amt_usd) sale_all
       FROM sales_reps s
       JOIN region r
       ON s.region_id = r.id
       JOIN accounts a
       ON a.sales_rep_id = s.id
       JOIN orders o
       ON o.account_id = a.id
       GROUP BY s.name,r.name
       ) csv1
   GROUP BY region_names
   ) csv2
JOIN 
   (SELECT s.name sale_name,r.name region_name,SUM(o.total_amt_usd) sale_all
   FROM sales_reps s
   JOIN region r
   ON s.region_id = r.id
   JOIN accounts a
   ON a.sales_rep_id = s.id
   JOIN orders o
   ON o.account_id = a.id
   GROUP BY s.name,r.name
   ) csv3
ON csv2.region_names = csv3.region_name AND csv2.max_sale = csv3.sale_all

#这道题目的难点在于把子查询JOIN在一起。两个在分别的表里

WITH
WITH 语句经常称为公用表表达式（简称 CTE）。

CTE使用示例

同样是这个题目，用WITH来解决

/*1、提供每个区域销售额 (total_amt_usd) 最高的销售代表的姓名。*/
WITH t1 AS (
SELECT s.name rep_name, r.name region_name, SUM(o.total_amt_usd) total_amt
FROM sales_reps s
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
JOIN region r
ON r.id = s.region_id
GROUP BY 1,2
ORDER BY 3 DESC),
t2 AS (
SELECT region_name, MAX(total_amt) total_amt
FROM t1
GROUP BY 1)
SELECT t1.rep_name, t1.region_name, t1.total_amt
FROM t1
JOIN t2
ON t1.region_name = t2.region_name AND t1.total_amt = t2.total_amt;

十、SQL 数据清理

学习本节的价值

清理和重新整理混乱的数据。
将列转换为不同的数据类型。
处理 NULL 的技巧。

我们查看三个新的函数：

LEFT 函数
RIGHT函数
LENGTH函数

使用方法如图

LEFT、RIGHT、LENGTH使用方法

练习

/*
对于公司名称（甚至名称的第一个字母）的作用存在颇多争议 - [https://www.entrepreneur.com/article/237643](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;

另外几个函数

POSITION
STRPOS
LOWER
UPPER
-POSITION 获取字符和列，并提供该字符在每行的索引。
-STRPOS 和 POSITION 提供的结果相同，但是语法不太一样，如下所示：STRPOS(city_state, ‘,’)。
-使用 LOWER 或 UPPER 让所有字符变成小写或大写。

STRPOS 和 POSITION 提供的结果相同，但是语法不太一样，如下所示：STRPOS(city_state, ‘,’)。

使用案例

练习

/*
使用 accounts 表格创建一个名字和姓氏列，用于存储 primary_poc 的名字和姓氏
*/

SELECT primary_poc,
LEFT(primary_poc,POSITION(' ' IN primary_poc)) AS name,
RIGHT(primary_poc,LENGTH(primary_poc)-POSITION(' ' IN primary_poc)) AS xing
FROM accounts

/*
现在创建一个包含 sales_rep 表格中每个销售代表姓名的列，同样，需要提供名字和姓氏列。
*/

SELECT name,
LEFT(name,POSITION(' ' IN name)) AS first_name,
RIGHT(name,LENGTH(name)-POSITION(' ' IN name)) AS xing
FROM sales_reps

CONCAT
Piping ||
这两个工具都能将不同行的列组合到一起。

使用方法

练习

/*
accounts 表格中的每个客户都想为每个 primary_poc 创建一个电子邮箱。邮箱应该是 primary_poc 的名字.primary_poc的姓氏@公司名称.com。
*/
WITH e1 AS
(SELECT primary_poc,name,LEFT(primary_poc,POSITION(' ' IN primary_poc)) AS first_name,RIGHT(primary_poc,LENGTH(primary_poc)-POSITION(' ' IN primary_poc)) AS xing
FROM accounts 
)
SELECT first_name,xing,name,CONCAT(first_name,'.',xing,'@',name,'.','com') AS email
FROM e1
#删掉公司名称中的空格⬇️

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;

新的数据处理功能，包括

TO_DATE
CAST
使用 :: 进行转型

用 CAST 将字符串改为日期。CAST 实际上可以用来更改各种列类型。像 CAST(date_column AS DATE) 将字符串改成日期。

TRIM可以用来删掉字符串开头和末尾的字符
- 将月份转换成数字
  
  将月份转换成数字

连接日期并转换格式

另外的新的函数

COALESCE
使用 COALESCE 来处理 NULL 值。
通常，COALESCE 返回的是每行的第一个非 NULL 值。

COALESCE的使用方法

#查看哪一行缺少数据
SELECT *
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;