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表数据集非常大的情况下,性能将比子查询的方式优秀很多。