Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Verschachtelte Abfrage

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von DaPeace, 6 Juli 2016.

  1. DaPeace

    DaPeace Neuer Benutzer

    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
     
  2. akretschmer

    akretschmer Datenbank-Guru

    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?
     
  3. ukulele

    ukulele Datenbank-Guru

    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
     
  4. DaPeace

    DaPeace Neuer Benutzer

    @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!
     
  5. akretschmer

    akretschmer Datenbank-Guru

    Warum mit R? Geht doch auch mit SQL?
     
  6. DaPeace

    DaPeace Neuer Benutzer

    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 ;-)
     
  7. ukulele

    ukulele Datenbank-Guru

    Vielleicht habe ich die falsche Freundin, die macht sowas nicht :)
     
  8. DaPeace

    DaPeace Neuer Benutzer

    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..
     
  9. Dukel

    Dukel Datenbank-Guru

    Mit Excel sollte es mittlerweile schon gehen. Siehe PowerPivot.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden