Hive full join 对数排查异常数据

「爱情、让人受尽委屈。」 2021-09-25 10:12 404阅读 0赞

select 
    t1.*,
    t2.*
    from (
    select  1 as c1 , 'a' as c2 
    union all 
    select  2 as c1 , 'b' as c2 
    union all 
    select  3 as c1 , null as c2 
    union all 
    select  4 as c1 , 'd' as c2 
    )t1 
    full join 
    (
    select  2 as c1 , 'b' as c2 
    union all 
    select  3 as c1 , null as c2 
    union all 
    select  4 as c1 , 'd4' as c2 
    union all 
    select  5 as c1 , 'e' as c2 
    )t2 
    on t1.c1 = t2.c1 
    order by coalesce(t1.c1 , t2.c1 ) ; -- 5条记录
    
    
    select 
    t1.*,
    t2.*
    from (
    select  1 as c1 , 'a' as c2 
    union all 
    select  2 as c1 , 'b' as c2 
    union all 
    select  3 as c1 , null as c2 
    union all 
    select  4 as c1 , 'd' as c2 
    )t1 
    full join 
    (
    select  2 as c1 , 'b' as c2 
    union all 
    select  3 as c1 , null as c2 
    union all 
    select  4 as c1 , 'd4' as c2 
    union all 
    select  5 as c1 , 'e' as c2 
    )t2 
    on t1.c1 = t2.c1 
    where  t1.c1 is null or  t2.c1 is null or (coalesce(t1.c2,'') <> coalesce(t2.c2,''))
    order by coalesce(t1.c1 , t2.c1 ) ; -- 3条记录
    
    
    select 
    t1.*,
    t2.*
    from (
    select  1 as c1 , 'a' as c2 
    union all 
    select  2 as c1 , 'b' as c2 
    union all 
    select  3 as c1 , null as c2 
    union all 
    select  4 as c1 , 'd' as c2 
    )t1 
    full join 
    (
    select  2 as c1 , 'b' as c2 
    union all 
    select  3 as c1 , null as c2 
    union all 
    select  4 as c1 , 'd4' as c2 
    union all 
    select  5 as c1 , 'e' as c2 
    )t2 
    on t1.c1 = t2.c1 
    where  (coalesce(t1.c2,'') <> coalesce(t2.c2,''))
    order by coalesce(t1.c1 , t2.c1 ) ; -- 3条记录 ,是包含第二种条件的结果的，
    -- 所以【t1.c1 is null or  t2.c1 is null】这个条件就不用写了！

一的结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70][]

二 三的结果：

![20210603143851396.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70]: /images/20210923/ddf5beab99144b88aace6500e171bb33.png
[20210603143851396.png]: /images/20210923/66130d6b115e48399b09e08f659b42e0.png