多数据源实体数据清洗：SQL按列筛选最优值的优化方案咨询

现有一张存储不同数据源同一实体信息的表dbo.infoBySource，表结构和测试数据如下：

create table dbo.infoBySource
(
    entityId     int
    ,dataSourceId int
    ,col1         varchar(50)
    ,col2         int
    ,constraint pk_infoBySource primary key (entityId, dataSourceId)
)

insert dbo.infoBySource values  
(1 ,1 ,'a' ,null),    
(1 ,2 ,'a2' ,10),    
(2 ,2 ,'c' ,20)

需要编写SQL查询，移除dataSourceId字段，按列保留最优可用信息（忽略NULL值），其中数据源的质量优先级由dataSourceId值决定（值越大优先级越高）。

原方案通过CTE标记各列的保留行，再分组聚合获取结果，但存在难以维护（新增列需额外添加ROW_NUMBER逻辑）、易出错、效率可能偏低的问题，寻求更优实现方案。

方案1：使用FIRST_VALUE窗口函数（最简洁） #

FIRST_VALUE可以直接在窗口内获取符合排序规则的首行非空值，无需额外标记和分组，代码简洁易维护：

SELECT DISTINCT
    entityId,
    FIRST_VALUE(col1) OVER (
        PARTITION BY entityId 
        ORDER BY CASE WHEN col1 IS NOT NULL THEN dataSourceId ELSE -1 END DESC
    ) AS col1,
    FIRST_VALUE(col2) OVER (
        PARTITION BY entityId 
        ORDER BY CASE WHEN col2 IS NOT NULL THEN dataSourceId ELSE -1 END DESC
    ) AS col2
FROM dbo.infoBySource

关键说明 #

• 窗口排序逻辑：优先将非空值按dataSourceId降序排列（优先级高的数据源在前），空值自动排到末尾
• DISTINCT用于去重，因为每个entityId会对应多行数据，只保留最终聚合后的一行结果
• 新增列时只需复制FIRST_VALUE的代码块并修改列名即可，维护成本极低

方案2：使用OUTER APPLY逐列获取最优值（逻辑最直观） #

这种方式针对每个实体和列单独查询优先级最高的非空值，逻辑清晰，调试和修改更方便：

SELECT
    i.entityId,
    c1.col1,
    c2.col2
FROM (SELECT DISTINCT entityId FROM dbo.infoBySource) i
OUTER APPLY (
    SELECT TOP 1 col1
    FROM dbo.infoBySource
    WHERE entityId = i.entityId AND col1 IS NOT NULL
    ORDER BY dataSourceId DESC
) c1
OUTER APPLY (
    SELECT TOP 1 col2
    FROM dbo.infoBySource
    WHERE entityId = i.entityId AND col2 IS NOT NULL
    ORDER BY dataSourceId DESC
) c2

关键说明 #

• 先提取所有唯一的entityId，再通过OUTER APPLY分别关联各列的最优值
• 利用主键(entityId, dataSourceId)的索引，查询效率较高
• 每列的查询逻辑独立，新增或修改列时只需调整对应APPLY块即可

方案3：简化聚合逻辑（针对原方案优化） #

如果偏好聚合方式，可以简化原有的标记逻辑，直接用行号判断保留行：

SELECT
    entityId,
    MAX(CASE WHEN rn_col1 = 1 THEN col1 END) AS col1,
    MAX(CASE WHEN rn_col2 = 1 THEN col2 END) AS col2
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY entityId 
            ORDER BY CASE WHEN col1 IS NOT NULL THEN dataSourceId ELSE -1 END DESC
        ) AS rn_col1,
        ROW_NUMBER() OVER (
            PARTITION BY entityId 
            ORDER BY CASE WHEN col2 IS NOT NULL THEN dataSourceId ELSE -1 END DESC
        ) AS rn_col2
    FROM dbo.infoBySource
) t
GROUP BY entityId

关键说明 #

• 去掉了原方案中isCol1ToKeep这类冗余标记字段，直接用行号rn_col1、rn_col2判断是否为最优行
• 逻辑更紧凑，减少了不必要的条件判断

方案对比 #

• 所有方案都比原方案更简洁，新增列时的修改成本更低
• FIRST_VALUE和OUTER APPLY的方式可读性更强，不易出错
• 合理利用索引的情况下，效率优于原方案的两次窗口函数+分组聚合

内容的提问来源于stack exchange，提问作者AleV