Is it possible in MySQL to order something by popularity with a timestamp and the number of hits?

Question

I only have two variables to work with here, age (unix timestamp) and hits. I'm wondering if there is a solution in MySQL to calculate and order by the popularity of something. The algorithm would have to acknowledge that new items would not necessarily have as many hits as older ones but are more popular.

Is this possible the way I'm imagining it?

Answer 1

You need to come up with your own method of weighting hits and age when calculating popularity. You can select this function as popularity, and then order on this. The following simple example orders on the daily hit ratio of the page.

SELECT table.*, (table.hits / TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(table.created))) AS popularity
FROM table
...
ORDER BY popularity DESC