(09)Hive——CTE 公共表达式-Toy模板网

这篇具有很好参考价值的文章主要介绍了(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 等语句中使用。文章来源地址https://www.toymoban.com/news/detail-836951.html

1.语法

[wtih CommonTableExpression]
select
        column1,
        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 (
    select
        oid,
        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) rk
    from t_order
)
 select
    uid,
    --每个用户一月份的订单数
    sum(if(dt = '2018-01', 1, 0)) as  m1_count,
    --每个用户二月份的订单数
    sum(if(dt = '2018-02', 1, 0)) as  m2_count
from tmp
 group by uid
 having m1_count >0 and m2_count=0;

chaining CTEs 链式


with tmp1 as (
    select
        oid,
        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 rk
    from t_order
),
     tmp2 as
         (select
              uid,
              --每个用户一月份的订单数
              sum(if(dt = '2018-01', 1, 0)) as m1_count,
              --每个用户二月份的订单数
              sum(if(dt = '2018-02', 1, 0)) as m2_count
          from tmp1
          group by uid
          having m1_count > 0
             and 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 (
    select
        oid,
        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 rk
    from t_order
),
     tmp2 as
         (select
              uid,
              --每个用户一月份的订单数
              sum(if(dt = '2018-01', 1, 0)) as m1_count,
              --每个用户二月份的订单数
              sum(if(dt = '2018-02', 1, 0)) as m2_count
          from tmp1
          group by uid
          having m1_count > 0
             and m2_count = 0)

insert into tmp3
select * from tmp2 limit 10;

create table as 语句

--- 从tmp2 表中取10条数据，基于此创建表tmp3 
create table tmp3 as 
with tmp1 as (
    select
        oid,
        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 rk
    from t_order
),
     tmp2 as
         (select
              uid,
              --每个用户一月份的订单数
              sum(if(dt = '2018-01', 1, 0)) as m1_count,
              --每个用户二月份的订单数
              sum(if(dt = '2018-02', 1, 0)) as m2_count
          from tmp1
          group by uid
          having m1_count > 0
             and m2_count = 0)
select * from tmp2 limit 10;

到了这里，关于(09)Hive——CTE 公共表达式的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！