hive中with..as与 inser... select 连用

with...as...也叫做子查询部分,语句允许 hive 定义一个sql片段,供整个sql使用

简介

with…as… 需要定义一个sql片段,会将这个片段产生的结果集保存在内存中，h后续的sql均可以访问这个结果集，它的作用与视图或临时表类似。

语法限制

1.with…as…必须和其他sql一起使用(可以定义一个with但在后续语句中不使用他)
2.with…as…是一次性的

with…as…的完整格式是这样的

--with table_name as(子查询语句) 其他sql
with temp as(
	select * from xxx
)
select * from temp;

只定义不实用

with temp as(
	select * from xxx
)
select * from othertable;

同级的多个temp之间用,分割 with 只需要用一次, as 后的子句必须用()，

with temp1 as(
	select * from xxx
),temp2 as(
	select * from xxx
)
select * from temp1,temp2;

with…as… 还可以用于嵌套,此处举一个简单例子

with temp2 as(
	with temp1 as(
		select * from xxx
	)
	select * from temp1
)
select * from temp2;

with…as… 只能在一条sql中使用 — 很重要

with temp1 as(
	select * from xxx
)
select * from temp1;
select xxx from temp1; --error! no table named temp1;

语句的优点
1.提高代码可读性(结构清晰)
2.简化sql,优化执行速度(with子句只需要执行一次)

栗子
现有 city表,结构如下：

city_number	city_name	province
010	北京	北京
021	上海	上海
025	南京	江苏
0512	昆山	江苏
0531	济南	山东
0533	淄博	山东

然后分别有商品表 good

city_number	good_name
010	A
021	B

现在需要分别统计上海商品，一般的sql如下：

select *
from `good`
where city_number in (
		select city_number 
		from city 
		where city_name = "上海");

除了子查询,上述的栗子还可以用 join 来实现，如果用 with…as… 语句来实现,如下：

with tmp_shanghai as(
	select city_number from city where city_name = "上海"
)
select * from `good` where tmp_shanghai in (select * from tmp_shanghai)

看起来使用 with…as… 语句反而更复杂一点，但如果tmp_shanghai要被多次使用

再来看一个实际的例子,有一张操作表 event主要字段如下：

date	event_key
20190530	Delete
20190530	Put
20190530	Get
20190530	Get
20190530	Set

…
现在要求一条sql 统计出 Get 与 Set 操作的数量，先使用子查询实现。

select(
	select count(*) from event where event_key = "Get"
) as get_num,(
	select count(*) from event where event_key = "Set"
) as set_num

如果再增加其他项的统计呢，是否每一个均需要增加一个对 event表进行扫描的子查询

使用 with…as…

with temp as(
	select * from event where event_key = "Get" or event_key
	= "Set"
)
select
	sum(case when event_key = "Get" then 1 else 0 end) as get_num,
	sum(case when event_key = "Set" then 1 else 0 end) as set_num
from temp

阅读性比之前有所提高? 此外,这条语句只对event 表进行了一次扫描，将符合条件的数据存入 temp 中供后续计算,
在event表数据集非常大的情况下,性能将比子查询的方式优秀很多。