select
ifnull(l2, ‘-1’) yess
from
(select
measure_status ,
lag (measure_status ,2) over (partition by patient_id order by create_time ) l2
from no_bug
) a
SELECT
ifnull( l1, ‘-1’ ) noo
FROM
( SELECT measure_status,
lag ( measure_status, 2 ) over ( PARTITION BY patient_id ORDER BY measure_time ) l1
FROM yes_bug ) b