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_fill_null ( -- 1st case
date int -- unfortunate column names
, value int
);
INSERT INTO test_fill_null VALUES
(1,2)
, (2, NULL)
, (3, 45)
, (4,NULL)
, (5, null)
;
CREATE TABLE table0 ( -- 2nd case
id1 int
, id2 int
, tms timestamptz
);
INSERT INTO table0 VALUES
(1,2, '2015-12-16 16:09:00+01')
, (1,3, '2015-12-16 16:11:00+01')
, (2,4, '2015-12-16 16:10:00+01')
, (2,5, '2015-12-16 16:12:00+01')
, (1,6, '2015-12-16 16:13:01+01')
, (2,7, '2015-12-16 16:52:00+01') -- new group after gap
, (2,8, '2015-12-16 16:54:00+01')
;
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 7
-- 1st case
SELECT date, max(value) OVER (PARTITION BY grp) AS value
FROM (
SELECT *, count(value) OVER (ORDER BY date) AS grp
FROM test_fill_null
) sub;
| date | value |
|---|---|
| 1 | 2 |
| 2 | 2 |
| 3 | 45 |
| 4 | 45 |
| 5 | 45 |
SELECT 5
-- 2nd case
SELECT id1, id2, tms
, count(step) OVER (ORDER BY id1, id2, tms) AS group_id
FROM (
SELECT *, lag(tms, 1, '-infinity') OVER (PARTITION BY id1 ORDER BY id2, tms) < tms - interval '5 min' AS step
FROM table0
) sub
ORDER BY id1, id2, tms;
| id1 | id2 | tms | group_id |
|---|---|---|---|
| 1 | 2 | 2015-12-16 15:09:00+00 | 1 |
| 1 | 3 | 2015-12-16 15:11:00+00 | 2 |
| 1 | 6 | 2015-12-16 15:13:01+00 | 3 |
| 2 | 4 | 2015-12-16 15:10:00+00 | 4 |
| 2 | 5 | 2015-12-16 15:12:00+00 | 5 |
| 2 | 7 | 2015-12-16 15:52:00+00 | 6 |
| 2 | 8 | 2015-12-16 15:54:00+00 | 7 |
SELECT 7
-- Remi
WITH second_case AS (
SELECT id1, id2, tms
, CASE WHEN lag(tms, 1, '-infinity') OVER (partition by id1 ORDER BY id2, tms) < tms - interval '5 min' THEN id2 END AS group_id
FROM table0
)
SELECT id1, id2, tms
, count(group_id) OVER (ORDER BY id1, id2, tms) AS group_id
FROM second_case
ORDER BY id1, id2, tms;
| id1 | id2 | tms | group_id |
|---|---|---|---|
| 1 | 2 | 2015-12-16 15:09:00+00 | 1 |
| 1 | 3 | 2015-12-16 15:11:00+00 | 1 |
| 1 | 6 | 2015-12-16 15:13:01+00 | 1 |
| 2 | 4 | 2015-12-16 15:10:00+00 | 2 |
| 2 | 5 | 2015-12-16 15:12:00+00 | 2 |
| 2 | 7 | 2015-12-16 15:52:00+00 | 3 |
| 2 | 8 | 2015-12-16 15:54:00+00 | 3 |
SELECT 7