mysql: Nach Datumswerten gruppieren

[Lukas.]

Hallo Rolf,

erstmal danke für Deine Hilfe und Mühe. Genau an so eine "pfiffige" Lösung hatte ich gedacht (auch, wenn ich sie noch nicht so ganz verstehe, aber daran kann man ja wachsen). Und Du hast auch genau beschrieben, was ich habe und was ich gerne hätte.

Du hast zwei Tabellen mit Timestamps und Werten. Die Timestamps bringst Du irgendwie auf Tage herunter und zählst, wieviele Werte es in den Tabellen pro Tag gibt. Bis dahin bist Du klar gekommen. Deshalb abstrahiere ich das Problem der Datumsgewinnung und nehme an, dass die Tabellen eine einfache und kompatible Datumsspalte hätten, so dass die Gruppierung pro Log-Tabelle so vereinfacht werden kann.

SELECT datum, COUNT(*) AS zeilen FROM logTab1 GROUP BY datum

Ja, genau. Ich habe jetzt auch mal 2 Testtabellen angelegt.

CREATE TABLE IF NOT EXISTS `access` (
`ID` int(3) NOT NULL,
  `last` int(20) NOT NULL DEFAULT '0'
) ENGINE=MyISAM;


INSERT INTO `access` (`ID`, `last`) VALUES
(1, 1474968700),
(2, 1474968700),
(3, 1474882300),
(4, 1474882300),
(5, 1474882300),
(6, 1474882300),
(7, 1474795900),
(8, 1474795900),
(9, 1474795900),
(10, 1474795900),
(11, 1474795900),
(12, 1474709500),
(13, 1474709500),
(14, 1474709500),
(15, 1474709500),
(16, 1474709500),
(17, 1474709500);



CREATE TABLE IF NOT EXISTS `slow` (
`ID` int(6) NOT NULL,
  `Erstelldatum` datetime NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `slow` (`ID`, `Erstelldatum`) VALUES
(1, '2016-09-24 11:54:53'),
(2, '2016-09-24 11:57:01'),
(3, '2016-09-24 11:57:57'),
(4, '2016-09-25 11:57:57'),
(5, '2016-09-25 11:57:01'),
(6, '2016-09-26 11:57:01');

Ich habe mir zwei Testtabellen logTab1 und logTab2 gemacht. Wenn ich den Select drüberlaufen lasse, liefern sie mir

Ja, auch meine liefern gute Einzelwerte:

SELECT FROM_UNIXTIME(last, '%Y%-%m-%d') , count( * ) AS Anzahl
FROM `access`
GROUP BY FROM_UNIXTIME(last, '%Y%-%m-%d')
ORDER BY Anzahl DESC

ergibt:

2016-09-24 	6
2016-09-25 	5
2016-09-26 	4
2016-09-27 	2

Und folgende Abfrage:

SELECT date( `Erstelldatum` ) , count( * )
FROM `slow`
GROUP BY date( `Erstelldatum` )

ergibt:

2016-09-24 	3
2016-09-25 	2
2016-09-26 	1

Lösung ist ein LEFT OUTER JOIN der beiden Gruppierergebnisse. Die FROM Klausel akzeptiert nämlich nicht nur Tabellennamen, sondern auch SELECT-Abfragen (weil die auch nur Tabellen liefern). Ein kleines Problem sind die Tage, wo Du keine langsamen Requests hattest, die fehlen in der logTab2-Auswertung und ein LEFT JOIN erzeugt dann NULL als Wert für diese Spalten. Dafür gibt's die Funktion COALESCE, der übergibst beliebig viele Parameter und zurück kommt der erste, der nicht NULL ist. Den CAST(... as float) habe ich hinzugefügt, weil sonst eine INTEGER-Division stattfindet und deren Ergebnis wäre in deinem Fall immer 0.

An dieser Erklärung knabbere ich gerade...

SELECT x.datum, x.zeilen, y.datum, y.zeilen, CAST(COALESCE(y.zeilen, 0) AS FLOAT)/ x.zeilen AS Quote
FROM (SELECT datum, COUNT(*) AS zeilen FROM logTab1 GROUP BY datum) x LEFT JOIN
     (SELECT datum, COUNT(*) AS zeilen FROM logTab2 GROUP BY datum) y ON x.datum = y.datum

Die Query ist etwas breiter als Du es brauchst, aber so siehst Du auch die Zwischenergebnisse. Das Ergebnis sieht so aus:

datum    zeilen    datum    zeilen    quote
   1        2         1        1        0,5
   2        3         2        2        0,67
   3        4       NULL     NULL       0
   4        2         4        1        0,5
   5        1       NULL     NULL       0

Wäre genau das Ergebnis, welches ich erzeugen will...

Die Anwendung dieses Vorschlages auf das Problem sei dem Leser als Übung überlassen ;-)

Bin schon dabei. Aber bisher nur mit einem MySQL-Error.

SELECT x.datum, x.zeilen, y.datum, y.zeilen, CAST(COALESCE(y.zeilen, 0) AS FLOAT)/ x.zeilen AS Quote
FROM (SELECT FROM_UNIXTIME(last, '%Y%-%m-%d'), COUNT(*) AS zeilen FROM access GROUP BY FROM_UNIXTIME(last, '%Y%-%m-%d')) x LEFT JOIN
     (SELECT date( `Erstelldatum` ), COUNT(*) AS zeilen FROM slow GROUP BY date( `Erstelldatum` )) y ON x.FROM_UNIXTIME(last, '%Y%-%m-%d') = y.date( `Erstelldatum` )

ergibt leider bei mir nicht obige Tabelle, sondern:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FLOAT)/ x.zeilen AS Quote
FROM (SELECT FROM_UNIXTIME(last, '%Y%-%m-%d'), COUNT(*' at line 1 

Da ich den oben eingesetzten Begriff "CAST(COALESCE(y.zeilen, 0) AS FLOAT)" nicht wirklich verstehe, kann ich auch die Fehlermeldung nicht verstehen.

Lukas