Как сгруппировать временные метки в острова (на основе произвольного разрыва)?

Это сделало бы это:

SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS grp
FROM  (
   SELECT done
       , (lag(done) OVER (ORDER BY done) <= done - interval '2 min') AS step
   FROM   tbl
   ) sub
ORDER  BY done;

Подзапрос sub записывает step как true, если предыдущая строка находится на расстоянии не менее 2 минут - в этом случае сортировка выполняется по столбцу временной метки done.

Внешний запрос добавляет скользящее количество шагов, фактически номер группы (grp), объединяя агрегатное предложение FILTER с другой оконной функцией.

db ‹> скрипт здесь

Связанный:

• Запрос к найти все временные метки, разнесенные более чем на определенный интервал
• Как метить группы в postgresql, когда принадлежность к группе зависит от предыдущей строки?
• Выбрать самую длинную непрерывную последовательность
• Группировка или окно

Об агрегатном предложении FILTER:

• Как упростить этот запрос игровой статистики?
• Функция условного опережения / запаздывания PostgreSQL?

Основываясь на ответе Эрвина, вот полный запрос для подсчета количества времени, которое люди потратили на эти сеансы / острова:

Мои данные показывают, только когда люди закончили что-то просматривать, а не когда они начали, что означает, что мы не знаем, когда действительно начался сеанс; а на некоторых островах есть только одна временная метка (что дает оценку продолжительности 0). Я учитываю и то, и другое, вычисляя среднее время просмотра и добавляя его к общей продолжительности островов.

Это, вероятно, очень характерно для моего варианта использования, но я кое-что узнал в процессе, так что, возможно, это поможет кому-то в будущем.

-- Returns estimated total study time and average time per review, both in seconds
SELECT (EXTRACT( EPOCH FROM logged) + countofislands * avgreviewtime) as totalstudytime, avgreviewtime -- add total logged time to estimate for first-review-in-island and 1-review islands
FROM
    (
    SELECT -- get the three key values that will let us calculate total time spent
      sum(duration) as logged
      , count(island) as countofislands
      , EXTRACT( EPOCH FROM sum(duration) FILTER (WHERE duration != '00:00:00'::interval) )/( sum(reviews) FILTER (WHERE duration != '00:00:00'::interval) - count(reviews) FILTER (WHERE duration != '00:00:00'::interval))  as avgreviewtime
    FROM
        (
        SELECT island, age( max(done), min(done) ) as duration, count(island) as reviews -- calculate the duration of islands
        FROM
            (
            SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS island -- give a unique number to each island
            FROM (
                SELECT -- detect the beginning of islands
                    done,
                    (
                        lag(done) OVER (ORDER BY done) <= done - interval '2 min'
                    ) AS step
                FROM review
                WHERE clicker_id = 71 AND "done" > '2015-05-13' AND "done" < '2015-05-13 15:00:00' -- keep the queries small and fast for now
               ) sub
            ORDER BY done
            ) grouped
        GROUP BY island
        ) sessions
    ) summary