在SQL中实现Hacker News排名算法

一尘不染

在SQL中实现Hacker News排名算法

mysql

这是Paul Graham如何描述Hacker
News的排名算法：

新闻.YC的只是

（p-1）/（t + 2）^ 1.5

其中p =点，t =年龄（小时）

鉴于下表，我想在纯mySQL中执行此操作：

具有字段postID（索引）和postTime（时间戳）的表发布。
带有字段表决ID（索引），帖子ID和表决（整数，0或1）的表投票。

投票域的想法是可以取消投票。出于排名目的，vote = 0等于完全没有投票。（所有投票均为赞成，无表决权。）

问题是如何构建返回前N个postID（按Paul
Graham公式排序）的查询。总共大约有10万个帖子，因此，如果您认为对成绩进行缓存或需要其他任何内容，我很乐意听取有关此方面的建议。

（显然，这不是火箭科学，我当然可以弄清楚，但是我发现吃SQL早餐，午餐和晚餐的人可能会觉得不高兴。而且在StackOverflow上使用它似乎很有价值。）

共1个答案

一尘不染

未经测试：

  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

2020-05-17