公用表表达式(CTE)是从WITH子句中指定的简单查询派生的临时结果集(会把查询的表数据放到内存中,供其他查询随时使用),
该子句紧跟在SELECT或INSERT关键字之前。
CTE仅在单个语句的执行范围内定义。
可以在Hive SELECT,INSERT,CREATE TABLE AS SELECT 或 CREATE VIEW AS SELECT 语句中使用一个或多个CTE 。
一个 with as 语句
--1
with cte_order as
(
select store_id,channel,paid_date,order_id,gmv
from testdb.order_line
where channel in ('SNG','JDDJ')
)
--with as 语句不能加分号 ;
select store_id,order_id,paid_date,order_id,gmv
from cte_order
where channel='SNG'
union all
select store_id,order_id,paid_date,order_id,gmv
from cte_order
where channel='JDDJ'
;
多个 with as 语句
--2
with cte_SNG as (
select store_id,channel,paid_date,order_id,gmv
from testdb.order_line
where channel = 'SNG'
)
--多个 with as 语句 用逗号, 隔开
, cte_JDDJ as (
select store_id,channel,paid_date,order_id,gmv
from testdb.order_line
where channel = 'JDDJ'
)
select store_id,order_id,paid_date,order_id,gmv
from cte_SNG
union all
select store_id,order_id,paid_date,order_id,gmv
from cte_JDDJ
;
内嵌 union
--3
with cte_order as
(
select store_id,channel,paid_date,order_id,gmv
from testdb.order_line
where channel = 'SNG'
union all
select store_id,channel,paid_date,order_id,gmv
from testdb.order_line
where channel = 'JDDJ'
)
select * from cte_order ;