mysql - 如何编写 SQL 查询以根据加权操作和时间衰减对用户进行排名？

Question

我想使用2 个操作(A 和 B)对用户进行排名。

随着时间的流逝，这两个 Action 都会线性地“松散”值。

Action A:60%权重

Action B:40%权重

Score = SUM [ Action Weight * 1000000 / (Current Timestamp - Action Timestamp) ]

包含用户操作的 MySQL 表

user_id | action | timestamp
1       | A      | 1524428013410
1       | B      | 1525431531237
2       | B      | 1525578131563

得分最高的用户应该排在最前面。

如何编写高性能的 SQL 查询来计算用户的分数？

查询的输出应采用以下格式:

user_id | score
1       | 0.92830
2       | 0.76382

(上面例子中的分数只是我需要的输出格式的占位符)

谢谢!

Answer 1

如果我没理解错的话，你只需要一个普通的GROUP BY。您可以JOIN 权重。使用 ORDER BY 进行排序。假设您的表名为 action_user。

SELECT au.`user_id`,
       sum(a.`weight` / (current_timestamp() - au.`timestamp`)) `score`
       FROM `action_user` au
            INNER JOIN (SELECT 'A' `action`,
                               .6 `weight`
                        UNION ALL
                        SELECT 'B' `action`,
                               .4 `weight`) a
                       ON a.`action` = au.`action`
       GROUP BY au.`user_id`
       ORDER BY 2 DESC;

但不确定您的公式。那是一些非常小的数字。也许根据需要更改它。