Dynamic SQL全表数据质量唯一性校验逻辑集成方案咨询

问题核心 #

你原来的单字段唯一性计算逻辑嵌套了多层子查询，拼接到动态SQL时会出现引号嵌套、聚合函数嵌套冲突的问题，同时单字段多表扫描的写法会导致全表计算时性能极低。

优化方案 #

先简化唯一性计算逻辑：
原多层子查询可以直接简化为单聚合计算：count(distinct 字段名) 就是该字段的去重非空值数量，除以 count(字段名) 即可得到非空值中的唯一性占比，除以 count(*) 则是全表范围的唯一性占比，完全不需要嵌套子查询。

单独批量计算唯一性占比的完整脚本 #

/*Unicidad（唯一性维度校验）*/
-- 清理临时表
drop table if exists tmp_unicidad;
declare @custom_sql VARCHAR(max)
declare @tablename as VARCHAR(255) = 'maestrodatoscriticos' -- 替换为你的目标表名

-- 拼接动态SQL头部
set @custom_sql = 'select ''' + @tablename + '_Unicidad' + ''' as tabla'

-- 拼接每个字段的唯一性计算逻辑
select @custom_sql = @custom_sql + ', ' + 
    'cast(count(distinct ' + QUOTENAME(c.name) + ') as decimal) / count(*) as ' + QUOTENAME(c.name)
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

-- 拼接收尾逻辑，结果写入临时表
set @custom_sql = @custom_sql + ' into tmp_unicidad from ' + QUOTENAME(@tablename)

-- 调试可打开print查看生成的SQL
-- print @custom_sql
exec (@custom_sql);

-- 写入维度结果表
insert into dimensiones
select *
from tmp_unicidad;

注：如果要和你原有逻辑一致，判断字段是否完全无重复（全唯一返回1，否则返回0），可以把字段计算逻辑替换为：

'cast(iif(count(distinct ' + QUOTENAME(c.name) + ') = count(' + QUOTENAME(c.name) + '), 1, 0) as decimal) as ' + QUOTENAME(c.name)

与原有完整性校验合并的脚本（一次计算两个维度） #

如果要一次性得到所有字段的完整性、唯一性两个维度的结果，可以直接修改原有动态SQL的拼接逻辑：

/*Completitud + Unicidad 两个维度合并计算*/
drop table if exists tmp_data_quality;
declare @custom_sql VARCHAR(max)
declare @tablename as VARCHAR(255) = 'maestrodatoscriticos'

set @custom_sql = 'select ''' + @tablename + '_DataQuality' + ''' as tabla'

-- 同时拼接完整性、唯一性两个维度的计算逻辑
select @custom_sql = @custom_sql + 
    ', sum(cast(iif(' + QUOTENAME(c.name) + ' is null,0,1) as decimal)) / count(*) as ' + QUOTENAME(c.name + '_completitud') +
    ', cast(count(distinct ' + QUOTENAME(c.name) + ') as decimal) / count(*) as ' + QUOTENAME(c.name + '_unicidad')
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

set @custom_sql = @custom_sql + ' into tmp_data_quality from ' + QUOTENAME(@tablename)
exec (@custom_sql);

insert into dimensiones
select *
from tmp_data_quality;

注意事项 #

• 代码中用QUOTENAME包裹字段名、表名，避免字段名包含特殊字符、关键字时报错
• 计算唯一性时如果字段存在空值，count(distinct)会自动忽略空值，如果需要把空值也视为唯一值的一种，可自行调整计算逻辑为count(distinct isnull(字段名, '占位符'))

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