用于计算不同事件之间的平均持续时间的SQL查询

Question

我需要有关构建SQL查询的帮助：

这是我用来存储测试运行统计信息的postgres表。

CREATE TABLE logs
(
  id bigint NOT NULL,
  test_id int,
  state text,
  start_date timestamp with time zone,
  end_date timestamp with time zone,
  CONSTRAINT logs_pkey PRIMARY KEY (id)
)

此表包含测试开始时间、结束时间和状态。我需要计算由于测试失败而使用的时间间隔。即测试失败和下一个立即测试开始之间的时间间隔。

即对于每个测试失败的记录，获取end_date并获取同一测试的下一个即时记录的start_date。计算时间差。将所有此类失败记录的持续时间相加，并按失败次数计算。以获得平均值。

例子：

假设我有下面的数据表，只有5列，最后一列失败状态下的持续时间应该是我查询的一部分。

i、 e我总共有3条失败记录，两条用于测试1，一条用于测试2。因此，总时间=130分钟/3=大约43.3分钟将以秒为单位。

有人能给我这个SQL查询吗？

Answer 1

如前所述，这可以使用窗口功能轻松完成：

select id,  
       test_id,
       start_date, 
       end_date,
       state,
       case 
         when state = 'FAIL' then lag(start_date) over (partition by test_id order by start_date desc) - end_date
         else null
       end as time_diff
from logs
order by test_id, start_date desc;

SQLFiddle：http://sqlfiddle.com/#!15/0d40f/2