Knifflige Datenabfrage

Hallo zusammen,

für mein Projekt habe ich zwei Tabellen:
Tabelle 1: Wetterstationen mit primary key iaco (= internationales Kürzel des Stationsnamens)
Tabelle 2: Wetterdaten, pro Station 25 Einträge

Bisher habe ich per PHP-Skript folgendes gemacht:
Tabelle 1 auslesen
Schleife über alle Stationen
Wetterdatenabfrage für jede einzelne Station
Ausgabe der Wetterdaten
Schleifenende

Wenn möglich, möchte ich das in eine Query packen. Ich stehe da irgendwie voll auf dem Schlauch, auch wenn ich einen Schritt zu Seite gehe, steh ich immer noch drauf :smiley:

Für sachdienliche Hinweise bin ich dankbar.

Die Wetterdatenabfrage - es wird immer die aktuellste Wettermeldung ausgegeben - sieht wie folgt aus:

    SELECT *,((datum + INTERVAL '200' MINUTE) > utc_timestamp()),-1 AS uvindex 
    		FROM t_wetterdaten_synop 
    		WHERE iaco='%s' AND ((datum + INTERVAL '200' MINUTE) > utc_timestamp()) 
    		ORDER BY datum DESC LIMIT 1

%s = Stationskürzel aus Tabelle 1

Im Voraus besten Dank für die Antworten

Jürgen

Bin mir nicht sicher, dich richtig zu verstehen. Du hast eine Tabelle mit den Stationen und je Station dann Messwerte und willst den aktuellsten Meßwert je Station, oder?

test=*# select * from stationen ;
 iaco  |   name    
-------+-----------
 iaco1 | station 1
 iaco2 | station 2
 iaco3 | station 3
(3 rows)

test=*# select * from daten ;
 iaco  |         ts          | wert 
-------+---------------------+------
 iaco1 | 2020-01-01 00:00:00 |   10
 iaco2 | 2020-01-01 00:00:00 |   12
 iaco3 | 2020-01-01 00:00:00 |   13
 iaco1 | 2020-02-01 00:00:00 |    5
 iaco2 | 2020-02-01 00:00:00 |    4
 iaco3 | 2020-02-01 00:00:00 |    7
 iaco1 | 2020-03-01 00:00:00 |    9
 iaco2 | 2020-03-01 00:00:00 |   12
 iaco3 | 2020-03-01 00:00:00 |   11
(9 rows)

test=*# select distinct on (s.iaco, s.name) s.iaco, s.name, d.ts, wert from stationen s left join daten d on s.iaco=d.iaco order by s.iaco, s.name, d.ts desc;
 iaco  |   name    |         ts          | wert 
-------+-----------+---------------------+------
 iaco1 | station 1 | 2020-03-01 00:00:00 |    9
 iaco2 | station 2 | 2020-03-01 00:00:00 |   12
 iaco3 | station 3 | 2020-03-01 00:00:00 |   11
(3 rows)

DISTINCT ON ist eine Erweiterung in PostgreSQL - und ziemlich cool. Dein utc_timestamp() gehört nicht zu den Grundfunktionen in PG, oder?

Ich würde es in etwa so schreiben. Da die Tabellen mir ja nicht genau bekannt sind.

with wetterdaten as (
	select 	d.*,((d.datum + INTERVAL '200' MINUTE) > utc_timestamp()),-1 AS uvindex
			, row_number() over (partition by d.iaco order by d.datum desc) as rownum_iaco
		from t_wetterstation s
		join t_wetterdaten_synop d on d.iaco = s.iaco
			and ((d.datum + INTERVAL '200' MINUTE) > utc_timestamp())
)
select * 
	from wetterdaten
	where rownum_iaco = 1
;

@akretschmer
Danke für Deine Antwort.

Genau.

Das werde ich mir genauer ansehen.

Die Funktion hatte ich mir nur aus Schreibfaulheit mal geschrieben und leistet immer noch gute Dienste :slightly_smiling_face:

@frastr
Ebenfalls Danke für Deine Antwort.
Ich hatte die beiden Tabellenstruktutren nicht gepostet, weil sie 24 bzw. 38 Spalten haben und schon bis zum Anschlag normalisiert sind.
Deine Query muss ich mal analysieren und versuchen zu verstehen.
Bei Fragen melde ich mich wieder :smiley:

Hier das Ergebnis des Istzustands:
https://juedan.nerdcamp.net/PHPSkripte/wetterdienst_karte.php?typ=wetterkarte&lkz=DE-BAYALPEN
So soll es hinterher auch wieder aussehen :smiley:

Beste Grüße und nochmals Danke

Jürgen

@frastr
Eine Verständnisfrage zu Deinem Query-Beispiel habe ich noch:
Was macht

row_number() over (partition by d.iaco order by d.datum desc) as rownum_iaco

?

Im Voraus Danke für die Antwort.

Grüßle Jürgen

row_number() ist eine sog. Window-Funktion, die die Zeilen zählt, unterteilt (partition by) nach der iaco-Spalte und sortiert (order by) nach dem Datum (desc, absteigend).

@akretschmer: Danke für deine schnelle Antwort

@juedan: Es wird also das End-Ergebnis (nach dem Join und where) genommen. Dann wird die Spalte iaco in Partitionen unterteilt, selbe icao gehören zu einer Partition und innerhalb jeder Partition nach dem Datum absteigend sortiert). Dann bekommt jeder Datensatz eine laufende Nummer von 1 aufsteigend (in Spalte rownum_iaco).

Moin, moin.

Jetzt verstehe ich die Query vollständig. Danke Euch für die Erklärung.

Schönen Tag noch

Jürgen