nicht angemeldet
Kreuztabelle / nach Datum (Monat, Jahr)
Hallo,
ich komme irgendwie nicht weiter ... Ich habe einen recht umfangreichen Datensatz zum Thema Klima für gut 2000 Standorte (durch lat und long definiert). Die Daten enthalten u.a. die Durchschnittstemperatur (tav), die durchschnittliche Minimal- (tmin) und Maximaltemperatur (tmax) sowie den durchschnittlichen Niederschlag (rrr). Die Daten sind für 9 Jahre vorhanden, jeweils 3 Werte pro Monate (also Dekaden, 10-Tages-Intervalle).
Die über die 9 Jahre gemessenen durchschnittlichen Januarwerte frage ich z.B. so ab:
SELECT latitude, longitude, AVG(tav) 01_tav, AVG(tmax) 01_tmax, AVG(tmin) 01_tmin, AVG(rrr) 01_rrr
FROM f2000to2008
WHERE MONTH = 1
GROUP BY latitude, longitude
ORDER BY latitude, longitude
Nun würde ich mit möglichst wenigen Abfragen
- die Durchschnittswerte für alle Monate über die 9 Jahre abfragen (also als Spalten erhalten: latitude, longitude, 01_tav, 01_tmax, ..., 12_rrr)
- sowie das gleich für die 9 Jahre in Folge (ist aber erst mal nebensächlich).
Wie stelle ich das am geschicktesten an? Irgendwie benötige ich wohl eine Pivot/Kreuztabelle, oder? Da komme ich aber nicht wirklich mit zurecht. Und mit Subqueries und Unions bin ich auch nicht weiter gekommen.
Vielen Dank & Grüße,
luti
ps: Ich verwende MySQL 5.
-
Hi!
Nun würde ich mit möglichst wenigen Abfragen
- die Durchschnittswerte für alle Monate über die 9 Jahre abfragen (also als Spalten erhalten: latitude, longitude, 01_tav, 01_tmax, ..., 12_rrr)
Reicht da nicht, wenn du über Latitude, Longitude, Jahr und Monat gruppierst?
Lo!
-
Hallo,
Reicht da nicht, wenn du über Latitude, Longitude, Jahr und Monat gruppierst?
Dann bekomme ich die Ergebnisse aber noch nicht als (zusätzliche) Spalten, sondern als (zusätzliche) Zeilen, also 12 (Monate) x 9 (Jahre) = 108 Zeilen pro Ort. Bei 2382 Orten macht das 257.256 Zeilen ... Die möchte ich ungern über PHP abfrage und weiterverarbeiten (die Abfrage dauert schon 15-20 Sekunden).
Dank & Gruß,
luti-
Hallo,
Reicht da nicht, wenn du über Latitude, Longitude, Jahr und Monat gruppierst?
Dann bekomme ich die Ergebnisse aber noch nicht als (zusätzliche) Spalten, sondern als (zusätzliche) Zeilen, also 12 (Monate) x 9 (Jahre) = 108 Zeilen pro Ort. Bei 2382 Orten macht das 257.256 Zeilen ... Die möchte ich ungern über PHP abfrage und weiterverarbeiten (die Abfrage dauert schon 15-20 Sekunden).
Indexe?
Ansonsten solltest Du meinen Ansatz aus dem Archiv auf Dein Problem anwenden können.
Freundliche Grüße
Vinzenz
-
Hallo,
Indexe?
jaja, ich weiß. Aber: siehe die Antwort oben ...
Ansonsten solltest Du meinen Ansatz aus dem Archiv auf Dein Problem anwenden können.
Das werde ich mir jetzt mal zu Gemüte führen ;)
Dank & Gruß,
luti -
Hallo,
Ansonsten solltest Du meinen Ansatz aus dem Archiv auf Dein Problem anwenden können.
Danke, das hat jetzt wunderbar geklappt! Weiter unten der "längliche" Code (für die Nachwelt).
Die Query hat ohne Indices jetzt 4,5 Sekunden gedauert. Was für Indices würdest Du / würdet ihr denn setzten? Also über welche Spalten, meine ich. Damit wollte ich mich schon immer mal etwas näher befassen ... Aber eine Kurze Antwort wäre z.Zt. auch sehr hilfreich.
Bei einer "einfachen" Abfrage á la "SELECT ... where ID=" würde ich natürlich einfach die ID-Spalte indizieren. Aber im obigen Beispiel? Ein Index für über alle Spalten? Einen über lat+long, plus je einen pro CASE???
Dank & Gruß,
lutiUnd hier noch die Abfrage:
SELECT latitude, longitude, # mittlere Durchschnittswerte AVG(CASE WHEN MONTH = 1 THEN tav ELSE NULL END) AS 01_tav, ... AVG(CASE WHEN MONTH =12 THEN tav ELSE NULL END) AS 12_tav, # mittlere Maximalwerte AVG(CASE WHEN MONTH = 1 THEN tmax ELSE NULL END) AS 01_tmax, ... AVG(CASE WHEN MONTH =12 THEN tmax ELSE NULL END) AS 12_tmax, # mittlere Minimalwerte AVG(CASE WHEN MONTH = 1 THEN tmin ELSE NULL END) AS 01_tmin, ... AVG(CASE WHEN MONTH =12 THEN tmin ELSE NULL END) AS 12_tmin, # mittlere Niederschläge AVG(CASE WHEN MONTH = 1 THEN rrr ELSE NULL END) AS 01_rrr, ... AVG(CASE WHEN MONTH =12 THEN rrr ELSE NULL END) AS 12_rrr FROM f2000to2008 GROUP BY latitude, longitude ORDER BY latitude, longitude
-
-
Hi!
Reicht da nicht, wenn du über Latitude, Longitude, Jahr und Monat gruppierst?
Dann bekomme ich die Ergebnisse aber noch nicht als (zusätzliche) Spalten, sondern als (zusätzliche) Zeilen, also 12 (Monate) x 9 (Jahre) = 108 Zeilen pro Ort. Bei 2382 Orten macht das 257.256 Zeilen ...Dann wüsste ich jetzt auch nicht, wie man das von senkrecht nach waagerecht bekommt, außer man baut korrelierte Subselects als Werte ein.
Die möchte ich ungern über PHP abfrage und weiterverarbeiten (die Abfrage dauert schon 15-20 Sekunden).
Passende Indexe sind gesetzt?
Lo!
-
Hallo,
Dann wüsste ich jetzt auch nicht, wie man das von senkrecht nach waagerecht bekommt, außer man baut korrelierte Subselects als Werte ein.
Was genau meinst Du mit "korrelierte Subselects"? Hast Du da mal ein Beispiel bzw. wie würdest Du die auf das Problem anwenden?
Passende Indexe sind gesetzt?
Ok-ok, habe ich nicht, aber trotzdem wäre mir die andere Anordnung wesentlich "lieber" ;)
Dank & Gruß,
luti-
Hi!
Was genau meinst Du mit "korrelierte Subselects"? Hast Du da mal ein Beispiel bzw. wie würdest Du die auf das Problem anwenden?
Dabei bezieht sich die Subquery auf Werte der äußeren Query. Beispielsweise so:
SELECT (SELECT irgendwas FROM table2 WHERE feld = a.feld) FROM table a
Lo!
-
Hallo,
Dabei bezieht sich die Subquery auf Werte der äußeren Query. Beispielsweise so:
SELECT (SELECT irgendwas FROM table2 WHERE feld = a.feld) FROM table aAchso, das hatte ich auch schon mal probiert. Aber irgendwie kam ich damit auch nicht recht weiter. Aber Vinzenz' Vorschlag hat jetzt "gefunzt" ...
Dank & Gruß,
luti
-
-
-
-