Gap-fill s lineární interpolací v PostgreSQL

Časová řada s pravidelnými intervaly (např. teplotní senzor měřící každých 5 minut) v praxi často obsahuje díry. Ukážu tři způsoby, jak chybějící body dopočítat lineární interpolací mezi sousedy: klasický PostgreSQL, PostgreSQL 16+/18 a TimescaleDB.

CREATE TABLE readings (
    time        timestamptz PRIMARY KEY,
    temperature numeric
);

INSERT INTO readings (time, temperature) VALUES
    ('2024-01-15 10:00+00', 20.0),
    ('2024-01-15 10:05+00', 21.0),
    -- 10:10 chybí
    -- 10:15 chybí
    ('2024-01-15 10:20+00', 24.0),
    ('2024-01-15 10:25+00', 25.0);

Chceme vyplnit 10:10 a 10:15 lineárně mezi 21,0 a 24,0, takže výsledek vyjde 22,0 a 23,0.

Strategie:

1. Vygenerovat kompletní řadu přes generate_series.
2. LEFT JOIN na původní data - chybějící body jsou NULL.
3. Pro každý NULL najít pomocí carry-forward partition triku nejbližší předchozí a následující známou hodnotu.
4. Lineárně interpolovat.

WITH grid AS (
    SELECT generate_series(
        '2024-01-15 10:00+00'::timestamptz,
        '2024-01-15 10:25+00'::timestamptz,
        interval '5 minutes'
    ) AS time
),
joined AS (
    SELECT g.time, r.temperature
    FROM grid g
    LEFT JOIN readings r USING (time)
),
flagged AS (
    SELECT time, temperature,
        SUM((temperature IS NOT NULL)::int)
            OVER (ORDER BY time ROWS UNBOUNDED PRECEDING) AS bgrp,
        SUM((temperature IS NOT NULL)::int)
            OVER (ORDER BY time DESC ROWS UNBOUNDED PRECEDING) AS fgrp
    FROM joined
),
neighbours AS (
    SELECT time, temperature,
        FIRST_VALUE(temperature) OVER (PARTITION BY bgrp ORDER BY time)      AS prev_y,
        FIRST_VALUE(time)        OVER (PARTITION BY bgrp ORDER BY time)      AS prev_t,
        FIRST_VALUE(temperature) OVER (PARTITION BY fgrp ORDER BY time DESC) AS next_y,
        FIRST_VALUE(time)        OVER (PARTITION BY fgrp ORDER BY time DESC) AS next_t
    FROM flagged
)
SELECT
    time,
    CASE
        WHEN temperature IS NOT NULL THEN temperature
        WHEN prev_y IS NOT NULL AND next_y IS NOT NULL
            THEN prev_y + (next_y - prev_y)
                          * EXTRACT(epoch FROM (time - prev_t))
                          / EXTRACT(epoch FROM (next_t - prev_t))
    END AS temperature
FROM neighbours
ORDER BY time;

Jak funguje bgrp/fgrp: kumulativní součet IS NOT NULL zepředu (resp. zezadu) vytvoří partition, ve které všechny NULL řádky za posledním známým bodem sdílí stejné číslo. FIRST_VALUE v takové partition vrátí předchozí (resp. následující) známou hodnotu.

Od PostgreSQL 16 (a tedy i v PG 17/18) je k dispozici standardní SQL klauzule IGNORE NULLS pro LAG, LEAD, FIRST_VALUE, LAST_VALUE a NTH_VALUE. Díky tomu odpadá celá konstrukce bgrp/fgrp:

WITH grid AS (
    SELECT generate_series(
        '2024-01-15 10:00+00'::timestamptz,
        '2024-01-15 10:25+00'::timestamptz,
        interval '5 minutes'
    ) AS time
),
joined AS (
    SELECT g.time, r.temperature
    FROM grid g
    LEFT JOIN readings r USING (time)
),
neighbours AS (
    SELECT
        time,
        temperature,
        LAG(temperature)  IGNORE NULLS OVER (ORDER BY time) AS prev_y,
        LAG(CASE WHEN temperature IS NOT NULL THEN time END)
                          IGNORE NULLS OVER (ORDER BY time) AS prev_t,
        LEAD(temperature) IGNORE NULLS OVER (ORDER BY time) AS next_y,
        LEAD(CASE WHEN temperature IS NOT NULL THEN time END)
                          IGNORE NULLS OVER (ORDER BY time) AS next_t
    FROM joined
)
SELECT
    time,
    CASE
        WHEN temperature IS NOT NULL THEN temperature
        WHEN prev_y IS NOT NULL AND next_y IS NOT NULL
            THEN prev_y + (next_y - prev_y)
                          * EXTRACT(epoch FROM (time - prev_t))
                          / EXTRACT(epoch FROM (next_t - prev_t))
    END AS temperature
FROM neighbours
ORDER BY time;

Pozor na detail: LAG(time) IGNORE NULLS přeskakuje řádky, kde je time NULL. Jenže time v naší tabulce NULL nikdy není. Proto time zabalíme do CASE WHEN temperature IS NOT NULL THEN time END, aby se IGNORE NULLS řídilo podle temperature.

I s touhle drobností je dotaz výrazně čitelnější než verze s bgrp/fgrp.

S extenzí TimescaleDB stačí jeden dotaz:

SELECT
    time_bucket_gapfill('5 minutes', time) AS bucket,
    interpolate(avg(temperature))          AS temperature
FROM readings
WHERE time >= '2024-01-15 10:00+00'
  AND time <= '2024-01-15 10:25+00'
GROUP BY bucket
ORDER BY bucket;

time_bucket_gapfill vygeneruje kompletní řadu 5-minutových bucketů, interpolate() pak doplní chybějící body lineární interpolací mezi sousedy.

Výhody:

• žádný WITH řetěz, žádné okenní funkce
• přirozeně funguje s GROUP BY (např. per sensor_id)
• existuje i locf() pro variantu last observation carried forward (pro skokové stavové hodnoty)

Omezení:

• vyžaduje TimescaleDB extenzi
• WHERE time BETWEEN ... AND ... je povinné - gapfill potřebuje znát hranice

Lineární interpolace je vhodná pro veličiny, které se mezi měřeními mění spojitě (teplota, tlak, vlhkost, hladina). Pro skokové stavové signály (online/offline příznak, kategoriální stav) volte radši LOCF. Lineární interpolace tu nedává smysl.

Nezapomeňte: interpolovaná hodnota není naměřená hodnota. U větších mezer (ručně nastavený limit, např. > 60 minut) raději nechte mezeru prázdnou, než abyste dopočítávali hodnotu z krajních bodů velkého rozsahu, kde se data mohou chovat nelineárně.