Abfrage zur Generierung von Tageswerten aus verschiedenen Zeilen

[mysqlview]

Hallo zusammen,

ich möchte gerne aus einer Tabelle gezielt verschiedene Tageswerte generieren und in einer neuen Tabelle speichern. Perspektivisch möchte ich dies über eine Stored-Procedure machen um das ganze zu routinineren.

Leider komme ich bei der Abfrage nicht so ganz zurecht. Folgende Struktur hat die Tabelle:

id | Wert | Zeitstempel | WertID
1 | 10.0 | 2000-1-1 00:00:00 | 1
2 | 12.3 | 2000-1-1 00:01:00 | 2
3 | 17.4 | 2000-1-1 00:02:00 | 3
4 | 10.9 | 2000-1-1 01:00:00 | 1
5 | 15.4 | 2000-1-1 01:01:00 | 2
6 | 20.9 | 2000-1-1 01:02:00 | 3
7 | 12.3 | 2000-1-2 02:00:00 | 1
8 | 17.6 | 2000-1-2 02:01:00 | 2
9 | 22.1 | 2000-1-2 02:02:00 | 3
10 | 14.2 | 2000-1-2 03:00:00 | 1
11 | 18.6 | 2000-1-2 03:01:00 | 2
12 | 24.1 | 2000-1-2 03:02:00 | 3


Für jede "WertID" werden unterrschiedlich viele Werte zu verschiedenen Zeitpunkten am Tag geloggt. Über folgende Abfrage würde ich für eine bestimmte WerteID die Tageswerte bekommen:

SELECT DATE(Zeitstempel), max(Wert) - min(Wert) from Datenbank.tabelle where WertID = 1 group by DATE(Zeitstempel);

Zeittempel | Tagessumme WertID1
2000-1-1 | 0.9
2000-1-2 | 1.9


Folgendes hätte ich aber gerne:

Zeittempel | Tagessumme WertID1 | Tagessumme WertID2 | Tagessumme WertID3
2000-1-1 | 0.9 | 3.1 | 3.5
2000-1-2 | 1.9 | 1.0 | 2.0

Mein Problem ist also, wie ich es hinbekomme die Spalten für die anderen Werte "nebeneinander" zu bekommen. Hat dabei jemand eine gute Idee?

Besten Dank und viele Grüße

 

[BerndB]

hier die Lösung

SELECT DATE(Zeitstempel) Zeitstempel,
SUM( IF(WertID = 1, Wert,0) ) WertID_1,
SUM( IF(WertID = 2, Wert,0) ) WertID_2,
SUM( IF(WertID = 3, Wert,0) ) WertID_3

FROM (
  SELECT Zeitstempel,WertID, max(Wert) - min(Wert) AS Wert
  FROM DATENBANK.tabelle
  GROUP BY DATE(Zeitstempel), WertID ) AS tabelle
GROUP BY DATE(Zeitstempel);

DATE(Zeitstempel)   WertID_1                       WertID_2                    WertID_3
2001-01-01                 0.8999996185302734   3.09999942779541   3.5
2001-01-02                21                     3.09999942779541   3.5

Die Werte sind etwas anders, da meine Tabelle anders ist.

Gruss

Bernd

 

[akretschmer]

"Für jede "WertID" werden unterrschiedlich viele Werte zu verschiedenen Zeitpunkten am Tag geloggt." und "Mein Problem ist also, wie ich es hinbekomme die Spalten für die anderen Werte "nebeneinander" zu bekommen." ist Dein Problem. Du kannst nicht (trivial) unterschiedlich viele Zeilen zu Spalten machen.

Was soll das werden?

 

[BerndB]

"Für jede "WertID" werden unterrschiedlich viele Werte zu verschiedenen Zeitpunkten am Tag geloggt." und "Mein Problem ist also, wie ich es hinbekomme die Spalten für die anderen Werte "nebeneinander" zu bekommen." ist Dein Problem. Du kannst nicht (trivial) unterschiedlich viele Zeilen zu Spalten machen.

Was soll das werden?

Ich versteh gar nicht was du meinst !!! Da steht nicht unterschiedlich viele WertId´s, sondern nur das es unterschiedlich viele Werte je ID gibt, was nun gar keinen Einfluss hat.

Und falls es doch unterschildlich viele ID (WertID) gibt stellt das ja wohl kein grosses Problem dar, oder ?

Einfach Werte von "SELECT DISTINCT WertID FROM ..." durchiterieren und "SUM( IF(WertID = $v, Wert,0) ) WertID_$v," ins Query einbauen, fertig.

Und wenn dir das nicht schön genug ist kann man das auch einfach in eine SP packen, und gut ists.

