对数老化的Mysql查询

Question

我想实现基于老化的“对数”分数衰减，并且我正在尝试找出 SUM/LOG 组合。这里是当前查询的简化版本:

SELECT SUM(1) as total_score FROM posts
JOIN votes ON votes.post_id = posts.post_id
WHERE 1
GROUP BY post_id
ORDER BY total_score DESC

我目前正在执行 SELECT 'SUM(1) as total_score' 但我想修改查询以考虑投票的日期/年龄；今天的投票权重为 1，15 天前的投票权重接近 0.8，30 天前的投票权重接近 0。我将日期字段存储在投票表 (vote_date) 中作为 unix_timestamp。

我并不真正关心 WHERE 子句；这很简单。我想弄清楚的是对数老化部分。

Answer 1

我认为您的回答分为两部分。先是权重函数，再是SQL实现。

加权函数:

根据您的图表，您不需要对数权重而是抛物线。

从这个你必须解决

Xc = y

在哪里

X = [1 1 1 ;
    15^2 15 1;
    30^2 30 1];

和

y = [1;.8;0];

你得到 c = X^(-1)y 或在 matlab 中

c = X\y

现在您已经拥有了所描述的二次函数的适当权重；即 y = ax^2+bx+c 和 (a,b,c) =(-.0013,.0073,.9941)。

SQL 部分:

你的选择语句应该看起来像(假设感兴趣的列被命名为“年龄”)

SELECT (-.0013*age*age + .0073*age + .9941) as age_weighted

希望对你有帮助

干杯

---

这是完整的 Matlab 代码(也是双重检查解决方案)

X = [1 1 1 ;
    15^2 15 1;
    30^2 30 1];

y = [1;.8;0];

c = X\y;

x= (1:30)';
y = [x.^2 x ones(30,1)]*c;

figure(1)
clf;hold on
plot(x,y)
plot([1 15 30],[1 .8 0],'o')