SQL:高效计算花费的时间

Question

给定一个事件时间戳列表，你如何计算“花费的时间”？ “花费的时间”是每个时间戳之间的时间间隔的总和，忽略超过某个阈值的间隔，例如1 分钟，在此期间用户可能处于非事件状态。

我试过这样的:

select sum(finish - start)
from (select start.time as start,
             finish.time as finish
      from events start, events finish
      where start.time < finish.time and
            finish.time - start.time < interval '1 minute'
      group by start.time) as intervals;

但这被证明效率极低，而且从未终止。

Answer 1

使用:

SELECT SUM ( time_interval_up_to_1_min )
FROM (
  SELECT 
        CASE WHEN time - lag( time ) over (ORDER BY time) 
                <= interval '1' minute
             THEN time - lag( time ) over (ORDER BY time)
        END As time_interval_up_to_1_min
  FROM events
) x

演示:http://sqlfiddle.com/#!17/6f814/7