如何在Union双表时标记数据来源并生成业务ID？

基于以下两个数据源创建数据库，需完成表合并、ID生成及数据来源标记的需求：

表结构与初始化数据 #

Create Table table_name1(Column_A VARCHAR(50), Column_B int, Column_C VARCHAR(50), Column_D VARCHAR(50));

Insert Into table_name1 Values
('A1',5,'C1','D1'),
('A1',23,'C2',null),
('A2',2,'C2','D1'),
('A12',23,'C2',null),
('A2',23,'C2',null), 
('A12',12,'D2','C1');

Create Table table_name2(Column_A VARCHAR(50), Column_B int, Column_C VARCHAR(50), Column_D VARCHAR(50));

Insert Into table_name2 Values
('A1',5,'C1','D1'),
('A1',23,'C2',null),
('A1',21,'C2',null),
('A2',2,'C2','D1'),
('A2',34,'C2','D1'),
('A21',23,'C2','D1'),
('A2',23,'C2','D2'), 
('A12',12,'D2','C1'),
('A12',23,'C2',null);

注：原Insert Into table_name2语句末尾多了冗余逗号，已修正。

核心需求 #

1. 对两个表执行去重合并（Union逻辑）
2. 生成两个排名ID：
   • id_1：按Column_B值做密集排名（dense_rank）
   • id_2：按Column_A+Column_C+Column_D的组合做密集排名
3. 新增source列标记数据来源：
   • 仅在table_name1存在：标记为'table1'
   • 仅在table_name2存在：标记为'table2'
   • 两个表都存在：标记为'table1 & table2'

现有SQL的问题 #

当前CTE仅用UNION合并了唯一行，但丢失了数据来源信息，无法生成要求的source标记列。

修正后的SQL #

WITH combined_data AS (
    -- 保留所有行并标记原始来源
    SELECT *, 'table1' AS source FROM table_name1
    UNION ALL
    SELECT *, 'table2' AS source FROM table_name2
),
aggregated_sources AS (
    -- 按行内容聚合，合并来源标记
    SELECT 
        Column_A,
        Column_B,
        Column_C,
        Column_D,
        CASE 
            WHEN COUNT(DISTINCT source) = 2 THEN 'table1 & table2'
            ELSE MAX(source)
        END AS source
    FROM combined_data
    GROUP BY Column_A, Column_B, Column_C, Column_D
)
-- 生成排名ID并输出最终结果
SELECT
    DENSE_RANK() OVER (ORDER BY Column_B) AS id_1,
    DENSE_RANK() OVER (ORDER BY Column_A, Column_C, Column_D) AS id_2,
    Column_A,
    Column_B,
    Column_C,
    Column_D,
    source
FROM aggregated_sources
ORDER BY id_1, id_2;

逻辑说明 #

1. combined_data：用UNION ALL保留两个表的所有行，同时添加各自的来源标记，避免UNION自动去重丢失来源信息
2. aggregated_sources：按每行核心字段分组，统计来源数量：
   • 若两个来源都存在，标记为'table1 & table2'
   • 若仅单个来源，保留对应标记
3. 最后在聚合后的结果上计算id_1和id_2的密集排名，得到符合要求的输出

内容的提问来源于stack exchange，提问作者Abhiram Reddy Kotu