大家好呀，這節(jié)課是 HIVE 的第三次課，我們要學(xué)習(xí) HIVE 的表連接。表連接我們之前在 MySQL 部分學(xué)過，其實就是 Join 和 Union，包括四種 Join 形式和兩種 Union 形式。MySQL 的表連接和 HIVE 的表連接用法大體相同，但存在一些細(xì)節(jié)上的差異，所以這節(jié)課我們會通過一些實際案例中相對復(fù)雜的需求來學(xué)習(xí) HIVE 中表連接的用法。

本節(jié)課主要內(nèi)容：

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、重點練習(xí)：需求10、11

一、JOIN

1、INNER JOIN

內(nèi)連接：返回兩個表的交集，

舉例說明：

表1：user_list_1

表2：user_list_2

找出既在 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;

注意：

• 表連接時，必須進(jìn)行重命名
• on后面使用的連接條件必須起到唯一鍵值的作用
• inner可省略不寫，效果一樣

需求1：找出在2019年購買后又退款的用戶

這個需求需要用到兩個表，一個是 user_trade，一個是 user_refund，要找出在2019年購買后又退款的用戶，那就需要在 user_trade 表中找出2019年購買過的用戶，再在 user_refund 表中找出在2019年退款過的用戶，兩個取交集即可。所以我們需要先從兩個子查詢中選出相應(yīng)的 user_name（注意去重），再用 JOIN 對 user_name 取交集。

注意： 由于一般情況下先有購買，才又退款，所以我們不用特別考慮時間先后的問題。對于2018年購買，2019年退款的情況，事實上并不在題目要求的范圍內(nèi)，因為題目要求的是2019年購買。

user_trade表結(jié)構(gòu)

user_refund表結(jié)構(gòu)

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;

??！注意：?定要先去重，再做表連接，養(yǎng)成良好習(xí)慣??！雖然可以先連接后再去重，但是這么做的話，執(zhí)行效率會低。

需求1結(jié)果

需求2：在2017年和2018年都購買的用戶

這個需求跟需求1類似，甚至更簡單，因為只用同一張表，當(dāng)然由于要取交集，所以還是要用兩個子查詢。

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;

這個題運(yùn)行結(jié)果太長，就不截圖了。

需求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 應(yīng)該是 b 和 c 的 user_name。當(dāng)然你也可以先 a b連接得到一張表再和 c 連接，道理都一樣，只是順序問題而已。

需求3結(jié)果

2、LEFT JOIN

首先，回到我們最開始的簡單例子：

表1：user_list_1

表2：user_list_2

先來看?下，對表1和表2進(jìn)行左連接后，發(fā)生了什么。

SELECT *
FROM user_list_1 a
LEFT JOIN user_list_2 b
ON a.user_id=b.user_id;

左連接結(jié)果

結(jié)論： 進(jìn)行左連接后，以左邊的表1為全集，返回能夠匹配上的右邊
表2的匹配結(jié)果，沒有匹配上的則顯示NULL。

拓展：
right join：以右邊的表為全集，返回能夠匹配上的右表的匹配結(jié)
果，沒有匹配上的則顯示NULL。但其完全可以由left join改寫出同樣的結(jié)果，所以較少使用。

左連接和右連接

舉例說明：

如何取出在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對應(yīng)，分別取出 user_trade 2019年購買用戶和user_refund 2019年退款用戶，再以左邊為準(zhǔn)，取右邊為 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 可以用來判斷數(shù)據(jù)是否為 null 。

需求4結(jié)果

這個結(jié)果可以跟需求1的結(jié)果對比一下，應(yīng)該是沒有重復(fù)的，有重復(fù)的話就不對了。

需求5：在2019年有購買的用戶的學(xué)歷分布

這個需求需要用到兩個表，一個是交易表 user_trade，一個是用戶信息表 user_info，學(xué)歷信息在 user_info 的 extra 字段的 education 鍵中。解題思路是，先把 user_trade 表中 2019 年有購買記錄的用戶選出來作為左表，再把 user_info 中的用戶名和學(xué)歷情況選出來作為右表，二者左連接即可取出在 2019 年有購買的用戶的學(xué)歷情況。那么要看學(xué)歷分布的話，只需要最后根據(jù)右表的 education 字段分一下組，再統(tǒng)計一下人數(shù)即可。

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結(jié)果

需求6：在2017和2018年都購買，但是沒有在2019年購買的用戶

這個需求跟前面的需求3類似，三張表連接，第三張表取 null 值。不過要注意連接類型，2017和2018年取交集用 inner join，但和2019年的連接應(yīng)該是左連接，因為要以 2017 和 2018 為準(zhǔn)。這里我們正好有 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;

這里運(yùn)行結(jié)果也很多，所以就不截圖了。

