计算设备每天运行的总时间。

Question

目前，我有一个sqlite3数据库，可以跟踪我的智能家居设备的状态。主表的架构的相关部分包括

CREATE TABLE states(
    state_id INTEGER NOT NULL,
    entity_id VARCHAR(255),
    state VARCHAR(255),
    last_updated DATETIME
);

我试图计算每一个实体每天在状态中的“开启”时间。目前，我的想法是使用lead函数创建一个列，并在下一次更新时：

CREATE VIEW states_with_next_update AS
  SELECT *, lead(last_updated,1) over (PARTITION BY entity_id) as next_update
  FROM states;

然后，可以从当前时间减去下一次更新时间，以得到每个实体处于特定状态的总时间。

CREATE VIEW states_with_durations AS
  SELECT *, julianday(next_update) - julianday(last_updated) as state_duration, date(last_updated) as day
  FROM states_with_next_update;

对于每种状态的持续时间，我现在可以使用聚合函数来计算每个状态处于"on“状态的总时间：

SELECT day, entity_id, sum(state_duration)
FROM state_with_durations
WHERE state = "on"
GROUP BY day, entity_id;

此方法的唯一问题是，next_update可能不会在同一天内下降，从而导致包含连续两天(或更多)时间的durations，这会导致在第一天进行过多的计算，而在随后的一天则会少计算。

例如，如果一个实体在2022-11-10 20:00至2022-11-11 02:00处于"on“状态，2022-11-10的总数为6,2020年-11-11的总数为0。

所以问题是，我如何使之在上面的例子中，"on“状态的和为2022-11-10天读到4个小时，2022-11-11天读到2个小时？

Answer 1

您需要在您感兴趣的范围内创建所有日期的列表。您可以使用递归CTE (使用start of day修饰符将时间倒带到00:00小时)：

WITH RECURSIVE datelist AS (
   SELECT datetime(min(last_updated),'start of day') AS day FROM states
   UNION ALL
   SELECT datetime(day,'+1 day') FROM datelist 
   WHERE datetime(day,'+1 day') < (SELECT max(last_updated) FROM states)
)

此列表可以与states_with_next_update一起生成事件(全部或部分)覆盖的每一天的行。

...
FROM states_with_next_update JOIN datelist 
WHERE last_updated <= datetime(datelist.day,'+1 day') AND next_update > datelist.day

现在，您可以使用min和max的标量版本(不要与聚合混淆)计算事件在当前一天相交的时间。

min(julianday(next_update),julianday(datelist.day,'+1 day')) - max(julianday(last_updated),julianday(datelist.day)) AS state_duration

现在，让我们将所有这些放在您的states_with_durations视图中：

CREATE VIEW states_with_durations AS
  WITH RECURSIVE datelist AS (
       SELECT datetime(min(last_updated),'start of day') AS day FROM states
       UNION ALL
       SELECT datetime(day,'+1 day') FROM datelist 
       WHERE datetime(day,'+1 day') < (SELECT max(last_updated) FROM states)
  )
  SELECT *, min(julianday(next_update),julianday(datelist.day,'+1 day')) - max(julianday(last_updated),julianday(datelist.day)) AS state_duration, date(datelist.day) as day
  FROM states_with_next_update JOIN datelist 
  WHERE last_updated <= datetime(datelist.day,'+1 day') AND next_update > datelist.day;

现在可以使用聚合查询来获得所需的结果。