时间戳

首页 > 解决方案 > 如何编写 SQL 查询根据加权操作和时间衰减对用户进行排名？

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

问题描述

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

随着时间的流逝，这两个动作都会线性地“失去”价值。

动作 A：60% 重量

动作 B：40% 重量

分数 = SUM [动作权重 * 1000000 /（当前时间戳 - 动作时间戳）]

带有用户操作的 MySQL 表

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

得分最高的用户应该在排名的顶部。

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

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

user_id | score
1       | 0.92830
2       | 0.76382

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

谢谢！

标签： mysqlsqlmariadb

解决方案

如果我没听错的话，你只需要一个普通的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;

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

推荐阅读