Unterschiedliche Werte bei Abfrage mit AVG und ohne

[der_Angler]

Nabend,

ich habe mich ein einfaches php-Script zur Datenbankabfrage geschrieben, welches Sensor-Werte mittels Charts ausgibt.
Dabei habe ich 2 x Möglichkeiten geschaffen:
1. alle Werte einzel angezeigt zu bekommen
2. einen stündlichen Durchschnitt mittels AVG errechnen und ausgeben

Optisch sehen die 2 x Charts dann auch genau so aus wie sie sollen, allerdings scheinen die Sensor-Werte nicht zu stimmen.
Während sich bei der Möglichkeit 1 der Chart zwischen 27% und 387% bewegt, bewegt er sich bei Möglichkeit 2 nur zwischen 50% und 262%

Und ich verstehe nicht warum, ich kann mir nur ein Fehler in der SQL Abfrage vorstellen.

Hier erstmal der Chart mit den einzelnen Werten

dann der Chart mit der stündlichen Zusammenfassung

zu guter Letzt hier der PHP Code mit der SQL Abfrage

Möglichkeit 1, alle Werte einzeln

$query = "SELECT * FROM `sensor_daten` WHERE sensor = 'xyz' AND timestamp > DATE_SUB(NOW(), INTERVAL 72 HOUR)";
​

Möglichkeit 2, werte stündlich zusammengefasst
$query = "SELECT AVG(data) AS data, timestamp, id, sensor FROM `sensor_daten` WHERE sensor = 'xyz' AND DATE_SUB(timestamp,INTERVAL 1 HOUR) AND timestamp > DATE_SUB(NOW(), INTERVAL 72 HOUR) GROUP BY HOUR(timestamp)";


Woran kann das liegen? Weiß jemand Rat?

 

[akretschmer]

Möglichkeit 2, werte stündlich zusammengefasst
$query = "SELECT AVG(data) AS data, timestamp, id, sensor FROM `sensor_daten` WHERE sensor = 'xyz' AND DATE_SUB(timestamp,INTERVAL 1 HOUR) AND timestamp > DATE_SUB(NOW(), INTERVAL 72 HOUR) GROUP BY HOUR(timestamp)";

Das ist syntaktisch falsch, auch wenn gegenwätige Versionen von MySQL das nicht erkennen, die nächste Version wird es erkennen (ab 5.7.irgendwass). Daher liefert Dir MySQL jetzt zwar ein Resultat, aber halt leider ein falsches.

Wenn Du aggregierst (avg() ist eine Aggregatsfunktion), müssen alle Spalten im Resultat entweder aggregiert oder gruppiert sein.

Wie sowas wie das, was Du willst, elegant geht, ist hier beschrieben: http://de.slideshare.net/hansjurgenschonig/postgresql-data-analysis-and-analytics

 

[der_Angler]

Danke für die Antwort.

Aber mit der Seite die du mir genannt hast bin ich leider erstmal überfordert.
Das wenige an SQL was ich kann hilft mir hier nicht weiter, außer das ich weiß das ich irgendwas falsch gemacht habe.

Ist meine SQL-Abfrage schwierig umzustellen?
Falls nicht, wäre bitte jemand so nett mir hier weiterzuhelfen?

Dafür wäre ich echt dankbar.

 

[akretschmer]

Das wenige an SQL was ich kann hilft mir hier nicht weiter,

Das wenige, was Du an Informationen geliefert hast, hielft auch uns nicht groß weiter.

Zeig Deine Tabellen, Demo-Datensätze, und was rauskommen soll.

 

[akretschmer]

Das wenige, was Du an Informationen geliefert hast, hielft auch uns nicht groß weiter.

Zeig Deine Tabellen, Demo-Datensätze, und was rauskommen soll.

Hier hast Du mal ein Minimal-Beispiel:

test=# create table messung as select '2015-03-01 00:00:00'::timestamp + s * '10 minutes'::interval as ts, random() as val from generate_series(0,25) s;
SELECT 26   
Time: 1,434 ms   
test=*# select * from messung ;
  ts  |  val   
