sql - 如何在 PostgreSQL 中动态执行加权随机行选择？

Question

我有一个应用程序的下表，其中为学生分配了玩教育游戏的任务。

学生{id, last_played_datetime, total_play_duration, total_points_earned}

该应用程序随机选择一名学生并分配任务。学生只需玩游戏即可获得积分。该应用程序记录了玩游戏的日期和时间以及持续了多长时间。我想随机选择一个学生并分配任务。一次只能给一个学生分配任务。为了给所有学生提供平等的机会，我使用学生上次玩游戏的日期和时间、总玩时长和学生获得的总分来动态计算学生的权重。然后将根据体重随机选择一名学生。

在 PostgreSQL 中，我如何根据行的动态计算权重从表中随机选择一行？

每个学生的权重计算如下:(minutes(current_datetime - last_played_datetime) * 0.75 + total_play_duration * 0.5 + total_points_earned * 0.25)/1.5

示例数据:

+====+======================+=====================+=====================+
| Id | last_played_datetime | total_play_duration | total_points_earned |
+====+======================+=====================+=====================+
| 1  | 01/02/2011           | 300 mins            |  7                  |
+----+----------------------+---------------------+---------------------+
| 2  | 06/02/2011           | 400 mins            |  6                  |
+----+----------------------+---------------------+---------------------+
| 3  | 01/03/2011           | 350 mins            |  8                  |
+----+----------------------+---------------------+---------------------+
| 4  | 22/03/2011           | 550 mins            |  9                  |
+----+----------------------+---------------------+---------------------+
| 5  | 01/03/2011           | 350 mins            |  8                  |
+----+----------------------+---------------------+---------------------+
| 6  | 10/01/2011           | 130 mins            |  2                  |
+----+----------------------+---------------------+---------------------+
| 7  | 03/01/2011           |  30 mins            |  1                  |
+----+----------------------+---------------------+---------------------+
| 8  | 07/10/2011           |   0 mins            |  0                  |
+----+----------------------+---------------------+---------------------+

Answer 1

下面是一个解决方案，其工作原理如下:

• 首先计算每个学生的体重
• 将所有学生的权重相加并乘以随机种子
• 然后选择高于该目标的第一个学生，随机，体重

查询:

with 
    student_with_weight as (
        select 
            id,
            (
                extract(epoch from (now() - last_played_datetime)) / 60 * 0.75
                + total_play_duration * 0.5
                + total_points_earned * 0.25
            ) / 1.5 weight
        from student
    ),
    random_weight as (
        select random() * (select sum(weight) weight from student_with_weight ) weight
    )
select id 
from 
    student_with_weight s
    inner join random_weight r on s.weight >= r.weight
order by id
limit 1;