提问人：Pipe Pi 提问时间：10/5/2023 最后编辑：Dale KPipe Pi 更新时间：10/5/2023 访问量：59
使用上一个非空行中的值填充空单元格的慢速查询
Slow query to fill down empty cells with values from a previous non-empty row
问：
我得到了这个查询，用上一个非空行的值填充空单元格，但它花了很长时间（有近 100.000 条记录）。
希望你能帮助我用更快的查询来实现这一点。
这些都是带有示例数据的 dll：
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;
答：
0赞
Patrick Hurst
10/5/2023
#1
如果没有计划，我们将无法知道发生了什么（正如戴尔所建议的那样）。 与此同时，也许这会好一点：
SELECT x.id_num, COALESCE(NULLIF(x.Provider,' '),y.provider) AS [Provider], x.Location, x.last_update, x.Total_Charge
FROM @TEST_INSURANCE_PAYMENTS x
OUTER APPLY (SELECT MAX(id_num) AS mid_num FROM @TEST_INSURANCE_PAYMENTS y WHERE y.id_num < x.id_num AND y.[Provider] IS NOT NULL AND y.Provider <> ' ' AND x.Provider = '') a
LEFT OUTER JOIN @TEST_INSURANCE_PAYMENTS y
ON a.mid_num = y.id_num;
| id_num | 供应商 | 位置 | last_update | Total_Charge |
|---|---|---|---|---|
| 1 | 维马尔库马尔·维拉潘 | 亚利桑那州心脏专家 | 2023-10-04 15:55:59.6833333 | 100.00 |
| 2 | 维马尔库马尔·维拉潘 | 班纳博斯韦尔医疗中心 - 住院 | 2023-10-04 15:55:59.6833333 | 102.00 |
| 3 | 维马尔库马尔·维拉潘 | 亚利桑那州心脏专家西部 | 2023-10-04 15:55:59.6833333 | 800.00 |
| 4 | 阿卡什·马卡尔 | 亚利桑那州心脏专家西部 | 2023-10-04 15:55:59.6833333 | 500.00 |
| 5 | 阿卡什·马卡尔 | Pinnacle Vein & Vascular Center， 太阳城 | 2023-10-04 15:55:59.6833333 | 500.00 |
| 6 | 阿卡什·马卡尔 | Abrazo Arizona Heart Hospital - 门诊 | 2023-10-04 15:55:59.6833333 | 60.00 |
| 7 | 阿卡什·马卡尔 | 班纳博斯韦尔医疗中心 - 住院 | 2023-10-04 15:55:59.6833333 | 60.00 |
| 8 | 阿卡什·马卡尔 | Banner Del E Webb 医疗中心 - 住院 | 2023-10-04 15:55:59.6833333 | 10.00 |
评论
0赞
Pipe Pi
10/6/2023
您好，非常感谢。我测试了您的所有解决方案，似乎最快的解决方案具有 LAG 功能。再次感谢。
1赞
siggemannen
10/5/2023
#2
有很多方法可以做到这一点：
老斯科尔：
SELECT id_num
, CASE WHEN provider = '' THEN (SELECT TOP 1 Provider FROM #TEST_INSURANCE_PAYMENTS tt WHERE tt.id_num < t.id_num AND Provider <> '' ORDER BY id_num DESC) ELSE provider END
, location, last_update, Total_Charge
FROM #TEST_INSURANCE_PAYMENTS t
如果当前提供程序为空，则从前几行中获取最后一个非空提供程序。缺点是额外的连接。
新热度：
SELECT id_num
, CASE
WHEN provider = '' THEN STUFF(MAX(RIGHT(CONCAT('0000000000',id_num), 10) + CASE WHEN Provider <> '' THEN Provider END) OVER(ORDER BY id_num), 1, 10, '')
ELSE provider END
, location, last_update, Total_Charge
FROM #TEST_INSURANCE_PAYMENTS t
我不知道这种技术的名称，但它的作用是创建一个id_num +提供者组合的单帧，例如：0000000001Vimalkumar Veerappan。如果 provider 为空，则将其更改为 NULL，因此 value 变为 NULL。
通过接听电话，我们获取该帧中的最后一个值。然后，通过这样做，我们删除了0000000001部分，剩下的就是我们需要的。MAX(...) OVER (ORDER BY id_num)STUFF(...)
此技术支持单个窗口调用，这可能会产生最佳性能，缺点是代码有些模糊。
评论
0赞
Alan Schofield
10/5/2023
喜欢那个 siggemanne 的表演！我正在测试我相当老式的方法，在一个小型 docker 容器上运行超过 100,000 行需要 3 分钟，所以实际时间不是太重要，但在同一个 docker 容器上运行这个版本需要 2 秒！现在只需要把我的头包起来:)
0赞
MatBailie
10/5/2023
#3
对于 Azure 或 SQL Server 2022，只需将 LAG（） 与IGNORE NULLS
这样可以避免 JOINS 相关的子查询等，并且只扫描表一次。
SELECT
*,
LAG(
NULLIF(Provider, ' '),
0
)
IGNORE NULLS
OVER (
ORDER BY id_num
)
FROM
#TEST_INSURANCE_PAYMENTS
| id_num | 供应商 | 位置 | last_update | Total_Charge | （无列名） |
|---|---|---|---|---|---|
| 1 | 维马尔库马尔·维拉潘 | 亚利桑那州心脏专家 | 2023-10-04 22:41:21.590 | 100.0000 | 维马尔库马尔·维拉潘 |
| 2 | 班纳博斯韦尔医疗中心 - 住院 | 2023-10-04 22:41:21.590 | 102.0000 | 维马尔库马尔·维拉潘 | |
| 3 | 亚利桑那州心脏专家西部 | 2023-10-04 22:41:21.590 | 800.0000 | 维马尔库马尔·维拉潘 | |
| 4 | 阿卡什·马卡尔 | 亚利桑那州心脏专家西部 | 2023-10-04 22:41:21.590 | 500.0000 | 阿卡什·马卡尔 |
| 5 | Pinnacle Vein & Vascular Center， 太阳城 | 2023-10-04 22:41:21.590 | 500.0000 | 阿卡什·马卡尔 | |
| 6 | Abrazo Arizona Heart Hospital - 门诊 | 2023-10-04 22:41:21.590 | 60.0000 | 阿卡什·马卡尔 | |
| 7 | 班纳博斯韦尔医疗中心 - 住院 | 2023-10-04 22:41:21.590 | 60.0000 | 阿卡什·马卡尔 | |
| 8 | Banner Del E Webb 医疗中心 - 住院 | 2023-10-04 22:41:21.590 | 10.0000 | 阿卡什·马卡尔 |
评论