计算最大连胜数

select
id,
max(rn-nvl(lag_rn,0)) x
from
(
select
*,
lag(rn) over (partition by id order by rn) lag_rn
from
(
select
*,
lead(fa_su) over (partition by id order by ts) lead_fs,
row_number() over (partition by id order by ts) rn
from
du_dw_dev.tmp_data3
) where fa_su <> nvl(lead_fs,'n')
) where fa_su='win' group by id;

±-----------±-----------±-----------+
| id | ts | fa_su |
±-----------±-----------±-----------+
| 1 | 2022-01-12 | win |
| 1 | 2022-01-13 | win |
| 1 | 2022-01-14 | win |
| 1 | 2022-01-15 | lose |
| 1 | 2022-01-16 | win |
| 1 | 2022-01-17 | win |
| 2 | 2022-01-17 | lose |
| 2 | 2022-01-18 | lose |
| 3 | 2022-01-18 | win |
±-----------±-----------±-----------+