时间戳

首页 > 解决方案 > 查询以查找自连续观察列的最后一个值以来的天数 - PostgreSQL

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

问题描述

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

表：测试结果

在此处输入图像描述

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

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

标签： sqlpostgresql

解决方案

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

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;

这是一个 SQL 小提琴。

推荐阅读