如何编写一个SQL查询，该查询在最近的一个分数子集上检索高分--请参阅解释

Question

给出一份列有下列各栏的答复表：

用户名，LessonNumber，QuestionNumber，响应，分数，时间戳

我如何运行一个查询，返回的用户在他们的第一次尝试得到90分或更高的每一个问题，在他们的最后5课？“最后五节课”是一个限制条件，而不是一个要求，所以如果他们只上了一堂课，但对每个问题的第一次尝试都是正确的，那么他们就应该被包括在结果中。我们只是不想再回头看5节课了。

关于数据:用户可能上的是不同的课程。一些用户可能还没有完成五个课程(例如，可能只在第3课上)。每堂课都有不同数量的问题。用户有不同的课程路径，因此他们可能跳过一些课程编号，甚至按顺序完成课程。

由于这似乎是一个将时间上不一致/不连续的值转换为每个用户的一致/连续值的问题，我想我可以通过一对排序函数调用来解决大部分问题。对于“第一次尝试每一个问题在最后5节课上”而言，评分高于90分的条件说明也很棘手，因为每个用户完成的问题数量是可变的。

目前为止..。

作为可能需要发生的事情的起点或提示，我将时间戳转换为针对每个问题的"AttemptNumber“，方法是将"row_number() over”(按用户名进行分区，LessonNumber，按时间戳排序)作为AttemptNumber。

我还试图将LessonNumber从绝对值转换为单个用户的连续排序值。我可以使用"dense_rank() over ( LessonNumber desc的用户名顺序分区)“作为LessonRank，但这假设订单课程已经完成，与LessonNumber的顺序相对应，不幸的是，并不总是这样。但是，让我们假设是这样的，因为我确实有一种通过几个连接生成这样一个数字的方法，所以我可以使用描述的dense_rank转换来选择“最后5个完成的课程”(即LessonRank <= 5)。

对于>90的条件，我想我可以把分数转换成一个整数，如果>= 90是"1“，如果< 90是"0”。然后，我可以引入一个子句，例如“(Score)=COUNT(Score).”，它将只选择所有分数等于1的用户。

如有任何解决办法或建议，将不胜感激。

Answer 1

你给出了解决办法：

SELECT DISTINCT Username
FROM Results 
WHERE Username NOT in (
    SELECT DISTINCT Username
    FROM (
        SELECT
            r.Username,r.LessonNumber, r.QuestionNumber, r.Score, r.Timestamp
            , row_number() over (partition by r.Username,r.LessonNumber,r.QuestionNumber order by r.Timestamp) as AttemptNumber
            , dense_rank() over (partition by r.Username order by r.LessonNumber desc) AS LessonRank
        FROM Results r
        ) as f
    WHERE LessonRank <= 5 and AttemptNumber = 1 and Score < 90
)

关于LessonRank，我使用了你所指摘的内容，因为它不清楚如何在其他方面排序:第一个问题的第一次尝试的时间戳？还是任何一个问题的第一次尝试的时间戳？或者仅仅是第一个(或最近的？)课上任何问题的结果的时间戳？

最内部的Select添加了您提供的所有AttemptNumber和LessonRank。

下一次Select只保留结果，这将取消用户进入最终名单的资格-所有的第一次尝试与一个不足的分数在过去的5节课。我们最终得到了一个用户列表，我们不想在最终结果中显示。

因此，在最外层的Select中，我们可以选择所有不在排除列表中的用户。基本上所有其他用户已经回答了任何问题。

编辑:就像很多时候一样，第二次尝试应该更好.

多一个编辑

这是一个版本，包括您的评论意见。

SELECT Username
FROM 
(
    SELECT Username, CASE WHEN Score >= 90 THEN 1 ELSE 0 END AS QuestionScoredWell
    FROM (
        SELECT
            r.Username,r.LessonNumber, r.QuestionNumber, r.Score, r.Timestamp
            , row_number() over (partition by r.Username,r.LessonNumber,r.QuestionNumber order by r.Timestamp) as AttemptNumber
            , dense_rank() over (partition by r.Username order by r.LessonNumber desc) AS LessonRank
        FROM Results r
        ) as f
    WHERE LessonRank <= 5 and AttemptNumber = 1
) as ff
Group BY Username
HAVING MIN(QuestionScoredWell) = 1

我在计算的Having值上使用了一个带有MIN表达式的QuestionScoredWell子句。

当比较两个查询的执行计划时，这个查询实际上更快。但不确定这是否部分是由于我的表中的数据行数量较少所致。

Answer 2

随机建议：

1

对于“第一次尝试每个问题”的评分超过90分的条件说明也很棘手，因为每个用户的问题数是可变的。

等于

没有第一次尝试用<= 90分，最近的5节课

这让我觉得用NOT EXISTS子查询更容易抓取。

2

第一次尝试与where timestamp = (select min(timestamp) ... )相同

Answer 3

您需要首先确定每个用户的前5名课程，使用时间戳对课程进行优先排序，然后可以按分数限制。尝试：

Select username
from table t inner join
(select top 5 username, lessonNumber
 from table
 order by timestamp desc) l 
on t.username = l.username and t.lessonNumber = l.lessonNumber
from table
where score >= 90