hive表连接总结

inner join

内连接返回两个表的交集：

select  
from user_list_1 a inner join user_list_2 b on a.user_id = b.user_id;

注意表连接时，必须进行***重命名***；
on 后面的连接条件必须起到唯一键值的作用；
inner 可以省略，效果一样；

在2019年购买后又退款的用户

select a.user_name
from 
		(select distinct user_name
		from user_trade
		whert year(dt)='2019')  a
		inner join
		(select distinct user_name
		from user_refund
		where year(dt)='2019') b 
		on a.user_name = b.user_name

注意：先去重，再做表连接，执行效率高

在2017年和2018年都购买的用户

SELECT 
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;

在2017年、2018年和2019年都有交易的用户

第一种写法：

select distinct a.user_name
from trade_2017  a 
join trade_2018 b on a.user_name = b.user_name
join trade_2019 c on b.user_name = b.user_name;

第二种写法：

select a.user_name
from 
		(select distinct user_name
		from trade_2017) a
		join 
		(select distinct user_name
		from trade_2018 ) b on a.user_name = b.user_name
		join
		(select distinct user_name
		from trade_2019) c on b.user_name=c.user_name;

表的数量级很大时，推荐第二种写法

left join

对表一和表二进行左连接：

select *
from user_list_1 left join user_list_2 on a.user_id=b.user_id;

进行左连接后，以左边的表为全集，返回能够匹配右边表的结果，如果没有匹配上，则为NULL

right join

以右边的表为全集，返回能够匹配右边表的结果，如果没有匹配上，则为NULL

在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_name is null

在2019年购买，但是没有退款的用户

select a.user_name
from
		(select distinct user_name
		from user_trade
		where year(dt)=2019) a 
		left join
		(select distinct user_name
		from user_refund
		where year(dt) = 2019) b on a.user_name= b.user_name
where b.user_name is null ;

在2019年购买用户的学历分布

select b.user_name,
		count(a.user_name)
from
	(select distict user_name
	from user_trade
	where year(dt) = 2019)  a 
	left join
	(select distinct user_name,
				get_json_object(extral,'$.education') as education
	from user_info)  b on a.user_name=b.user_name
group by b.education;

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

select a.user_name
from
		(select distinct user_name
		from trade_2017) a
		inner join
		(select distinct user_name
		from trade_2018) b on a.user_name = b.user_name
		left join 
		(select distinct user_name
		from trade_2019 ) c on b.user_name=c.user_name
where c.user_name is null;

a.user_name 换成b.user_name 也可以

full join

对表一和表二进行全连接

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 , ex_2 ,ex_3…)依次参考各个表达式，遇到非null停止并返回该值；

union all

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;

注意：
字段名称顺序必须一致
没有连接条件

2017年-2019年所有有交易的用户数

第一种写法：union all

select  count(distinct a.user_name),
			count(a.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 ) a

第二种写法：union

select  count(distinct a.user_name),
			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

union all 和 union 区别：

union all :
不会去除重复记录；
不会排序，合并两个结果够返回；
效率更快；
不去重不排序

union：
对表连接后，筛选掉重复的记录；
按照字段的顺序进行排序；】
效率慢
去重排序；
PS：对于比较大的表推荐先去重，再石勇union all ;