sql - 如何设置事件持续时间限制以在 Access 2010 查询或 SQL 中定义 "same event"和 "new event"？

Question

我在 Access 2010 中使用带有日期时间戳记的记录(来自相机陷阱的照片)表示特定动物(物种 ID (0-10)、动物 ID (1-20) 到不同相机站点 (StationID) 的访问。我想计算每个AnimalID对每个StationID的访问次数和访问时长。

问题是有时动物会在同一天多次访问同一个站点。我尝试过按日期对记录进行分组并显示日期时间字段的“第一个”和“最后一个”的查询，但这只是给出了当天每个站点该动物的第一个和最后一个记录的日期时间，而不是每次访问。

我想使用的标准是“如果同一动物、物种和站点的连续记录相隔 >20 分钟，则它们是单独的访问”。我想知道解决这个问题的方法是否是创建一个带有更新查询的新字段，使用此条件为每次访问提供唯一的“VisitID”编号，这样我就可以按 VisitID 对记录进行分组以计算每个单独的第一个和最后一个日期时间访问？任何人都可以建议一种方法来执行此操作作为查询或在 SQL 中，或者想出另一种方法来执行此操作吗？？

我的数据表(称为 Capture)布局如下:捕获ID |站号 |物种编号 |动物编号 | c日期时间

CaptureID 是每条记录的唯一自动编号。 SpeciesID 可以是 1-10，AnimalID 可以是 1-20(但 AnimalID 只分配给 Species 1 的记录)，StationID 可以是 1-12，cDateTime 可以是任何时间，因为相机陷阱是运动触发的，并且被格式化作为 DD/MM/YYYY hh:mm:ss。我希望访问持续时间的格式为 hh:mm:ss。

非常感谢任何帮助或建议!!

Answer 1

这是我的解决方案。我的测试数据是

CaptureID  AnimalID  StationID  cDateTime            VisitStart           VisitEnd
---------  --------  ---------  -------------------  -------------------  -------------------
        1         1          1  2013-05-21 08:00:00                      
        2         2          1  2013-05-21 08:02:00                      
        3         1          1  2013-05-21 08:07:00                      
        4         2          1  2013-05-21 08:21:00                      
        5         1          1  2013-05-21 08:28:00                      

注意事项:

1. 我省略了 SpeciesID，因为 AnimalID 是唯一标识符，所以 SpeciesID 确实属于 [Animals] 表，其中包含关于该特定动物的其他详细信息。

2. 所有 VisitStart 值最初都是 NULL。这对于以下查询之一很重要。

为了填充 VisitStart，我们将只对任何在 20 分钟内没有针对相同 AnimalID 和 StationID 的先前捕获的捕获使用 cDateTime。

UPDATE Captures SET VisitStart = cDateTime
WHERE NOT EXISTS 
    (
        SELECT * FROM Captures c2 
        WHERE c2.AnimalID=Captures.AnimalID AND c2.StationID=Captures.StationID
            AND c2.cDateTime<Captures.cDateTime
            AND c2.cDateTime>=DateAdd("n", -20, Captures.cDateTime)
    )

这为我们提供了离散访问的开始时间:

CaptureID  AnimalID  StationID  cDateTime            VisitStart           VisitEnd
---------  --------  ---------  -------------------  -------------------  -------------------
        1         1          1  2013-05-21 08:00:00  2013-05-21 08:00:00          
        2         2          1  2013-05-21 08:02:00  2013-05-21 08:02:00          
        3         1          1  2013-05-21 08:07:00                               
        4         2          1  2013-05-21 08:21:00                               
        5         1          1  2013-05-21 08:28:00  2013-05-21 08:28:00          

现在我们可以通过找到该 AnimalID/StationID 的最大先前 VisitStart 来填充其余的 VisitStart 值

UPDATE Captures 
SET VisitStart = DMax("VisitStart", "Captures", "AnimalID=" & AnimalID & " AND StationID=" & StationID & " AND cDateTime<#" & Format(cDateTime, "yyyy-mm-dd Hh:Nn:Ss") & "#")
WHERE VisitStart IS NULL

这给了我们

CaptureID  AnimalID  StationID  cDateTime            VisitStart           VisitEnd
---------  --------  ---------  -------------------  -------------------  -------------------
        1         1          1  2013-05-21 08:00:00  2013-05-21 08:00:00          
        2         2          1  2013-05-21 08:02:00  2013-05-21 08:02:00          
        3         1          1  2013-05-21 08:07:00  2013-05-21 08:00:00          
        4         2          1  2013-05-21 08:21:00  2013-05-21 08:02:00          
        5         1          1  2013-05-21 08:28:00  2013-05-21 08:28:00          

类似的查询可以计算 VisitEnd 值

UPDATE Captures 
SET VisitEnd = DMax("cDateTime", "Captures", "AnimalID=" & AnimalID & " AND StationID=" & StationID & " AND VisitStart=#" & Format(VisitStart, "yyyy-mm-dd Hh:Nn:Ss") & "#")

结果是

CaptureID  AnimalID  StationID  cDateTime            VisitStart           VisitEnd           
---------  --------  ---------  -------------------  -------------------  -------------------
        1         1          1  2013-05-21 08:00:00  2013-05-21 08:00:00  2013-05-21 08:07:00
        2         2          1  2013-05-21 08:02:00  2013-05-21 08:02:00  2013-05-21 08:21:00
        3         1          1  2013-05-21 08:07:00  2013-05-21 08:00:00  2013-05-21 08:07:00
        4         2          1  2013-05-21 08:21:00  2013-05-21 08:02:00  2013-05-21 08:21:00
        5         1          1  2013-05-21 08:28:00  2013-05-21 08:28:00  2013-05-21 08:28:00

计算访问持续时间只需在 VisitStart 和 VisitEnd 上使用 DateDiff() 即可。请注意，最后一次访问的持续时间将为零，因为只有一次捕获。