苦练基本-整表数据对比（二）

数据仓库晨曦

发布于 2025-07-17 17:02:24

8300

代码可运行

文章被收录于专栏：数据仓库技术数据仓库技术

运行总次数：0

代码可运行

在上一篇中我们提到了两个问题,今天我们来填坑

使用union all 方式想要知道差异数据来源于哪张表，该如何处理？
使用full outer join 方式，如果有字段存在空值该如何处理？

1.union all 方式区分数据来源

这里我们只比较t1表和t1_3表中的差异，希望使用union all方式,并且希望知道差异数据来源于哪张表

1.1 表中不存在重复记录

样例数据

--t1表数据
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

-- t1_3表数据
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 3   | cc    | 80     |
+-----+-------+--------+

t1_3表中增加了cc的数据，但是没有bb的数据；

原始数据中每行数据均不重复，增加数据来源字段，其中t1表我们source=1,t1_3表source=2,然后使用union all 将两表数据进行合并，group by 表中原始所有字段，对source 字段求和，如果sum(source)的结果为3 则代表该行数据一致，如果为2代表该行数据来源于t1_3,如果为1代表该行数据来源t1；

对比SQL

--t1与t1_3比较
select id,
       name,
       score,
       sum(source) as source_val
from (select *, 1 as source
      from t1
      union all
      select *, 2 as source
      from t1_3) t
group by id,
         name,
         score
--执行结果
+-----+-------+--------+-------------+
| id  | name  | score  | source_val  |
+-----+-------+--------+-------------+
| 1   | aa    | 90     | 3           |
| 2   | bb    | 87     | 1           |
| 3   | cc    | 80     | 2           |
+-----+-------+--------+-------------+

以上就是全部结果数据了，如果想要看差异数据，限定source_val即可，例如下面我们希望找到所有差异数据，限定不为3。

以t1与t1_3比较为例

--t1与t1_3比较
select *
from (select id,
             name,
             score,
             sum(source) as source_val
      from (select *, 1 as source
            from t1
            union all
            select *, 2 as source
            from t1_3) t
      group by id,
               name,
               score) tt
where tt.source_val <> 3
--执行结果
+-----+-------+--------+-------------+
| id  | name  | score  | source_val  |
+-----+-------+--------+-------------+
| 2   | bb    | 87     | 1           |
| 3   | cc    | 80     | 2           |
+-----+-------+--------+-------------+

1.2 原始数据存在重复记录

样例数据

--t6，表内 id=2是完全一致的两条数据。
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 2   | bb    | 87     |
| 3   | cc    | 80     |
+-----+-------+--------+

--t6_2，id=2的数据只有一条。
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 3   | cc    | 80     |
+-----+-------+--------+

因为存在重复数据，所以需要对原始数据进行数据行数统计之后，再进行对比。

对比SQl

--t6与t6_2比较
select id,
       name,
       score,
       row_cnt,
       sum(source) as source_val
from (
         --原始数据重复行计数
         select id,
                name,
                score,
                count(1) as row_cnt,
                1        as source
         from t6
         group by id, name, score
         union all
         --对比表重复行计数
         select id,
                name,
                score,
                count(1) as row_cnt,
                2        as source
         from t6_2
         group by id, name, score) tt1
group by id,
         name,
         score,
         row_cnt
having sum(source) <> 3

--对比结果
+-----+-------+--------+----------+-------------+
| id  | name  | score  | row_cnt  | source_val  |
+-----+-------+--------+----------+-------------+
| 2   | bb    | 87     | 2        | 1           |
| 2   | bb    | 87     | 1        | 2           |
+-----+-------+--------+----------+-------------+
--备注：
--这里可以看到， 存在数据差异，id=2的 t6表中的数据行数是2行（row_cnt=2),t6_2中数据一行（row_cnt=1)。

2.full outer join 方式对空值比较

因为存在空值，这里会涉及到具体的判断逻辑，例如空串与null是否一致等情况。这里我们期望按照下面原则进行对比，

注意：

正常情况下 null!=null 这里规则定义为null=null是希望大家能进行处理。

'' = ''
null =null 
'' != null

样例数据

--t7
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 3   |       | 82     |
| 4   | NULL  | 91     |
| 5   |       | 99     |
+-----+-------+--------+

--t7_1
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 3   |       | 82     |
| 4   | NULL  | 91     |
| 5   | NULL  | 99     |
+-----+-------+--------+

按照上面逻辑，我们预期相同行的id为1,2,3,4是相同的，5是不同的。

对比SQl

--先直接进行比较
select t7.id,
       t7.name,
       t7.score,
       t7_1.id,
       t7_1.name,
       t7_1.score
from t7
         full outer join t7_1
                         on t7.id = t7_1.id
                             and t7.name = t7_1.name
                             and t7.score = t7_1.score
where t7.id is null
   or t7_1.id is null;

--执行结果

+-------+-------+--------+-------+-------+--------+
|  id   | name  | score  |  id   | name  | score  |
+-------+-------+--------+-------+-------+--------+
| 4     | NULL  | 91     | NULL  | NULL  | NULL   |
| NULL  | NULL  | NULL   | 4     | NULL  | 91     |
| NULL  | NULL  | NULL   | 5     | NULL  | 99     |
| 5     |       | 99     | NULL  | NULL  | NULL   |
+-------+-------+--------+-------+-------+--------+

我们发现上面结果中，5符合预期，4是null！=null。这里大家一定要注意。那么怎么解决呢？

使用equal_null 函数

equal_null(expr1, expr2) - 对于非空操作数，返回与等于（EQUAL(=)）运算符相同的结果，但如果两个操作数都是 null，则返回真，如果其中一个是 null，则返回假。

对比SQl

select t7.id,
       t7.name,
       t7.score,
       t7_1.id,
       t7_1.name,
       t7_1.score
from t7
         苦练基本功-整表数据对比（二） outer join t7_1
                         on t7.id = t7_1.id
                             and equal_null(t7.name,t7_1.name)
                             and t7.score = t7_1.score
where t7.id is null
   or t7_1.id is null;

--执行结果

+-------+-------+--------+-------+-------+--------+
|  id   | name  | score  |  id   | name  | score  |
+-------+-------+--------+-------+-------+--------+
| 5     |       | 99     | NULL  | NULL  | NULL   |
| NULL  | NULL  | NULL   | 5     | NULL  | 99     |
+-------+-------+--------+-------+-------+--------+

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-07-16，如有侵权请联系 cloudcommunity@tencent.com 删除

null