计算查询中活动时间戳之间的时间差

我对 Access 相当陌生，无法解决（我希望）一个简单的问题 - 我想我可能是通过 Excel 护目镜来查看它。

我有一个名为importedData我每天（并不奇怪）导入一个日志文件。该日志文件来自某些采矿设备上的简单数据记录应用程序，本质上它保存了timestamp and status当前活动更改为新活动的点。

数据示例如下所示：

然后使用查询过滤此信息以定义我想要查看信息的范围，例如从 29/11/2013 06:00:00 AM 到 29/11/2013 06:00:00 PM

现在的目的是采取status条目的timestamp并获取它与查询结果的后续行记录之间的时间差。由于设备轮班工作 12 小时，因此我应该能够了解设备在轮班期间执行每项活动所花费的时间。

在上面的示例中，设备在 00:01:00 处于“START_SHIFT”状态，在 06:08:26 处于“DELAY_WAIT_PIT”状态，依此类推。然后，我将构建所选期间的状态条目的唯一列表，并对每个状态的总时间进行求和以获得轮班摘要。

您可以使用相关子查询来获取下一个timestamp对于每一行。

SELECT
    i.status,
    i.timestamp,
    (
        SELECT Min([timestamp])
        FROM importedData
        WHERE [timestamp] > i.timestamp
    ) AS next_timestamp
FROM importedData AS i
WHERE i.timestamp BETWEEN #2013-11-29 06:00:00#
    AND #2013-11-29 18:00:00#;

然后，您可以将该查询用作另一个查询中的子查询，在其中计算之间的持续时间timestamp and next_timestamp。然后在第三个查询中使用整个新查询作为子查询GROUP BY status并计算每个状态的总持续时间。

这是我在 Access 2007 中测试的版本...

SELECT
    sub2.status,
    Format(Sum(Nz(sub2.duration,0)), 'hh:nn:ss') AS SumOfduration
FROM
    (
        SELECT
            sub1.status,
            (sub1.next_timestamp - sub1.timestamp) AS duration
        FROM
            (
                SELECT
                    i.status,
                    i.timestamp,
                    (
                        SELECT Min([timestamp])
                        FROM importedData
                        WHERE [timestamp] > i.timestamp
                    ) AS next_timestamp
                FROM importedData AS i
                WHERE i.timestamp BETWEEN #2013-11-29 06:00:00#
                    AND #2013-11-29 18:00:00#
            ) AS sub1
    )  AS sub2
GROUP BY sub2.status;

如果遇到麻烦或需要修改它，请分解最里面的子查询，sub1，并自行测试。然后做同样的事情sub2。我怀疑你会想要改变WHERE子句使用参数而不是硬编码时间。

注意查询Format如果您的持续时间超过 24 小时，则表达式将不合适。这是一个即时窗口会话，它说明了问题......

' duration greater than one day:
? #2013-11-30 02:00# - #2013-11-29 01:00#
 1.04166666667152 
' this Format() makes the 25 hr. duration appear as 1 hr.:
? Format(#2013-11-30 02:00# - #2013-11-29 01:00#, "hh:nn:ss")
01:00:00

但是，如果您只处理 12 小时的数据。转变，这应该不是问题。请记住这一点，以防您需要分析跨度超过 24 小时的数据。

如果子查询不熟悉，请参阅 Allen Browne 的页面：子查询基础知识 http://allenbrowne.com/subquery-01.html。他在标题为“相关子查询”的部分中讨论了相关子查询获取另一条记录中的值.