sql - Access SQL : Calculating Min date value and duration for each day

Question

我正在查看车辆的 GPS 数据，其中包含以下信息:

vehicle        day              ignition   landmark
---------------------------------------------------
Sample Guy 1   7/5/2013 14:32   on         
Sample Guy 1   7/5/2013 15:10   off        Random Place B
Sample Guy 1   7/5/2013 15:15   on         Random Place B
Sample Guy 1   7/5/2013 15:20   off
Sample Guy 1   7/20/2013 18:14  on
Sample Guy 1   7/20/2013 18:20  off        Random Place H
Sample Guy 1   7/20/2013 18:22  on         Random Place H
Sample Guy 1   7/20/2013 18:24  off
Sample Guy 2   8/1/2013 12:10   on         Random Place ZZ
Sample Guy 2   8/1/2013 12:12   on         
Sample Guy 2   8/1/2013 12:15   off        Random Place ZY
Sample Guy 2   8/1/2013 13:10   on         Random Place ZY
Sample Guy 2   8/1/2013 13:15   off

我试图找到地标不为空的第一次点火关闭点，以及打开<的最后点火点 每一天地标不为空的点。理想情况下，可以包含“持续时间”的计算字段以显示这两个值之间的 DateDiff。

目标输出:

    vehicle        day          ignition   landmark  
---------------------------------------------------------
Sample Guy 1   7/5/2013 15:10   off        Random Place B
Sample Guy 1   7/5/2013 15:15   on         Random Place B
Sample Guy 1   7/20/2013 18:20  off        Random Place H
Sample Guy 1   7/20/2013 18:22  on         Random Place H
Sample Guy 2   8/1/2013 12:15   off        Random Place ZY
Sample Guy 2   8/1/2013 13:10   on         Random Place ZY

我知道这可能很复杂，但我希望开始了解完成它的构建 block 。谢谢大家!

Answer 1

您的 GPS 数据表中必须有一个唯一的主键。我认为没有一个就无法解决这个问题。

如果您还没有，只需将名为 ID 的 AutoNumber 字段添加到您的 GPSData 表

这是一个在您完成后将在 Access 中运行的查询:

SELECT R.ResultID,
       vehicle,
       day,
       ignition,
       landmark
FROM   (SELECT First(ID) AS ResultID
        FROM   (SELECT ID,
                       vehicle,
                       DateSerial(day([day]), month([day]), year([day])) AS NormalisedDate,
                       ignition
                FROM   GPSData
                WHERE  ((ignition = "off") AND (landmark IS NOT NULL))
                ORDER  BY day ASC)
        GROUP  BY vehicle, normalisedDate, ignition
        UNION ALL
        SELECT Last(ID) AS ResultID
        FROM   (SELECT ID,
                       vehicle,
                       DateSerial(day([day]), month([day]), year([day])) AS NormalisedDate,
                       ignition
                FROM   GPSData
                WHERE  ((ignition = "on") AND (landmark IS NOT NULL))
                ORDER  BY day ASC)
        GROUP  BY vehicle, NormalisedDate, ignition) AS R
       INNER JOIN GPSData
         ON R.ResultID = GPSData.ID
ORDER  BY vehicle, day 

可能有更好的方法来做到这一点，但这应该可行，即使它并不是想变得聪明。

基本上，我们将问题分成更小的 block ，然后重新构建:

• 查找所有最先“下车”的车辆的所有日常记录的 ID。
  要找到这个列表，我们必须按天对数据进行分组，因此我们必须将日期时间 day 规范化为其日期部分。

• 做完全相同的事情，但针对每天的最后一个“开启”记录。

• UNION ALL 这 2 个列表为我们获取与第一个每日“关闭”和最后一个匹配的所有记录的 ID(我们称之为 ResultID)每辆车每天“开启”。

• 使用原始 GPSData 重新加入此记录 ID 列表。