Hive里的With关键字用法（调优）

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

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

语法限制
with...as...必须和其他sql一起使用(可以定义一个with但在后续语句中不使用他)
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;

语句的优点
提高代码可读性(结构清晰)
简化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
20190601 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上面，比如insert
insert into table table_name partititon(dt='2020-1-1')
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