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. Tento článek ukazuje, jak chybějící body dopočítat lineární interpolací mezi sousedy ve třech variantách: 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);

Cílem je vyplnit 10:10 a 10:15 lineárně mezi 21,0 a 24,0 — výsledek by měl být 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 nejbližší předchozí a následující známou hodnotu pomocí carry-forward partition triku.
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 po posledním známém bodu 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. Tím se zbavíme nutnosti mít 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 skutečně dělá to, že přeskočí řádky, kde je time NULL — ale time v naší tabulce nikdy NULL není. Proto musíme time zabalit do CASE WHEN temperature IS NOT NULL THEN time END, aby se IGNORE NULLS chovalo podle temperature.

I přes tuto drobnost je dotaz výrazně čitelnější než bgrp/fgrp verze.

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ů a interpolate() 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 v těchto případech nedává smysl.

Nezapomeňte že - interpolovaná hodnota není naměřená hodnota. U chybějících dat ve větším rozsahu (ručně nastavený limit, např. > 60 minut) raději mezeru nechte prázdnou než dopočítávat hodnotu s krajních hodnot velkého rozsahu, kde se data mohou chovat nelineárně.