Hive SQL——优化 - 减少查询job 加速得到查询结果

小白问题描述：

图书馆图书按照类目进行区分，且由于图书的量级过大，一般仅一级类目无法满足快速定位到图书的需求，因此人们往往把图书划分为多级类目，例如一级类目cate1和二级类目cate2。

我想同时统计一级类目cate1下书的个数和二级类目cate2下书的个数，怎么解决？

图书表存储数据

	book_id	book_name	cate1	cate2
1	1001	book1	3001	5001
2	1002	book2	3001	5002
3	1003	book3	3001	5002
4	1004	book4	3001	5003
5	1005	book5	3002	5004
6	1006	book6	3002	5005
7	1007	book7	3002	5004
8	1008	book8	3002	5005
9	1009	book9	3002	5005

通过上面数据举例，一级类目cate1有2个类别、二级类目cate2有5个类别，目标的统计要求如下所示：

图书数据按类目统计

cate	count
cate1-3001	4
cate1-3002	5
cate2-5001	1
cate2-5002	2
cate2-5003	1
cate2-5004	2
cate2-5005	3

在你思考的这段时间我先建立Hive表、把数据写进去：

create table `tmp.tmp_book_info`
	(
		`book_id` string COMMENT '图书id',
		`book_name` string COMMENT '图书名',
		`cate1` string COMMENT '图书一级类目',
		`cate2` string COMMENT '图书二级类目'
	)
	COMMENT '图书基本信息表' row format delimited fields terminated by '\t' stored as textfile;

insert into tmp.tmp_book_info values ('1001', 'book1', '3001', '5001');
insert into tmp.tmp_book_info values ('1002', 'book2', '3001', '5002');
insert into tmp.tmp_book_info values ('1003', 'book3', '3001', '5002');
insert into tmp.tmp_book_info values ('1004', 'book4', '3001', '5003');
insert into tmp.tmp_book_info values ('1005', 'book5', '3002', '5004');
insert into tmp.tmp_book_info values ('1006', 'book6', '3002', '5005');
insert into tmp.tmp_book_info values ('1007', 'book7', '3002', '5004');
insert into tmp.tmp_book_info values ('1008', 'book8', '3002', '5005');
insert into tmp.tmp_book_info values ('1009', 'book9', '3002', '5005');

表建完了、数据插完了，到底咋做？！

首先，想到的就是暴力破解：我分别统计一级类目cate1和二级类目cate2，最后union一下，完美暴破！！

但是，这样一来，3个job（俩group by + 一个union）提上了跑任务的日程，当数据量级很大时，会慢...

 

好！此时祭出我们的大杀器：lateral view explode 只用一个job完美解决。

我们先贴代码，再解释是为啥...

select
	cate,
	type,
	count( *) as cnt
from
	(
		select
			split(part, '_') [0] as cate,
			split(part, '_') [1] as type,
			book_id
		from
			tmp.tmp_book_info a 
        	lateral view explode(split(concat(cate1, '_1-', cate2, '_2'), '-')) lv as part
	)
	b
group by
	cate,
	type;

分析一下：注意lateral view explode行，我们将该行数据拼接成cate1_1*cate2_2的形式，这样当我们对该表进行拆分扩张时，一行数据book_id, cate1, cate2可以拆分为2行：

cate1, type=1, book_id

cate2, type=2, book_id

这里，我专门把中间数据抽出来了，可以看看效果吼~

此时，就可以直接group by了，只用一个任务即可搞定！！！

看看结果（跟我们预料的一样，通过type可以区分）：