Hive的join操作

假设有三张表，结构和数据如下：

-- 创建表 test_a,test_b,test_c
CREATE TABLE test_a(
id int,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

--分别导入数据到三个表中
--test_a
1   a1
2   a2
4   a4
--test_b
1   b1
3   b3
4   b4
--test_c
1   c1
4   c4
5   c5

hive只支持等值连接;
不支持非等值的join条件(可以通过其他方式实现,如left outer join),
因为它很难在mapreduce job实现这样的条件.

inner join

-- 内连接，显示两表匹配上的结果.
-- inner join 可以简写为 join
select * from test_a a inner join test_b b on a.id=b.id;

left join

-- 左外连接，左表数据全显示,右表数据匹配上的显示,未匹配上的显示NULL.
--left join是left outer join的简写
select a.*,b.* from test_a a left join test_b b on a.id=b.id;

right join

-- 右外连接，右表数据全显示,左表数据匹配上的显示,未匹配上的显示NULL.  
--right join是right outer join的简写
select a.*,b.* from test_a a right join test_b b on a.id=b.id;

full join

全外连接,相当于left join union right join,包括两个表的join结果(左边有,右边NULL union 右边有,左边NULL)  
-- full join是full outer join的简写
select a.*,b.* from test_a a full outer join test_b b on a.id=b.id;

join的应用

求id相同的所有用户

-- 求出三表中id相同的数据
select a.*,b.name,c.name 
from test_a a 
inner join 
test_b b on a.id=b.id 
inner join 
test_c c on b.id=c.id;

计算新增用户

--思路：
--1.使用右连接列出右表的所有数据; 
--2.通过左表的aid is null筛选除左表也就是以前不存在的数据; 
--3.通过count(1)计算出右表有而左表没有的数据总和(即新增用户数).

select count(1) from
(select aid from user_install_status where dt='20141117' group by aid) a
right join
(select aid from user_install_status where dt='20141228' group by aid) b
on b.aid=a.aid where a.aid is null;

笛卡尔积

--笛卡尔积的SQL,是需要避免的查询操作.
select * from test_a inner join test_b;

自连接

-- 顾名思义，就是一张表自己连接自己；
--假设有表ods_self,只有一个字段a【string类型】，数据如下：
2
3
4
--需求：将ods_self表中a字段的值小于等于当前行的值拼接起来，最终得到的结果展示如下：
a b 
3 2、3 
2 2 
4 2、3、4
--解题思路：由于给定的条件就只有ods_self这一张表，所以要想得到结果中一张表有两个字段的效果，就需要ods_self表进行自连接
--第一步：自连接（也可以说是笛卡尔积，cross join 或者 join 不带 on 条件），并且对自连接之后的结果过滤（获取小于等于当前a值的数据）
create table dwd_self as 
select 
m.a as a,
n.a as b
from 
ods_self m
cross join 
ods_self n
where m.a>=n.a;
--第二步：将数据拼接成想要的结果
select
a, 
concat_ws('、',collect_list(b)) as nb
from dwd_self
group by a;