时间戳

首页 > 解决方案 > Oracle SQL - 计算正在运行的最新值和关联值

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

问题描述

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

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

任何帮助将不胜感激。

标签： sqloracleoracle-sqldeveloper

解决方案

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

SQLFiddle 演示

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像varchars一样制作列YEAR是个坏主意。

推荐阅读