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.

[Furtbichler]

Zitat von 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.

Nö, einmal für 100 Jahre. Es gibt diverse Generatoren im Netz, die dir für jeden Tag noch diverse weitere Informationen liefern.

Wenn du natürlich (was sinnvoll wäre) die arbeitsfreien Tage (Betriebsfest, -Versammlung etc.) eintragen willst, dann lieferst Du noch einen Editor dazu. Die Feiertage (je Bundesland) gibts im Netz (und eine Delphi-Komponente gibts auch)

Ich z.B. finde folgende Infos sehr spannend: Der wievielte Tag im Monat/Jahr, Der wievielte Arbeitstag im Monat/Jahr usw. Damit kannst Du wunderbare Statistiken erstellen (Summe bis zu diesem Tag / der wievielte AT). Zuhause habe ich noch ein olles Projekt in Delphi, das so eine Dimensionstabelle erstellt. Kann ich ja hier mal reinstellen.

[Zacherl]

Zitat von Furtbichler:

Zitat von 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.

Nö, einmal für 100 Jahre. Es gibt diverse Generatoren im Netz, die dir für jeden Tag noch diverse weitere Informationen liefern.

Du hast natürlich recht. Ich arbeite nicht allzu oft mit Datenbanken und vergesse daher immer ganz gerne, dass 36500 Datensätze für eine moderne relationale Datenbank kein großes Problem darstellen Zumal die PageViews vermutlich (hoffentlich ) sehr viel schneller wachsen werden.

Da ich für dieses Projekt sowieso PHP verwenden muss, kann ich die Datumstabelle mit den doch sehr mächtigen Time/Date Funktionen vermutlich recht einfach geneieren. Ich versuche mal mein Glück.

Danke euch allen!

[Furtbichler]

Zitat von Zacherl:

Da ich für dieses Projekt sowieso PHP verwenden muss, kann ich die Datumstabelle mit den doch sehr mächtigen Time/Date Funktionen vermutlich recht einfach geneieren. Ich versuche mal mein Glück.

Mach's doch gleich richtig: Such mal nach so einem Date Dimension Generator und lass dir ne richtige Dimensionstabelle bauen. Die kannst Du fast immer gebrauchen.

PS: Das wäre dann natürlich ein LEFT JOIN

Code:

Select DateDimension.DateCol, 
       myData.myResults

 from  DateDimension LEFT JOIN 
       myData
where  DateDimension.DateCol between <FirstDate> and <LastDate>