Zeilen zusammenfassen in mySQL

[kaiwe]

HAllo zusammen,

ich sammle in einer Datenbank im Stundentakt Werte, die jeweils ein bestimmtes Objekt betreffen.
Das bedeutet, dass ich nach einem Tag 24 Zeilen mit den Spalten Objekt, Wert, timestamp habe.
Diese 24 Zeilen habe ich dann n-mal, je nach Anzahl der Objekte.

Jetzt möchte ich diese Werte in einer Ergebnisdatenbank bündeln, d.h. ich möchte die 24 Einzelwerte
jeweils in eine Zeile schreiben, die dann die Spalten Objekt, S0001, S0102, S0203 ... S2324 hat.

Nach vielem Suchen im GOOGLE habe ich schon mal folgendes getestet

[src=mysql]

SELECT OBJEKT_ID, ( SELECT number from OBJEKT.actual_number where timestamp LIKE '2016-09-22 01%' ) AS S0001,
...
...
( SELECT number from OBJEKT.actual_number where timestamp LIKE '2016-09-23 00%' ) AS S2324

FROM OBJEKT.actual_number
GROUP BY OBJEKT_ID

[/src]

Damit bekomme ich zwar alle Werte richtig, was die entsprechende Stunde anbelangt, jedoch stehen in allen Zeilen die gleiche OBJEKT_ID.

Zur Info: Ich arbeite mit mySQL und PHP.


Vielen Dank schon mal.

 

[werner]

Wieso schreibst du die Daten in eine extra Tabelle, wenn die Daten nicht beschnitten/bearbeitet werden?

Zum Problem an sich - in den SELECTs steht doch zB OBJEKT_ID = '193r4wnf5w'. Dann gehst du ja nur dieses konkrete Objekt durch und keine anderen. Und was ist 'number'? D sagtest doch, in der Tabelle wäre nur "objekt", "value" und "timestamp". Oder?

 

[BurnerR]

ich sammle in einer Datenbank im Stundentakt Werte, die jeweils ein bestimmtes Objekt betreffen.
Das bedeutet, dass ich nach einem Tag 24 Zeilen mit den Spalten Objekt, Wert, timestamp habe.

Das klingt so, als ob du dir das selber so baust. Mach es doch direkt in der Form, in der du es haben willst.

 

[kaiwe]

Hallo zusammen,

erst mal vielen Dank für die Antworten.

Sorry, der Code war natürlich falsch, das OBJEKT_ID = '193r4wnf5w' darf da natürlich nicht stehen.

Hab ich schon berichtigt.

Es hat seinen Grund, warum ich für jede "Buchung" eine einzelne Zeile verwende, ansonsten wäre
zb. kein sauberer timestamp / status für jede einzelne Buchung möglich.

Ohne dem OBJEKT_ID = '193r4wnf5w' bekomme ich die Meldung, dass die SUBQUERY mehrere
Werte liefert, was auch logisch ist, nachdem es jedesmal so viele Ergebnisse wie OBJEKT_ID's
gibt.

Ich will die Abfrage dahingehend optimieren, dass sozusagen schrittweise alle OBJEKT_ID's
durchgegangen werden und dabei jeweils alle 24 Einzelwerte ( number ) als Spalte ausgegeben
werden.

So sollte das Ergebnis ( Beispiel ) aussehen:

[src=text]

OBJEKT_ID Datum S0001 S0102 S0203 ... S2223 S2300
--------------------------------------------------------------------------------------------------------------------
193r4wnf5w 2016-09-25 12 17 4 7 19

[/src]



