bei fehlenden Wert, den des Vortags zurückgeben

[Ludwigmller]

Moin,
ich habe eine Tabelle Messwerte, wo je raum_id, messgroesse und Tag ein Wert gespeichert ist. Wenn Werte fehlen, sollen diese in der Abfrage x Tage fortgeführt werden.
Zum Beispiel:

INSERT INTO messwerte (zeitstempel, raum_id, messgroesse, wert) VALUES 
    ('2023-12-26'::timestamp, 1, 'SW', 2.5),
    ('2023-12-30'::timestamp, 1, 'SW', 2.8),

Dann soll die Ausgabe für x=3 wie folgt aussehen:
2023-12-26 1 SW 2.5
2023-12-27 1 SW 2.5
2023-12-28 1 SW 2.5
2023-12-29 1 SW 2.5
2023-12-30 1 SW 2.8

Für messgroesse sollen alle in Tabelle messgroessen vorhandenen gejoined werden.
Hier ein Fiddle mit Testdaten und meinem Versuch. Wenn ich DISTINCT ON weglasse, gibt es pro Tag auch mehrere Werte der Vortage, auch wenn für den jeweiligen Tag tatsächliche WErte vorhanden sind. Mit DISTINCT ON, ist es zum einen nur eine Notlösung "mit Gewalt", zum anderen wird manchmal der Wert des Vortags ausgegeben, obwohl es einen Wert für den Tag gibt.

 

[dabadepdu]

Deine Lösung mit AVG finde ich nicht so schlecht, eine Alternative wäre Lag/Lead, bzw. Lag für den Vortag.
Allerdings haben beide Varianten das Problem, dass sie auf feste Tage eingeschränkt sind (3 Tage Durchschnitt oder DER Vortag)
Diese Einschränkung passt wahrscheinlich nicht zur Realität, wo auch mal mehrere Tage fehlen können. (Mehrfachnennung innerhalb eines Tages wäre mit dem 3 Tage Durchschnitt aber gut abgedeckt, ein Durchschnitt wäre vermutlich auch besser (näher an der Realität) als die Fortschreibung.
Muss ich noch später noch mal drüber nachdenken.

 

[Ludwigmller]

AVG sollte eigentlich zum Mitteln von mehreren Werten pro Tag, nicht bei fehlenden Werten sein.
Bei fehlenden Werten soll fortgeschrieben werden, da angenommen wird, dass bei einer Änderung ein neuer Wert gespeichert wird. Es handelt sich real um Einstellungen, nicht Messwerte. Das ist nur im Beispiel so benannt worden.

 

[dabadepdu]

Als Erweiterung von Lag/Lead Window Functions kann man an der Stelle first_value/last_value verwenden, um auch Lücken von mehreren Tagen zu verarbeiten.

 

[Ludwigmller]

Ein neuer Versuch, der offenbar das gewünschte Ergebnis liefert:

            SELECT
                ad.datum,
                pr.raum_id::integer,
                pr.parameter::text as bezeichnung,
                pr.wert::text,
                'Einstellungen' as kategorie
            FROM
                all_dates ad
                CROSS JOIN LATERAL (
                    SELECT DISTINCT ON (raum_id, parameter)
                        inner_pr.raum_id,
                        parameter,
                        inner_pr.wert -- bei Mehrfachwerten pro Tag wird der letzte zurückgegeben
                        --round(avg(wert),1) as wert
                    FROM
                        parameter_raum as inner_pr
                        INNER JOIN parameter as p on p.id = inner_pr.parameter 
                    WHERE
                        zeitstempel::date <= ad.datum
                        AND AGE(ad.datum, zeitstempel) <= interval '1 day' * c_interval_setting
                        and p."isSetting" = true        
                        and (p_raum_id IS NULL OR inner_pr.raum_id = p_raum_id)
                    ORDER BY
                        raum_id ASC, parameter DESC, zeitstempel desc
                ) pr
            ORDER BY
                ad.datum, pr.raum_id;

 

[dabadepdu]

Wie gesagt: schau Dir mal die Windows Function first_value und last_value an.