sql - 填补时间空白
问题描述
我需要一些帮助来填补一些缺失的时间(以分钟为单位)空白,并保持当前状态。我在医院工作,我们试图随时了解医院急诊科的瓶颈。
我表中的数据如下所示:
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
等等,等等,直到我达到“等待分流”的下一个状态......然后我需要为该状态的每一分钟做同样的事情,直到下一个......等等。等等
如何在不使用循环的情况下编写执行此操作的查询?有数百万条记录(和分钟)要考虑,所以我需要一个便宜的查询。
谢谢你的帮助!
解决方案
您可以在此处使用递归 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()
用于映射后续事件。将该最大值减去一作为最大递归级别。
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;
推荐阅读
- windows - 如何从 DLL 文件中获取一些基本信息?
- c++ - c++ 中没有 timeout_exception 吗?
- sql-server - 运行总计和运行减法
- kubernetes - 如何从同一台计算机访问在 minikube 中运行的 kubernete 服务
- tsql - 数据库中奇怪的日期格式
- ios - admob 测试广告 -> 没有广告可显示
- r - stri_unescape_unicode() 在某些字符上失败
- utf-8 - Windows Xampp 上的 elFinder,不查看非 utf-8 字符的文件
- sql-server - 关系数据库设计 - 用户最喜欢的数据库设计
- magento-2.0 - 在magento 2过滤器中添加新的自定义列后不适用于日期排序