我一直在尝试解决的相当复杂的查询,无法完全得到。我将布局架构然后进入问题。学生们玩游戏,在游戏中,他们“尝试”一个问题。每一次尝试都会承载一个重量,这取决于他们是否正确获得它,以及他们获得它的速度。查询的目标是基于此历史数据生成ProblemMastery对象
ATTEMPT
id | problem_id | weight | game_round_id | ended_at
----|------------|--------|---------------|-----------
1 | 4 | 3 | 1 | 4/11/2018
PROBLEM
id |..irrelevant math fact information
----|------------|-----------
1 |
GAMEROUND
id | student_id | ended_at
----|------------|-----------
1 | 1 | 4/11/2018
PROBLEM MASTERY
id | student_id | student_id | date_mastered
----|------------|------------|--------------
1 | 1 | 1 | 4/11/2018
当一个学生在游戏回合中完成一次尝试时,根据尝试中的问题在游戏过程中解决的速度,该尝试的权重将为0-4。如果一个学生在考虑到他们对某个问题的所有尝试后得到sum(weight)+=7,他们会得到一个分配给他们的ProblemMastery对象,将他们链接到该问题,并添加一个日期-掌握时间戳。
这个架构最近被修改了,旧的问题控制需要被删除,因为它们不准确,我们需要重新生成所有的问题控制基于旧的游戏回合和尝试数据。
对于通过学生尝试的每个问题,我们需要选择将总运行重量设置为7的尝试。这样我们就知道问题是在什么时候解决的。如果一个学生有4次尝试一个问题,第一次是体重=2,第二次是体重=2,第三次是体重=4,第四次是体重=4,那么问题ID应该与掌握日期一起返回。因此,一个例子可能是这样的[问题uu 1_u id]:“1-12-2018”,[问题u 2_u id]:“1-14-2018”,等等
我有一种潜移默化的怀疑,这可以在一个查询组中完成,但我不确定如何完成。
编辑
@alexandermp提供的答案非常接近。我接受了它,并修改了我系统上的答案。对于记录而言,这是嵌入在Rails平台中的
game_round_ids_array = "("+student.game_rounds.pluck(:id).join(", ")+")"
query_data = ActiveRecord::Base.connection.execute('
WITH progress AS (
SELECT
sum(weight) OVER (PARTITION BY problem_id ORDER BY ended_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum,
problem_id,
game_round_id,
ended_at
FROM attempts
WHERE game_round_id in '+game_round_ids_array+'
)
SELECT DISTINCT ON (problem_id)
problem_id,
game_round_id,
ended_at
FROM progress
WHERE running_sum > 6
ORDER BY problem_id, ended_at
')
