SQL练习笔记——统计每个老师的连续上课天数

表d信息如下，查询出每个老师的连续上课天数

解题思路

#1.对每个老师的时间进行窗口排序，得到一个等差序列rn

select teacher,上课日期, 
row_number()over(partition by teacher order by 上课日期) as rn
from 表d;

#2.根据等差数列的特性：

如果是连续的日期，连续减去等差序列，则得到的值是一样的

select t.teacher,t.上课日期, adddate(t.上课日期,interval -t.rn day) as sub_date
from 
(select teacher,上课日期, 
row_number()over(partition by teacher order by 上课日期) as rn
from 表d)t;

#3.开始日期，结束日期，连续天数

with a as
(
select t.teacher,t.上课日期, adddate(t.上课日期,interval -t.rn day) as sub_date
from 
(select teacher,上课日期, 
row_number()over(partition by teacher order by 上课日期) as rn
from 表d)t
)
select teacher,count(sub_date)as 连续天数,
    MIN(上课日期) AS 开始日期,
    MAX(上课日期) AS 结束日期
from a
group by teacher,sub_date;

输出结果：