By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test (
id bigint
, ts timestamp
);
INSERT INTO test VALUES
(1, '2013-08-08 10:00')
, (2, '2013-08-08 10:30')
, (3, '2013-08-08 10:35')
, (4, '2013-08-08 11:01')
, (5, '2013-08-08 13:00')
;
CREATE TABLE
INSERT 0 5
CREATE OR REPLACE FUNCTION running_window_ct(_intv interval = '1 hour')
RETURNS TABLE (id bigint, ts timestamp, ct int)
LANGUAGE plpgsql AS
$func$
DECLARE
cur CURSOR FOR
SELECT t.ts + _intv AS ts1
, row_number() OVER (ORDER BY t.ts ROWS UNBOUNDED PRECEDING) AS rn
FROM test t
ORDER BY t.ts;
rec record;
rn int;
BEGIN
OPEN cur;
FETCH cur INTO rec;
ct := -1; -- init
FOR id, ts, rn IN
SELECT t.id, t.ts
, row_number() OVER (ORDER BY t.ts ROWS UNBOUNDED PRECEDING)
FROM test t ORDER BY t.ts
LOOP
IF rec.ts1 >= ts THEN
ct := ct + 1;
ELSE
LOOP
FETCH cur INTO rec;
EXIT WHEN rec.ts1 >= ts;
END LOOP;
ct := rn - rec.rn;
END IF;
RETURN NEXT;
END LOOP;
END
$func$;
CREATE FUNCTION
-- call with default '1 hour'
SELECT * FROM running_window_ct();
| id | ts | ct |
|---|---|---|
| 1 | 2013-08-08 10:00:00 | 0 |
| 2 | 2013-08-08 10:30:00 | 1 |
| 3 | 2013-08-08 10:35:00 | 2 |
| 4 | 2013-08-08 11:01:00 | 2 |
| 5 | 2013-08-08 13:00:00 | 0 |
SELECT 5
SELECT * FROM running_window_ct('2h');
| id | ts | ct |
|---|---|---|
| 1 | 2013-08-08 10:00:00 | 0 |
| 2 | 2013-08-08 10:30:00 | 1 |
| 3 | 2013-08-08 10:35:00 | 2 |
| 4 | 2013-08-08 11:01:00 | 3 |
| 5 | 2013-08-08 13:00:00 | 1 |
SELECT 5