Gruss

Bernd

 

[akretschmer]

Ähm, wohl a bissl falsch gelesen. Wenn das IMMER 3 WertID sind, dann geht das so wie von @BerndB gezeigt. Aber dennoch, solche Abfragen sind oft unsinnig bzw. die falsche Lösung. Sie funktioniert nicht, wenn da weitere Werte dazukommen.

 

[akretschmer]

das hat sich gekreuzt ;-)

 

[BerndB]

sorry,
Du hast natürlich recht. Das scaliert null.

Gruss

Bernd

 

[mysqlview]

Hallo BerndB, Hallo akretschmer, vielen Dank für eure Antworten. Die von BerndB vorgeschlagene Lösung im ersten Post ist genau das was ich brauche. Der Hintergrund ist folgender: Es werden unterschiedlich oft Werte (WertIDx) geloggt. Für eine Übersicht möchte ich gerne eine Übersichtstabelle haben, wo in einer Zeile der Tag und die jeweiligen Tageswerte stehen. Falls das DB-technisch unsauber ist bin ich natürlich für andere Vorschläge offen. Ich würde das ganze aber einfach in eine SP packen, die jeden Tag den Vortageswert berechnet.

Eine Frage hätte ich noch zum Aufbau der Abfrage: Prinzipiell kann ich halbwegs nachvollziehen was passiert. Aber im ersten Schritt ist mir das "Selektieren" der Summe nicht ganz klar. Du sagst ja, dass die Summe gebildet werden soll, wenn die WertID eine bestimmte Zahl ist, ansonsten 0. Hier ist mir der Schritt warum hier die Summe und in der inneren Abfrage die Differenz genommen wird nicht klar. Die Abfrage liefert zwar genau das richtige Ergebnis, aber so Sachen will man ja auch immer ganz gerne verstehen.

Besten Dank und viele Grüße

@edit: Kleine Ergänzung: Das ganze werden wahrscheinlich so um die 20 "WertIDs", die dann aber im Laufe der Messung fix bleiben, wenns denn einmal läuft.

 

[BerndB]

Hallo,

die Erklärung ist relativ einfach. Nehmen wir mal ein einfaches Beispiel mit einer Tabelle mit 2 Spalten id und wert.
Diese hat 3 Rows

SELECT id, wert FROM tabelle; würde das Ergebnis liefern.
1 10
2 20
3 30

Erweitern wir das nun um 3 virtuelle Spalten wert_1, wert_2, wert_3 die nur den Inhalt von wert+1 wiedergeben sollen. Damit würde

SELECT id , wert,
if( id = 1, wert+1, 0),
if( id = 1, wert+1, 0),
if( id = 1, wert+1, 0)
FROM tabelle;
folgendes ergeben
id wert wert_1 wert_2 wert_3
1 10 11 0 0
2 20 0 21 0
3 30 0 0 31

Wenn du nun das ganze gruppierst wird aus den 3 Zeilen eine und MySQL liefert dir dann natürlich nur die Werte der letzten Zeile die gruppiert wurde zurück also die mit id 3 also 0 für wert_1 und wert_2 und 31 für wert_3.
Mit SUM addierst du nun alle Werte von wert_1 (11+0+0) und das Ergebnis stimmt.

Das fertige Query müsste dem nach so aussehen.
SELECT id , wert,
SUM( if( id = 1, wert+1, 0)),
SUM( if( id = 1, wert+1, 0)),
SUM( if( id = 1, wert+1, 0))
FROM tabelle;

Wenn du das gleiche mit Strings machen willst musst du GROUP_CONCAT nehmen
Beispiel:

GROUP_CONCAT( if( id=1 , text_spalte, null))

Im Beispiel habe ich mit dir Spalte zum gruppieren (bei dir Datum) gespart, damit es leichter zu erklären ist.

Ich hoffe ich konnte dir weiterhelfen.

Gruss

Bernd

 

[Distrilec]

MySQL liefert dir dann natürlich nur die Werte der letzten Zeile die gruppiert wurde zurück also die mit id 3

Na ich hoffe doch nicht... Immerhin willst du die Summe aller Zeilen?

 

[akretschmer]

FROM ( SELECT Zeitstempel,WertID, max(Wert) - min(Wert) AS Wert FROM DATENBANK.tabelle GROUP BY DATE(Zeitstempel), WertID ) AS tabelle

Ich denke, das ist nicht korrekt, was MySQL aber nicht (in derzeitigen Versionen) erkennt. Nimm das DATE(Zeitstempel) anstelle von Zeitstempel in die SELECT-Liste. Dann sollte das sogar in MySQL 7.x später gehen...