Statistik nach Datum mit Nullwerten

[gpl]

Eher ein theroretischer Ansatz als ein konkretes Problem:

Nehmen wir an, ich habe eine Tabelle 'Besucher' in meiner Datenbank, die die jeden neuen Besucher einer Website mit Datum, Uhrzeit und z.B. die IP-Adresse erfasst.
Jetzt möchte ich diese Tabelle statistisch Auswerten in der Art, dass ich für jeden Tag angezeigt bekomme, wieviel Besucher auf der Website waren.

Nehmen wir mal diese Tabelle an:
DATUM | IP
2009-01-01 10:00:00 | 123.123.123.123
2009-01-01 11:23:25 | 231.231.231.231
2009-01-03 08:20:20 | 111.111.111.111
...

Mein Ansatz war zunächst folgender:

select datum, count(ip) from besucher 
 group by datum

Das klappt auch, bis auf das Problem, dass ich für Tage, an denen KEIN Besucher da war, auch keinen Datensatz angezeigt bekomme.
Das Ergebniss der o.g. Abfrage wäre:

2009-01-01 | 2
2009-01-03 | 1

Ich hätte jedoch gerne eine Tagesstatistik, die mir auch Nullwerte ausgibt, die also z.B. so aussieht:

2009-01-01 | 2
2009-01-02 | 0 <-- Keine Besuche am 2.1. !
2009-01-03 | 1

mir also (bei diesem Beispiel) für den 2.1. den Wert 0 ausgibt.

Ich hoffe, ich habe mich klar ausgedrückt, was ich möchte.
Diese Tabelle soll möglichst direkt per SQL erzeugt werden. Ich möchte nicht in PHP per Schleife für jeden einzelnen Tag eine Abfrage erzeugen, oder sowas ähnliches.

Wer hat dafür eine Lösung?

[Crisps]

Solche Probleme löst man am besten mit einer Integer Tabelle:

CREATE TABLE integers (
  i tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (i)
);

INSERT INTO integers (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

Damit basteln wir uns dann alle Daten (ist Daten eigentlich das Plural von Datum? ) die von heute gesehen in der Verganenheit liegen (in diesem Fall die letzen 1000 Tage) und verbinden das Ergebniss mit einem LEFT JOIN mit dem Datum der Besucher-Tabelle:

SELECT DATE_SUB(CURRENT_DATE, INTERVAL 100 * h.i +10 * t.i + u.i DAY) AS d
     , COUNT(b.ip)
  FROM integers AS h
CROSS
  JOIN integers AS t
CROSS
  JOIN integers AS u
LEFT OUTER
  JOIN besucher AS b
    ON DATE(b.datum) = DATE_SUB(CURRENT_DATE, INTERVAL 100 * h.i +10 * t.i + u.i DAY)
GROUP
    BY d
ORDER
    BY d DESC

Da ich hier allerdings DATE() verwenden muss (mir fällt keine andere Möglichkeit ein) um das Datum aus DATETIME zu extrahieren, könnte diese Query vielleicht etwas langsam sein.

[gpl]

Super! Danke! Das sieht ja schon mal gut aus. Bin zwar kein SQL-Guru, kann mir aber trotzdem vorstellen, was in der Abfrage so ungefähr vonstatten geht.

In der Praxis würde man für eine Statistik aber wahrscheinlich eher mit einem von- und einem bis-Datum arbeiten und nicht über die letzten 1000 Tage. Oder, bei einer Monatsstatistik, alle Tage eines Monats.

Wie würde das mit dem Code aussehen? Geht das?

Habe eben folgendes dazu gefunden: http://www.blognotizen.de/archives/1...ll-Werten.html

Da spricht jemand ein ähnliches Problem an, allerdings nicht mit Datums-Werten.

[Crisps]

Das mit den 1.000 Tagen ist natürlich eine Limitation, aber man könnte daraus auch 10.000 oder noch mehr machen. Anders ist das jedenfalls nicht möglich, da ersteinmal alle verfügbaren Datumsangaben für jede Woche/Monat/Jahr generiert werden müssen.

Habe eben folgendes dazu gefunden: http://www.blognotizen.de/archives/1...ll-Werten.html

Da spricht jemand ein ähnliches Problem an, allerdings nicht mit Datums-Werten.

Ja, aber hier ist das Problem:

SELECT k.name
     , COUNT(*) AS anzahl
  FROM kategorie AS k
LEFT OUTER
  JOIN item AS i
    ON i.kategorie_id = k.id
GROUP 
    BY k.id

Würde das Ergebniss so anzeigen wie dort gewünscht. Aber mit deiner Statistik ist das leider nicht so einfach, weil das Datum für einen verfügbaren Zeitraum ja von irgendwo herkommen muss - während in diesem Beispiel die Kategorien schon ihren eigenen Eintrag in einer Tabelle haben. Um ähnlich vorzugehen, müsste man erst eine Tabelle mit allen verfügbaren Daten erstellen:

CREATE TABLE daten (
  datum DATE NOT NULL,
  PRIMARY KEY (datum)
);

Für die folgende Query braucht man die integers Tabelle die ich bereits gepostet habe:

INSERT 
  INTO daten
SELECT DATE_ADD('2000-01-01', INTERVAL tage DAY)
 FROM (SELECT tousands.i*1000 + hundreds.i*100 + tens.i*10 + units.i AS tage
         FROM integers AS tousands
       CROSS 
         JOIN integers AS hundreds
       CROSS 
         JOIN integers AS tens
       CROSS 
         JOIN integers AS units) AS x

So, jetzt wurde die Tabelle mit 10.000 Tagen ab dem 1.1.2000 gefüllt - also in etwa bis 2027. Jetzt ist es möglich, alle Einträge aus der Besucher Tabelle mit den Einträgen aus der Daten Tabelle zu verbinden:

SELECT d.datum
     , COUNT(b.ip)
  FROM daten AS d
LEFT OUTER
  JOIN besucher AS b
    ON DATE(b.datum) = d.datum
 WHERE d.datum BETWEEN '2009-02-01' AND '2009-02-28'
GROUP
    BY d.datum
ORDER
    BY d.datum DESC

Das würde dann alle Ergebnisse dieses Monats anzeigen. Die Abfrage hat aber möglicherweise auch ein Performance Problem, da man aus der DATETIME Spalte ja irgendwie das Datum extrahieren muss, was hier mit der "DATE" Funktion geschieht - Funktionen können aber leider keinen Index nutzen.