3、FULL JOIN

舉例說明：

首先，我們還是來看看對一開始最簡單的表1和表2進(jìn)行全連接后，會發(fā)生什么。

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 函數(shù)，它主要用來取全集，它的參數(shù)是 (expression_1, expression_2, ...,expression_n)，它會依次看各個參數(shù)表達(dá)式，遇到非 null 的值就會返回該值，并繼續(xù)。如果所有的表達(dá)式都是空值，它最終將返回?個空值。

二、UNION & UNION ALL

表1：user_list_1

表3：user_list_3

舉例說明：

將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的區(qū)別：

注意：如果表很大時推薦先去重，再進(jìn)行union all。

常見錯誤：

常見錯誤一：沒有對UNION ALL后的表進(jìn)行重命名：

-- 錯誤寫法
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);

常見錯誤二：直接對表進(jìn)行UNION ALL：

-- 錯誤寫法
SELECT count(distinct user_name)
FROM trade_2017
UNION ALL trade_2018
UNION ALL trade_2019;

練習(xí)

需求7：2017-2019年有交易的所有用戶數(shù)

這個需求就是取出2017-2019所有有交易的用戶進(jìn)行 union，得到的就是去重的所有用戶，最后再對這些用戶 count 一下求出用戶數(shù)即可。

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結(jié)果

需求8：2019年每個用戶的支付和退款金額匯總

這個需求是要求出2019年每個用戶的支付金額和退款金額的匯總數(shù)，支付金額和退款金額分別來自 user_trade 和 user_refund 兩張表，每張表根據(jù) user_name 分組并進(jìn)行聚合計算即可算出每個人的金額總數(shù)。最后把兩張表 UNION ALL 一下。但是，這里有一個問題需要注意一下，user_trade 表中選出來的數(shù)據(jù)有兩列：user_name 和 每個人的 pay_amount 加總，user_refund 表選出來的數(shù)據(jù)也有兩列： user_name 和每個人的 refund_amount 加總，但是我們知道，UNION ALL 要求兩張表的字段名稱和字段順序都一樣，但是現(xiàn)在這兩張需要 UNION ALL 的表字段不一樣，怎么辦呢？

我們可以給它們分別補(bǔ)一列數(shù)據(jù)，user_trade 表補(bǔ)充一列 refund_amount，user_refund 表補(bǔ)充一列 pay_amount，兩列補(bǔ)充的數(shù)據(jù)都規(guī)定它們?yōu)?0 即可。同時還要注意補(bǔ)充字段的順序，兩張表要保持一致！

代碼如下：

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;

注意： 最后父查詢也還是要根據(jù) user_name 進(jìn)行分組并做聚合運(yùn)算，因為 UNION ALL 不會去重！

需求8結(jié)果1

當(dāng)然，這個需求也可以用 FULL JOIN 實現(xiàn)，思路就是先每張表做分組聚合，再兩張表 FULL JOIN 最后用 coalesce 函數(shù)取個全集：

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;

運(yùn)行結(jié)果就不截圖啦~

需求9：2019年每個支付用戶的支付金額和退款金額

這個需求比上個需求多了一個條件，就是要求必須是支付用戶。上一個需求可能還存在沒有支付只有退款的用戶，這個需求要求是必須有支付的用戶，那就應(yīng)該用 LEFT JOIN 來做表連接，那父查詢也就不需要用 coalesce 函數(shù)取全集了，直接取左表的字段即可。

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結(jié)果

三、重點練習(xí)

需求10：首次激活時間在2017年，但是一直沒有支付的用戶年齡段分布

這個需求有首次激活時間、年齡以及支付情況，那就需要用到 user_info 和 user_trade 兩個表，先從 user_info 表中選出首次激活時間在 2017 年的用戶及其年齡分組，再和從 user_trade 表中選出的 user_name （注意去重）表進(jìn)行左連接，篩選條件是右邊表的 user_name 為 null。最后再對結(jié)果按照年齡段分組，進(jìn)行聚合計算即可。

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結(jié)果

需求11：2018、2019年交易的用戶，其激活時間段分布

這個需求是要 得出2018和2019支付用戶全集的激活時間段分布，需要用到 trade_2018 、trade_2019 和 user_info 三張表。

先分別從兩張交易表中取出去重過的用戶進(jìn)行 union，然后再和 user_info 表基于 user_name 進(jìn)行左連接。連接后的表中包含了 user_info 表的完整字段，我們從中選出激活時間字段，將其轉(zhuǎn)換為小時，再進(jìn)行分組聚合計算。

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結(jié)果

好啦，這節(jié)課的內(nèi)容就是這些，還是那句話，自己先思考，試著寫寫，再看參考代碼。