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?.
WITH dataset AS (
SELECT *
FROM
(
VALUES
('2010-04-14T00:00'::TIMESTAMP, 12.6941, 12.6941, 12.6941, 12.6941, 1),
('2010-04-14T00:00'::TIMESTAMP, 12.3851, 12.3851, 12.3851, 12.3851, 2),
('2010-04-14T00:20'::TIMESTAMP, 12.389, 12.389, 12.389, 12.389, 1),
('2010-04-14T00:20'::TIMESTAMP, 12.1836, 12.1836, 12.1836, 12.1836, 2),
('2010-04-14T00:20'::TIMESTAMP, 11.3887, 11.3887, 11.3887, 11.3887, 13)
) AS data(t, mean, max, min, std, data_id)
),
dataset_full AS (
SELECT
coalesce(t, time) AS t,
mean,
max,
min,
std,
data_id
FROM
generate_series(
(SELECT min(t) FROM dataset),
(SELECT max(t) FROM dataset),
'10 minutes')
AS times(time)
CROSS JOIN generate_series(
(SELECT min(data_id) FROM dataset),
(SELECT max(data_id) FROM dataset))
AS data_id(id)
JOIN dataset ON times.time = dataset.t AND data_id.id = dataset.data_id
), cte2 AS (
SELECT t, string_agg(concat(mean, ',', max, ',', min, ',', std), ',') AS s
, COUNT(*) AS c
FROM dataset_full
GROUP BY t
| t | ?column? |
|---|---|
| 2010-04-14 00:00:00 | 12.6941,12.6941,12.6941,12.6941,12.3851,12.3851,12.3851,12.3851,,,, |
| 2010-04-14 00:20:00 | 12.389,12.389,12.389,12.389,12.1836,12.1836,12.1836,12.1836,11.3887,11.3887,11.3887,11.3887 |