这在8.0中是相当直接的分组

select patient_id,  min(load_date) s,  max(load_date) e
from (
   select t.*, 
      sum(seq) over(partition by patient_id order by load_date) grp
   from (
      select *,
         lag(monthstart, 1, DATE_ADD(monthstart, interval -1 month)) over(partition by patient_id order by load_date) <> DATE_ADD(monthstart, interval -1 month) seq
      from (
            select *, DATE_ADD(load_date, interval - day(load_date)+1 day) monthstart
            from events
           )t
       ) t
    )t
group by patient_id, grp
order by patient_id, min(load_date)

将其翻译回5.7：

select patient_id,  min(load_date) s,  max(load_date) e
from (
     select *, 
        (select sum(seq)
         from(
             select *, 
                not exists(
                   select 1 
                   from events e2
                   where DATE_ADD(t.monthstart, interval -1 month) = DATE_ADD(e2.load_date, interval - day(e2.load_date)+1 day) and t.patient_id = e2.patient_id
                          ) seq
             from (
                  select *, DATE_ADD(load_date, interval - day(load_date)+1 day) monthstart
                  from events
                  ) t
              ) t 
         where t.patient_id = e.patient_id and t.load_date <= e.load_date) grp
     from events e
     ) t
group by patient_id, grp
order by patient_id, min(load_date)

db<>小提琴