DQS 如何清除主表中的重复项并更正所有引用表中的 ID

Question

主表中有很多重复项。请参阅以下示例，客户 ABC 公司在主表中存在 3 次，并且所有 3 个 CustID 都在"订单"表中引用。

**Customers Table**
<table>
<tr><td><strong>CustID</strong></td><td><strong>CustName</strong></td></tr>
<tr><td>1001</td><td>ABC Corp.</td></tr>  
<tr><td>1002</td><td>XYZ Corp.</td></tr>  
<tr><td>1003</td><td>ABC Corp Ltd.</td></tr>  
<tr><td>1002</td><td>ABC Corporation Limited.</td></tr>  
</table>
**Orders Table**
<table>
<tr><td><strong>OrderID</strong></td><td><strong>CustID</strong></td></tr>
<tr><td>23425</td><td>1001</td></tr>  
<tr><td>23466</td><td>1003</td></tr>  
<tr><td>23488</td><td>1003</td></tr>  
<tr><td>43877</td><td>1004</td></tr>  
</table>

如何使用数据质量服务从"客户"表中删除重复的客户，以及如何更新"订单"表以反映更改。

即从订单表中合并为 1001 和 1003 和

1004 的 CustID 1003 和 1004 也需要更新为 1001。

到目前为止，我做了什么。使用CustName的同义词为客户创建了知识库，并定义了前导值。然后在 DQS 中创建数据清理项目并分析数据，然后对主数据进行更正，并将这些更正的值导入客户知识库。我知道 SSIS 中的 DQS 清理转换通过连接到源表和 DQS KB 自动执行此操作，并提供匹配和不匹配的行，但我仍然不明白从这里开始该怎么做。如何更正"客户"表中的数据并相应地更新"订单"表中的数据。请指教。到目前为止，我看到的示例使用的是非常基本的东西，只有一个表格和一个简单的 excel 表。

Answer 1

Geek，

当您运行 SQL Server 数据质量服务项目进行清理作为最后一步时，向导会要求您如何导出结果。

如果选择与目标相同的源表，则清理后会将原始数据替换为新数据

但我认为将数据导出到临时表中，然后执行SQL查询以更新经过验证的更改是最佳选择

我有一个国家列表表。然后，通过运行 DQS 项目进行数据清理，并将结果（包含清理信息的数据）导出到 SQL Server 数据库中的新表 CountryListDQS

稍后，通过运行类似于下面的 SQL Update 语句，您可以使用 DQS 中经过验证的更改更新原始数据

update dbo.CountryList
set
    country = Q.country_Output
from dbo.CountryList C
inner join dbo.CountryListDQS Q
on C.id = Q.id_Output
where Q.country_Status = 'Corrected'

Answer 2

将数据行的清理和匹配列表存储在新的数据库表中后，可以首先使用新值更新引用表字段（对于该记录的重复项），然后删除查找表中的重复项。

不幸的是，我不知道DQS是否可以为我们做到这一点。但以下查询可能会对您有所帮助。

以下一个将引用表更新为 CountryId 字段。我更喜欢将具有最小 Id 值的行保留为原始行，将所有其他行保留为重复行。

;with cte as (
select
    NewCountryId = Min(id_Output) Over (Partition By Country_Output),
    *
from dbo.CountryListDQS
where country_Status = 'Corrected'
)
Update CityList
Set
    CountryId = cte.NewCountryId
From CityList
Inner Join cte on cte.id_Output = CityList.CountryId
where CityList.CountryId <> cte.NewCountryId

您可以将 SQL 聚合函数与 Partition By 子句一起使用，如 Sum（）、Count（） 或 Min（） 等。

在下面的删除命令删除查找表中的重复行。也许最好使用标志字段设置非活动状态，而不是从表中删除行

with cte as (
select
    rn = ROW_NUMBER() Over (Partition By Country_Output Order By id_Output),
    *
from dbo.CountryListDQS
where country_Status = 'Corrected'
)
delete #CountryListTmp
from #CountryListTmp
inner join cte on #CountryListTmp.id = cte.id_Output
where rn > 1

或者最好保留查找表的副本。如果由于 CountryId 的孤立记录缺少外键关系而出现数据完整性问题，您仍然可以在备份表中找到原始记录。

您可以使用 SQL Row_Number（） 函数作为其他选项进一步检查如何删除重复行。

最后一点，我不确定数据质量服务是否删除重复的行并将所有其他引用表行映射到原始值。