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.

 

[Sempervivum]

Update:
1. Inzwischen habe ich die Lösung für das Problem mit der virtuellen Spalte und dem Index gefunden, und zwar hier:
[MDEV-11553] Can't restore a PERSISTENT column that uses DATE_FORMAT() - Jira
Benutzt man DATE_FORMAT kann die locale ins Spiel kommen und die Sache inkonsistent machen. Lösung indem man die Locale explizit angibt, z. B.

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

2. Man kann jedoch das Ganze umgehen, indem man durchgängig, auch in der temp. Tabelle, DATETIME benutzt und dann mit DATE() auf das Datum reduziert. Dadurch konnte ich die Verarbeitungszeit um mehr als den Faktor 10 verringern, siehe hier:
MySQL: Optimize left join on formatted date

 

[Sempervivum]

Noch ein Update:
Die Leute bei Stackoverflow haben es echt drauf: Da ist jetzt einer mit dieser minimalen Version heraus gekommen, die genau so gut funktioniert:

SELECT t.t_date, COUNT(v.id)
   FROM temp_dates_2 t
      LEFT JOIN visits v ON DATE(v.visit_date) = t.t_date
   GROUP BY t.t_date