数据分析课程笔记 - 18 - HIVE 核心技能之表连接

作者: 爱学习的ai酱 | 来源:发表于2021-01-01 11:05 被阅读0次

大家好呀，这节课是 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年购买。

user_trade表结构

user_refund表结构

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;

！！注意：⼀定要先去重，再做表连接，养成良好习惯！！虽然可以先连接后再去重，但是这么做的话，执行效率会低。

需求1结果

需求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 连接，道理都一样，只是顺序问题而已。

需求3结果

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 。

需求4结果

这个结果可以跟需求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;

需求5结果

需求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 ;

image.png

注意：

字段名称必须⼀致！
字段顺序必须⼀致！
没有连接条件！

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;

需求7结果

需求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 不会去重！

需求8结果1

当然，这个需求也可以用 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;

需求9结果

三、重点练习

需求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;

需求10结果

需求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);

需求11结果

好啦，这节课的内容就是这些，还是那句话，自己先思考，试着写写，再看参考代码。

数据分析课程笔记 - 18 - HIVE 核心技能之表连接

一、JOIN

1、INNER JOIN

需求1：找出在2019年购买后又退款的用户

需求2：在2017年和2018年都购买的用户

需求3：在2017年、2018年、2019都有交易的用户

2、LEFT JOIN

需求4：在2019年购买，但是没有退款的用户

需求5：在2019年有购买的用户的学历分布

需求6：在2017和2018年都购买，但是没有在2019年购买的用户

3、FULL JOIN

二、UNION & UNION ALL

UNION ALL和UNION的区别：

常见错误：

练习

需求7：2017-2019年有交易的所有用户数

需求8：2019年每个用户的支付和退款金额汇总

需求9：2019年每个支付用户的支付金额和退款金额

三、重点练习

需求10：首次激活时间在2017年，但是一直没有支付的用户年龄段分布

需求11：2018、2019年交易的用户，其激活时间段分布

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读