使用内部联接确定表中特定记录和相关事件之间的间隔 [英] Using Inner Join to determine the interval between specific records and related events in a table

问题描述

我有一个简单的表，用于记录针对特定访问的事件:

I have a simple table that is used to record events against specific visits:

Describe Histories;
    +------------------+
    | Field            |
    +------------------+
    | HistoryId        |
    | VisitId          |
    | Location         |
    | Event            |
    | EventTime        |
    +------------------+

个人与访问次数(VisitId)相关联.对于每次访问，一个人可能有多个历史记录.活动可以是入场，转场或出场.

Individuals are associated with Visits (VisitId). For each Visit, an individual may have multiple History records. Events can be Admission, Transfer or Discharge.

我正在尝试编写一个查询来计算每个位置在每个位置的持续时间.请注意，他们每次访问可能会多次访问一个位置.个人进入具有录取或转移事件的位置，然后离开与释放或转移.

I am trying to write a Query to calculate the duration in each Location for each individual. Note, that they may visit a Location multiple times for each Visit. An individual enters a Location with an Admission or Transfer Event and leaves with a Discharge or Transfer.

如果某人进入位置"A"，则其录取或转移记录将列出位置"A"，但是，如果他们转出(或解除)转移，则将列出另一个位置，例如"B".

If an individual enters Location 'A', their Admission or Transfer record will list Location 'A', however if they transfer out their transfer out (or discharge) will list another Location, say 'B'.

因此，我必须找到转移到位置"A"和随后(及时)转移到位置"B"之间的间隔.不评估位置内转移.

I therefore have to find the interval between the transfer into Location 'A' and the subsequent (in time) transfer to Location 'B'. Intra location transfers are not evaluated.

我知道该解决方案可能基于INNER JOIN，但是我不知所措，不知道如何选择与最近的转帐"in"相对应的转帐"out"记录.

I understand that the solution will probably be based on an INNER JOIN, however I am at a loss to understand how to select for the transfer "out" record that corresponds to the most recent transfer "in".

我想这很复杂-我希望我的解释足够清楚.

I guess that this is reasonably complex - I hope I have been clear enough in my explanation.

任何指导都将不胜感激.

Any guidance greatly appreciated.

推荐答案

假设转移或解除排放是一个独特的事件，您可以这样写

Assuming a transfer or discharge is a unique event you could write like so

SELECT
   b.EventTime - a.EventTime        
FROM
   Histories a
   INNER JOIN Histories b
   ON a.VisitID = b.VisitID
WHERE
   a.event = 'Admission'
   and
   b.event in ('Transfer', 'Discharge')

如果您对上次汇款或清算感兴趣，请写信

If you were interested in the last transfer or discharge you would write

SELECT
   b.EventTime - a.EventTime        
FROM
   Histories a
   INNER JOIN  Histories b
    ON a.VisitID = b.VisitID

   INNER JOIN 
   (SELECT
         VisitId, 
         MAX(HistoryID) HistoryID
    FROM Histories 
    WHERE 
       b.event in ('Transfer', 'Discharge')
    GROUP BY 
       VisitId) maxHistory
   ON b.HistoryID = maxHistoryId.HistoryId

WHERE
   a.event = 'Admission'

但是，如果一次访问可以导致多次访问(如Andriy M提到的那样)，则您存在空白和孤岛问题(特别是孤岛)

However if a Visit can result in multiple visits as Andriy M mentions you have a Gaps And islands problem (specifically the islands)

在这种情况下，您需要以下内容

In that case you want the following

SELECT  
       a.VisitId,
       a.Event a_Event, 
       a.Event b_Event, 
       a.EventTime a_EventTime,
       b.EventTime b_EventTime,
       b_EventTime - a_EventTime

FROM   histories a 
       INNER JOIN histories B 
         ON a.visitID = b.visitID 
            AND a.EventTime < b.eventTime 
       INNER JOIN (SELECT a.VisitId, 
                          a.EventTime      a_EventTime, 
                          Min(b.EventTime) b_EventTime 
                   FROM   histories a 
                          INNER JOIN histories B 
                            ON a.visitID = b.visitID 
                               AND a.EventTime < b.eventTime 
                   GROUP  BY a_EventTime, 
                             a.VisitId) MinTime 
         ON a.VisitID = MinTime.VisitID 
            AND a.EventTime = a_EventTime 
            AND b.EventTime = b_EventTime 

演示

使用以下示例数据

CREATE TABLE Histories 
    (
     HistoryId int auto_increment primary key, 
     VisitId int,
     Location varchar(20),
     Event varchar(20), 
     EventTime datetime
    );

INSERT INTO Histories
(VisitId, Location, Event, EventTime)
VALUES
(1, 'A', 'Admission', '2012-01-01'),
(1, 'A', 'Discharge', '2012-01-03'),
(2, 'B', 'Admission', '2012-01-02'),
(2, 'C', 'Transfer', '2012-01-05'),
(2, 'C', 'Discharge', '2012-01-06'),
(3, 'D', 'Admission', '2012-01-06'),
(3, 'E', 'Transfer', '2012-01-07'),
(3, 'F', 'Transfer', '2012-01-08'),
(3, 'F', 'Discharge', '2012-01-10');

您得到以下结果

VISITID    A_EVENT   B_EVENT    A_EVENTTIME                     B_EVENTTIME                     B_EVENTTIME - A_EVENTTIME
1          Admission Discharge  January, 01 2012 00:00:00-0800  January, 03 2012 00:00:00-0800  2000000
2          Admission Transfer   January, 02 2012 00:00:00-0800  January, 05 2012 00:00:00-0800  3000000
2          Transfer  Discharge  January, 05 2012 00:00:00-0800  January, 06 2012 00:00:00-0800  1000000
3          Admission Transfer   January, 06 2012 00:00:00-0800  January, 07 2012 00:00:00-0800  1000000
3          Transfer  Transfer   January, 07 2012 00:00:00-0800  January, 08 2012 00:00:00-0800  1000000
3          Transfer  Discharge  January, 08 2012 00:00:00-0800  January, 10 2012 00:00:00-0800  2000000

注意:

• 这是假定您不关心尚未进行相应排放/转移的录取/转移.
• 如果您知道输入记录后eventTime不会更改，则可以使用historyID而不是eventime来确定事件的顺序.
• 您知道如何以自己喜欢的格式获取事件时间差

这篇关于使用内部联接确定表中特定记录和相关事件之间的间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！