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
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
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
;
Die Funktion hatte ich mir nur aus Schreibfaulheit mal geschrieben und leistet immer noch gute Dienste
@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
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).
@anon40162420: 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).
