时间戳

首页 > 解决方案 > 在 Postgres 中获取具有复杂分区的重复值的连续计数

sql - 在 Postgres 中获取具有复杂分区的重复值的连续计数

问题描述

给定一组 3 列（蓝色列：学生、学期、年级），我需要使用 Postgres 中的查询计算每个学生连续多少个学期具有相同的成绩（绿色列）。

（连续第一个学期平均为 8：consec = 1。第二个 consec 学期平均为 8：consec = 2...）

样本数据小提琴：https ://www.db-fiddle.com/f/v31a5Bpere26tXQb5L1fFJ/0

预期结果：

在此处输入图像描述

我试过使用

ROW_NUMBER() OVER(partition by student)

ROW_NUMBER() OVER(partition by avg_grade)

ROW_NUMBER() OVER(partition by student, avg_grade)

ROW_NUMBER() OVER(partition by student, semester)

但以上都没有得到预期的结果。

非常感谢你的帮助！

标签： sqlpostgresqlwindow-functionspartition-by

解决方案

这是一种间隙和孤岛问题。最简单的方法可能是使用每个年级和学期的序列之间的差异来定义组。然后使用row_number()更多时间：

select g.*,
       row_number() over (partition by student, avg_grade, semester - seqnum order by semester) as consec_avg
from (select g.*,
             row_number() over (partition by student, avg_grade order by semester) as seqnum
      from grades
     ) g;

这是一个 db<>fiddle。

推荐阅读