hive表連接配接沒有SQL強，是以hive多構造大寬表，而不是，多個小表之間的表連接配接。
hive表連接配接 join可以用，但是，效率低。
下面，舉一個可以用UNION ALL+group by +計算函數，代替表連接配接的例子。
- 需求：2019年每個使用者的支付和退款金額彙總
--union all
select a.user_name,
sum(a.total_amount),
sum(a.refund_amount)
from
(select user_name,
sum(pay_amount) total_amount,
0 as refund_amount
from user_trade
where year(dt)=2019
group by user_name
union all
select user_name,
0 as total_amount,
sum(refund_amount) refund_amount
from user_refund
group by user_name)a
group by a.user_name;
-- full join（表連接配接也可以，但是效率低）
select coalesce(a.user_name,b.user_name),
if(a.total_amount is null, 0,a.total_amount),
if(b.refund_amount is null,0,b.refund_amount)
full join
group by user_name)b
on a.user_name=b.user_name;
PS：解釋一下coalesce（）函數
生活其實也很簡單，喜歡的就去争取、得到的就珍惜、失去的就忘記。