Oracle SQL - 计算运行的最新值和关联的值

Question

我有一个学生考试成绩表，以及所参加的年份和参加的具体考试，如下所示：

Student ID     Score    Year    TestName  GradeLevel
100001         347      2010    Algebra   8
100001         402      2011    Geometry  9
100001         NA       NA      NA        10
100001         NA       NA      NA        11
100001         525      2014    Calculus  12

此示例表中只有一个学生 ID，但我的实际数据中显然有很多学生 ID。

我正在尝试编写一个查询，该查询将告诉我，在每个学年中，每个学生在最近参加的测试中的分数是多少，这是哪个测试，以及他们处于什么年级。我想要的输出应如下所示：

StudentID     Year    MostRecentScore  MostRecentTest  MostRecentTestGrade
100001         2010    347              Algebra         8
100001         2011    402              Geometry        9
100001         NA      402              Geometry        9
100001         NA      402              Geometry        9
100001         2014    525              Calculus        12

这是我到目前为止得到的：

SELECT
STUDENTID,
YEARID,
MAX(Score) OVER (PARTITION BY StudentID ORDER BY Year) as "MostRecentScore",
MAX(TestName) OVER (PARTITION BY StudentID ORDER BY Year) as "MostRecentTest",
MAX(GradeLevel) OVER (PARTITION BY StudentID  ORDER BY Year) as "MostRecentTestGrade"
FROM TEST_SCORES

但这只返回最新的测试及其相关值：

StudentID     Year    MostRecentScore  MostRecentTest  MostRecentTestGrade
100001         2010    525              Calculus        12
100001         2011    525              Calculus        12
100001         NA      525              Calculus        12
100001         NA      525              Calculus        12
100001         2014    525              Calculus        12

任何帮助将不胜感激。

Answer 1

根据您的示例，我们可以使用gradelevel来确定顺序。如果是这样，您可以使用lag() ignore nulls.但是要做到这一点，首先，我们需要对列gradelevel做一些事情，这有点问题。我添加了gl当年份也为空时为空。其余的很简单：

^{SQLFiddle demo}

select studentid, yearid, 
nvl(score, lag(score) ignore nulls 
over (partition by studentid order by gradelevel)) score,
nvl(testname, lag(testname) ignore nulls 
over (partition by studentid order by gradelevel)) test,
nvl(gl, lag(gl) ignore nulls 
over (partition by studentid order by gradelevel)) grade
from (select ts.*, case when yearid is null then null else gradelevel end gl 
from test_scores ts)
order by studentid, gradelevel

我假设值NA在您的数据中为空。如果不是，您首先必须使用nullif，并且可能对于第score列中的数字使用to_number.将列制作为score，YEAR为varchars是一个坏主意。