Ich bin mir nicht sicher, ob eine solche Abfrage überhaupt möglich ist, oder ob man
es eben doch in einer Schleife laufen lassen ( und n einzelne Abfragen machen muss.



Schon mal vielen Dank für die Hilfe !

 

[Shodan]

Ich bin mir nicht sicher, ob eine solche Abfrage überhaupt möglich ist, oder ob man
es eben doch in einer Schleife laufen lassen ( und n einzelne Abfragen machen muss.

Ja ist möglich. Du hast ja schon
[src=mysql]SELECT OBJEKT_ID [...] GROUP BY OBJEKT_ID;[/src]
mit einem GROUP BY verwendet man Aggregate Functions: Da unter einer OBJEKT_ID mehrere Werte in einer Spalte stehen, müssen diese verarbeitet werden, um einen Wert zu bekommen.
In deinem Fall wäre folgendes möglich:
[src=mysql]MAX(CASE WHEN timestamp LIKE '2016-09-22 01%' THEN number ELSE NULL END) AS S0001[/src] (Source: http://stackoverflow.com/questions/3392956/sql-how-to-transpose )

Das GROUP BY fasst alle Zeilen mit der gleichen OBJEKT_ID zu einer Zeile zusammen. Für die Ergebnisspalte S001 werden alle zu dieser OBJEKT_ID gehörenden Timestamps angesehen. Fast alle bleiben an der Bedingung hängen, weil der Timestamp nicht passt. Aus denen, bei denen der Timestamp passt wird das größte ausgewählt und steht dann in S0001. Jeder Wert ist trivialerweise größer als NULL.

Das würde allerdings nur gehen, wenn du absolut sicher bist, dass es pro LIKE nur einen Treffer gibt.
Unter der Annahme, dass sich mehrere Einträge in einer Stunde befinden könnten, und dass "number" ein Integer ist, der für die Zusammenfassung addiert werden soll, wäre es angemessener SUM zu benutzen.
[src=mysql]SUM(CASE WHEN timestamp LIKE '2016-09-22 01%' THEN number ELSE 0 END) AS S0001[/src]

Außerdem würde ich empfehlen die Bedingung aufzutrennen:
[src=mysql]SUM(CASE WHEN DATE(timestamp) = '2016-09-22' AND HOUR(timestamp) = 1 THEN number ELSE 0 END)[/src]
und dann aus der Aggregate Function heraus zu nehmen:
[src=mysql]SELECT
DATE(timestamp) as Datum,
SUM(CASE WHEN HOUR(timestamp) = 1 THEN number ELSE 0 END) AS S0001
WHERE
DATE(timestamp) = '2016-09-22'
GROUP BY
Datum[/src]

Die Idee hier ist zuerst mit WHERE die Zahl der Datensätze zu reduzieren und dann noch nach Datum zu gruppieren, bevor sich SUM die einzelnen timestamps ansieht.

Insgesamt sieht das dann so aus:
[src=mysql]SELECT
OBJEKT_ID,
DATE(timestamp) AS Datum,
SUM(CASE WHEN HOUR(timestamp) = 0 THEN number ELSE 0 END) AS S00,
SUM(CASE WHEN HOUR(timestamp) = 1 THEN number ELSE 0 END) AS S01,
SUM(CASE WHEN HOUR(timestamp) = 2 THEN number ELSE 0 END) AS S02,
SUM(CASE WHEN HOUR(timestamp) = 3 THEN number ELSE 0 END) AS S03
[...]
FROM
OBJEKT.actual_number
WHERE
DATE(timestamp) = '2016-09-22'
GROUP BY
OBJEKT_ID, Datum;[/src]

Implizit habe ich dabei mal das Problem mit der Datumsgrenze raus genommen, aber ich befürchte deine erste Tabelle ist schon das Ergebnis einer Aggregation und du willst den Wert mit dem 00:x:y timestamp gerne noch in der Zusammenfassung von gestern stehen haben, auch wenn er mit einem timestamp von heute eingefügt wurde

BTW es gibt ein schönes Tool namens sqlfiddle mit dem du eine Tabelle und ein paar Beispieldatensätze anlegen und teilen kannst.
Das ermöglicht dann anderen erst mal zu prüfen, ob sie nur Stuss labern, bevor sie dir antworten ;-)

 

[BurnerR]

Es hat seinen Grund, warum ich für jede "Buchung" eine einzelne Zeile verwende, ansonsten wäre
zb. kein sauberer timestamp / status für jede einzelne Buchung möglich.

Klingt ein bisschen nach verbesserungswürdigem Design.
Brauchst du die timestamps nur temporär oder schreibst du die parallel woanders noch rein?
Mach doch z.B. direkt eine zweite Tabelle für die timestamps bzw. Buchungen.
Ich habe das starke Gefühl, du machst es dir deutlich schwerer als es sein müsste.

 

[kaiwe]

Hallo,

vielen Dank für eure Hilfe !

Ich probier das heute Abend gleich mal aus.

Ohne Frage könnte man das Design durchaus ändern, dazu müsste ich aber den relativ aufwändigen "Inbound Dispatcher",
der die Daten sammelt auch komplett umbauen.

Zudem muss ich die Daten in der neuen Tabelle später manipulieren und bin daher ganz froh, die "Rohdaten" in einer
separaten Tabelle jederzeit zugreifbar und originär vorliegen zu haben.

Der Vorschlag, die Daten parallel innerhalb des Inbound Dispatchers in zwei Tabellen zu schreiben, finde ich sehr gut.
Das würde einen weiteren CRON Job und damit eine Fehlerquelle hinfällig machen.

Trotz allem bin ich sehr dankbar um die Hilfe von Shodan, nachdem ich ja auf jeden Fall die bestehenden Daten in eine
neue Tabelle überführen muss.

Habe mir gerade das sqlfiddle angesehen, nette Sache !

Allerdings so dermaßen langsam, dass es für og. Anwendung wahrscheinlich eher nicht geeignet ist.

Ich teste das mal, dann kann ich in Zukunft meine Fragen besser mit Fakten hinterlegen.