hive 使用CTE的配置单元联接

v8wbuo2f  于 2022-11-05  发布在  Hive
关注(0)|答案(1)|浏览(278)

有人能帮我在下面的查询中添加注解的连接条件吗?如果我正在执行此查询,它工作正常,但当我尝试添加另一个在下面的查询中注解的连接条件时,它无法访问列high_V2,并给出列/表alisa找不到问题。

with MYTABLE as (
select
  T1.LOW_V2 as LOW_V2,
  T2.LOW_V as LOW_V,
  T2.HIGH_V as HIGH_V 
  from TAB1 T1 
  inner join 
  TAB2 T2
  on 
    T1.LOW_V2=T2.LOW_V 
    -- and high_V2=T2.HIGH_V
    ) 
select lpad(concat(splitted[0],translate(splitted[1],'0','9')),18,0) as high_V2,
LOW_V2,LOW_V,HIGH_V from 
   (
select  split(regexp_replace(LOW_V2,'(\\d*?)(0+)$','$1|$2'),'\\|') splitted,
 LOW_V2, 
 LOW_V,HIGH_V 
 from MYTABLE )s;
pdkcd3nj

pdkcd3nj1#

操作的顺序不是这样的
首先处理CTE。在处理时,high_v2不存在
然后处理子查询(select split),再处理销售线索查询(select lpad)。
编写多个CTE以便从上到下处理可能会更简单。这样您可以更容易地看到顺序。如果您从较早的一个CTE开始处理,则只能使用较晚的CTE中的内容

with MYTABLE as (
select
  T1.LOW_V2 as LOW_V2,
  T2.LOW_V as LOW_V,
  T2.HIGH_V as HIGH_V 
  from TAB1 T1 
  inner join 
  TAB2 T2
  on 
    T1.LOW_V2=T2.LOW_V 
), 

s AS (
 select    
  split(regexp_replace(LOW_V2,'(\\d*?)(0+)$','$1|$2'),'\\|') splitted,
  LOW_V2, 
  LOW_V,HIGH_V 
 from 
  MYTABLE
),

t AS (

 select   
  lpad(concat(splitted[0],translate(splitted[1],'0','9')),18,0) as high_V2,
  LOW_V2,
  LOW_V,
  HIGH_V 
 from 
  s
)

SELECT * from t WHERE high_v = high_v2

你能看到流是如何从上到下的,每个连续的CTE只使用前一个的东西吗?

相关问题