计算行和两组之间的平均时差

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

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