显示最受欢迎/浏览过的文章 - 按时间划分

Question

i have a news website

On the home page i want to list the 4 most viewed articles in the last 7 days

i have a table, Articles: id, title, body, publishTime, viewCount...

i cant just display the top 4 in order of view count, as the longer it has been out, the generally more views it will have. so i need to divide views by hours displayed

so my query needs to get all articles in last 168 hours, divide each view count by hours shown, then just show top 4

is it possible to do this with just commands, or do i need to create some kind of virtual table?

(using php & mysql)

thanks

Answer 1

Per our discussion in the comments, it seems that you're not after "the 4 most viewed articles in the last 7 days" but rather "the 4 articles with the highest average view rate that have been published in the last 7 days". This can be achieved as follows:

SELECT   *
FROM     Articles
WHERE    publishTime > CURRENT_TIME - INTERVAL 7 DAY
ORDER BY viewCount / (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(publishTime)) DESC
LIMIT    4