SQL Server 2019+中计算Benchmark与Sample的缺失数值占比

针对SQL Server 2019+环境下的需求，我们可以利用STRING_SPLIT函数拆分逗号分隔的字符串，结合窗口函数统计基准总数和匹配数，最终计算未匹配占比。以下是实现代码：

WITH NumberedRows AS (
    -- 为每行生成唯一标识，用于区分不同行的独立计算
    SELECT 
        Sample,
        Benchmark,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowId
    FROM #TempTable
),
SplitBenchmark AS (
    -- 拆分Benchmark列到单独行，去除值两端可能的空格
    SELECT 
        RowId,
        TRIM(value) AS BenchmarkValue
    FROM NumberedRows
    CROSS APPLY STRING_SPLIT(Benchmark, ',')
),
SplitSample AS (
    -- 拆分Sample列到单独行，去除值两端可能的空格
    SELECT 
        RowId,
        TRIM(value) AS SampleValue
    FROM NumberedRows
    CROSS APPLY STRING_SPLIT(Sample, ',')
),
Stats AS (
    -- 统计每行的基准总数和匹配成功的数量
    SELECT 
        sb.RowId,
        COUNT(sb.BenchmarkValue) OVER (PARTITION BY sb.RowId) AS TotalBenchmark,
        COUNT(ss.SampleValue) OVER (PARTITION BY sb.RowId) AS MatchedCount
    FROM SplitBenchmark sb
    LEFT JOIN SplitSample ss ON sb.RowId = ss.RowId AND sb.BenchmarkValue = ss.SampleValue
)
-- 计算最终未匹配占比，去重后返回每行结果
SELECT DISTINCT
    ROUND((TotalBenchmark - MatchedCount) * 100.0 / TotalBenchmark, 2) AS UnmatchedPercentage
FROM Stats;

代码说明 #

• NumberedRows：给临时表每行生成唯一RowId，确保拆分后的字符串能关联回原行，保证每行独立计算。
• SplitBenchmark/SplitSample：用STRING_SPLIT拆分逗号分隔的字符串，TRIM处理可能存在的空格，避免因空格导致匹配失败。
• Stats：通过左连接匹配基准值和样本值，用窗口函数统计每行的基准总数、匹配成功的数量。
• 最终计算：代入公式(基准总数-匹配数)*100.0/基准总数得到占比，ROUND用于控制小数位数（可按需调整），DISTINCT确保每行仅返回一个结果。

运行结果 #

针对给定的测试数据，执行后会返回：

UnmatchedPercentage
--------------------
50.00
0.00

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