Abfrageproblem bei MySQL

[Klaus]

Hallo,

ein System speichert Statistikwerte in eine MySQL-Tabelle, die ich jetzt auswerten möchte.
Die Daten werden im folgenden Format (vereinfacht) gespeichert:

Datum Wert
2014-08-13, 08:00 12
2014-08-13, 09:00 38
2014-08-13, 10:00 64
2014-08-14, 08:00 7
2014-08-14, 09:00 19
2014-08-14, 10:00 41

Ich möchte jetzt die Werte für den Monat 8 summieren, allerdings nur den höchsten Wert des jeweiligen Tages. Also z.B. hier die 64 + 41.

Kann ich das in einer einzigen Abfrage realisieren?

Klaus

[dedlfix]

Tach!

Die Daten werden im folgenden Format (vereinfacht) gespeichert:
Datum Wert
2014-08-13, 08:00 12

Ist das Datum ein String-Feld oder eins für Datümer?

Ich möchte jetzt die Werte für den Monat 8 summieren, allerdings nur den höchsten Wert des jeweiligen Tages. Also z.B. hier die 64 + 41.
Kann ich das in einer einzigen Abfrage realisieren?

Monat aus dem Datum extrahieren (Datums- und Zeit-Funktionen oder String-Operationen, kommt auf deinen Spaltentyp an) und auf 8 beschränken, gruppieren nach Tag und MAX() vom Wert nehmen.

dedlfix.

[Klaus]

Die Daten werden im folgenden Format (vereinfacht) gespeichert:
Datum Wert
2014-08-13, 08:00 12

Ist das Datum ein String-Feld oder eins für Datümer?

Das Datumsfeld ist (leider) Typ String.

Ich möchte jetzt die Werte für den Monat 8 summieren, allerdings nur den höchsten Wert des jeweiligen Tages. Also z.B. hier die 64 + 41.
Kann ich das in einer einzigen Abfrage realisieren?

Monat aus dem Datum extrahieren (Datums- und Zeit-Funktionen oder String-Operationen, kommt auf deinen Spaltentyp an) und auf 8 beschränken, gruppieren nach Tag und MAX() vom Wert nehmen.

ich habe es schonmal hinbekommen, dass ich für jeden Tag den Max-Wert erhalte:

select datum,max(werte) as wert,substring(datum,1,10) as dat from tabelle group by dat

2014-08-13 08:00:00 64 2014-08-13
2014-08-14 08:00:00 41 2014-08-14
2014-09-20 09:00:00 33 2014-09-14
2014-09-21 09:00:00 22 2014-09-14
2015-01-07 10:00:00 42 2015-01-07

Aber wie bekomme ich jetzt noch die Summe für je Monat?

Klaus

[dedlfix]

Tach!

ich habe es schonmal hinbekommen, dass ich für jeden Tag den Max-Wert erhalte:
select datum,max(werte) as wert,substring(datum,1,10) as dat from tabelle group by dat
Aber wie bekomme ich jetzt noch die Summe für je Monat?

Mit dem Zusatz WITH ROLLUP.

dedlfix.

[Klaus]

Mit dem Zusatz WITH ROLLUP.

Wäre es möglich, dass Du mir das beim http://sqlfiddle.com/#!2/6b875/3
zeigst?

Klaus

[dedlfix]

Tach!

Mit dem Zusatz WITH ROLLUP.
Wäre es möglich, dass Du mir das beim http://sqlfiddle.com/#!2/6b875/3 zeigst?

Hmm, also WITH ROLLUP funktioniert zwar prinzipiell, aber - mein Fehler - es rechnet natürlich mit der angegebenen Aggragatfunktion und nicht einfach so mit SUM(). Also, die Summe über die Maximalwerte bekommst du so nicht und ich bezweifle auch, dass das mit einem Statement geht. Das heißt, es könnte schon von hinten durch die Brust ins Auge mit Subquery und einem UNION gehen, aber das will man nur im Notfall machen.

