full join 与 union

淡淡的烟草味﹌ 2023-01-19 03:06 1阅读 0赞

### full join 与 union ###

*  描述
 *  一、表简介
 *  二、转换步骤
 *   *  2.1 完整sql
     *  2.2 转换中的问题
     *   *  2.2.1 nvl
         *  2.2.2 full join ods\_table\_c\_rnk 表
 *  三、UNION效验
 *  总结

# 描述 #

有三张表需要对每个表进行去除重复后关联进行列的补充，并且保留未匹配上的所有数据。由于有列的补充并保留所有数据，所以选择了full join进行操作处理，最终使用union进行数据量的验证

--------------------

提示：以下是本篇文章正文内容，下面案例可供参考

# 一、表简介 #

ods\_table\_a(主键 product\_id , dt) 、ods\_table\_b(主键 product\_id , dt) 、ods\_table\_c(主键 product\_id , dt)

# 二、转换步骤 #

## 2.1 完整sql ##

代码如下（示例）：

tmp_ods_table_a as (
        select 
            *,
            row_number() over(PARTITION BY product_id,dt order by dt desc) rnk 
        from ods_table_a
        where product_id is not null and product_id !=''),
    ods_table_a_rnk as (
        select * from tmp_ods_table_a where rnk = 1),
    tmp_ods_table_b as (
        select 
            *,
            row_number() over(PARTITION BY product_id,dt order by dt asc) rnk 
        from ods_table_b
        where product_id is not null and product_id !=''),
    ods_table_b_rnk as (
        select * from tmp_ods_table_b where rnk = 1),
    tmp_ods_table_c as (
        select 
            *,
            row_number() over(PARTITION BY product_id,dt order by dt desc) rnk 
        from 
            ods_table_c 
        where product_id is not null and product_id !=''),
    ods_table_c_rnk as (
        select * from tmp_ods_table_c where rnk = 1)
    select 
        nvl(nvl(a.product_id,b.product_id),c.product_id) as product_id,
        nvl(nvl(a.dt,b.dt),c.dt) as dt
    from 
        ods_table_a_rnk as a 
    full join
        ods_table_b_rnk as b
    on a.product_id = b.product_id and a.dt = b.dt
    full join
    	ods_table_c_rnk as c
    on nvl(a.product_id, b.product_id) = c.product_id and nvl(a.dt, b.dt) = c.dt

## 2.2 转换中的问题 ##

### 2.2.1 nvl ###

Alias for the isnull() function. Tests if an expression is NULL, and returns the expression result value if not. If the first argument is NULL, returns the second argument. Equivalent to the nvl() function from Oracle Database or ifnull() from MySQL.

由于关联的表为ods层的原始数据所以关联前需要对其进行去除空值的处理，使用的是full join 所以会存在两张表没有匹配上的情况，最终生成的product\_id与dt数据就需要使用nvl进行处理才能拿到所有的product\_id 和dt的数据！！

### 2.2.2 full join ods\_table\_c\_rnk 表 ###

由于ods\_table\_a\_rnk与ods\_table\_b\_rnk join出的数据会存在product\_id与dt为null值问题，所以这里的需要使用 nvl(a.product\_id, b.product\_id) = c.product\_id and nvl(a.dt, b.dt) = c.dt ，最终数据插入dwd\_table\_a中

# 三、UNION效验 #

WITH union_a as (
    	SELECT product_id, dt from ods_table_a where product_id is NOT NULL and product_id !=''
        UNION 
        SELECT product_id, dt  from ods_table_b where product_id is NOT NULL and product_id !=''
        UNION
        SELECT product_id, dt from ods_table_c where product_id is NOT NULL and product_id !=''
    )
    SELECT count(*) as cnt_1 ,dt ,1 as type from union_a GROUP BY dt
    UNION all 
    select count(*) as cnt_1 ,dt ,2 from dwd_table_a GROUP BY dt;

校验结果展示：  
![union校验结果][union]

--------------------

# 总结 #

提示：这里对文章进行总结：  
以上就是今天要讲的内容，本文仅仅使用一个案例简单的介绍了full join 使用时的一些注意事项，以及如何去对full join出的结果数据量进行校验，达到更好的一个结果产出！

[union]: /images/20221021/bc336ee9c2ab4e2ab7adb14fc359e3d0.png