填补时间空白

Question

我需要一些查询帮助，该查询将填补一些缺失的时间(以分钟为单位(空白，并保留当前状态。 我在医院工作，我们试图在任何特定时刻了解医院急诊科的瓶颈。
表中的数据如下所示：

Patient_Id      event_time             Event_Status_Name
98676249    2018-09-24 18:39:00.000    Expected
98676249    2018-09-24 19:17:00.000    Waiting for Triage
98676249    2018-09-24 19:28:00.000    In Triage
98676249    2018-09-24 19:29:00.000    Waiting for Room
98676249    2018-09-24 19:45:00.000    Waiting for Provider
98676249    2018-09-24 19:48:00.000    In Process
98676249    2018-09-24 21:02:00.000    Await IP Orders
98676249    2018-09-24 22:59:00.000    Await IP Bed
98676249    2018-09-25 21:44:00.000    Ready for Admit

这基本上告诉我患者在什么时候进入特定状态。 但是，我需要做的是填写缺失的分钟数，并保留它们所处的状态。 例如，从 2018-09-24 18：39：00.000 到 2018-09-24 19：16：00.000，患者处于"预期"状态。 仅以第一个状态为例，我想要的查询输出需要如下所示：

Patient_Id  event_time             Event_Status_Name
98676249    2018-09-24 18:39:00.000    Expected
98676249    2018-09-24 18:40:00.000    Expected
98676249    2018-09-24 18:41:00.000    Expected
98676249    2018-09-24 18:42:00.000    Expected
98676249    2018-09-24 18:43:00.000    Expected
98676249    2018-09-24 18:44:00.000    Expected
98676249    2018-09-24 18:45:00.000    Expected
98676249    2018-09-24 18:46:00.000    Expected
98676249    2018-09-24 18:47:00.000    Expected
98676249    2018-09-24 18:48:00.000    Expected

等等，等等，直到我遇到"等待分类"的下一个状态 ...然后我需要做同样的事情，对于该状态的每一分钟，直到下一分钟......等等。

如何在不使用循环的情况下编写执行此操作的查询？ 有数百万条记录(和分钟(需要考虑，所以我需要一个便宜的查询。

感谢您的帮助！

Answer 1

您可以在此处使用递归 CTE。作为锚点，获取所有现有行及其按每位患者的时间戳排序的行号。然后从锚点中选择将下一个事件应用于它们的行(通过行号和患者(，并检查下一分钟是否小于后续事件的时间戳。

WITH cte
AS
(
SELECT x.patient_id,
x.event_time,
x.event_status_name,
x.rn
FROM (SELECT t.patient_id,
t.event_time,
t.event_status_name,
row_number() OVER (PARTITION BY t.patient_id
ORDER BY t.event_time) rn
FROM elbat t) x
UNION ALL
SELECT c.patient_id,
dateadd(minute, 1, c.event_time),
c.event_status_name,
c.rn
FROM cte c
CROSS APPLY (SELECT y.patient_id,
y.event_time
FROM (SELECT t.patient_id,
t.event_time,
row_number() OVER (PARTITION BY t.patient_id
ORDER BY t.event_time) rn
FROM elbat t) y
WHERE y.rn = c.rn + 1) x
WHERE x.patient_id = c.patient_id
AND x.event_time > dateadd(minute, 1, c.event_time)
)
SELECT *
FROM cte c
ORDER BY c.patient_id,
c.event_time
OPTION (MAXRECURSION 1364);

不过有一个问题。您的差距如此之大，以至于超过了最大递归级别 100。您可以使用OPTION (MAXRECURSION n)放大它。要找到合适的n，可以查询数据以查找后续事件的最大差异(以分钟为单位(。同样，这使用row_number()来映射后续事件。取最大递归级别的最大减 1。

WITH cte
AS
(
SELECT t.patient_id,
t.event_time,
row_number() OVER (PARTITION BY t.patient_id
ORDER BY event_time) rn
FROM elbat t
)
SELECT max(datediff(minute, c2.event_time, c1.event_time)) - 1
FROM cte c1
INNER JOIN cte c2
ON c2.patient_id = c1.patient_id
AND c2.rn = c1.rn - 1;

数据库<>小提琴

Answer 2

你不认为插入这些分钟会使你的数百万行变成数百万行，里面有很多冗余数据吗？目的可能是什么？(也许是某种与显示相关的工作？

无论如何，如果你认为你必须这样做，那么你可以通过一些交叉应用来做到这一点。 即：

WITH tally
AS (SELECT TOP (1440 * 10)
ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
FROM master.sys.all_columns t1
CROSS JOIN master.sys.all_columns t2),
missedMinutes
AS (SELECT *
FROM dbo.Patients t1
CROSS APPLY
(
SELECT DATEDIFF(MINUTE, t1.event_time, MIN(t2.event_time))
FROM Patients t2
WHERE t1.Patient_Id = t2.Patient_Id
AND t2.event_time > t1.event_time
) t(missed)
CROSS APPLY
(
SELECT TOP (ISNULL(missed, 1) - 1)
DATEADD(MINUTE, N, t1.event_time)
FROM dbo.Patients t2
CROSS JOIN tally
WHERE t2.Patient_Id = t1.Patient_Id
AND t1.event_time = t2.event_time
ORDER BY tally.N
) tt(missing) )
SELECT Patient_Id,
missing AS event_time,
Event_Status_Name
INTO #missInsert
FROM missedMinutes
ORDER BY event_time;
SELECT *
FROM #missInsert;
DROP TABLE #missInsert;

DBFiddle 演示在这里

Answer 3

Declare @calc1 as table (Paitent_event_sno int,Patient_Id int, event_time DateTime,Event_Status_Name varchar(100))                                      
insert into @calc1 Select Paitent_event_sno = ROW_NUMBER() Over(partition by Patient_Id order by event_time Asc), *  from @Hospital 
Declare @final as table (Patient_Id int, event_time DateTime,Event_Status_Name varchar(100))
Declare @Pno int, @Sno int
declare cur CURSOR LOCAL for 
Select Distinct Patient_id from @calc1
open cur
fetch next from cur into @Pno
while @@FETCH_STATUS = 0 
BEGIN
declare cur2 CURSOR LOCAL for 
Select Distinct Paitent_event_sno from @calc1  where  Patient_Id =  @Pno 
open cur2
fetch next from cur2 into @Sno
while @@FETCH_STATUS = 0 
BEGIN
declare @starttime datetime , @endtime datetime, @eventtime datetime
insert into @final
Select Patient_Id, event_time , Event_Status_Name from @calc1 
where Patient_Id =   @Pno and   Paitent_event_sno = @Sno
Select @starttime = event_time , @eventtime = event_time from  @calc1 where Patient_Id =   @Pno and   Paitent_event_sno = @Sno
Select @endtime = event_time from @calc1 where Patient_Id =   @Pno and   Paitent_event_sno = @Sno+1

set @eventtime = dateadd(mi,1,@eventtime )
while @eventtime < @Endtime
begin
insert into @final
Select Patient_Id,  @eventtime,Event_Status_Name   
from @calc1 where Patient_Id =   @Pno and   
Paitent_event_sno = @Sno
set @eventtime = dateadd(mi,1,@eventtime )
end                    
fetch next from cur2 into @Sno
END
close cur2
deallocate cur2
fetch next from cur into @pno
END
close cur
deallocate cur
Select * from @final