FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

create table t (u int, d timestamp);
insert into t values
	('1', '2024-01-19 16:24:11'), -- 1st (and only one of its serie).
	('1', '2024-01-19 16:27:29'), -- 1st
	('1', '2024-01-19 16:27:34'), -- -
	('1', '2024-01-19 16:27:38'), -- -
	('1', '2024-01-19 16:27:43'), -- -
	('1', '2024-01-19 16:29:30'), -- 1st, because >= 2 mn from the last valid (even if it's < 2 mn from the last invalid).
	('1', '2024-01-19 16:29:40'), -- -
	('1', '2024-01-19 16:29:50'), -- -
	('1', '2024-01-19 16:31:40'), -- 1st
	('1', '2024-01-19 16:31:50'), -- -
	('2', '2024-01-19 11:08:49'), -- 1st
	('2', '2024-01-19 11:09:32'), -- -
	('2', '2024-01-19 11:12:24'), -- 1st
	('2', '2024-01-19 11:14:49'); -- 1st

--------------------------------------------------------------------------------
-- 1. Simple but effective, although incomplete
-- https://stackoverflow.com/a/79372622/1346819

with l as (select *, lag(d) over (partition by u order by d) < d - interval '2 minutes' first from t)
select u, d from l where coalesce(first, true) order by u, d;

--------------------------------------------------------------------------------
-- 2. With (PostgreSQL-specific) a custom aggregate function
-- https://stackoverflow.com/a/79375532/1346819

create type firstAndWhen as (first bool, lastFirst timestamp);
create function firstSinceNSecondsAgg(accu firstAndWhen, new timestamp, nSeconds int) returns firstAndWhen language sql as
$$
    select
    	coalesce(new >= accu.lastFirst + cast(nSeconds||' seconds' as interval), true),
        case when coalesce(new >= accu.lastFirst + cast(nSeconds||' seconds' as interval), true)
        	then new
            else accu.lastFirst
        end
$$;
create function firstSinceNSecondsPop(accu firstAndWhen) returns bool language sql as 'select accu.first';
create aggregate firstSinceNSeconds(timestamp, int)
(
    stype = firstAndWhen,
    initcond = '(false,)',
    sfunc = firstSinceNSecondsAgg,
    finalfunc = firstSinceNSecondsPop
);

with l as (select *, firstSinceNSeconds(d, 120) over (partition by u order by d) ok from t)
select u, d from l where ok order by u, d;

--------------------------------------------------------------------------------
-- 3. With (somewhat more portable) recursivity
-- https://stackoverflow.com/a/79376860/1346819

with recursive
	-- Index our entries:
	i as (select row_number() over (partition by u order by d) id, * from t),
    -- Know whose election as a "first" will make each entry redundant for good.
	l as
	(
      	-- "pass" increments to keep hesitating entries queued for the next iteration
      	-- "kind":
      	--   1: first of a serie
      	--   0: don't know yet if first of serie or not
      	--  -1: confirmed duplicate (follows a confirmed first)
        --  -2: first, but finished (has no more followers to evaluate)
		select *, 0 pass, 0 kind from i
			union
		select id, u, d, pass + 1,
      		case
      			when kind = 0 then
      				case
      					-- Is the preceding entry more than 2 mn before? We're a first!
      					when coalesce(lag(d) over byu < d - interval '2 minutes', true) then 1
      					-- Else (if preceding is less than 2 mn away), if said preceding entry is itself a first, we're a duplicate.
      					when lag(kind) over byu = 1 then -1
      					-- Still not sure.
      					else 0
      				end
      			-- If we are a first but have no more followers waiting for us, get away.
      			when kind = 1 and coalesce(lead(kind) over byu <> 0, true) then -2
      			else kind
      		end
      	from l
      	where kind >= 0 -- Only work with confirmed firsts, and still hesitating ones.
and pass < 99 -- In case I missed something...
      	window byu as (partition by u order by d)
	)
--select * from l;
select u, d from i where (u, id) in (select u, id from l where kind = -2)
order by u, d;

u	d
1	2024-01-19 16:24:11
1	2024-01-19 16:27:29
2	2024-01-19 11:08:49
2	2024-01-19 11:12:24
2	2024-01-19 11:14:49

u	d
1	2024-01-19 16:24:11
1	2024-01-19 16:27:29
1	2024-01-19 16:29:30
1	2024-01-19 16:31:40
2	2024-01-19 11:08:49
2	2024-01-19 11:12:24
2	2024-01-19 11:14:49

u	d
1	2024-01-19 16:24:11
1	2024-01-19 16:27:29
1	2024-01-19 16:29:30
1	2024-01-19 16:31:40
2	2024-01-19 11:08:49
2	2024-01-19 11:12:24
2	2024-01-19 11:14:49

CREATE TABLE
INSERT 0 14

CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE AGGREGATE