GrazerTourer Geschrieben 17. Januar 2015 Teilen Geschrieben 17. Januar 2015 (bearbeitet) Hallo, Meine SQL Fähigkeiten sind irgendwie gewaltig eingerostet *g* Bitte helft mir kurz. Ich habe eine Tabelle mit Wetterdaten, die ich minütlich befülle. Es gibt also einen Datensatz pro Minute. Ich werte die Daten mit diversen Tools grafisch aus. Da wo ich die Daten wirklich minutengenau brauche, ist das okay, allerdings ergibt das nicht immer Sinn. Beispielsweise wäre es für eine Wochengrafik am besten, die Daten bspw. auf 30min zu gruppieren, oder eine Stunde.... die Unschärfe ist hier besser, als zu viele Datenpunkte im Diagramm. Aber der Reihe nach. Die Spalten meiner Tabelle 'wettertabelle': datetime, temp, hum, pressure,windspeed,rainfall,rainrate Beispiel Daten: SELECT * FROM wettertabelle WHERE DATE( DATETIME ) = '2014-12-24' Ergebnis: datetime temp hum pressure windspeed rainfall rainrate 2014-12-24 00:00:00 8.9 22.0 0.0 0.0 0.0 0.0 2014-12-24 00:01:00 8.9 22.0 0.0 0.0 0.0 0.0 2014-12-24 00:02:00 9.1 22.0 0.0 0.0 0.0 0.0 2014-12-24 00:03:00 9.1 22.0 0.0 0.0 0.0 0.0 2014-12-24 00:04:00 9.6 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:05:00 9.6 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:06:00 9.6 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:07:00 9.6 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:08:00 9.6 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:09:00 9.5 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:10:00 9.5 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:11:00 9.5 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:12:00 9.5 21.0 0.0 0.0 0.0 0.0 2014-12-24 00:13:00 9.5 21.0 0.0 0.0 0.0 0.0 usw. usf. da sind natürlich sonst bessere DAten drin, als nur 0 ;-) Ich hätte jetzt gerne eine abfrage, die mir die werte bwp nach 15 min, 1h, 1 tag, 1 monat usw gruppiert... also zB sowas: SELECT DATE_FORMAT(DATETIME , '%Y-%d-%m') as datetime, avg(TEMP) as avgtemp FROM wettertabelle where DATE( DATETIME ) = '2014-12-24' ___group by 15 minuten___ Ich hätt halt einfach für die spätere Diagrammgenerierung die Daten aggregiert auf 15min, oder 30mn oder 1 Tag usw usf... WEnn ich mir immer die ROhdaten reinhole und erst im Diagram aggregiere, ist mir das zu langsam. Wäre cool, wenn mir wer helfen könnte. //Edit Ha, auf einmal hatte ich einen Geistesblitz! Kann ich das so verwenden, oder habe ich da noch wo einen Denkfehler? Für mich schaut'S gut aus.... Bitte kurz drüber schauen stündlich aggregiert für einen bestimmten Tag SELECT datetime, avg(temp) as avgtemp FROM wettertabelle where date(datetime)='2014-10-24' GROUP BY DATE(datetime), HOUR(TIME(datetime)) stündlich für ein monat SELECT datetime, avg(temp) as avgtemp FROM wettertabelle WHERE Month(datetime)='12' and year(datetime)='2014' GROUP BY DATE(datetime), HOUR( TIME(datetime) Daten pro Tag aggregiert für alle daten von einem jahr SELECT datetime, avg(temp) as avgtemp FROM wettertabelle WHERE year(datetime)='2014' GROUP BY DATE(datetime), day(datetime) Bearbeitet 17. Januar 2015 von GrazerTourer Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
Rowerek Geschrieben 17. Januar 2015 Teilen Geschrieben 17. Januar 2015 Für solche Aufzeichnungen verwendet man im Normalfall eher rrdtool. Das erstellt auch die Diagramme am Webserver. Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
GrazerTourer Geschrieben 17. Januar 2015 Autor Teilen Geschrieben 17. Januar 2015 Für solche Aufzeichnungen verwendet man im Normalfall eher rrdtool. Das erstellt auch die Diagramme am Webserver. Habe ich in Verwendung und finde ich ehrlich gesagt nicht immer optimal. Für ein paar Standardanforderungen ist es super, aber wenn man sich mit einem Archiv spielen will wird'S lästig. Dynamisch kannst da nicht wirklich was tun... Vergleiche: hier mein gnuplot chart über die Temperatur und Luftfeuchtigkeit das mittels cronjobs regelmäßig erstellt wird. http://www.trickytrails.com/Wordpress/webcam/graphs/temphum7days.png und hier eine dynamische Version über die mySQL Datenbank mit amcharts/stockcharts zur Darstellung. Hier kannst zommen usw usf. Das hat schon Vorteile und geht mit einer rrd datenbank (meines Wissens nach) nicht so einfach. rrd grafiken aus php heraus zu erstellen war mir irgendwie zu mühsam. Ich mag net jedes Mal herum programmieren, wenn ich eine neue Auswertung möchte oder DEtails sehen will. Da ist die mySQL Version und eben sowas wie amcharts klar von Vorteil! http://www.trickytrails.com/Wordpress/webcam/stockcharts/monthcharts_hourly.php?jahr=2014&monat=12 Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
coly Geschrieben 23. Januar 2015 Teilen Geschrieben 23. Januar 2015 bei deiner stündlichen Abfrage pro Tag müsste ein Fehler kommen, weil du datetime nicht aggregierst. Du sagst einerseits aggregier mir über eine Stunde, andererseits willst du aber jede Minute ausgeben, kann nicht gehen! Für die zweite Abfrage gilt das selbe, außerdem fehlt am Ende eine Klammer. Ich gehe mal davon aus, das du die Abfrage noch nicht getestet hast, der Ansatz wär schon richtig. Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
GrazerTourer Geschrieben 25. Januar 2015 Autor Teilen Geschrieben 25. Januar 2015 bei deiner stündlichen Abfrage pro Tag müsste ein Fehler kommen, weil du datetime nicht aggregierst. Du sagst einerseits aggregier mir über eine Stunde, andererseits willst du aber jede Minute ausgeben, kann nicht gehen! Für die zweite Abfrage gilt das selbe, außerdem fehlt am Ende eine Klammer. Ich gehe mal davon aus, das du die Abfrage noch nicht getestet hast, der Ansatz wär schon richtig. Ja, da waren copy/paste Fehler drin. Meine Monatliche Abfrage auf Stunden aggregiert schaut nun so aus. SELECT YEAR(datetime) AS dy, MONTH(datetime) -1 AS dm, DAY(datetime) AS dd, HOUR(datetime) AS th, avg(temp) as temp, avg(hum) as hum, avg(windspeed) as windspeed, max(windspeed) as windgusts, avg(pressure) as pressure, sum(rainfall) as rainfall, max(temp) as maxtemp, min(temp) as mintemp FROM wettertabelle WHERE Month(datetime)='$monat' and year(datetime)='$jahr' GROUP BY DATE(datetime), HOUR( TIME(datetime) ) ORDER BY datetime Für die Tagesabfrage musste ich tricksen. Hier habe ich minutengenaue Daten zur Verfügung und wollte aber 5min aggregierte. Das ist nicht ganz so einfach wie gehofft, aber ich hab'S geschafft SELECT YEAR(datetime) AS dy, MONTH(datetime) -1 AS dm, DAY(datetime) AS dd, HOUR(datetime) AS th, MINUTE(datetime) AS tm, avg(temp) as temp, avg(hum) as hum, avg(windspeed) as windspeed, max(windspeed) as windgusts, avg(pressure) as pressure, sum(`rainfall`) as rainfall FROM wettertabelle WHERE DATE(datetime) = '$datum' GROUP BY ( 12 * HOUR( `datetime`) + FLOOR( MINUTE( `datetime`) / 5 )) ORDER BY datetime Leider ist die Abfrage mit Floor recht langsam, weil eben so viele Summen und Durchschnitte berechnet werden. Eventuell hole ich mir von der DB die Minutenwerte und rechne den Rest in php weiter... Da muss ich eben testen was schneller ist. Ich tippe fast auf Zweiteres. Es kommen halt doch einige Datensätze zusammen.... hmm. Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
coly Geschrieben 25. Januar 2015 Teilen Geschrieben 25. Januar 2015 brauchst du die Monate von 0-11, wegen month()-1? FLOOR habe ich noch nie verwendet, aber probier mal von Ansatz her folgendes, müsste schneller sein CASE WHEN MOD(MINUTE( `datetime`),5)>0 THEN 5-MOD(MINUTE( `datetime`),5) ELSE 0 END das musst aber im select und im group by haben, weil mit MINUTE(datetime) kann das nicht gehen, ist quasi ein Aggregationsweiderspruch. PHP kann eigentlich nicht schneller sein, sofern die Abfrage passt. Ich mache immer so viel als möglich in der DB, alles andere hat oft zuviel overhead. Wobei in bestimmten Fällen kann es sein, wenn du z.B. nur eine Teilmenge aus der DB benötigst oder u.U. mit Curser herumfuhrwerken musst, dann kann es schon Sinn machen. Bei dir kommts drauf an wieviel Daten du mit mit PHP weiterverarbeiten musst. Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
GrazerTourer Geschrieben 26. Januar 2015 Autor Teilen Geschrieben 26. Januar 2015 (bearbeitet) Ja, ich brauche die Monate für amcharts so. Jänner is dort 0.... Danke! Probiere ich aus. Ich melde mich, falls ich zu blöd dafür bin bzw. mit einem Ergebnis wie schnell es ist. Bearbeitet 26. Januar 2015 von GrazerTourer Zitieren Link zu diesem Kommentar Auf anderen Seiten teilen Mehr Optionen zum Teilen...
Empfohlene Beiträge
Dein Kommentar
Du kannst jetzt schreiben und Dich später registrieren. Wenn Du ein Konto hast, melde Dich jetzt an, um unter Deinem Benutzernamen zu schreiben.