大家好呀，这节课是 HIVE 的第三次课，我们要学习 HIVE 的表连接。表连接我们之前在 MySQL 部分学过，其实就是 Join 和 Union，包括四种 Join 形式和两种 Union 形式。MySQL 的表连接和 HIVE 的表连接用法大体相同，但存在一些细节上的差异，所以这节课我们会通过一些实际案例中相对复杂的需求来学习 HIVE 中表连接的用法。
本节课主要内容：
1、JOIN
（1）INNER JOIN：需求1、2、3
（2）LEFT JOIN：需求4、5、6
（3）FULL JOIN
2、UNION & UNION ALL：需求7、8、9
3、重点练习：需求10、11
一、JOIN
1、INNER JOIN
内连接：返回两个表的交集，
举例说明：
表1：user_list_1
| user_id | user_name |
|---|---|
| 10001 | Abby |
| 10002 | Ailsa |
| 10003 | Alice |
| 10004 | Alina |
| 10005 | Allison |
| 10006 | Angelia |
表2：user_list_2
| user_id | user_name |
|---|---|
| 10001 | Abby |
| 10003 | Alice |
| 10004 | Alina |
| 10007 | Amanda |
| 10008 | Anne |
| 10009 | Ann |
找出既在 user_list_1 也在 user_list_2 的用户：
select *
from user_list_1 a
inner join user_list_2 b
on a.user_id = b.user_id;
注意：
- 表连接时，必须进行重命名
- on后面使用的连接条件必须起到唯一键值的作用
- inner可省略不写，效果一样
需求1：找出在2019年购买后又退款的用户
这个需求需要用到两个表，一个是 user_trade，一个是 user_refund，要找出在2019年购买后又退款的用户，那就需要在 user_trade 表中找出2019年购买过的用户，再在 user_refund 表中找出在2019年退款过的用户，两个取交集即可。所以我们需要先从两个子查询中选出相应的 user_name（注意去重），再用 JOIN 对 user_name 取交集。
注意： 由于一般情况下先有购买，才又退款，所以我们不用特别考虑时间先后的问题。对于2018年购买，2019年退款的情况，事实上并不在题目要求的范围内，因为题目要求的是2019年购买。
SELECT trade.user_name
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2019) as trade
JOIN
(SELECT distinct user_name
FROM user_refund
WHERE year(dt)=2019) as refund
on trade.user_name = refund.user_name;
！！注意：⼀定要先去重，再做表连接，养成良好习惯！！虽然可以先连接后再去重，但是这么做的话，执行效率会低。
需求2：在2017年和2018年都购买的用户
这个需求跟需求1类似，甚至更简单，因为只用同一张表，当然由于要取交集，所以还是要用两个子查询。
SELECT a.user_name
FROM
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2017) a
JOIN
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2018) b
on a.user_name = b.user_name;
这个题运行结果太长，就不截图了。
需求3：在2017年、2018年、2019都有交易的用户
很简单，做两次连接即可。
SELECT a.user_name
FROM
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2017) a
JOIN
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2018) b
on a.user_name = b.user_name
JOIN
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2019) c
on b.user_name = c.user_name;
注意： 这里 b 和 c 的 JOIN ON 应该是 b 和 c 的 user_name。当然你也可以先 a b连接得到一张表再和 c 连接，道理都一样，只是顺序问题而已。
2、LEFT JOIN
首先，回到我们最开始的简单例子：
表1：user_list_1
| user_id | user_name |
|---|---|
| 10001 | Abby |
| 10002 | Ailsa |
| 10003 | Alice |
| 10004 | Alina |
| 10005 | Allison |
| 10006 | Angelia |
表2：user_list_2
| user_id | user_name |
|---|---|
| 10001 | Abby |
| 10003 | Alice |
| 10004 | Alina |
| 10007 | Amanda |
| 10008 | Anne |
| 10009 | Ann |
先来看⼀下，对表1和表2进行左连接后，发生了什么。
SELECT *
FROM user_list_1 a
LEFT JOIN user_list_2 b
ON a.user_id=b.user_id;
结论： 进行左连接后，以左边的表1为全集，返回能够匹配上的右边
表2的匹配结果，没有匹配上的则显示NULL。
拓展：
right join：以右边的表为全集，返回能够匹配上的右表的匹配结
果，没有匹配上的则显示NULL。但其完全可以由left join改写出同样的结果，所以较少使用。
举例说明：
如何取出在user_list_1表中但是不在user_list_2的用户？
SELECT a.user_id,
a.user_name
FROM user_list_1 a
LEFT JOIN user_list_2 b
ON a.user_id=b.user_id
WHERE b.user_id is null;
需求4：在2019年购买，但是没有退款的用户
这个需求和需求1对应，分别取出 user_trade 2019年购买用户和user_refund 2019年退款用户，再以左边为准，取右边为 null 的用户即可。
SELECT trade.user_name
FROM
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2019) trade
LEFT JOIN
(SELECT DISTINCT user_name
FROM user_refund
WHERE year(dt)=2019) refund
on trade.user_name = refund.user_name
WHERE refund.user_name is null;
注意： is null 可以用来判断数据是否为 null 。
这个结果可以跟需求1的结果对比一下，应该是没有重复的，有重复的话就不对了。
需求5：在2019年有购买的用户的学历分布
这个需求需要用到两个表，一个是交易表 user_trade，一个是用户信息表 user_info，学历信息在 user_info 的 extra 字段的 education 键中。解题思路是，先把 user_trade 表中 2019 年有购买记录的用户选出来作为左表，再把 user_info 中的用户名和学历情况选出来作为右表，二者左连接即可取出在 2019 年有购买的用户的学历情况。那么要看学历分布的话，只需要最后根据右表的 education 字段分一下组，再统计一下人数即可。
SELECT b.education,
count(a.user_name)
FROM
(SELECT DISTINCT user_name
FROM user_trade
WHERE year(dt)=2019) a
LEFT JOIN
(SELECT user_name,
get_json_object(extra1,'$.education') as education
FROM user_info) b
on a.user_name = b.user_name
GROUP BY b.education;
需求6：在2017和2018年都购买，但是没有在2019年购买的用户
这个需求跟前面的需求3类似，三张表连接，第三张表取 null 值。不过要注意连接类型，2017和2018年取交集用 inner join，但和2019年的连接应该是左连接，因为要以 2017 和 2018 为准。这里我们正好有 trade_2017、trade_2018、trade_2019 三张表，所以我们就不从 user_trade 表中取了。
SELECT a.user_name
FROM
(SELECT user_name
FROM trade_2017) a
JOIN
(SELECT user_name
FROM trade_2018) b
on a.user_name = b.user_name
LEFT JOIN
(SELECT user_name
FROM trade_2019) c
on b.user_name = c.user_name
WHERE c.user_name is null;
这里运行结果也很多，所以就不截图了。
3、FULL JOIN
举例说明：
首先，我们还是来看看对一开始最简单的表1和表2进行全连接后，会发生什么。
SELECT *
FROM user_list_1 a
FULL JOIN user_list_2 b
ON a.user_id=b.user_id;
那么，选出 user_list_1 和 user_list_2 的所有用户就可以用如下语句：
SELECT coalesce(a.user_name,b.user_name)
FROM user_list_1 a
FULL JOIN user_list_2 b
on a.user_id=b.user_id;
这里有一个新知识点：coalesce 函数，它主要用来取全集，它的参数是 (expression_1, expression_2, ...,expression_n)，它会依次看各个参数表达式，遇到非 null 的值就会返回该值，并继续。如果所有的表达式都是空值，它最终将返回⼀个空值。
二、UNION & UNION ALL
表1：user_list_1
| user_id | user_name |
|---|---|
| 10001 | Abby |
| 10002 | Ailsa |
| 10003 | Alice |
| 10004 | Alina |
| 10005 | Allison |
| 10006 | Angelia |
表3：user_list_3
| user_id | user_name |
|---|---|
| 10290 | Michael |
| 10291 | Avery |
| 10292 | Reilly |
| 10293 | Dillon |
| 10294 | Walton |
举例说明：
将user_list_1和user_list_3合并在⼀起：
SELECT user_id,
user_name
FROM user_list_1
UNION ALL
SELECT user_id,
user_name
FROM user_list_3 ;
注意：
- 字段名称必须⼀致！
- 字段顺序必须⼀致！
- 没有连接条件！
UNION ALL和UNION的区别：
| 对比 | UNION ALL | UNION |
|---|---|---|
| 对重复结果的处理 | 不会去除重复记录 | 在进行表连接后会筛选掉重复的记录 |
| 对排序的处理 | 只是简单的将两个结果合并后就返回 | 将会按照字段的顺序进行排序 |
| 效率 | 更快 | 更慢 |
| 总述 | 不去重不排序 | 去重且排序 |
注意：如果表很大时推荐先去重，再进行union all。
常见错误：
常见错误一：没有对UNION ALL后的表进行重命名：
-- 错误写法
SELECT count(distinct user_name)
FROM
(
SELECT user_name
FROM trade_2017
UNION ALL
SELECT user_name
FROM trade_2018
UNION ALL
SELECT user_name
FROM trade_2019);
常见错误二：直接对表进行UNION ALL：
-- 错误写法
SELECT count(distinct user_name)
FROM trade_2017
UNION ALL trade_2018
UNION ALL trade_2019;
练习
需求7：2017-2019年有交易的所有用户数
这个需求就是取出2017-2019所有有交易的用户进行 union，得到的就是去重的所有用户，最后再对这些用户 count 一下求出用户数即可。
SELECT count(a.user_name)
FROM
(SELECT user_name
FROM trade_2017
UNION
SELECT user_name
FROM trade_2018
UNION
SELECT user_name
FROM trade_2019) a;
需求8：2019年每个用户的支付和退款金额汇总
这个需求是要求出2019年每个用户的支付金额和退款金额的汇总数，支付金额和退款金额分别来自 user_trade 和 user_refund 两张表，每张表根据 user_name 分组并进行聚合计算即可算出每个人的金额总数。最后把两张表 UNION ALL 一下。但是，这里有一个问题需要注意一下，user_trade 表中选出来的数据有两列：user_name 和 每个人的 pay_amount 加总，user_refund 表选出来的数据也有两列： user_name 和每个人的 refund_amount 加总，但是我们知道，UNION ALL 要求两张表的字段名称和字段顺序都一样，但是现在这两张需要 UNION ALL 的表字段不一样，怎么办呢？
我们可以给它们分别补一列数据，user_trade 表补充一列 refund_amount，user_refund 表补充一列 pay_amount，两列补充的数据都规定它们为 0 即可。同时还要注意补充字段的顺序，两张表要保持一致！
代码如下：
SELECT a.user_name,
sum(a.pay_amount),
sum(a.refund_amount)
FROM
(SELECT user_name,
sum(pay_amount) as pay_amount,
0 as refund_amount
FROM user_trade
WHERE year(dt)=2019
GROUP BY user_name
UNION ALL
SELECT user_name,
0 as pay_amount,
sum(refund_amount) as refund_amount
FROM user_refund
WHERE year(dt)=2019
GROUP BY user_name) a
GROUP BY a.user_name;
注意： 最后父查询也还是要根据 user_name 进行分组并做聚合运算，因为 UNION ALL 不会去重！
当然，这个需求也可以用 FULL JOIN 实现，思路就是先每张表做分组聚合，再两张表 FULL JOIN 最后用 coalesce 函数取个全集：
select coalesce(a.user_name,b.user_name) as user_name,
if(a.pay_amount is null,0,a.pay_amount) as pay_amount,
if(b.refund_amount is null,0,b.refund_amount) as refund_amount
from
(select user_name,
sum(pay_amount) as pay_amount
from user_trade
where year(dt)=2019
group by user_name) a
full join
(select user_name,
sum(refund_amount) as refund_amount
from user_refund
where year(dt)=2019
group by user_name) b on a.user_name=b.user_name;
运行结果就不截图啦~
需求9：2019年每个支付用户的支付金额和退款金额
这个需求比上个需求多了一个条件，就是要求必须是支付用户。上一个需求可能还存在没有支付只有退款的用户，这个需求要求是必须有支付的用户，那就应该用 LEFT JOIN 来做表连接，那父查询也就不需要用 coalesce 函数取全集了，直接取左表的字段即可。
select a.user_name,
a.pay_amount,
b.refund_amount
from
(select user_name,
sum(pay_amount) as pay_amount
from user_trade
where year(dt)=2019
group by user_name) a
left join
(select user_name,
sum(refund_amount) as refund_amount
from user_refund
where year(dt)=2019
group by user_name) b on a.user_name=b.user_name;
三、重点练习
需求10：首次激活时间在2017年，但是一直没有支付的用户年龄段分布
这个需求有首次激活时间、年龄以及支付情况，那就需要用到 user_info 和 user_trade 两个表，先从 user_info 表中选出首次激活时间在 2017 年的用户及其年龄分组，再和从 user_trade 表中选出的 user_name （注意去重）表进行左连接，筛选条件是右边表的 user_name 为 null。最后再对结果按照年龄段分组，进行聚合计算即可。
select a.age_type,
count(*)
from
(select case when age < 20 then '20岁以下'
when age >=20 and age < 30 then '20-30岁'
when age >=30 and age < 40 then '30-40岁'
else '40岁以上' end as age_type,
user_name
from user_info
where year(firstactivetime)=2017) a
left join
(select distinct user_name
from user_trade
where dt is not null) b on a.user_name=b.user_name
where b.user_name is null
group by a.age_type;
需求11：2018、2019年交易的用户，其激活时间段分布
这个需求是要 得出2018和2019支付用户全集的激活时间段分布，需要用到 trade_2018 、trade_2019 和 user_info 三张表。
先分别从两张交易表中取出去重过的用户进行 union，然后再和 user_info 表基于 user_name 进行左连接。连接后的表中包含了 user_info 表的完整字段，我们从中选出激活时间字段，将其转换为小时，再进行分组聚合计算。
select hour(b.firstactivetime) as hour,
count(*)
from
(select user_name
from trade_2018
union
select user_name
from trade_2019) a
left join user_info b on a.user_name=b.user_name
group by hour(b.firstactivetime);
好啦，这节课的内容就是这些，还是那句话，自己先思考，试着写写，再看参考代码。
