MySQL: Lücken bei Gruppierung nach Tagen auffüllen

[Sempervivum]

Ich bin jetzt bei zwei verschiedenen Aufgaben auf folgendes Problem gestoßen: Daten, in meinem Beispiel visits bzw. Besuche, werden mit SUM aufsummiert und nach Tagen gruppiert. Dabei tritt das Problem auf, dass nicht für jeden Tag Daten vorhanden sind, so dass im Ergebnis Lücken entstehen. Diese müssen mit 0 aufgefüllt werden, da es sonst Probleme bei der Darstellung als Chart gibt. Ich habe das bis jetzt durch eine Nachverarbeitung in PHP gelöst:

$sql = "SELECT DATE_FORMAT(visit_date, '%Y-%m-%d') as d, COUNT(1) AS visits
    FROM `visits`
    WHERE visit_date > DATE_SUB(NOW(), INTERVAL 21 DAY)
    GROUP BY d";
$result = $pdo->query($sql);
// wir erzeugen zunaechst ein ass. Array mit dem Datum als Schluessel:
$resultArr = [];
foreach ($result as $row) {
    // Anzahl der Besuche mit Datum als Schluessel in Array eintragen
    $resultArr[$row['d']] = $row['visits'];
}
// Referenzdatum fuer heute vor einer Woche bereitstellen:
// aktuelles Datum ermitteln und 3 Wochen subtrahieren
$refDate = new DateTime();
$refDate->modify('-3 week');
$visits = [];
$days = [];
// ueber 3 Wochen iterieren:
for ($i = 0; $i < 21; $i++) {
    // String für das aktuelle Datum bereit stellen
    $refDateStr = $refDate->format('Y-m-d');
    // ist ein Element im Ergebnis vorhanden?
    if (isset($resultArr[$refDateStr])) {
        // Daten übernehmen
        $visits[$i] = $resultArr[$refDateStr];
    } else {
        // 0 eintragen
        $visits[$i] = 0;
    }
    $days[$i] = $refDateStr;
    $refDate->modify('+1 day');
}
var_dump($resultArr);
var_dump($visits);
var_dump($days);

Meine Frage ist, ob man das besser und einfacher lösen kann, u. U gleich in der Query und die Arbeit der Datenbank überlassen?

Edit: Abfrage verschönert.

 

[Andreas-B]

Hi. Du weißt ja, dass meine Ansätze nicht immer die Einfachtesten sind, aber ich würde das so machen:

DDL:

DROP TABLE IF EXISTS `visits`;
CREATE TABLE `visits` (
    visit_date date
);

INSERT INTO `visits` (visit_date) VALUES ('2021-02-01');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-01');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-02');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-03');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-03');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-03');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-05');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-07');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-07');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-08');
INSERT INTO `visits` (visit_date) VALUES ('2021-02-09');

DROP PROCEDURE IF EXISTS temp_dates;
DELIMITER //
CREATE PROCEDURE temp_dates(
    row_count INT,
    format varchar(10)
)
    BEGIN
        DECLARE i INT DEFAULT 0;
        CREATE TABLE IF NOT EXISTS `temp_dates` ( t_date date );
        SET i=0;
        WHILE i<row_count DO
            INSERT INTO `temp_dates` (t_date) VALUES (
                DATE_ADD(DATE_FORMAT(NOW(), format), INTERVAL -i DAY)
            );
            SET i = i + 1;
        END WHILE;
    End;
//
DELIMITER ;

DQL:

call temp_dates(21,'%Y-%m-%d');

SELECT
    DATE_FORMAT(td, '%Y-%m-%d') as d,
    SUM(CASE
      WHEN vd IS NULL THEN 0
      ELSE 1
    END) AS visits
  FROM
    (
      SELECT t.t_date td, v.visit_date vd
      FROM `temp_dates` t
      LEFT JOIN `visits` v ON v.visit_date = t.t_date
      ORDER BY t.t_date
    ) as st
GROUP BY st.td
ORDER BY st.td;

Im Grunde ähnlich, wie du es in PHP gemacht hast.

 