Wenn du die Summe nicht im abfragenden Programm bilden kannst, dann mach es so:

SELECT SUM(wert) FROM (select max(werte) as wert, substring(datum,1,10) as dat from t group by dat) sub

dedlfix.

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

Wenn du die Summe nicht im abfragenden Programm bilden kannst, dann mach es so:

  
SELECT SUM(wert)  
FROM (select max(werte) as wert, substring(datum,1,10) as dat  
    from t group by dat) sub  
  

Welche Bedeutung hat das 'sub' am Ende?

Spirituelle Grüße
Euer Robert

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

Wenn du die Summe nicht im abfragenden Programm bilden kannst, dann mach es so:

SELECT SUM(wert)
FROM (select max(werte) as wert, substring(datum,1,10) as dat
    from t group by dat) sub

>   
> Welche Bedeutung hat das 'sub' am Ende?  
  
Ach, wie doof :-O  
  
Das ist das "as `groupmax`", Ergebnis der jeweiligen Zeile der Unterabfrage, um es in der Hauptabfrage weiterverarbeiten zu können.  
  
Das hatte ich vergessen. Darum hat es bei mir auch nicht geklappt...  
  
  
  
  
Spirituelle Grüße  
Euer Robert

-- 
Möge der Forumsgeist wiederbelebt werden!

[dedlfix]

Tach!

SELECT SUM(wert)

FROM (select max(werte) as wert, substring(datum,1,10) as dat
    from t group by dat) sub

> > Welche Bedeutung hat das 'sub' am Ende?  
  
Lass es weg, dann wirst du es wissen, wenn du die Fehlermeldung liest.  
  

> Das ist das "as `groupmax`", Ergebnis der jeweiligen Zeile der Unterabfrage, um es in der Hauptabfrage weiterverarbeiten zu können.  
  
Genauer gesagt, das ist ein Alias für die Subquery, damit deren Inhalte mit einem Tabellennamenersatz angesprochen werden können. In der vorliegenden Abfrage wird sub nicht weiter verwendet, weil "werte" bereits eindeutig ist, aber es könnte auch als sub.werte geschrieben werden. Jedenfalls ist das sub syntaktisch notwendig, sonst geht es nicht.  
  
  
dedlfix.

[Klaus]

Mit dem Query

  
SELECT sum( wert ) AS erg  
FROM (  
SELECT max( werte ) AS wert, substring( datum, 1, 10 ) AS dat  
FROM t  
WHERE datum LIKE '2014-08-%'  
GROUP BY dat  
) AS sub

komm ich schonmal weiter. Dann lauf ich per Schleife durch die Monate durch und bilde den Where-Teil neu.

Vielen Dank für die Hilfe.

Klaus

[Mattes]

SELECT sum( wert ) AS erg
FROM (
SELECT max( werte ) AS wert, substring( datum, 1, 10 ) AS dat
FROM t
WHERE datum LIKE '2014-08-%'
GROUP BY dat
) AS sub

>   
> komm ich schonmal weiter. Dann lauf ich per Schleife durch die Monate durch und bilde den Where-Teil neu.  
  
Ich habe möglicherweise etwas falsch verstanden, denn ich sehe das Problem nicht so ganz. Die Summen der Tagesmaxima für den August hast du ja schon selbst rausgefunden, geht es um alle Monate, ist das auch nicht weiter schlimm:  
  
select substring(tag,1,7) as monat,sum(wert) from (select substring(datum,1,10) as tag,max(werte) as wert from t group by tag) as tageswerte group by monat  
  
|   MONAT | SUM(WERT) |  
|---------|-----------|  
| 2014-08 |       105 |  
| 2014-09 |        33 |  
| 2015-01 |        42 |  
  
Wolltest du das nicht haben?  

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

Ich habe möglicherweise etwas falsch verstanden, denn ich sehe das Problem nicht so ganz. Die Summen der Tagesmaxima für den August hast du ja schon selbst rausgefunden, geht es um alle Monate, ist das auch nicht weiter schlimm:

  
select substring(tag,1,7) as monat,sum(wert)  
from (select substring(datum,1,10) as tag,max(werte) as wert  
    from t group by tag) as tageswerte group by monat;  
  

