Verschachtelte Abfrage

D

DaPeace

Neuer Benutzer
Beiträge
4
Hallo,
Ich habe 2 kleine Probleme die ich mit ausführlicher Suche nicht wirklich beheben konnte.

Meine Datenbank hat folgende Struktur

ID, Datum+Zeit, Messwert, Messwert, Messwert etc

Die Daten stammen aus einem Datalogger der Temperaturen misst.
Wenn ein Sensor kaputt geht (was bei langen Messungen passiert) bekomme ich in der Datenbank beim Import eine 0.

Problem Nr 1.
Ich müsste aus den Messwerten einen Durchschnitt errechnen. Allerdings müsste ich Felder mit der 0 auslassen.

Wenn sie komplett gefüllt sind ist es ja recht Trivial. Messwert1 + Messwert2 + Messwert3 / 3 und fertig..
Wie kann ich das aber nun gestalten wenn eben bei einem Wert eine 0 drinsteht?

Problem Nr 2.
Ich habe ja Datum und Zeit in der DB.
Ich müsste für jeden Tag ( über 3 Jahre, Messinterval 15min ) aus den vorher errechneten Mittelwerten den höchsten und den niedrigsten raussuchen.

Geht sowas überhaupt in Mysql?

Wäre echt cool wenn mir jemand dabei helfen könnte.

Besten Dank schonmal,

Grüsse, Jan
 
Werbung:
1. Dafür gibt es allgemein coalesce():

Code: 
test=# select coalesce(1,0) + coalesce(2,0) + coalesce(3,0);
 ?column?
----------
  6
(1 Zeile)

test=*# select coalesce(1,0) + coalesce(2,0) + coalesce(NULL,0);
 ?column?
----------
  3
(1 Zeile)

Vielleicht gibt es das auch in MySQL (was ja bekanntermaßen vieles nicht kann)

2. ich leite das mal her ...

Code: 
test=# create table messung as select s, random()*10 as m1, random()*10 as m2, random() * 10 as m3 from generate_series(1,10) s;
SELECT 10
test=*# select * from messung ;
 s  |  m1  |  m2  |  m3   
----+-------------------+-------------------+--------------------
  1 |  7.24913330283016 |  5.23658810183406 |  3.76901956275105
  2 |  3.44759023748338 |  3.81446267943829 |  0.678804330527782
  3 |  1.19651894085109 |  2.42196834180504 |  6.26386231277138
  4 |  3.04150430485606 |  5.66010001581162 |  9.29584033321589
  5 |  2.99443652387708 | 0.761940060183406 | 0.0573457265272737
  6 |  5.7263362640515 |  2.66665185336024 |  4.87791045568883
  7 |  3.71047312859446 |  2.22746235318482 |  7.04711377620697
  8 | 0.566657967865467 |  5.02775335684419 |  7.29077847674489
  9 |  3.37952513247728 |  6.1721848603338 |  9.03944614343345
 10 |  4.26388561725616 |  2.56518855225295 |  4.6441422495991
(10 Zeilen)

test=*# select * , (coalesce(m1,0)+coalesce(m2,0)+coalesce(m3,0)) / 3 as avg from messung ;
 s  |  m1  |  m2  |  m3  |  avg   
----+-------------------+-------------------+--------------------+------------------
  1 |  7.24913330283016 |  5.23658810183406 |  3.76901956275105 | 5.41824698913842
  2 |  3.44759023748338 |  3.81446267943829 |  0.678804330527782 | 2.64695241581649
  3 |  1.19651894085109 |  2.42196834180504 |  6.26386231277138 | 3.29411653180917
  4 |  3.04150430485606 |  5.66010001581162 |  9.29584033321589 | 5.99914821796119
  5 |  2.99443652387708 | 0.761940060183406 | 0.0573457265272737 | 1.27124077019592
  6 |  5.7263362640515 |  2.66665185336024 |  4.87791045568883 | 4.42363285770019
  7 |  3.71047312859446 |  2.22746235318482 |  7.04711377620697 | 4.32834975266208
  8 | 0.566657967865467 |  5.02775335684419 |  7.29077847674489 | 4.29506326715151
  9 |  3.37952513247728 |  6.1721848603338 |  9.03944614343345 | 6.19705204541485
 10 |  4.26388561725616 |  2.56518855225295 |  4.6441422495991 | 3.82440547303607
(10 Zeilen)

test=*# select min(avg), max(avg) from (select * , (coalesce(m1,0)+coalesce(m2,0)+coalesce(m3,0)) / 3 as avg from messung) foo ;
  min  |  max   
------------------+------------------
 1.27124077019592 | 6.19705204541485
(1 Zeile)

Du erkennst den Weg?
 
1)
Ansonsten könnte man das mit CASE abbilden.
Code: 
SELECT
( wert1 + wert2 + wert3 ) / (
( CASE WHEN wert1 = 0 THEN 0 ELSE 1 END ) + 
( CASE WHEN wert2 = 0 THEN 0 ELSE 1 END ) + 
( CASE WHEN wert3 = 0 THEN 0 ELSE 1 END ) )
FROM tabelle

2)
Das würde ich auf jedenfall schachteln, aber dann müsste es mit min() max() gehen.
Code: 
SELECT t.datumzeit,
min(t.wert) AS minwert,
max(t.wert) AS maxwert
FROM (

SELECT
datumzeit,
( wert1 + wert2 + wert3 ) / (
( CASE WHEN wert1 = 0 THEN 0 ELSE 1 END ) + 
( CASE WHEN wert2 = 0 THEN 0 ELSE 1 END ) + 
( CASE WHEN wert3 = 0 THEN 0 ELSE 1 END ) ) AS wert
FROM tabelle

) t
GROUP BY t.datumzeit
 
@ukulele du bist mein Held! Damit hast du mir sehr weiter geholfen. Ersteres habe jetzt verwendet. Letzteres wird meine Freundin dann mit dem Statistik-Programm R aufarbeiten.
1000 Dank schonmal für die Hilfe!
 
Geht Hauptsächlich drum das die Werte dann Grafisch dargestellt werden sollen und das R gleich mit macht.. Außerdem weniger Arbeit für mich ;-)
 
Ich geh stark davon aus das deine Freundin das wie meine Freundin mit Excel machen würde.. um dann festzustellen das 700000 Records irgendwie nicht so wirklich funktioniert.
Ich hab ihr dann das besser geeignete Werkzeug für diese Datenmenge gezeigt.... Statistik macht dann ein Studienkollege von ihr..
 
Werbung:
Zurück
Oben