数据库面试开窗函数row_number()的使用

请写出一条SQL语句，查询出在此期间连续获取冠军的有哪些,其连续的年份的起止时间是多少

create table NBA(
    team VARCHAR(200),
    y NUMBER(4)
);

insert into NBA(team, y) values ('活塞',1990);
insert into NBA(team, y) values ('公牛',1991);
insert into NBA(team, y) values ('公牛',1992);
insert into NBA(team, y) values ('公牛',1993);
insert into NBA(team, y) values ('火箭',1994);
insert into NBA(team, y) values ('火箭',1995);
insert into NBA(team, y) values ('公牛',1996);
insert into NBA(team, y) values ('公牛',1997);
insert into NBA(team, y) values ('公牛',1998);
insert into NBA(team, y) values ('马刺',1999);
insert into NBA(team, y) values ('湖人',2000);
insert into NBA(team, y) values ('湖人',2001);
insert into NBA(team, y) values ('湖人',2002);
insert into NBA(team, y) values ('马刺',2003);
insert into NBA(team, y) values ('活塞',2004);
insert into NBA(team, y) values ('马刺',2005);
insert into NBA(team, y) values ('热火',2006);
insert into NBA(team, y) values ('马刺',2007);
insert into NBA(team, y) values ('凯尔特人',2008);
insert into NBA(team, y) values ('湖人',2009);
insert into NBA(team, y) values ('湖人',2010);
commit;

        

-- 按照team分组,按照年份排序
select NBA.*, ROW_NUMBER() over (PARTITION BY team ORDER BY y ) r from NBA;
-- 利用夺冠年份减去排名 得出连续分组条件
select NBA.*, y-ROW_NUMBER() over (PARTITION BY team ORDER BY y ) r from NBA;
-- 通过子查询 连续条件分组  得出最后结果
select team, min(y), max(y)
from (select NBA.*, y - ROW_NUMBER() over (PARTITION BY team ORDER BY y ) r from NBA)
group by team, r
having count(1) >= 2
order by min(y) asc;

结果如下