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 列，最后一列处于失败状态的持续时间应该是我查询的一部分。

即我总共有 3 条失败的记录，两条用于测试 1，一条用于测试 2。所以 total time = 130minutes/3 = around 43.3 minutes will be in secs。

有人可以给我这个的 SQL 查询吗？

Answer 1

如前所述，这可以使用 window function 轻松完成。 :

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