优化用上一行非空值填充空白单元格的慢查询请求

问题说明 #

现有查询可实现用上方最近的非空Provider值填充空白单元格，但数据量接近10万条时查询速度极慢，急需更高效的实现方案。以下是包含示例数据的DDL及原查询：

示例数据DDL #

CREATE TABLE #TEST_INSURANCE_PAYMENTS(
     id_num int IDENTITY(1,1),
     [Provider] varchar(59), 
     [Location] varchar(104),
     last_update datetime2, 
     [Total_Charge] money
);
INSERT INTO #TEST_INSURANCE_PAYMENTS
    ([Provider], [Location],[last_update],[Total_Charge])
VALUES
    ('Vimalkumar Veerappan', 'Arizona Heart Specialists',CURRENT_TIMESTAMP,100.0),
    (' ', 'Banner Boswell Medical Center - Inpatient',CURRENT_TIMESTAMP,102.0),
    (' ', 'Arizona Heart Specialists WEST',CURRENT_TIMESTAMP,800.0),
    ('Akash Makkar', 'Arizona Heart Specialists WEST',CURRENT_TIMESTAMP,500.0),
    (' ', 'Pinnacle Vein & Vascular Center Sun City',CURRENT_TIMESTAMP,500.0),
    (' ', 'Abrazo Arizona Heart Hospital - Outpatient',CURRENT_TIMESTAMP,60.0),
    (' ', 'Banner Boswell Medical Center - Inpatient',CURRENT_TIMESTAMP,60.0),
    (' ', 'Banner Del E Webb Medical Center - Inpatient',CURRENT_TIMESTAMP,10.0);

原查询（性能瓶颈版本） #

SELECT id_num,[Provider],[Location],[Total_Charge],[last_update]
FROM #TEST_INSURANCE_PAYMENTS WHERE ISNULL([Provider], '') <> ''
UNION
SELECT y.id_num, x.[Provider], y.[Location],y.[Total_Charge],y.[last_update]
FROM #TEST_INSURANCE_PAYMENTS AS x
JOIN(
    SELECT t1.id_num,MAX(t2.id_num) AS MaxID, t1.[Location],t1.[Total_Charge],t1.[last_update]
    FROM (SELECT * FROM #TEST_INSURANCE_PAYMENTS WHERE ISNULL([Provider], '') = '') AS t1
    JOIN (SELECT * FROM #TEST_INSURANCE_PAYMENTS WHERE ISNULL([Provider], '') <> '') AS t2 ON t1.id_num > t2.id_num
    GROUP BY t1.id_num,  t1.[Location],t1.[Total_Charge],t1.[last_update]
)AS y ON x.id_num = y.MaxID 
ORDER BY id_num;

优化方案：窗口函数实现高效填充 #

原查询通过自连接+分组匹配最近非空值，会产生大量笛卡尔积，10万条数据下性能极差。推荐使用SQL Server窗口函数实现，仅需一次全表扫描，时间复杂度为O(n)：

WITH ProviderGroups AS (
    SELECT 
        id_num,
        Provider,
        Location,
        Total_Charge,
        last_update,
        -- 标记分组：每遇到非空Provider则递增分组ID，连续空白行归为同一组
        SUM(CASE WHEN LTRIM(RTRIM(ISNULL(Provider, ''))) <> '' THEN 1 ELSE 0 END)
            OVER (ORDER BY id_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM #TEST_INSURANCE_PAYMENTS
)
SELECT 
    id_num,
    -- 在分组内提取第一个非空Provider值，填充所有行
    FIRST_VALUE(CASE WHEN LTRIM(RTRIM(ISNULL(Provider, ''))) <> '' THEN Provider END)
        OVER (PARTITION BY GroupID ORDER BY id_num) AS Provider,
    Location,
    Total_Charge,
    last_update
FROM ProviderGroups
ORDER BY id_num;

方案优势 #

• 性能高效：窗口函数仅扫描数据集一次，避免原查询中O(n²)级别的自连接操作，10万条数据下执行速度提升数十倍。
• 逻辑清晰：通过分组标记明确归属关系，填充逻辑直观易懂。
• 兼容处理：加入LTRIM(RTRIM())处理空白字符串（原数据中空白为空格），避免误判。

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