Unterschiedlich strukturierte Daten kombinieren

[Ludwigmller]

Hallo,
ich möchte Daten unterschiedlicher Struktur in einer Übersicht kombiniert anzeigen. Siehe Beispiel im Anhang. Alle Daten sind raumspezifisch.
Die Daten sollen in einer ÜBersicht
1) je Raum für mehrere Tage
2) je Tag für mehrere (alle) Räume
3) graphisch je Raum über mehrere Tage
dargestellt werden. Die Abfrage sollte als Basis für alle Anwendungsfälle dienen, sodass keine Redundanzen vorhanden sind (bisher hab ich für 1)+2) jeweils seperaten Code).

Unten ein Ansatz. Ein Problem dabei ist, dass unterschiedliche Datentypen mit UNION kombiniert werden.
Die Struktur des Ergebnis ist:
datum, raum_id, bezeichnung, wert, kategorie
Bei der Erstellung der HTML-Tabelle müsste man dann alle Werte der selben kategorie zeilenweise ausgeben. die bezeichnung ist die Zeilenüberschirft.

Allgemein gefragt: wie kombiniere ich unterschiedliche Daten, die unterschiedlich abgefragt werden müssen in einem Ergebnis?

with
    all_dates AS (
        SELECT generate_series('2023-12-20'::date, '2023-12-24'::date, '1 day')::date AS datum
    ),
    cte_parameter as(
        select
                p.id,
                p.bezeichnung,
                CASE
                        WHEN "isSetting" = true THEN 'Einstellungen'
                        ELSE 'Messwerte'
                END AS kategorie
        from   
                parameter as p
        ),
     last_status AS (
        SELECT
            ad.datum,
            sr.raum_id,
            (
                SELECT status_id
                FROM lagerstatus_raum AS sr_inner
                WHERE sr_inner.zeitstempel::date <= ad.datum
                AND sr_inner.raum_id = sr.raum_id
                ORDER BY sr_inner.zeitstempel::date DESC
                LIMIT 1
            ) AS status_id
        FROM
            all_dates AS ad
            CROSS JOIN (SELECT DISTINCT raum_id FROM lagerstatus_raum)  AS sr
    )


-- Messwerte und Einstellungen
select
        pr.zeitstempel::date as datum,
        pr.raum_id,
        
        p.bezeichnung,
        pr.wert,
        p.kategorie
from
        cte_parameter as p
        LEFT JOIN parameter_raum as pr ON pr.parameter = p.id
where
        pr.zeitstempel::date = '2023-12-24'
        and pr.raum_id = 5
        
union

-- Kühlung
SELECT
    fz.datum,
    fz.raum_id,
    Case
        When fz.art = 1 then 'Kühlzeit'
        when fz.art = 2 then 'Kühltakte'
        when fz.art = 3 then 'Entfeuchtung'
    end as bezeichnung,
    fz.value_per_day,
    'Kühlung' as kategorie
FROM
    f_zaehler_pro_tag() fz
WHERE fz.datum >='2023-12-23'

UNION

-- Sonstiges (Status)
select ls.datum, ls.raum_id, 'Status' as bezeichnung, ls.status_id, 'Sonstiges' as kategorie
from last_status as ls

UNION

 -- Sonstiges (Bemerkung)''
SELECT
    a.datum,
    b.raum_id,
    'Bemerkung' as bezeichnung,
    b.bemerkung ,
    'Sonstiges' as kategorie
FROM
    all_dates AS a
    LEFT JOIN lagerung.bemerkung_raum AS b
        ON a.datum = b.zeitstempel::date
GROUP BY a.datum,b.raum_id,b.bemerkung
ORDER BY a.datum    ;

 

[Kampfgummibaerlie]

Convert to text zB, fällt mir mal ein.

Select 1::text as raum
Union
Select 'raum 2'::text as raum

Ob das mit time so einfach geht, weiß ich nicht.
Auf jeden fall struktur überdenken...

 

[Kampfgummibaerlie]

Time geht auch so, nur mit to_char

Select to_char(now()::time, 'hh24:mi')
Union
Select to_char(now()::date, 'dd.mm.yyyy')

Ungetestet, sollte aber gehen

Edit:
Ich rate von sowas ab...

 

[Kampfgummibaerlie]

Sodala, jetzt habe ich wieder einen Rechner ;D

soeben getestet:

Select to_char(now()::time, 'hh24:mi')
Union
Select to_char(now()::date, 'dd.mm.yyyy');

funktioniert
Ich kombiniere sowohl "time" als auch "date" in eine Spalte... beides halt in Form von Text

Für späteres weiterarbeiten mit diesen Werten müsste man dann wieder "zurückconverten"...