---------------------+-------------------
 2015-03-01 00:00:00 |  0.39042558753863
 2015-03-01 00:10:00 | 0.150113984476775
 2015-03-01 00:20:00 |  0.61915073543787
 2015-03-01 00:30:00 | 0.149333409965038
 2015-03-01 00:40:00 | 0.836897326167673
 2015-03-01 00:50:00 | 0.703680934850127
 2015-03-01 01:00:00 | 0.437533780466765
 2015-03-01 01:10:00 |  0.46383531531319
 2015-03-01 01:20:00 |  0.63543460611254
 2015-03-01 01:30:00 | 0.809679989237338
 2015-03-01 01:40:00 | 0.873460240196437
 2015-03-01 01:50:00 | 0.724126518703997
 2015-03-01 02:00:00 |  0.16330090817064
 2015-03-01 02:10:00 | 0.093328501097858
 2015-03-01 02:20:00 | 0.118078260682523
 2015-03-01 02:30:00 | 0.801268832758069
 2015-03-01 02:40:00 | 0.636486495379359
 2015-03-01 02:50:00 | 0.217674095183611
 2015-03-01 03:00:00 | 0.802376202773303
 2015-03-01 03:10:00 | 0.639594167936593
 2015-03-01 03:20:00 |  0.7220583059825
 2015-03-01 03:30:00 | 0.690978715661913
 2015-03-01 03:40:00 | 0.456716768909246
 2015-03-01 03:50:00 | 0.900138773955405
 2015-03-01 04:00:00 | 0.628542384598404
 2015-03-01 04:10:00 | 0.210679352749139
(26 rows)

Time: 0,292 ms
test=*# select extract(hour from ts), avg(val) from messung group by extract(hour from ts);
 date_part |  avg
-----------+-------------------
  0 | 0.474933663072685
  1 | 0.657345075005045
  2 |  0.33835618221201
  3 | 0.701977155869827
  4 | 0.419610868673772
(5 rows)

Time: 1,776 ms
test=*# rollback;
ROLLBACK
Time: 0,678 ms

 

[der_Angler]

okay, das scheine ich halbwegs zu verstehen.

Habe das jetzt so angepasst:
SELECT EXTRACT(hour from timestamp) as ts, avg(data) as value, id, sensor FROM `".$tbname."` GROUP BY EXTRACT(hour from timestamp) ORDER BY id

wenn ich das jetzt richtig sehe, dann bekomme ich damit den Durchschnitt pro Stunde aller Tage zusammengefasst, sprich Montag, Dienstag, etc... 10-11 Uhr ergibt einen Wert.
Ich bräuchte es aber für jeden Tag einzel. Ich vermute das ich hierfür mit dem "GROUP BY EXTRACT(hour from timestamp)" rumspielen muss.
Das werde ich jetzt mal versuchen.

Vielen Dank schon einmal.

 

[akretschmer]

Ich bräuchte es aber für jeden Tag einzel. Ich vermute das ich hierfür mit dem "GROUP BY EXTRACT(hour from timestamp)" rumspielen muss.

Rrrrrrichtig!

test=*# select now(), now()::date;
  now  |  now
-------------------------------+------------
 2015-03-22 09:32:45.195924+01 | 2015-03-22
(1 row)

 

[der_Angler]

jetzt habe ich es:
SELECT timestamp as ts, avg(data) as value, id, sensor FROM `".$tbname."` WHERE sensor = '".$sensor."' AND timestamp > DATE_SUB(NOW(), INTERVAL ".$period." HOUR) GROUP BY EXTRACT(DAY_HOUR from timestamp) ORDER BY id ASC

DANKE

 

[akretschmer]

jetzt habe ich es:
SELECT timestamp as ts, avg(data) as value, id, sensor FROM `".$tbname."` WHERE sensor = '".$sensor."' AND timestamp > DATE_SUB(NOW(), INTERVAL ".$period." HOUR) GROUP BY EXTRACT(DAY_HOUR from timestamp) ORDER BY id ASC

DANKE

Siehe meine Antwort in #1. Auch das ist syntaktisch (und logisch) falsch. Alle DB-Systeme würden Dir da SOFORT einen Fehler bringen, und auch MySQL in der nächsten Version. Bis dahin viel Spaß mit vermutlich falschen Resultaten.

 

[der_Angler]

ergebnisse stimmen jetzt aber soweit ich das beurteilen kann.
Woran störst du dich den jetzt, an "... AND timestamp > DATE_SUB(NOW(), INTERVAL 48 HOUR) ..." ?

Wie kann ich den Zeitraum den richtig begrenzen? Muss das irgendwo in das "GROUP BY"?

 

[akretschmer]

Woran störst du dich den jetzt, an "... AND timestamp

Nein. Daran:

SELECT timestamp as ts, avg(data) as value, id, sensor FROM ... GROUP BY EXTRACT(DAY_HOUR from timestamp)

Soll hier jetzt über alle Sensoren das avg() gelten? Das ist nicht eindeutig.

