sql - 按列分组，选择最新值

Question

我正在对一个跟踪学生考试结果的表执行查询。考试由多个部分组成，每个部分得分有一栏。每行都是学生参加的测试的一个实例。这些部分可以一次全部完成，也可以分成多次尝试。例如，学生今天可以选一个部分，明天选其余部分。此外，学生可以重新参加考试的任何部分。

样本学生:

StudentID   WritingSection   ReadingSection   MathSection   DateTaken1           65               85               54            4/1/2013 14:531           98               NULL             NULL          4/8/2013 13:131           NULL             NULL             38            5/3/2013 12:43

NULL 表示该部分未针对给定的测试实例管理，第二部分分数表示该部分已重考。

我想要一个按 StudentID 分组的查询，这样每个学生只有一行，并返回每个部分的最新分数。我正在寻找一种有效的方法来解决这个问题，因为我们在数据库中进行了数十万次测试尝试。

预期结果:

StudentID    WritingSection    ReadingSection    MathSection    DateTaken1            98                85                38             5/3/2013 12:43

编辑:已经有很多好的解决方案。在选择答案之前，我想在下周的每个星期都多做一些实验。谢谢大家!

Answer 1

抱歉 - 我之前的回答回答了一个与提出的问题不同的问题 :) 它将返回最新行中的所有数据。所问的问题是聚合所有行以单独获取每个主题的最新分数。

但我把它留在那里是因为我回答的问题很常见，也许有人着陆于这个问题实际上有那个问题:)

现在回答实际问题:

我认为最简洁的方法是使用 PIVOT 和 UNPIVOT:

SELECT StudentID, [WritingSection], [ReadingSection], [MathSection], MAX(DateTaken) DateTaken
FROM (
  SELECT StudentID, Subject, DateTaken, Score
  FROM (
    SELECT StudentID, Subject, DateTaken, Score
      , row_number() OVER (PARTITION BY StudentID, Subject ORDER BY DateTaken DESC) as rowNum
    FROM Students s
    UNPIVOT (
      Score FOR Subject IN ([WritingSection],[ReadingSection],[MathSection])
    ) u
  ) x
  WHERE x.rowNum = 1
) y
PIVOT (
  MAX(Score) FOR Subject IN ([WritingSection],[ReadingSection],[MathSection])
) p
GROUP BY StudentID, [WritingSection], [ReadingSection], [MathSection]

最里面的子查询 (x) 使用 SQL 的 UNPIVOT 函数对数据进行规范化(意思是将每个学生在测试的每个部分的分数变成一行)。

下一个子查询 (y) 只是将行过滤为每个主题的最新分数(这是 SQL 错误的解决方法，您不能在 WHERE 中使用像 row_number() 这样的窗口函数条款)。

最后，由于您希望数据以非规范化原始格式显示(每个测试部分一列)，我们使用 SQL 的 PIVOT 函数。这只是将行变成列 - 一个用于测试的每个部分。最后，您说您希望显示最近进行的测试(尽管每个部分都可以有自己独特的“最近”日期)。因此，我们只需汇总这 3 个可能不同的 DateTakens 即可找到最新的。

如果将来添加更多部分，这将比其他解决方案更容易扩展 - 只需将列名称添加到列表中即可。