SQL for Data Analysis - Lesson 2 - SQL JOIN

目录

正文

JOIN 简介

目前为止, 我们每次只处理一张表格, 然而SQL的强大之处在于它可以同时处理多张表格.

image.png

关系数据库指数据库中的表格相互关联, 它们含有相同的标识符, 从而能让人轻松整合多个表格中的信息

image.png

将所有公司数据, 包括采购交易, 员工满意度, 甚至库存等, 集中在同一个Excel数据集中, 其实并没有太大用处, 信息很多, 很难为不同类型的数据建立行列和结构, 而多个数据库能在各个表格中有序地组织数据, 使这些数据能够轻松被找到并使用, 在需要多种类型数据解决问题时, 还可以整合表格.

为何要将数据拆分为不同地表格?

要了解JOIN是什么以及它们的作用, 我们来看看Parch&Posey的订单表格

image.png

会发现订单中没有出现客户的名字, 相反表格在账户ID栏中用值来表示客户, 我们需要结合另外一张表格, 把数据与名字对应起来, 但是首先有个问题, 为什么这张表格中没有客户的名字? 将关系数据库做成这样, 有多种原因, 首先订单和账户是不同类型的对象, 分开的话会更容易组织, 其次, 多表格结构可以保证更快的查询.

账户表格和订单表格实际上记录的是不同类型的对象, Parch&Posey可能想要给每个公司一个账户, 并更新最新消息.

image.png

而另一方面, 订单就不会发生变化了, 一个客户可能有多个订单Parch&Posey不会去改变之前的订单, 而是会添加新订单, 因为对象运作不同, 所以分为不同的表格是合理的.

image.png

另外, 将账户和订单分开, 也是为了数据库修改数据的速度, 查询速度决定于你需要数据库读取的数据量, 以及需要进行的计算数量和类型.

那么如何将账户数据与订单数据连接起来呢?

image.png

你的第一个JOIN

和其他查询一样, 首先需要SELECT和FROM子句,注意，我们需要在 SELECT 语句中指定某列所来自的每个表格。
SELECT orders.*, accounts.*
FROM demo.orders
接下来是JOIN, 你可以把它想成是第二条FROM子句, 他可以确定我们希望建立JOIN的数据所在表格
JOIN demo.accounts
最后我们需要指出两张表格之间的关系, 可以通过写出ON中的逻辑语句来完成, ON 表示你想如何合并 FROM 和 JOIN 语句中的表格。ON 语句存储的是两个表格中相连的两列。此外，= 两边的列顺序并不重要.
ON orders.account_id = accounts.id

练习
1.尝试获取 accounts 表格中的所有数据，以及 orders 表格中的所有数据。

SELECT accounts.*, orders.*
FROM accounts
JOIN orders
ON orders.id = accounts.id;

2.尝试从 orders 表格中获取 standard_qty、gloss_qty 和 poster_qty，并从 accounts 表格中获取 website 和 primary_poc。

SELECT accounts.website, accounts.primary_poc, orders.standard_qty,orders.gloss_qty,orders.poster_qty
FROM accounts
JOIN orders
ON orders.id = accounts.id;

ERD回顾

实体关系图 (ERD) 是查看数据库中数据的常见方式, 它也是了解如何从多个表格中获取数据的关键要素。如下为 Parch & Posey handy 的 ERD, 在完成剩下的练习时可以参考。

image.png

表格与列
在 Parch & Posey 数据库中，有 5 个表格, PK 在每个表格中与第一列相关。PK 表示主键。每个表格都存在主键，它是每行的值都唯一的列。

键

主键 (PK)
主键是特定表格的唯一列。在我们的每个表格中是第一列，并且这些列都叫做 id，但是并非必须都要这样。通常，在大多数数据库中，主键是表格的第一列。

外键 (FK)
外键是另一个表格中的主键。我们可以在上一个 ERD 中看到，外键如下所示：

  • region_id
  • account_id
  • sales_rep_id
image.png

主键 - 外键关联性
在上图中，可以看出：

  1. region_id 是外键。
  2. region_id 与 id 相连 - 它是将这两个表格相连的主外键关系。
  3. Crow's foot 表示 FK 实际上可以出现在 sales_reps 表格中的很多行中。
  4. 虽然这一行告诉我们 PK 显示在此表格中 id 每行仅出现一次。