Ich zeig es noch einmal an einem kleinen Beispiel:

test=*# select * from produktion ;
 jahr | land  | menge
------+-------+-------
 2001 | Land1 |  10
 2001 | Land2 |  11
 2001 | Land3 |  12
 2002 | Land1 |  20
 2002 | Land2 |  22
 2002 | Land3 |  24
(6 rows)

test=*# select jahr, avg(menge) from produktion group by jahr;
 jahr |  avg
------+---------------------
 2002 | 22.0000000000000000
 2001 | 11.0000000000000000
(2 rows)

test=*# select land, avg(menge) from produktion group by land;
 land  |  avg
-------+---------------------
 Land2 | 16.5000000000000000
 Land1 | 15.0000000000000000
 Land3 | 18.0000000000000000
(3 rows)

Bis hier ist das klar, Du versuchst nun dies: Du fragst nach Jahr und Land und dem avg(Menge), gruppierst aber nur nach Jahr. Welches Land soll er nun anzeigen? Das ist nicht eindeutig, daher bekommst Du einen Fehler:

test=*# select jahr, land, avg(menge) from produktion group by jahr;
ERROR:  column "produktion.land" must appear in the GROUP BY clause or be used in an aggregate function at character 14
STATEMENT:  select jahr, land, avg(menge) from produktion group by jahr;
ERROR:  column "produktion.land" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select jahr, land, avg(menge) from produktion group by jahr;

MySQL ist auf dem Auge blind und erkennt diesen Bullshit nicht. Hier greift dann das SISO-Prinzip: Shit In, Shit Out.

Derzeit liefert Dir MySQL eine Warnung, ab MySQL 5.7.x ist es gefixt. Nachdem der Rest der Welt seit 20 Jahren oder so drüber gelacht hat.

 

[der_Angler]

Soll hier jetzt über alle Sensoren das avg() gelten? Das ist nicht eindeutig.

ich dachte das würde ich mit der WHERE-Klausel lösen: "WHERE sensor = 'xyz'" ... falsch gedacht.

Gehe ich recht in der Annahme das ich das Problem dann so lösen könnte:

SELECT timestamp as ts, avg(data) as value, sensor FROM `sensor_data` WHERE sensor = 'xyz' AND timestamp > DATE_SUB(NOW(), INTERVAL 48 HOUR) GROUP BY EXTRACT(DAY_HOUR from timestamp), sensor ORDER BY id ASC

 

[akretschmer]

ich dachte das würde ich mit der WHERE-Klausel lösen: "WHERE sensor = 'xyz'" ... falsch gedacht.

Gehe ich recht in der Annahme das ich das Problem dann so lösen könnte:

SELECT timestamp as ts, avg(data) as value, sensor FROM `sensor_data` WHERE sensor = 'xyz' AND timestamp > DATE_SUB(NOW(), INTERVAL 48 HOUR) GROUP BY EXTRACT(DAY_HOUR from timestamp), sensor ORDER BY id ASC

Du müßtes im Select nicht den timestamp nehmen, sondern auch dort das, was dann später im GROUP BY ist.

 

[der_Angler]

Okay, aber das einfügen von "GROUP BY sensor" war aber auch richtig oder?
Damit habe ich dann doch zumindest folgendes Problem gelöst:

Soll hier jetzt über alle Sensoren das avg() gelten? Das ist nicht eindeutig.

Richtig?

Du müßtes im Select nicht den timestamp nehmen, sondern auch dort das, was dann später im GROUP BY ist.

Aber wenn ich schreibe "SELECT EXTRACT(DAY_HOUR from timestamp) as ts, avg(data) as value, sensor FROM ...." bekomme ich dann als "ts"-Daten auch ein Datum+Uhrzeit zurück?

Ich kann es leider erst heute abend testen, aber in der ersten Version stand ja noch "SELECT EXTRACT(HOUR from ts) ...." und als Ausgabe habe ich dann anstatt eines Datums+Uhrzeit nur die Stundenzahlen bekommen, sprich 1-24.

Trotzdem schon einmal vielen Dank an dich, ich glaube wir sind auf dem richtigen Weg und ich habe bald eine SQL-Abfrage die "richtig" ist

 

[Distrilec]

Welchen Zeitraum möchtest du denn sehen?
Du kannst ja auch nach Jahr, Monat, Tag gruppieren und dir dann nur die einzelnen Stunden anzeigen lassen ? Oder nach Jahr + Monat und dann die Tage + Uhrzeit anzeigen... ?