如何查找存在总条件的位置

Question

我正在尝试创建一个报告，该报告将显示自动喷水灭火系统已经运行了多长时间。该系统由几个喷头组成，每个喷头只跟踪自己，然后将信息发送到数据库。我的问题是，每个喷头都有自己的运行时间(即，如果5个喷头同时运行10分钟，它将报告50分钟的总运行时间)，而我只想知道运行时间的净值-在本例中，它将是10分钟。

数据库由时间戳和布尔值组成，每次喷水装置关闭或关闭时，它都会记录时间戳(其开/关状态由布尔值的1/0表示)。

因此，为了计算出系统每天的总净时间-无论是1个喷水装置运行还是所有喷水装置运行-我需要检查数据库中根本没有打开喷水装置的时间段(或者任何喷水装置被打开的时间段)。我认为查询的开头应该是这样的

SELECT * FROM MyTable
WHERE MyBoolean = 0
AND [ ... ]

但我不确定和后面的条件语句是什么，以便检查时间戳。

是否有查询可以发送到数据库，以报告此格式的信息？

编辑：

下面是记录数据的表--它实际上只是一个名称、一个布尔值和一个更改布尔值的日期时间，这就是整个数据库

​

Answer 1

每次打开喷水装置时，运行的喷水装置的数量递增1，每次关闭喷水装置时，该数量递减1。如果对数据进行变换，则会得到以下结果：

timestamp   on/off
07:00:05    1
07:03:10    1
07:05:45    -1

然后你就有了一系列的事件；它们所指的喷头是无关紧要的。(我已经将0更改为-1，原因稍后将变得显而易见。您可以使用"(2 * value) -1“来完成此操作)

现在把运行总数放在一起：

select a.timestamp, (SELECT SUM(a.on_off)
                   FROM sprinkler_events b
                   WHERE b.timestamp <= a.timestamp) as run_total
  from sprinkler_events a
 order by a.timestamp;

其中sprinkler_events是我上面列出的转换后的数据。这将为您提供：

timestamp    run_total
07:00:05     1
07:03:10     2
07:05:45     1

诸若此类。这里的每一行都是所有喷水装置关闭的时间，我想这就是你要找的。如果您需要计算它们打开或关闭的时间，您将需要做额外的处理:搜索“连续行之间的日期差异”，您将看到解决方案。

Answer 2

您可以考虑查看是否所有的喷水装置当前都已关闭。例如：

SELECT COUNT (DISTINCT s._NAME) AS sprinkers_currently_off
FROM (
    SELECT 
        _NAME,
        _VALUE,
        _TIMESTAMP,
        ROW_NUMBER() OVER (PARTITION BY _NAME ORDER BY _TIMESTAMP DESC, _VALUE) AS latest_rec
    FROM sprinklers
    ) s
WHERE 
    _VALUE = 0
    AND latest_rec = 1

内部查询对记录进行排序，以便您可以获得所有喷水装置的最新状态，而外部查询计算当前关闭的喷水装置数量。如果您有10个喷水装置，当此查询返回10个时，您将报告它们全部关闭。

如果您想查看过去，可以通过将日期范围应用于内部查询来修改这一点，但这应该会让您走上正确的道路。