Obtaining an average per day value of a field with MySQL's DATEDIFF function

Obtaining an average per day value of a field with MySQL's DATEDIFF function

Quite some time ago, I added a views field to my articles table, and implemented a small function to log a "view" on each of my articles as it was read. A lot of CMS & blog systems do this, and people often display the number of clicks/views/reads on the article page.

While I don't display that specific information currently on my articles, I wanted to calculate the average number of views/reads per day as a measure of determining which of my articles were the most popular, as simply putting up the total number of view/reads is basically going to give you a persistent list of the oldest articles, unless you get dugg, that list will likely not change.

This is easy to accomplish when using a MySQL datetime field for the article publish date.

Here is the basic MySQL code used;

SELECT title, views / DATEDIFF(CURDATE(), date) AS views_per_day
FROM articles
WHERE DATEDIFF(CURDATE(), date) > '14'
    AND views > '100'
ORDER BY `views_per_day` DESC
LIMIT 0, 10

This produces a list of the top 10 articles (over 14 days old with an excess of 100 views to avoid recent/front page posts diluting results), and displays their titles along with the average views per day, descending in order from highest views per day.

Tags

 

Comments

No comments have been posted to this page.

Comments are closed. No new comments allowed.

Copyleft 2002 - 2017 Matt Jones
Hand crafted with HTML5 & CSS3
↑ Back to top