苦练基本功-数据合并

数据仓库晨曦

发布于 2025-06-17 10:39:55

2290

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

日常工作中我们需要把不同数据来源的数据，合并到一起，不同的数据来源，不同的业务逻辑合并方式也不同。日常遇到的需求有以下几种：

数据内容不交叉；
存在整行数据交叉（重复），需要进行去重处理；
存在主键，主键有交叉，需要根据数据来源确定选取不同行；
存在主键，主键有交叉，字段取值需要根据数据来源选取；
存在主键，主键有交叉，字段取值需要根据本字段值来选取；
存在主键，主键有交叉，字段取值需要根据其他字段值来选取；

1.数据内容不交叉

已知t1,t2两张表，其中数据不重复，请把两表数据合并

--有t1表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

--t2表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 3   | cc    | 92     |
| 4   | dd    | 86     |
+-----+-------+--------+

SQL 这种数据，数据我们直接使用union all进行合并。

select id,
       name,
       score
from t1
union all
select id,
       name,
       score
from t2;

执行结果

+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 3   | cc    | 92     |
| 4   | dd    | 86     |
+-----+-------+--------+

2.去重合并

已知有t1,t3两张表，其中数据（可能）存在重复，要求结果不重复。

--有t1表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

--t3表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 4   | dd    | 86     |
+-----+-------+--------+

因为需要去重，所以直接使用union

SQL

select id,
       name,
       score
from t1
union
select id,
       name,
       score
from t3;

执行结果

+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 4   | dd    | 86     |
+-----+-------+--------+

3.根据数据来源选取行

已知有t1，t4两张表，其中id存在重复，如果存在重复id，优先取t1表数据。

--有t1表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

--t4表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 55     |
| 4   | dd    | 86     |
+-----+-------+--------+

因为需要根据不同的数据来源确定优先级，所以

先给数据增加一列数据来源source,t1表来的数据，source=1,t4表来的数据source=4；
使用union all将数据进行合并；
使用开窗函数，根据id进行分组，根据来源进行排序rn；
选取rn=1的数据

SQL

select id,
       name,
       score,
       source
from (select id,
             name,
             score,
             source,
             row_number() over (partition by id order by source asc) as rn
      from (select id,
                   name,
                   score,
                   1 as source
            from t1
            union all
            select id,
                   name,
                   score,
                   4 as source
            from t4) tt) ttt
where ttt.rn = 1

执行结果

+-----+-------+--------+---------+
| id  | name  | score  | source  |
+-----+-------+--------+---------+
| 1   | aa    | 90     | 1       |
| 2   | bb    | 87     | 1       |
| 4   | dd    | 86     | 4       |
+-----+-------+--------+---------+

注意： 这里我们给出了source代表数据行来源数据哪个表。实际可以不带该列。

4.根据数据来源选取字段值

已知有t1，t5两张表，其中id存在重复，对于相同id的得分，name字段优先取t1表，score字段优先取t5表

--有t1表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

--t5表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | ee    | 67     |
| 4   | dd    | 86     |
+-----+-------+--------+

这里我们使用coalesce(expr1, expr2, ...)函数，从左到右依次取值，优先取值的字段放到前面。

SQL

select coalesce(t1.id, t5.id)       as id,
       coalesce(t1.name, t5.name)   as name,
       coalesce(t5.score, t1.score) as score
from t1
         full outer join t5
                         on t1.id = t5.id

执行结果

+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 67     |
| 2   | bb    | 87     |
| 4   | dd    | 86     |
+-----+-------+--------+

5.根据本字段值选取字段来源

已知有t1，t5两张表，其中id存在重复，对于相同id的得分，name取字母序大的，score取分值小的；

--有t1表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

--t5表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | ee    | 67     |
| 4   | dd    | 86     |
+-----+-------+--------+

这里涉及到了函数

greatest(expr, ...) 取参数中的最大值

least(expr, ...)取参数中的最小值

SQL

select coalesce(t1.id, t5.id)       as id,
       greatest(t1.name, t5.name)   as name,
       least(t1.score, t5.score) as score
from t1
         full outer join t5
                         on t1.id = t5.id

执行结果

+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | ee    | 67     |
| 2   | bb    | 87     |
| 4   | dd    | 86     |
+-----+-------+--------+

6.根据其他字段值选取本字段来源

已知有t1，t5两张表，其中id存在重复，对于相同id的得分，name取score分值大的，score取分值大的。

--有t1表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
+-----+-------+--------+

--t5表如下
+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | ee    | 67     |
| 4   | dd    | 86     |
+-----+-------+--------+

这里需要判断条件，可以使用if或者case when，这里需要注意取值的时候需要coalsece()处理。 欢迎留言说出原因

SQL

select coalesce(t1.id, t5.id)       as id,
       case when t1.score > t5.score then coalesce(t1.name,t5.name) else coalesce(t5.name,t1.name) end as name,
       greatest(t1.score, t5.score) as score
from t1
         full outer join t5
                         on t1.id = t5.id

执行结果

+-----+-------+--------+
| id  | name  | score  |
+-----+-------+--------+
| 1   | aa    | 90     |
| 2   | bb    | 87     |
| 4   | dd    | 86     |
+-----+-------+--------+

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

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

数据