MONAT	SUM(WERT)
2014-08	105
2014-09	33
2015-01	42

Dein Beitrag war auch für mich äußerst hilfreich. Mit Dedlfix Aussage zum Temp-Table-Bezeichner der Unterabfrage (hier hieß er 'sub') und deinem Beitrag konnte ich mir dann endlich auch zusammenreimen, was ich falsch gemacht hatte.

Ich poste daher meine Lösung (sie ist eigentlich nur Deine, übersetzt auf meine Tabellenstruktur...) nochmal. Sie benutzt im Unterschied zu Klaus' Lösung eine Datetime-Spalte.

  
  
--Tabelle:  
CREATE TABLE IF NOT EXISTS `inno_gruppieren` (  
	`id` INT(11) NOT NULL AUTO_INCREMENT,  
	`datum` DATETIME NOT NULL,  
	`wert` INT(11) NOT NULL,  
	PRIMARY KEY (`id`)  
)  
COMMENT='Übungen zum Filtern, Gruppieren, Summieren etc'  
COLLATE='utf8_general_ci'  
ENGINE=InnoDB  
CHECKSUM=1  
AUTO_INCREMENT=1;  
  
--Query:  
select concat(year(tageswerte.datum),'-',month(tageswerte.datum)) as monat,  
    sum(maxwert) as  monatssumme  
from (select datum, date(t1.datum) as tag, max(wert) as maxwert  
	from inno_gruppieren t1 group by tag) as tageswerte group by monat;  

Musterdaten:

  
id;datum;wert  
1;2014-01-09 10:51:24;10  
2;2014-01-09 10:52:23;15  
3;2014-01-09 10:54:59;9  
4;2014-02-09 11:00:54;0  
5;2014-02-01 11:01:11;1  
6;2014-02-01 10:30:00;5  
7;2014-02-02 11:03:14;33  
8;2014-02-07 11:03:47;20  
9;2014-02-07 11:04:08;12  
10;2014-02-07 11:04:32;17  
11;2014-01-07 11:04:54;2  
12;2014-02-07 11:05:16;45  
13;2014-02-08 11:05:34;3  
14;2014-04-17 11:05:54;22  
15;2014-04-17 11:06:19;80  
16;2014-01-17 11:06:42;1  
17;2014-04-09 11:07:02;13  
18;2014-05-01 11:07:31;22  
19;2014-05-22 21:07:50;44  
20;2014-05-01 11:08:14;7  
21;2014-05-22 11:08:35;13  
22;2014-05-01 11:08:59;88  
  

Ergebnis:

  
"monat";"monatssumme"  
"2014-1";"18"  
"2014-2";"86"  
"2014-4";"93"  
"2014-5";"132"  

Was hier besser deutlich wird ist, dass man im äußeren select den Temporärbezeichner der Unterabfrage benutzen muss, wenn man keine Fehlermeldung kassieren will:

  
/* SQL Fehler (1054): Unknown column 'datum' in 'field list' */  
  

Spirituelle Grüße
Euer Robert

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

ja!

Und noch eins drauf für die nächste Stufe...

Ich poste daher meine Lösung (sie ist eigentlich nur Deine, übersetzt auf meine Tabellenstruktur...) nochmal. Sie benutzt im Unterschied zu Klaus' Lösung eine Datetime-Spalte.

--Tabelle:
CREATE TABLE IF NOT EXISTS inno_gruppieren (
id INT(11) NOT NULL AUTO_INCREMENT,
datum DATETIME NOT NULL,
wert INT(11) NOT NULL,
PRIMARY KEY (id)
)
COMMENT='Übungen zum Filtern, Gruppieren, Summieren etc'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
CHECKSUM=1
AUTO_INCREMENT=1;

--Query:
select concat(year(tageswerte.datum),'-',month(tageswerte.datum)) as monat,
    sum(maxwert) as  monatssumme