[Sempervivum]

Coole Lösung, danke! Ich wusste bisher nicht, dass man in SQL auch programmieren kann.
Jetzt wäre mal interessant, wie sich das Ganze von der Geschwindigkeit her verhält.

 

[Zvoni]

Hier auch eine Lösung ohne temp. Tabelle:
Generating a Series of Dates in MySQL
Wobei ich zustimme: Eine Calendar-Table ist das einfachste

 

[Sempervivum]

Danke für diesen Hinweis, Zvoni, ich werde mir das auch ansehen.

Jetzt wäre mal interessant, wie sich das Ganze von der Geschwindigkeit her verhält.

Inzwischen habe ich das bei beiden Varianten mit microtime ausgemessen:

ca. 300 ms für die Lösung mit nur-SQL und temporärer Tabelle
ca. 3,2 ms für die Lösung mit Nachverarbeitung in PHP

Die Tabelle visits hat 2344 Datensätze.

Eine Subquery und ein Join fordern offenbar ihren Tribut.

 

[Andreas-B]

Hast du schonmal versucht, dem Calendar-Table einen index zu verpassen?

außerdem wird in diesem Beispiel:
Using MySQL to generate daily sales reports with filled gaps - Media Division
die Tabelle zuerst erstellt und in der procedure nur gefüllt. Wäre auch eine Idee.

(Dort ist die Abfrage ohne Subquery.)


edit:

Würde dann so aussehen:

SELECT temp_dates.t_date AS date,
    SUM(CASE
      WHEN visits.visit_date IS NULL THEN 0
      ELSE 1
    END) AS visits
FROM visits
RIGHT JOIN temp_dates ON visits.visit_date = temp_dates.t_date
GROUP BY date

 

[Sempervivum]

Ja, an einen Index hatte ich auch gedacht, aber nicht getestet, da die Lösung mit Nachverarbeitung ja ihren Zweck erfüllt.
Das Anlegen der Hilfstabelle trägt offenbar nur wenig zum Zeitbedarf bei, wenn ich sie von der Messung ausschließe, verringert sich die Zeit nur wenig.

 

[Sempervivum]

Den Link aus #6 habe ich mir auch angesehen. Der Verfasser schreibt:

This is a classic problem but I was surprised to see that there’s not a great deal of info on the web on how to do a proper daily report – one that doesn’t have any gaps when data is missing.

Genau so ging es mir auch, als ich auf dieses Problem gestoßen bin.

 

[Andreas-B]

Der Artikel lößt im Prinzip genau dein Problem und wendet dabei eine sehr ähnliche Lösung an, wie ich vorgeschlagen hatte

Falls du es nicht gesehen hast:
Ich habe meinen Beitrag nochmal editiert und den Query aus dem Artikel auf mein erstes Beispiel angepasst (ohne Subquery)

 

[Sempervivum]

Ich habe meinen Beitrag nochmal editiert und den Query aus dem Artikel auf mein erstes Beispiel angepasst (ohne Subquery)

Das sieht viel versprechend und weniger kompliziert aus, werde ich demnächst testen.

Inzwischen habe ich es mit einem Index auf das Datum in der Tabelle visits versucht und bin dabei nach diesem Thread vorgegangen:
How to optimize mysql group by with DATE_FORMAT

Einrichten der virtuellen Spalte mit

ALTER TABLE visits ADD COLUMN datestr varchar(10) AS (DATE_FORMAT(visit_date, '%Y-%m-%d')) VIRTUAL;

hat geklappt aber wenn ich darauf dann einen Index einrichten will mit

CREATE INDEX idx_visit_date on visits(datestr)

laufe ich in diesen Fehler:

#1901 - Function or expression 'date_format()' cannot be used in the GENERATED ALWAYS AS clause of `datestr`

Richte ich die Spalte mit PERSISTENT ein, bekomme ich den gleichen Fehler gleich beim Einrichten der virt. Spalte.

Mache ich da noch etwas verkehrt? Oder wird so etwas von meiner DB nicht unterstützt?
Maria-DB 10.4.8