Использование SQL для определения периодов времени с датами начала и окончания

Вот ответ с использованием всегда интересного Match_Recognize. Обратите внимание, что вам не следует называть столбцы «Код» или «Дата», так как они являются зарезервированными ключевыми словами.

Match_Recognize работает с несколькими строками и пытается сопоставить заданный шаблон. В вашем случае вы пытаетесь сопоставить шаблон начального кода, за которым следует ноль или более недопустимых конечных кодов/других кодов, за которыми следует действительный конечный код.

WITH test_vals AS (
    SELECT 1 as person_ID,'Start_period' as my_code,to_date('Jan 1','mon dd') as my_date FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('Jan 15','mon dd') FROM DUAL
    UNION ALL SELECT 1,'Random_code1',to_date('Feb 15','mon dd') FROM DUAL
    UNION ALL SELECT 1,'Random_code2',to_date('Feb 28','mon dd') FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('March 31','mon dd') FROM DUAL
    UNION ALL SELECT 1,'Start_period',to_date('May 31','mon dd') FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('June 11','mon dd') FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('October 28','mon dd') FROM DUAL
)

SELECT m.person_id,
       m.my_code,
       m.my_date,
       m.period_id
FROM test_vals t
match_recognize(
    PARTITION BY person_id
    ORDER BY my_date
    MEASURES
        match_number() AS period_id /* Return the match number as the period ID */
    ALL ROWS PER match
    pattern (
        start_code /* Match a single start code */
        (invalid_end_code | other_code)* /* Match zero or more invalid end codes or other codes */
        valid_end_code /* Match a single end code */
    )
    define
        start_code AS my_code = 'Start_period', /* Start codes are always valid */
        valid_end_code AS my_code = 'End_period' AND (my_date - FIRST(my_date)) > 28, /* End codes are only valid if they come more than 28 days after the start of the pattern match */
        invalid_end_code AS my_code = 'End_period' AND (my_date - FIRST(my_date)) <= 28,
        other_code AS my_code NOT IN ('Start_period', 'End_period')
) m

Просто подсчитайте количество начальных периодов в каждой строке:

select t.*,
       sum(case when code = 'Start_period' then 1 else 0 end) over (partition by person_id order by date) as period_id
from t;

Это работает для данных, которые вы представили. Формально он не включает другие правила, такие как время между окончанием периодов.

Я бы использовал рекурсивный cte, как здесь:

with c(row_, code, date_, st_date, period, chg) as (
    select row_, code, date_, date_, 1, 0 from t where row_ = 1
    union all
    select t.row_, t.code, t.date_, 
           case when chg = 1 then t.date_ else st_date end, 
           case when chg = 1 then period + 1 else period end, 
           case when t.code = 'End_period' and t.date_ - c.st_date > 28 then 1 else 0 end
      from t join c on t.row_ = c.row_ + 1
    )
select row_, code, date_, period from c

^{демонстрация dbfiddle}

Логика заключается в использовании столбца chg, который управляет изменением периода. Chg устанавливается на 1, когда код End period и дата больше, чем ранее запомненная дата начала. На следующем шаге период увеличивается, chg сбрасывается до нуля и устанавливается новая начальная дата.