(09)Hive——CTE 公共表达式

目录

1.语法

 2. 使用场景

select语句

chaining CTEs 链式

union语句

insert into 语句

create table as 语句

前言

   Common Table Expressions（CTE）：公共表达式是一个临时的结果集，该结果集是从with子句中指定的查询派生而来的，紧跟在select 或 insert关键字之前。CTE可以在 select，insert，  create table as select 等语句中使用。

1.语法

[wtih CommonTableExpression]
selectcolumn1,column2, ...
from table 
[where 条件] 
[group by column]
[order by column] 
[cluster by column| [distribute by column] [sort by column] 
[limit [offset,] rows];

 2. 使用场景

• select语句

with tmp as (selectoid,uid,otime,date_format(otime, 'yyyy-MM') as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) rkfrom t_order
)selectuid,--每个用户一月份的订单数sum(if(dt = '2018-01', 1, 0)) as  m1_count,--每个用户二月份的订单数sum(if(dt = '2018-02', 1, 0)) as  m2_count
from tmpgroup by uidhaving m1_count >0 and m2_count=0;

• chaining CTEs 链式

with tmp1 as (selectoid,uid,otime,date_format(otime, 'yyyy-MM') as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) as rkfrom t_order
),tmp2 as(selectuid,--每个用户一月份的订单数sum(if(dt = '2018-01', 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt = '2018-02', 1, 0)) as m2_countfrom tmp1group by uidhaving m1_count > 0and m2_count = 0)
select * from tmp2 limit 1;

• union语句

with q1 as (select * from student where num = 95002),q2 as (select * from student where num = 95004)
select * from q1 union all select * from q2;

• insert into 语句

with tmp1 as (selectoid,uid,otime,date_format(otime, 'yyyy-MM') as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) as rkfrom t_order
),tmp2 as(selectuid,--每个用户一月份的订单数sum(if(dt = '2018-01', 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt = '2018-02', 1, 0)) as m2_countfrom tmp1group by uidhaving m1_count > 0and m2_count = 0)insert into tmp3
select * from tmp2 limit 10;

• create table as 语句

--- 从tmp2 表中取10条数据，基于此创建表tmp3 
create table tmp3 as 
with tmp1 as (selectoid,uid,otime,date_format(otime, 'yyyy-MM') as dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) as rkfrom t_order
),tmp2 as(selectuid,--每个用户一月份的订单数sum(if(dt = '2018-01', 1, 0)) as m1_count,--每个用户二月份的订单数sum(if(dt = '2018-02', 1, 0)) as m2_countfrom tmp1group by uidhaving m1_count > 0and m2_count = 0)
select * from tmp2 limit 10;