Select to_date(to_char(now()::date, 'dd.mm.yyyy'), 'DD.MM.YYYY');

um eine gegebene "zeit" (24 Stunden Format) aus einem text wieder in eine time umwandeln möchte, braucht man ein wenig mehr, da es keine to_time-Funktion gibt (aus standard)

select to_timestamp('23:59', 'HH24:MI')::time

wobei die Uhrzeit in Form von text die 23:59 ist

Wieder was gelernt xD (dass es keine to_time-Funktion gibt)

LG Baerlie

 

[akretschmer]

soeben getestet:

Select to_char(now()::time, 'hh24:mi')
Union
Select to_char(now()::date, 'dd.mm.yyyy');

funktioniert
Ich kombiniere sowohl "time" als auch "date" in eine Spalte... beides halt in Form von Text

Nun ja, das ist jetzt keine so dolle Überraschung, oder? Es ist halt beides TEXT. Du kannst da auch noch Temperaturen, Geldbeträge und Bibelsprüche via UNION dranklatschen ...

 

[dabadepdu]

Allgemein gefragt: wie kombiniere ich unterschiedliche Daten, die unterschiedlich abgefragt werden müssen in einem Ergebnis?

Um unterschiedliche Datenabfragen zu kombinieren, verwendest Du Union.
Unterschiedliche Daten(Strukturen) kombinierst Du damit nicht, das geht nicht. Das ist gegen das Prinzip von RDBMS.

Union verwendet man idR bei unterschiedlichen Datenquellen, das sind normalerweise verschiedene Tabellen. Gegenbeispiel dazu, was gerne falsch verstanden wird. Eine Kundentabelle, in der ich z.B. 3 Gruppen von Kunden suche. Das ist kein Fall für Union. Die Struktur der Kundentabelle und die Ausgabe einzelner Spalten daraus ist immer gleich. Die Unterscheidung/ Eingrenzung wird über die Where Clause geregelt.

Wenn Du über Union nachdenkst, musst Du zwangsläufig über die gewünschte Struktur im Ergebnis nachdenken. Typumwandlung zu Text, der alles schluckt, wurde schon vorgeschlagen. Das ist einfach, aber tückisch, weil die Konvertierung von Zahlen und Datumswerten zu Text einen Funktionsverlust bedeutet. Jede Varianz, die Du in ein Union Ergebnis reinzauberst, musst Du im Zweifel später irgendwo im Code weiter verarbeiten. Fraglich ob das gewünscht, sinnvoll und praktikabel ist.

Das macht man nicht aus Spaß, am ehesten vielleicht in einem Report. Also eine Ergebnisform, die Menschen als Übersicht lesen oder der in irgendeiner Form als Export dient und maschinell verarbeitet wird (sprich: jemand anders darf das Durcheinander wieder einlesen).

Eine weitere Möglichkeit, eine Zusammenführung mit strukturelle Varianz zu erzielen, ist die Transformation Deiner Daten nach JSON (oder in Postgres sogar JSONB, in dem Fall aber sehr wahrscheinlich sinnlos). Du würdest mit Union alles machbare und notwendige in klassichen Typen zusammenfassen / erhalten und variable Teile in einer JSON Spalte zusammenfassen. Damit hast Du aus SQL Perspektive eine gleichförmige Struktur, aber beliebig starke Varianz im Inhalt.

In Postgres hast Du dazu sehr gute Möglichkeiten. Wichtig ist allerdings auch hier, dass Du für eine mögliche Weiterverarbeitung dieser Daten keine strukturellen Informationen im Union verschenkst/verlierst. Ein schnödes Textarray wäre also nur ein winziger Schritt weiter als die Konvertierung zu Text. Eine definierte JSON Objekt Struktur, die hirarchisch wäre die bessere Wahl.
Proof of Conzept wären Abfragen, die aus dem Union Ergebnis mit JSON Bestandteilen wieder originale Daten und - Struktur erstellen.
Ob das so weitreichend notwendig ist, hängt vom Einsatzzweck ab.

 

[ukulele]

Zunächst stellt sich die Frage wie die Daten logisch zusammen hängen. Liefern beide deiner Datenquellen Daten zur selben Entität? Also z.B. je einen Datensatz zu Raum 5? Dann wäre ein JOIN angebracht. Ist jeder Raum nur in einer Datenquelle enthalten? Dann wäre UNION korrekt, dann müsste man sich nur die Attribute anschauen. Werden unterschiedliche Attribute geliefert gibt es Spalten die in einer Quelle immer NULL sind, sind die Attribute identisch muss man die Spalten entsprechend konvertieren damit sie zusammen passen.