行之间的时间差总和 [英] Sum of time difference between rows

问题描述

我有一个表，记录一个实体的每个状态更改

I have a table which records every status change of an entity

id      recordTime        Status
ID1 2014-03-01 11:33:00 Disconnected  
ID1 2014-03-01 12:13:00 Connected  
ID2 2014-03-01 12:21:00 Connected  
ID1 2014-03-01 12:24:00 Disconnected  
ID1 2014-03-01 12:29:00 Connected  
ID2 2014-03-01 12:40:00 Disconnected  
ID2 2014-03-01 13:03:00 Connected  
ID2 2014-03-01 13:13:00 Disconnected  
ID2 2014-03-01 13:29:00 Connected  
ID1 2014-03-01 13:30:00 Disconnected

我需要计算总的非活动时间，即已连接与上次断开连接之间的时间

I need to calculate the total inactive time i.e time between 'Connected' and last 'Disconnected' status per ID for a given time window.

对于上表和时间范围，2014-03-01 11:00:00到2014-03-01 14:00 ：00输出应为：

For above table and time range of 2014-03-01 11:00:00 to 2014-03-01 14:00:00 the output should be:

ID  InactiveTime
ID1  01:15:00
ID2  02:00:00

推荐答案

特殊困难

假定任何给定 id 的下一行始终具有相反的状态。
>
使用列名 ts 代替 recordTime ：

The special difficulty is not to miss the time spans to the outer time frame.
Assuming that the next row for any given id always has the opposite status.
Using the column name ts instead of recordTime:

WITH span AS (
   SELECT '2014-03-01 13:00'::timestamp AS s_from  -- start of time range
        , '2014-03-01 14:00'::timestamp AS s_to    -- end of time range
   )
, cte AS (
   SELECT id, ts, status, s_to
        , lead(ts, 1, s_from) OVER w AS span_start
        , first_value(ts)     OVER w AS last_ts
   FROM   span s
   JOIN   tbl  t ON t.ts BETWEEN s.s_from AND s.s_to
   WINDOW w AS (PARTITION BY id ORDER BY ts DESC)
   )
SELECT id, sum(time_disconnected)::text AS total_disconnected
FROM  (
   SELECT id, ts - span_start AS time_disconnected
   FROM   cte
   WHERE  status = 'Connected'

   UNION  ALL  
   SELECT id, s_to - ts
   FROM   cte
   WHERE  status = 'Disconnected'
   AND    ts = last_ts
   ) sub
GROUP  BY 1
ORDER  BY 1;

按要求返回时间间隔。

在选定时间范围内没有输入的ID不露面。您将不得不另外查询它们。

Returns intervals as requested.
IDs without entries in the selected time range don't show up. You would have to query them additionally.

SQL小提琴。

注意：我将生成的 total_disconnected 转换为 text ，因为类型 interval 的显示格式很糟糕。

SQL Fiddle.
Note: I cast the resulting total_disconnected to text in the fiddle, because the type interval is displayed in a terrible format.

^{每个请求在注释中。}

添加到上面的查询中（在最终 ORDER BY 1 ）：

...
UNION  ALL
SELECT id, total_disconnected
   FROM  (
   SELECT DISTINCT ON (id)
          t.id, t.status, (s.s_to - s.s_from)::text AS total_disconnected
   FROM   span     s
   JOIN   tbl      t ON t.ts < s.s_from  -- only from before time range
   LEFT   JOIN cte c USING (id)
   WHERE  c.id IS NULL         -- not represented in selected time frame
   ORDER  BY t.id, t.ts DESC   -- only the latest entry
   ) sub
WHERE  status = 'Disconnected' -- only if disconnected
ORDER  BY 1;

SQL提琴。

现在，只有ID在或更早版本中没有条目的ID >所选时间范围不会显示。

Now, only IDs without entries in or before the selected time range don't show up.

这篇关于行之间的时间差总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！