Zum Inhalt springen

mySQL Profis bitte um Hilfe zu Abfragen :)


 Teilen

Empfohlene Beiträge

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 von GrazerTourer
Link zu diesem Kommentar
Auf anderen Seiten teilen

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

Link zu diesem Kommentar
Auf anderen Seiten teilen

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.
Link zu diesem Kommentar
Auf anderen Seiten teilen

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.

Link zu diesem Kommentar
Auf anderen Seiten teilen

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.

Link zu diesem Kommentar
Auf anderen Seiten teilen

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.

Gast
Auf dieses Thema antworten...

×   Du hast formatierten Text eingefügt.   Formatierung jetzt entfernen

  Nur 75 Emojis sind erlaubt.

×   Dein Link wurde automatisch eingebettet.   Einbetten rückgängig machen und als Link darstellen

×   Dein vorheriger Inhalt wurde wiederhergestellt.   Editor leeren

×   Du kannst Bilder nicht direkt einfügen. Lade Bilder hoch oder lade sie von einer URL.

 Teilen

×
×
  • Neu erstellen...