Wie lassen sich die Seitenzugriffe pro Tag in MySQL am besten berechnen?

Question

En mein Blog In der rechten Navigationsleiste zeige ich die 10 beliebtesten Artikel in Bezug auf die Anzahl der Seitenaufrufe an. So bekomme ich das:

SELECT *
FROM entries
WHERE is_published = 1
ORDER BY hits DESC, created DESC
LIMIT 10

Ich würde gerne die Top 10 nach Seitenaufrufen anzeigen pro Tag. Ich verwende MySQL. Gibt es eine Möglichkeit, dies in der Datenbank zu tun?

Übrigens, die created ist ein Datum und eine Uhrzeit.

UPDATE: Ich glaube, ich habe mich nicht klar ausgedrückt. Ich möchte, dass der Blogbeitrag mit 10.000 Treffern, der vor 1.000 Tagen veröffentlicht wurde, die gleiche Popularität hat wie der Blogbeitrag mit 10 Treffern, der vor 1 Tag veröffentlicht wurde. In Pseudo-Code:

ORDER BY hits / days since posting

...wo hits ist einfach ein int, der jedes Mal erhöht wird, wenn der Blogbeitrag angesehen wird.

OK, ich werde Folgendes verwenden:

SELECT *, AVG(
    hits / DATEDIFF(NOW(), created)
) AS avg_hits
FROM entries
WHERE is_published = 1
GROUP BY id
ORDER BY avg_hits DESC, hits DESC, created DESC 
LIMIT 10

Danke, Stephen! (Ich liebe diese Seite...)

Answer 1

Ich bin mir nicht ganz sicher, ob Sie die von Ihnen vorgeschlagene Tabellenstruktur in Ihrer Abfrage verwenden können. Die einzige Möglichkeit, die mir einfällt, ist, die Top 10 über den höchsten Wert zu erhalten Durchschnitt Treffer pro Tag. Auf diese Weise wird Ihre Anfrage:

SELECT *, AVG(hits / DATEDIFF(NOW(), created)) as avg_hits
FROM entries
WHERE is_published = 1
GROUP BY id
ORDER BY avg_hits DESC
LIMIT 10

Diese Abfrage geht davon aus, dass das von Ihnen erstellte Feld den Datentyp DATETIME (oder einen ähnlichen Datentyp) hat.

Answer 2

Ich denke, Sie könnten eine Spalte hits_day_count, die bei jeder Ansicht erhöht wird, und eine Spalte hits_day_current haben.

Bei jedem Seitenaufruf prüfen Sie, ob die Spalte hits_day_current heute ist. Wenn nicht, setzen Sie die Trefferzahl zurück. Dann erhöhen Sie die Spalte hits_day_count und setzen hits_day_current auf den aktuellen Zeitpunkt.

Pseudocode:

if article_data['hits_day_current'] == datetime.now():
    article_data['hits_day_count'] ++
else:
    article_data['hits_day'] = 0

article_data['hits_day_current'] = datetime.now()

Das offensichtliche Problem dabei ist einfach: die Zeitzonen. Die Gesamtzahlen werden um 00:00 Uhr zurückgesetzt, egal wo sich der Server befindet, was nicht unbedingt sinnvoll ist.

Eine bessere Lösung wäre eine rollierende 24-Stunden-Summe. Ich bin mir nicht ganz sicher, wie man das sauber machen kann. Der einfachste (wenn auch nicht so elegante) Weg wäre, die Logs Ihres Webservers regelmäßig zu analysieren. Holen Sie sich die letzten 24 Stunden der Protokolle, zählen Sie die Anzahl der Anfragen zu jedem Artikel und geben Sie diese Zahlen in die Datenbank ein.