今天是同学提到的需求即

找到连续三次提交都是满分(80)的用户

怎么整呢？

先上完成代码

select *,count(*)
from (
select
    *,
    row_number() over (
        partition by   uid
        order by  start_time
        )   -  row_number() over (
        partition by   score,uid
        order by  start_time
        )   ran
from exam_record) e
group by uid,score,ran
having
    score = 80 and count(*) = 3;

1.先用窗口函数对用户分组对时间进行排序

select
    *,
    row_number() over (
        partition by   uid
        order by  start_time
        )  ranking
from exam_record

2.再用窗口函数对用户和成绩分组，对时间进行排序

select
    *,
    row_number() over (
        partition by   score,uid
        order by  start_time
        )  ranking2
from exam_record

3.对前两个rank减

注意：相减是根据id来进行列之间的减法

select
    *,
    row_number() over (
        partition by   uid
        order by  start_time
        )   -  row_number() over (
        partition by   score,uid
        order by  start_time
        )   ran
from exam_record

这样连续出现的相同的分数就被筛选出来了，至于说用户不一样怎么办，那就再按照用户分组就是不同的两个了

3.分组

这里为了看得更清晰就rank1，rank2都贴出来

select *,count(*)
from (
select
    *,row_number() over (
        partition by   uid
        order by  start_time
        )  ranking,
    row_number() over (
        partition by   score,uid
        order by  start_time
        )  ranking2,
    row_number() over (
        partition by   uid
        order by  start_time
        )   -  row_number() over (
        partition by   score,uid
        order by  start_time
        )   ran
from exam_record) e
group by uid,score,ran

4.结束

一个having就可以，如果是连续相同n个数字只要改变count就行，或者所有数字改变score

having
    score = 80 and count(*) = 3;