Reddit Style Score Degridation Over Time In MySQL

Question

I'm attempting to create a reddit style score degradation system for entries on a system. I've got a MySQL view setup to calculate the total "Score" (sum of all up/down votes). I'm having trouble creating a simple, but effective system for moving entries down the page (so that newer entries end up at the top, but a high score can move entries to the top that would otherwise have aged off)...

Here's the closest bit of SQL I've been able to create thus far:

(SUM(v.Score) - (TIMESTAMPDIFF(MINUTE, t.Genesis, NOW()) *
              IF(TIMESTAMPDIFF(MINUTE, t.Genesis, NOW()) > 1440,
                    0.1, 0.003))
) as "Weight",

v.Score is a 1 or a -1 dependent on user votes. t.Genesis is the timestamp on the entry itself.

Any help or suggestions would be appreciated.

There is a better option than doing a sum of all up/down votes. You can look here : http://www.evanmiller.org/how-not-to-sort-by-average-rating.html And you can add a time var, if the data is out of date, you decrease the scoring — J-C FOREST, Apr 11 '17 at 07:21
@J-CFOREST - While that looks promising... I'm unsure how I'd actually implement a time variable in it? Thoughts? — Bravo Delta, Apr 12 '17 at 00:30
What is the question? Looking for a "scoring algorithm"? Implementation problems? Performance? Something else? — Rick James, Apr 20 '17 at 03:39

score 2 · Answer 1 · edited Dec 25 '19 at 11:03

One solution could be to use a sort of exponential decay for the relevance of time as a ranking parameter. For example:

SELECT 
    article, ranking 
FROM (
   SELECT 
       article,
       (upvotes + downvotes) AS Total,
       (upvotes - downvotes) AS Score,
       (EXP(-(Published - Genesis) * Constant / 86400) * (Score / Total)) AS Ranking
   FROM Table) 
ORDER BY ranking DESC

Where Published is the time of publishing, Genesis is some really early date, and Constant is a scaling factor, to determine how late the weight should drop to zero at:

For example: if you want to give all posts a very small score advantage after 7 days from now (say 0.1) then -ln(0.1) / 7 is your Constant.

Score / Total for the average rating rather than the absolute value and 86400 for a one day in seconds (assuming that's how you're measuring your time).

Once again, apologies for my lack of knowledge on SQL functions, I know that EXP is definitely possible, only the time difference function can be adjusted in order to get the time difference in seconds.

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

You can implement the same ranking algorithm than Hacker News :

Implementing the Hacker News ranking algorithm in SQL

@OMG Ponies solution:

 SELECT x.*
    FROM POSTS x
    JOIN (SELECT p.postid, 
                 SUM(v.vote) AS points
            FROM POSTS p
            JOIN VOTES v ON v.postid = p.postid
        GROUP BY p.postid) y ON y.postid = x.postid
ORDER BY (y.points - 1)/POW(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(x.timestamp))/3600)+2, 1.5) DESC
   LIMIT n

x.timestamp is your t.Genesis, v.vote is your v.Score

edited May 23 '17 at 12:25

Community

1
1

answered Apr 12 '17 at 08:05

J-C FOREST

321
4
11

So this has a small flaw in that newer posts aren't ranked near the top and posts that have equal points seem to be ordered oldest to newest? It's essentially a ORDER BY points, genesis – Bravo Delta Apr 13 '17 at 05:06

Reddit Style Score Degridation Over Time In MySQL

2 Answers2