07 数据分析与SQL Lesson7 (选修) SQL高级并集与性能优化


文章目录

  • 07 数据分析与SQL Lesson7 (选修) SQL高级并集与性能优化
  • 2.文本+图片：全连接（FULL OUTER JOIN）
  • 4.解决方案：全连接（FULL OUTER JOIN）
  • 7.练习：带比较运算符的并集
  • 8.视频：自并集
  • 9.练习：自并集
  • 11.视频：UNION
  • 13.解决方案：UNION
  • Q1
  • 17.性能优化3


2.文本+图片：全连接（FULL OUTER JOIN）

对应这节的韦恩图和代码，对于JOIN的理解就非常清楚了，两点提示：

  • 如果只写 JOIN 就是 INNER JOIN
  • 最后一个图是在全连接的基础上做限定得出的：WHERE Table_A.column_name IS NULL OR Table_B.column_name IS NULL 意思是虽然我是选全部的数据，但我的输入限制于那些两列中有一个是缺项的数据，这样就可以过滤掉两列中都有的数据了。
  • FULL JOIN 与 FULL OUTER JOIN 相同

4.解决方案：全连接（FULL OUTER JOIN）

这里的练习有点怪怪的，不过链接：
/什么情况下使用全连接比较好？/里的一段话特别的好：

The alternative is to use an INNER JOIN, a LEFT JOIN (with right side IS NULL) and a RIGHT JOIN (with left side IS NULL) and do a UNION - sometimes this approach is better because you can customize each individual join more obviously (and add a derived column to indicate which side is found or whether it’s found in both and which one is going to win).

大意是说用 OUTER JOIN 的场景很少，即使有也可以用 INNER JOIN，LEFT JOIN，RIGHT JOIN 的结果来进行 UNION，这种方式，可以让你标记那个记录是那一边独有的，在数据处理过程中带来便利。

ON event.account_id = orders.account_id
-- 这句用 ON 定义 JOIN 的键值
AND events.occurred_at < orders.occurred_at
-- 增加限定 events 的发生日期要比 orders 早
WHERE DATE_TRUNC('month',orders.occurred_at) = 
(SELECT DATE_TRUNC('month'.MIN(orders.occurred_at)) FROM demo.orders)
-- 在前面处理之前，先通过判断 orders 中最早发生的时间，来过滤掉一些数据，可以提高运算速度
ORDER BY orders.account_id, orders.occurred_at
-- 定义 ORDER BY

如果你回忆之前关于并集的课程，会发现并集子句是在WHERE 子句前被评估，而并集子句的过滤器将会在被连接前被剔除。 WHERE子句的过滤器将保留这些列并产生一些结果。

7.练习：带比较运算符的并集

这节的练习主要是理解最后那句 AND accounts.primary_poc < sales_reps.name 的限制，这里是说只有前面的字母列顺序比后面列的字母顺序在前面，才进行筛选。下一节的答案是 LEFT JOIN，我改成了 JOIN，就能比较出有没有这句的区别了。（有的话是100多条，没有是300多条）

SELECT accounts.name as account_name,
       accounts.primary_poc as poc_name,
       sales_reps.name as sales_rep_name
  FROM accounts
  JOIN sales_reps
    ON accounts.sales_rep_id = sales_reps.id
   AND accounts.primary_poc < sales_reps.name

8.视频：自并集

这节介绍了一个很巧妙的比较方法，自并集，一点扩展解释如下：

mysql两个结果取并集_数据

9.练习：自并集

这里练习只用修改一点，也便于大家理解。（记得自并集一定要给左边和右边起不同的别名）

SELECT we1.id AS we_id,
       we1.account_id AS we1_account_id,
       we1.occurred_at AS we1_occurred_at,
       we1.channel AS we1_channel,
       we2.id AS we2_id,
       we2.account_id AS we2_account_id,
       we2.occurred_at AS we2_occurred_at,
       we2.channel AS we2_channel
  FROM web_events we1 
 LEFT JOIN web_events we2
   ON we1.account_id = we2.account_id
  AND we1.occurred_at > we2.occurred_at
  AND we1.occurred_at <= we2.occurred_at + INTERVAL '1 day'
ORDER BY we1.account_id, we2.occurred_at

11.视频：UNION

UNION 就是在现有的表上从别的表增加新行，有2个限制：

  • 两个表的列数相同
  • 两个表的顺序相同（名字不用相同，但格式需要相同）
  • UNION 添加的是去重后的数据，如果要添加所有行，需要使用 UNION ALL

SQL定义了两项严格的数据并集准则：

两个表必须有相同的列数。
这些列必须有第一张表中相同的顺序 并且数据类型相同。
一个常见的误解是列名必须相同。其实，列名并不需要相同就可以连接两张表格。但是，你会发现大多数情况下 列名是相同的。

13.解决方案：UNION

Q1

这个练习的结果是702行，因为 UNION ALL 会添加重复行，所以就相当于 accounts 又加上一个 acoounts。如果只用 UNION 的话，就不会有变化，还是351行。这个情况在Q3中也通过COUNT验证了一遍。

SELECT *
FROM accounts

UNION ALL

SELECT *
FROM accounts

17.性能优化3

在进行性能相关的优化时，使用 EXPLAIN 进行检查是一个好方法：

mysql两个结果取并集_数据_02