SQL Query zum Ermitteln von Besucherstatistiken über mehrere Zeiträume

[Zacherl]

Hallo zusammen,

ich versuche grade ein paar einfache Statistiken in ein PHP Projekt zu implementieren. Dazu habe ich mir eine Datenbanktabelle angelegt, in der ich jeden Seitenzugriff (View) protokolliere. Dabei speichere ich neben einigen anderen Informationen primär einen Timestamp und die IP des Besuchers.

Will ich jetzt die "eindeutigen" Besucher über einen bestimmten Zeitraum abfragen, mache ich das mit:

Code:

SELECT COUNT(DISTINCT `ip`) AS `count` FROM `webstats` WHERE `timestamp` >= {min} AND `timestamp` <= {max}

Nun habe ich mich daran gewagt einen Grafen mit der Statistik für die letzten 14 Tage zu erstellen. Bisher berechne ich auf PHP Seite in einer Schleife jeweils den Start- und Endzeitpunkt des jeweiligen Tages und feuere dann die Query ab. Das macht allerdings in diesem Falle schon 14 einzelne Querys, was meiner Meinung nach alles andere als schön ist und vermutlich auch ziemlich auf die Performance geht.

Meine Frage ist nun, ob es möglich ist, in einer einzigen Query die Statistik für mehrere Zeiträume zu ermitteln?

Viele Grüße
Zacherl

[Zacherl]

Habe einen Weg gefunden:

Code:

SELECT COUNT(DISTINCT ip) as count FROM `webstats` WHERE (`timestamp` >= $time_start AND `timestamp` <= $time_end) GROUP BY DATE(FROM_UNIXTIME(`timestamp`))

In diesem Falle entspricht $time_start dem Begin des -14. Tages und $time_end ist der Timestamp des aktuellen Tages. Jetzt gibt es allerdings noch einen kleinen Schönheitsfehler. Und zwar werden Tage, für die kein einziger Datensatz existiert, natürlich auch nicht in die Ergebnismenge mit aufgenommen. Sprich: Mein Result über 14 Tage kann durchaus nur 3 Datensätze enthalten. Dadurch wird die Zuordnung von Besucheranzahl zu Tag recht umständlich.

Ist es möglich an dieser Stelle für nicht vorhandene Tage einen "NULL Datensatz" einzufügen?

[Jumpy]

Nicht schön, aber könnte man nicht für jeden Tag ein "Select 0, Tag..." via Union hinzufügen.
Auch im ursprünglichen Select den Tag dazupacken.

Dann um das Ganze ein "Select Max(count) from (...) Group By Tag" drumpacken?

[sx2008]

Kleine Erweiterung um neben der Besucherzahl auch die Anzahl der Zugriffe abzufragen:
SELECT COUNT(DISTINCT ip) as count, COUNT(*) as totalHits FROM webstats WHERE ...

Um die fehlenden Datensätze an denen es keine Zugriffe gab aufzufüllen könnte man eine "Datumstabelle" verwenden.

Datum	Woche	Monat	Wochentag
01.01.2013	1	1	3
02.01.2013	1	1	4
...	...	...	...

Diese Datumstabelle wird per Software gefüllt und kann dann mit der Tabelle webstats verjoint (LEFT oder RIGHT OUTER JOIN) werden.
So kannst du nicht nur Tages- sondern auch Wochen- und Monatsstatistiken erstellen.
Mit dem Wochentag kannst du z.B. die Zugriffe eines ganzen Jahres verwenden und dann zu ermitteln wie sich das über die Wochentage Montag bis Sonntag darstellt.

[Furtbichler]

---gelöscht--- sx2008 hatte genau das gleiche geschrieben. Ich brauch ne Brille.

[Zacherl]

Ahh so eine JOIN Lösung sollte genau das sein, was ich brauche. Allerdings verstehe ich noch nicht so ganz, wie ich das umsetzen soll. Es würde ja nicht reichen diese "Datumstabelle" einmalig anzulegen, sondern man müsste sie ja z.b. jedes Jahr erweitern. Gibt es da eine geschickte Möglichkeit, die ich grade übersehe? Ich möchte ja auch nicht vor jedem INSERT erstmal mit einem SELECT prüfen, ob die Datumstabelle aktuell ist, um sie ggfls. erst zu erweitern.

Und noch eine kurze Frage:
Ist mein Ansatz von der Performance her überhaupt tragbar für große Websites? Es werden durch die Seitenaufrufe ja doch eine riesige Menge an Daten generiert.