练习

image.png

JOIN 回顾

我们回顾下你编写的第一个 JOIN 语句。

SELECT orders.*
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

以下是这两个表格的 ERD：


image.png

注意

注意，我们的 SQL 查询包含两个想要连接的表格：一个来自 FROM，另一个来自 JOIN。然后在 ON 中，我们始终让主键等于外键：

如果我们按照以下方式连接任何两个表格。

image.png

查询代码如下:

在这个语句中，表格名称的顺序并不重要。因此，也可以写成 ON region.id = sales_reps.region_id

连接多个表格

可以利用同一逻辑连接多个表格。看看下面的三个表格。

image.png

代码
如果我们想连接所有这三个表格，我们可以采用相同的逻辑。

FROM web_events
JOIN accounts
ON web_events.account_id = accounts.id
JOIN orders
ON accounts.id = orders.account_id

现在，我们的 SELECT 语句可以从所有三个表格中获取数据。同样，JOIN 存储的是表格，ON 是让主键等于外键。

SELECT 语句将需要指定你想从中获取列的表格：

SELECT web_events.channel, accounts.name, orders.total

别名

运行JOIN时, 最轻松的方法就是给表格取别名, 比如将orders替代为o, 要给表格取别名, 只需在FROM的表格名称后面留个空格, 然后再输入想要的使用的别名, 最好时使用全小写字母并用下划线替代空格

SELECT o.*,
a.*
FROM orders o
JOIN accounts a
ON o.account_id = a.id;

当我们连接表格时，最好能为每个表格设置一个别名。你实际上在算术运算符部分见过类似的列名称别名。

示例：

FROM tablename AS t1
JOIN tablename2 AS t2
之前，你见过如上所示的语句：
SELECT col1 + col2 AS total, col3
经常你可能还会见到没有 AS 部分的语句。上述每个语句都可以改写为以下形式，结果依然相同：
FROM tablename t1
JOIN tablename2 t2
以及
SELECT col1 + col2 total, col3

练习
image.png

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

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

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

SELECT r.name region, s.name rep, a.name accounts
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
ORDER BY accounts;

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

