What is the best way to calculate page views per day in MySQL?

Question

In my blog , I display in the right navigation window the 10 most popular articles in terms of page hits. Here is how I understand it:

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

What I would like to do is show the top 10 results per page per day. I am using MySQL. Is there a way to do this in the database?

BTW, the created field is a datetime.

UPDATE: I guess I didn’t explain. What I want is a 10,000 hit blog post that was published 1,000 days ago to be as popular as a 10 hit blog post that was posted 1 day ago. In pseudo code:

 ORDER BY hits / days since posting 

... where hits is just an int that increments every time a blog post is viewed.

OK, here is what I will use:

 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 

Thanks Steven! (I like this site ...)

Answer 1

I'm not quite sure what you can using the table structure that you offer in your query. The only way I can think of is to get the top 10 at the highest average rates per day. By doing so, your request will look like this:

 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 

This query assumes that your created field has a DATETIME data type (or similar).

Answer 2

I assume you might have a hits_day_count column that grows on every view, and hit_day_current.

In each page view, you check to see if there is a hits_day_current column. If not, reset the number of hits. Then you increment the hits_day_count column and set hits_day_current to the current datetime.

Pseudo Code:

 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() 

The obvious problem with this is simple - time zones. The resulting data is reset at 00:00, where the server is located, which may be unsuitable.

The best solution would be a rolling 24-hour amount. Not quite sure how to do it neatly. The easiest (though not very elegant) way is to periodically analyze the logs of your web server. Get the last 24 hours of magazines, count the number of queries for each article and put them in the database.