I have a table that resembles the following. This is a data from an urgent care center, with the ROOMED_DT being when a patient is given a room, and the CHECKOUT_DT being the time the patient is cleared to leave:
我有一个类似于以下的表。这是来自紧急护理中心的数据,其中ROOMED_DT是患者获得房间的时间,而CHECKOUT_DT是患者被清除的时间:
PAT_ID ROOMED_DT CHECKOUT_DT ROOM_NUM
1 11/12/2015 03:22PM 11/12/2015 06:30PM Room 4
2 11/12/2015 07:40PM 11/12/2015 10:30PM Room 4
3 11/12/2015 10:55PM 11/12/2015 11:30PM Room 4
4 11/12/2015 09:45AM 11/12/2015 12:00PM Room 5
5 11/12/2015 12:02PM 11/12/2015 01:30PM Room 5
6 11/12/2015 02:25PM 11/12/2015 04:30PM Room 5
7 11/13/2015 07:22AM 11/13/2015 08:04AM Room 2
8 11/13/2015 09:40AM 11/13/2015 10:30AM Room 2
9 11/13/2015 10:55AM 11/13/2015 11:55AM Room 2
10 11/13/2015 12:45PM 11/13/2015 02:00PM Room 1
11 11/13/2015 04:02PM 11/13/2015 05:30PM Room 1
12 11/13/2015 05:25PM 11/13/2015 08:30PM Room 1
I would like the calculate the average time from when the previous patient is cleared to leave (CHECKOUT_DT) to when the next patient appears (ROOMED_DT) for each room, on each day, so the result would look something like this (I'm making the average numbers up, for the sake of the visual), with the time to the next patient being in minutes.
我想计算从前一个病人被清除离开(CHECKOUT_DT)到每个房间的下一个病人出现(ROOMED_DT)的平均时间,每天,所以结果看起来像这样(我正在制作平均数量,为了视觉的目的),下一个患者的时间是几分钟。
SERVICE_DT TIME_TO_NEXT_PAT
11/12/2015 65.7
11/13/2015 72.4
But I'm not sure how to account for the difference between CHECKOUT_DT and ROOMED_DT across multiple patients and multiple rooms. Does anyone have any ideas? I'm using Netezza.
但我不确定如何解释多个患者和多个房间的CHECKOUT_DT和ROOMED_DT之间的差异。有没有人有任何想法?我正在使用Netezza。
1 个解决方案
#1
0
You can use lead
analytic function to get these results.
您可以使用潜在分析功能来获得这些结果。
here is the query
这是查询
SELECT SERVICE_DATE
, AVG(MINUTES_BETWEEN)
FROM (
SELECT PAT_ID
, ROOMED_DT::DATE SERVICE_DATE
, ROOMED_DT
, CHECKOUT_DT
, ROOM_NUM
, LEAD(ROOMED_DT,1) OVER (PARTITION BY ROOM_NUM ORDER BY ROOMED_DT) NEXT_CHECKED_IN
, MINUTES_BETWEEN(NEXT_CHECKED_IN,CHECKOUT_DT)
FROM SAMPLE
ORDER BY 1
) FOO
WHERE MINUTES_BETWEEN IS NOT NULL
GROUP BY 1;
SERVICE_DATE | AVG
--------------+-----------
2015-11-12 | 38.000000
2015-11-13 | 62.000000
(2 rows)
#1
0
You can use lead
analytic function to get these results.
您可以使用潜在分析功能来获得这些结果。
here is the query
这是查询
SELECT SERVICE_DATE
, AVG(MINUTES_BETWEEN)
FROM (
SELECT PAT_ID
, ROOMED_DT::DATE SERVICE_DATE
, ROOMED_DT
, CHECKOUT_DT
, ROOM_NUM
, LEAD(ROOMED_DT,1) OVER (PARTITION BY ROOM_NUM ORDER BY ROOMED_DT) NEXT_CHECKED_IN
, MINUTES_BETWEEN(NEXT_CHECKED_IN,CHECKOUT_DT)
FROM SAMPLE
ORDER BY 1
) FOO
WHERE MINUTES_BETWEEN IS NOT NULL
GROUP BY 1;
SERVICE_DATE | AVG
--------------+-----------
2015-11-12 | 38.000000
2015-11-13 | 62.000000
(2 rows)