sql - 查询以查找自连续观察列的最后一个值以来的天数 - PostgreSQL

Question

给定下表，任务是编写一个 SQL 查询(在 PostgreSQL/MySQL 中 - PostgreSQL 首选)返回自连续观察到列的最后一个值以来的天数:

表:测试结果

因此，对于“StudentID=1001”和“TestID=2001”的学生，由于上次观察到“A”级是在 2018-12-04 00:00:00，因此查询必须返回“日期”差异对于 ID=5014 和 ID=5012 的记录；这是 DATE_PART('day', 2018-12-04 00:00:00 - 2018-11-23 00:00:00)

谁能指导我如何编写查询？该表将包含数百万条记录，因此性能受到高度关注。如果需要，还可以建议更好的表模式结构。

Answer 1

您可以仅使用窗口函数获取您关心的行:

select tr.*
from (select tr.*,
             max(tr.dated) filter (where tr.grade <> tr.last_grade) over (partition by tr.studentID, tr.testID) as max_other_grade_date
      from (select tr.*,
                   first_value(tr.grade) over (partition by tr.studentID, tr.testID order by tr.dated desc) as last_grade
            from testresult tr
          ) tr
     ) tr
where max_other_grade_date is null or dated > max_other_grade_date ;

一个简单的聚合然后得到你想要的天数:

select tr.studentID, tr.testID,
       max(dated) - min(dated)
from (select tr.*,
             max(tr.dated) filter (where tr.grade <> tr.last_grade) over (partition by tr.studentID, tr.testID) as max_other_grade_date
      from (select tr.*,
                   first_value(tr.grade) over (partition by tr.studentID, tr.testID order by tr.dated desc) as last_grade
            from testresult tr
          ) tr
     ) tr
where max_other_grade_date is null or dated > max_other_grade_date    
group by tr.studentID, tr.testID;

Here是一个 SQL fiddle 。