from (select datum, date(t1.datum) as tag, max(wert) as maxwert
from inno_gruppieren t1 group by tag) as tageswerte group by monat;

>   
> Musterdaten:  
>   
> ~~~text
  

> id;datum;wert  
> 1;2014-01-09 10:51:24;10  
> 2;2014-01-09 10:52:23;15  
> 3;2014-01-09 10:54:59;9  
> 4;2014-02-09 11:00:54;0  
> 5;2014-02-01 11:01:11;1  
> 6;2014-02-01 10:30:00;5  
> 7;2014-02-02 11:03:14;33  
> 8;2014-02-07 11:03:47;20  
> 9;2014-02-07 11:04:08;12  
> 10;2014-02-07 11:04:32;17  
> 11;2014-01-07 11:04:54;2  
> 12;2014-02-07 11:05:16;45  
> 13;2014-02-08 11:05:34;3  
> 14;2014-04-17 11:05:54;22  
> 15;2014-04-17 11:06:19;80  
> 16;2014-01-17 11:06:42;1  
> 17;2014-04-09 11:07:02;13  
> 18;2014-05-01 11:07:31;22  
> 19;2014-05-22 21:07:50;44  
> 20;2014-05-01 11:08:14;7  
> 21;2014-05-22 11:08:35;13  
> 22;2014-05-01 11:08:59;88  
>   
> 

Ergebnis:

"monat";"monatssumme"
"2014-1";"18"
"2014-2";"86"
"2014-4";"93"
"2014-5";"132"

>   
>   
> Was hier besser deutlich wird ist, dass man im äußeren select den Temporärbezeichner der Unterabfrage benutzen muss, wenn man keine Fehlermeldung kassieren will:  
>   
> ~~~sql
  

> /* SQL Fehler (1054): Unknown column 'datum' in 'field list' */  
>   
> 

  
select sum(liste.monatssumme) as totalamount  
from (select concat(year(tageswerte.datum),'-',month(tageswerte.datum)) as monat,  
    sum(maxwert) as monatssumme  
    from (select datum, date(t1.datum) as tag, max(wert) as maxwert  
	from inno_gruppieren t1 group by tag) as tageswerte group by monat) as liste;  
  

So langsam macht das spaß ;-)

Wie weit kann man das Spiel wohl treiben?
Irgendwann wird wohl der DB-Server stehenbleiben?

Spirituelle Grüße

Euer Robert

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

ja!

ein System speichert Statistikwerte in eine MySQL-Tabelle, die ich jetzt auswerten möchte.
Die Daten werden im folgenden Format (vereinfacht) gespeichert:

Datum Wert
2014-08-13, 08:00 12
2014-08-13, 09:00 38
2014-08-13, 10:00 64
2014-08-14, 08:00 7
2014-08-14, 09:00 19
2014-08-14, 10:00 41

Ich möchte jetzt die Werte für den Monat 8 summieren, allerdings nur den höchsten Wert des jeweiligen Tages. Also z.B. hier die 64 + 41.

Kann ich das in einer einzigen Abfrage realisieren?

Das ist eine durchaus spannende Frage!

Ich befürchte, dass das nicht geht.
Das Summieren über den Gruppenwechsel hinweg ist IMHO nicht möglich.
Ich habe eben noch mit Benutzervariablen herumexperimentiert, aber das hat leider auch nicht geklappt, und das Summieren der Ergebnisse aus einem Subselect leider auch nicht.

Du könntest mit einer temporären Tabelle arbeiten, in der Du zunächst die Maxima des Tages sammelst. Diese kannst Du dann in der temporären Tabelle mit sum() addieren.

Damit Du das nach außen durch einen Aufruf erledigen kannst, kannst Du Dir eine stored Procedure dafür schreiben. Voraussetzung dafür sind die passenden Rechte.

Ich werde auf jeden Fall mal am Thread kleben bleiben, denn das interessiert mich auch :-)

Spirituelle Grüße
Euer Robert