Hive中with/with as 的用法

1.with as 是什么

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

2.用法

① 完整格式

with q1 as ( select key from src where key = '5')
select * from q1;

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

with q1 as ( select key from q2 where key = '5'),
q2 as ( select key from src where key = '5')
select * from (select key from q1) a;

③ 嵌套

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

④ 只能在一条sql中使用

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

3.优秀的参考文献，该文献后面有栗子

https://www.jianshu.com/p/d518e9f5d5f9