MySQL - Fülle Datumslücken in einer Abfrage für die Statistik
In einer Tabelle speichern wir Anfragen über ein Formular. Die Anzahl Anfragen wollen wir gruppiert pro Tag für eine Statistik aufbereiten. Leider gibt es aber auch hie und da Tage ohne ausgefüllte Formulare. Das soll in der Statistik natürlich ersichtlich sein.
Die Hilfstabelle
Leider gibt es in MySQL keine Funktion mit der das direkt möglich wäre. Deshalb behelfen wir uns mit einem kleinen Umweg. Dafür benötigen wir eine Tabelle, die mindestens soviele Zeilen hat, wie wir für die Ausgabe benötigen. Dies kann man im Anschluss für unterschiedliche Zwecke einsetzen. Obs dann eine Statisik für Tage pro Monat oder Monat pro Jahr wird spielt keine Rolle. Im konkreten Beispiel wird gleich klar warum das so ist.
CREATE TABLE IF NOT EXISTS `numberhelper` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ;
INSERT INTO `numberhelper` (`id`) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31);SQL Abfrage
Die folgende Abfrage erstellt mit der Hilfstabelle eine Ausgabe von fortlaufenden Daten. Dafür gilt als Grundlage das aktuelle Datum. Der Start der Abfolge ist Heute vor einem Monat und geht bis Heute. Das Resultat dieser SQL Abfrage steht rechts.
SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),
INTERVAL n.id - 1 DAY) AS datum
FROM numberhelper nMit einer anderen Abfrage bekommt man mit der gleichen Tabelle auch eine Ausgabe, welche die letzten 12 Monate auflistet.
+------------+
| datum |
+------------+
| 2015-03-01 |
| 2015-03-02 |
| 2015-03-03 |
| 2015-03-04 |
| 2015-03-05 |
........SELECT DATE_FORMAT(DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 YEAR),
INTERVAL n.id - 1 MONTH), '%Y-%m') AS datum
FROM numberhelper n
HAVING CAST(CONCAT(datum, '-01') AS DATE) <= CURDATE()+---------+
| datum |
+---------+
| 2014-05 |
| 2014-06 |
......Konkrete Daten dazuladen
Nun laden wir zu der fortlaufenden Datumsreihe die gewünschte Information. Die Tabelle object_12 enthält diese Daten. Ausschlaggebend für die Gruppierung ist die Spalte o_creationDate. Die Tabelle holen wir uns per LEFT JOIN hinzu, dies mit der Bedingung, dass das Datum mit dem Datum der aktuellen Zeile übereinstimmen muss. In der Abfrage holen wir uns neben dem generierten Datum aus dem Subselect die Anzahl Einträge aus der Tabelle. Am Ende gruppieren wir die Abfrage nach dem Datum.
SELECT
datetable.datum, COUNT(jointable.oo_id) AS anzahl
FROM (
SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),
INTERVAL n.id - 1 DAY) AS datum
FROM numberhelper n
) AS datetable
LEFT JOIN object_12 AS jointable
ON DATE(FROM_UNIXTIME(jointable.o_creationDate))
= datetable.datum
GROUP BY datetable.datumDer Anwendungsfall in pimcore
Die Abfrage können wir so nun im pimcore in den Individuellen Reports einbauen. Zu finden sind diese unter Einstellungen -> Berichte & Marketing. Dort erstellen wir einen neuen Report und verteilen die vorige Abfrage in die richtigen Formularfelder im SQL Report. Zuunterst kann man dann noch ein Diagramm erstellen. Für diesen Fall ist das Liniendiagramm das richtige. Als X-Achse wählen wir das Datum, als Y-Achse die Anfragen.
Und so sieht die Grafik dann aus. Da die Abfrage mit dem jeweils aktuellen Datum arbeitet, ist Sie immer automatisch auf dem neusten Stand.