SELECT r.name region, a.name accounts, o.total_amt_usd / (o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
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;

为何要执行不同类型的 JOIN

你连接的数据可能是一对一或一对多的关系, 也就是说一个账户可能只下过一个订单, 而另一个账户可能下过很多订单. 一些账户与Parch&Posey销售团队刚刚建立关系, 但还没有下达任何订单, 所以当我们连接这些表格时, 那没有订单的账户会是什么情况, 你可能选择保留它们或者把它们从你的结果集中删掉, 具体取决于你的目的. 比如说如果你只是简单地想把账户名和每个订单联系起来, 那可以排除这些没有订单的账户, 比如前面内容中的inner join, 如果你的目的时计算本地区所有的账户, 以及它们购买的纸张数量, 你可能就需要包括这些没有订单的账户. 这就需要用到OUTER JOIN

LEFT JOIN 和 RIGHT JOIN

回顾INNER JOIN

只返回两个表格中都有的行

韦恩图

image.png
image.png

中间重叠的部分代表满足ON子句的所有行, INNER JOIN 只会返回两个圆圈交叉部分的行, 在这个例子中两个表中都包含的行为1001, 1011, 1021.
而没有下达订单的新账户, 如果我们向显示订单表格中未出现的已有账户, INNER JOIN就不起作用了.

image.png

JOINS类型

如果想展示的数据并未同时存在与两张表格, 而只存在于其中一张表格. 将有三种连接类型实现.

image.png

黑色部分时不包括的行

每种JOIN都会显示INNER JOIN的所有结果行(黄色部分), 并额外包含一些其他行(蓝色部分), 所以OUTER JOIN的结果数至少与INNER JOIN 的结果数相同, 只要有相同的ON子句

LEFT JOIN

将INNER JOIN 转化为LEFT JOIN只需要在JOIN前写下LEFT, 这样就可以匹配右表所满足的ON子句结果, 同时还会返回左表中没有匹配项的所有结果

image.png

代码

image.png
RIGHT JOIN
image.png

ACCOUNTS表中有四个没有订单的账户,1031,1041,1051,1061. 意味着有四行数据不满足JOIN的条件, 因为现在时RIGHT JOIN 而不是INNER JOIN, 所以这几行也会显示在结果集的底部, 由于它们在左表中没有匹配的行, 所以左表的列中不会包含这些行的数据.

image.png

可以看出账户,1031,1041,1051,1061, 也出现在了JOIN后的表中, 不过在ORDERS中没有下订单, 所以它们的total列为空值, 叫做 NULL.

有些时候LEFT JOIN和RIGHT JOIN是可以互换的, 只需将FROM和JOIN子句的顺序进行更换, 很多时候大多数默认使用LEFT JOIN

image.png
注意

你可能见过以下 SQL 语法

LEFT OUTER JOIN

或

RIGHT OUTER JOIN

这些命令和我们在上个视频中学过的 LEFT JOIN 和 RIGHT JOIN 完全一样。

OUTER JOIN

最后一种连接类型是外连接，它将返回内连接的结果，以及被连接的表格中没有匹配的行。

这种连接返回的是与两个表格中的某个表格不匹配的行，完整的外连接用例非常罕见。

你可以在此处查看外连接示例，并在此处查看罕见使用情况说明。由于这种连接的使用情况很少见，因此我们将不消费时间讨论了。

和上面的相似，你可能还会看到 FULL OUTER JOIN，它和 OUTER JOIN 一样。

练习
image.png

JOIN 和过滤

JOIN的复杂性在于可以做各种不同类型的分析, 整合数据本身并不是它的最终目的, 它只是一种工具, 让你能筛选或整合一个扩充的信息集, 要得到你所需要的数据集, 要非常仔细的考虑如何筛选数据, 而使用JOIN的话, 你就有多种选择, 为了说明这一点, 现在让我们来看看由一个销售代表带来的所有订单.

假设你是Parch&Posey的一名销售经理, 你当然希望你的销售代表能轻松地找到自己完成的交易, 而不是要查阅所有订单, 回忆哪些是自己完成地, 订单表中没有销售代表ID, 所以不需要JOIN来得到此消息.

SELECT orders.*,
      accounts.*
  FROM demo.orders
  LEFT JOIN demo.accounts
         ON orders.account_id = accounts.id

筛选这类数据的第一种方法是我们非常熟悉地WHERE子句, 现在我们来找出整合后地订单和账户表格, 对结果集进行筛选

SELECT orders.*,
      accounts.*
  FROM demo.orders
  LEFT JOIN demo.accounts
         ON orders.account_id = accounts.id
  WHERE accounts.sales_rep_id = 321500

结果集中仅展示符合WHERE 字句标准地行

image.png

但是如果我们想要标记所有由321500代表完成地订单, 同时将所有其他地订单保留在结果集中呢? 我们可以通过ON子句进行筛选, 通过将WHERE改为AND, 将321500变成 ON子句的一个并列逻辑语句, 这样能够在连接前有效的对右边表格进行预筛选, 仅筛选处销售代表ID为321500的相关行.

SELECT orders.*,
      accounts.*
  FROM demo.orders
  LEFT JOIN demo.accounts
         ON orders.account_id = accounts.id
        AND accounts.sales_rep_id = 321500

也就是说就像在连接前而不是连接后使用WHERE 子句一样, 可以把它看成将订单连接至另外一个表格, 这个表格只包括最初账户表格某些行的子集.

问题

image.png

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

SELECT r.name region, s.name rep, a.name accounts
  FROM sales_reps s
  JOIN region r
    ON s.region_id = r.id
  JOIN accounts a
    ON a.sales_rep_id = s.id
 WHERE r.name = 'Midwest'
ORDER BY a.name

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

SELECT r.name region, s.name rep, a.name accounts
  FROM sales_reps s
  JOIN region r
    ON s.region_id = r.id
  JOIN accounts a
    ON a.sales_rep_id = s.id
 WHERE r.name = 'Midwest'
   AND s.name LIKE 'S%'
ORDER BY a.name

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

SELECT r.name region, s.name rep, a.name accounts
  FROM sales_reps s
  JOIN region r
    ON s.region_id = r.id
  JOIN accounts a
    ON a.sales_rep_id = s.id
 WHERE r.name = 'Midwest'
   AND s.name LIKE 'K%'
ORDER BY a.name;

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

SELECT r.name region, a.name accounts, o.total_amt_usd/(total+0.01) unit_price
    FROM region r
  JOIN sales_reps s
    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
WHERE o.standard_qty > 100;

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

SELECT r.name region, a.name accounts, o.total_amt_usd/(total+0.01) unit_price
    FROM region r
  JOIN sales_reps s
    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
WHERE o.standard_qty > 100
  AND o.poster > 50
ORDER BY   unit_price;

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

SELECT r.name region, a.name accounts, o.total_amt_usd/(total+0.01) unit_price
    FROM region r
  JOIN sales_reps s
    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
WHERE o.standard_qty > 100
  AND o.poster > 50
ORDER BY   unit_price DESC;

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

SELECT DISTINCT a.id, w.channel
FROM accounts a
RIGHT 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;

总结

主键和外键

你学习了在连接数据库中的表格时与主键和外键相关的关键要素：

  • 主键 - 对于表格中的每行都是唯一的。主键通常是数据库中的第一列（就像 Parch & Posey 数据库中每个表格的 id 列）。

  • 外键 - 是出现在另一个表格中的主键，允许行不是唯一的行。

数据库的数据设置非常重要，但通常不是数据分析师的职责。这一过程称为数据库规范化。

JOIN

在这节课，你学习了如何使用 JOIN 组合多个表格的数据。你将最常用到的三个 JOIN 语句为：

  1. JOIN - 一种 INNER JOIN，仅获取在两个表格中都存在的数据。
  2. LEFT JOIN - 用于获取 FROM 中的表格中的所有行，即使它们不存在于 JOIN 语句中。
  3. RIGHT JOIN - 用于获取 JOIN 中的表格中的所有行，即使它们不存在于 FROM 语句中。

还有几个没有讲解的高级 JOIN，它们仅适用于非常特定的情况。UNION 和 UNION ALL、CROSS JOIN 和比较难的 SELF JOIN。这些内容比较深奥，这门课程不会再过多介绍，但是有必要知道这些连接方法是存在的，它们在特殊情况下比较实用。

别名

你学习了可以使用 AS 或直接对表格和列设定别名。这样可以减少要输入的字符数，同时确保列标题可以描述表格中的数据。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 200,527评论 5赞 470
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,314评论 2赞 377
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 147,535评论 0赞 332
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,006评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,961评论 5赞 360
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,220评论 1赞 277
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,664评论 3赞 392
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,351评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,481评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,397评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,443评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,123评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,713评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,801评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,010评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,494评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,075评论 2赞 341

推荐阅读更多精彩内容

  • SQL for Data Analysis - Lesson 1 - 基本SQL
    目录 实体关系图(ERD) 项目介绍 课程大纲 简介为什么数据分析师也用 SQL？SQL 与 NoSQL为什么企业...
    IntoTheVoid阅读 1,354评论 1赞 3
  • 04 SQL的 JOIN 操作
    一些数据库之间是存在关联的，编写一个JOIN可以联结多个不同的数据库。 数据库规范化 在创建数据库时，一定要思考下...
    夏威夷的芒果阅读 1,168评论 0赞 2
  • T-SQL学习笔记分享
    2017/3/14 RDBMS:关系型数据库管理系统 关系模型独立于语言 SQL有几种不同类型的语言：数据定义语言...
    ancherl阅读 1,572评论 0赞 6
  • 6.4可爱的汤圆妹
    一早起来给汤圆妹绑了个头发，哈哈，那几根头发笑死我了，但确实可爱啊，头发细，很难长，但现在长大了，不想给她剃了，等...
    我是汤圆妈妈阅读 485评论 0赞 0
  • 父母之爱（微小说）
    想你的时候 妈妈说：“想你的时候，就一个人去逛街，想像着那些漂亮衣服穿在我女儿身上是什么样子的，这样我就开心了。”...
    narode阅读 526评论 0赞 0