Let's say I have a screensaver website. I want to display the CURRENT top 100 screensavers on the front page of the website.
What I mean is, "RECENT" top 100 screensavers. What would be an example query to do this?
My current one is:
SELECT * FROM tbl_screensavers WHERE WEEK(tbl_screensavers.DateAdded) = WEEK('".date("Y-m-d H:i:s",strtotime("-1 week"))."') ORDER BY tbl_screensavers.ViewsCount, tbl_screensavers.DateAdded
This will select the most viewed ("tbl_screensavers.ViewsCount") screensavers that were added ("tbl_screensavers.DateAdded") in the last week.
However, in some cases there are no screensavers, or less than 100 screensavers, submitted in that week.
So, how can I perform a query which would select "RECENT" top 100 screensavers? Hopefully you have an idea of what I'm try to accomplish when开发者_高级运维 I say "RECENT" or "CURRENT" top screensavers. -- aka. the most viewed, recently - not the most viewed, all-time.
Given no other algorithm to weigh the value of a view vs. a recent view, you would just simply want
SELECT * FROM tbl_screensavers ORDER BY ViewsCount limit 100
However, to capture the concept of "recent" you may want to introduce an algorithm to weigh the recent-ness of a particular view. One way to do that is to assign a daysOld score to each view and show the 100 with the lowest score (with this mechanism, low score is good like in golf).
I'm not enough of a MySQL guru to write the query for that, but it would involve summing up the score, computed based on daysOld=today-dateOfScore and then ordering the result set based on that score, with a limit of 100.
精彩评论