sql - 如何重写在行中给出特定值数量的查询以避免某些值并进一步计算其他值？

Question

我有一个查询，它为我提供了行中每个学生的 5 分(如果学生没有任何其他成绩):

select distinct on (student, class) scg.*
from (select student, class, grade, count(*) as cnt,
         min(gradeDate), max(gradeDate), min_gradeDate, max_gradeDate
  from (select t.*,
               row_number() over (partition by student, class, grade order by gradeDate) as seqnum_scg,
               row_number() over (partition by student, class order by gradeDate) as seqnum_sc
        from t
       ) t
  where grade = 5
  group by student, class, grade, (seqnum_sc - seqnum_scg)
 ) scg
order by student, class, cnt desc;

原来的问题在这里解释: How to count data with specific values and for specific user/person (in row)?

但现在我想用另一个功能扩展此查询。除非某些学生的成绩为 4/3/2/1，否则此计数器会为我提供最大值，但现在我希望它:

• 如果学生有 4 或 3 年级，则停止计数，并在学生再获得 5 分时重新开始(使用之前的最大值)我的意思是:

实际查询:5, 5, 5, 4, 3, 5, 5, 2 --> max = 3

新查询:5, 5, 5, 4, 3, 5, 5, 2 --> 给我最大值 = 5，因为 4 和 3 停止计数器并在用户得到另一个 5 时启动它

• 如果学生获得 2 年级或 1 年级则停止计数(并在获得 2/1 年级之前给我最大值)所以查询现在对除 5 年级以外的每个年级都做同样的事情，但我只希望它用于 2 年级或更低年级(即我可以在查询中指定)。

有人可以帮我重写@Gordon Linoff 给出的第二个查询，让它像那样工作并告诉我发生了什么变化吗？

编辑:根据要求编辑示例:

id student grade class gradeDate
1     1      5     1       2017-03-03
2     1      5     1       2017-03-04
3     1      1     1       2017-03-05
4     1      5     1       2017-03-06
5     1      5     1       2017-03-07
6     1      5     1       2017-03-08
7     1      1     1       2017-03-09
8     2      5     2       2017-03-03
9     3      5     3       2017-03-03
10    4      5     4       2017-03-03
11    4      5     4       2017-03-04
12    4      4     4       2017-03-05
13    4      3     4       2017-03-06    
14    4      5     4       2017-03-07
15    4      5     4       2017-03-08
16    5      5     5       2017-03-01
17    5      5     5       2017-03-03
18    5      5     5       2017-03-04
19    5      5     5       2017-03-05
20    5      5     5       2017-03-06
21    5      2     5       2017-03-07
22    5      5     5       2017-03-08
23    5      5     5       2017-03-09

学生一:最大值 = 3

学生二:最大值 = 1

学生三:最大值 = 1

学生四:max = 4(4 年级和 3 年级停止计数器，但不要重置它)

学生五:最大值 = 5(因为 2 年级重置计数器，缺少日期的分数2017-03-02 对计数器来说不是问题)

Answer 1

其中一种方法可以使用 2 个子查询和一个分析函数

演示:http://sqlfiddle.com/#!15/74b71/10

SELECT student, max( xxx )
FROM (
   SELECT student, grp_nbr, count(CASE WHEN grade = 5 THEN 1 END) As xxx
   FROM (
      SELECT *,
             SUM (  CASE WHEN grade in (1,2)
                         THEN 1 ELSE 0
                    END 
             ) OVER (Partition by student Order By gradeDate ) As grp_nbr
      FROM table1
   ) x
   GROUP BY student, grp_nbr
) y
GROUP BY student
ORDER BY student

---

| student | max |
|---------|-----|
|       1 |   3 |
|       2 |   1 |
|       3 |   1 |
|       4 |   4 |
|       5 |   5 |