Может ли оконная функция LAG ссылаться на столбец, значение которого вычисляется?

Альтернативой рекурсивному подходу является настраиваемый агрегат. Когда вы овладеете техникой написания собственных агрегатов, создание переходных и конечных функций станет простым и логичным.

Функция перехода между состояниями:

create or replace function is_duplicate(st int[], time_stamp int, timeframe int)
returns int[] language plpgsql as $$
begin
    if st is null or st[1] + timeframe <= time_stamp
    then 
        st[1] := time_stamp;
    end if;
    st[2] := time_stamp;
    return st;
end $$;

Конечная функция:

create or replace function is_duplicate_final(st int[])
returns boolean language sql as $$
    select st[1] <> st[2];
$$;

Совокупный:

create aggregate is_duplicate_agg(time_stamp int, timeframe int)
(
    sfunc = is_duplicate,
    stype = int[],
    finalfunc = is_duplicate_final
);

Запрос:

select *, is_duplicate_agg(time_stamp, 10) over w
from event
window w as (partition by type order by time_stamp asc)
order by type, time_stamp;

 id | type | time_stamp | is_duplicate_agg 
----+------+------------+------------------
  1 |    1 |          1 | f
  2 |    1 |          2 | t
  4 |    1 |          3 | t
  5 |    1 |         10 | t
  7 |    1 |         15 | f
  8 |    1 |         21 | t
 10 |    1 |         40 | f
  3 |    2 |          2 | f
  6 |    2 |         10 | t
  9 |    2 |         13 | f
(10 rows)   

Прочтите документацию: 37.10. Пользовательские агрегаты и СОЗДАТЬ АГРЕГАТ.

Наивная рекурсивная вязальщица цепочки:

        -- temp view to avoid nested CTE
CREATE TEMP VIEW drag AS
        SELECT e.type,e.time_stamp
        , ROW_NUMBER() OVER www as rn                   -- number the records
        , FIRST_VALUE(e.time_stamp) OVER www as fst     -- the "group leader"
        , EXISTS (SELECT * FROM event x
                WHERE x.type = e.type
                AND x.time_stamp < e.time_stamp) AS is_dup
        FROM event e
        WINDOW www AS (PARTITION BY type ORDER BY time_stamp)
        ;

WITH RECURSIVE ttt AS (
        SELECT d0.*
        FROM drag d0 WHERE d0.is_dup = False -- only the "group leaders"
    UNION ALL
        SELECT d1.type, d1.time_stamp, d1.rn
          , CASE WHEN d1.time_stamp - ttt.fst > 20 THEN d1.time_stamp
                 ELSE ttt.fst END AS fst   -- new "group leader"
          , CASE WHEN d1.time_stamp - ttt.fst > 20 THEN False
                 ELSE True END AS is_dup
        FROM drag d1
        JOIN ttt ON d1.type = ttt.type AND d1.rn = ttt.rn+1
        )
SELECT * FROM ttt
ORDER BY type, time_stamp
        ;

Результаты:

CREATE TABLE
INSERT 0 10
CREATE VIEW
 type | time_stamp | rn | fst | is_dup 
------+------------+----+-----+--------
    1 |          1 |  1 |   1 | f
    1 |          2 |  2 |   1 | t
    1 |          3 |  3 |   1 | t
    1 |         10 |  4 |   1 | t
    1 |         15 |  5 |   1 | t
    1 |         21 |  6 |   1 | t
    1 |         40 |  7 |  40 | f
    2 |          2 |  1 |   2 | f
    2 |         10 |  2 |   2 | t
    2 |         13 |  3 |   2 | t
(10 rows)

Это больше похоже на рекурсивную проблему, чем на оконную функцию. Следующий запрос дал желаемые результаты:

WITH RECURSIVE base(type, time_stamp) AS (

  -- 3. base of recursive query
  SELECT x.type, x.time_stamp, y.next_time_stamp
    FROM 
         -- 1. start with the initial records of each type   
         ( SELECT type, min(time_stamp) AS time_stamp
             FROM event
             GROUP BY type
         ) x
         LEFT JOIN LATERAL
         -- 2. for each of the initial records, find the next TIMEFRAME (10) in the future
         ( SELECT MIN(time_stamp) next_time_stamp
             FROM event
             WHERE type = x.type
               AND time_stamp > (x.time_stamp + 10)
         ) y ON true

  UNION ALL

  -- 4. recursive join, same logic as base
  SELECT e.type, e.time_stamp, z.next_time_stamp
    FROM event e
    JOIN base b ON (e.type = b.type AND e.time_stamp = b.next_time_stamp)
    LEFT JOIN LATERAL
    ( SELECT MIN(time_stamp) next_time_stamp
       FROM event
       WHERE type = e.type
         AND time_stamp > (e.time_stamp + 10)
    ) z ON true

)

-- The actual query:

-- 5a. All records from base are not duplicates
SELECT time_stamp, type, false
  FROM base

UNION

-- 5b. All records from event that are not in base are duplicates
SELECT time_stamp, type, true
  FROM event
  WHERE (type, time_stamp) NOT IN (SELECT type, time_stamp FROM base) 

ORDER BY type, time_stamp

С этим есть много нюансов. Он не предполагает дублирования time_stamp для данного type. На самом деле объединения должны основываться на уникальном идентификаторе, а не на type и time_stamp. Я так много не тестировал, но, по крайней мере, это может предложить подход.

Я впервые пытаюсь присоединиться к LATERAL . Так что может быть способ упростить это мо. На самом деле я хотел сделать рекурсивный CTE с рекурсивной частью, использующей MIN(time_stamp) на основе time_stamp > (x.time_stamp + 10), но агрегатные функции не разрешены в CTE таким образом. Но похоже, что в CTE можно использовать боковое соединение.