with tmp as ( select a.*, rownum() as sid, row_number() over (partition by name order by year) as rid from nba )select name,min(year) as startyear,max(year) as endyear from tmp group by name,rid-sid
可以考虑用层级函数,例如SELECT NAME, MIN(YEAR), MAX(YEAR) FROM (SELECT a.*, LEVEL lv, MAX(LEVEL) OVER(PARTITION BY ROWID) max_lv, connect_by_root(YEAR) root_year FROM nba a CONNECT BY (YEAR - 1 = PRIOR YEAR AND NAME = PRIOR NAME)) WHERE lv = max_lv GROUP BY NAME, root_year
用oracle 的 lead 或者 lag分析函数测试一下
最初的想法是想把红框内的记录分为一组,但这需要一个标志位,将year-rank置为标志位,想了许久 WITH a AS (SELECT NAME,YEAR,dense_rank()over(PARTITION BY NAME ORDER BY YEAR) rank FROM nba), b AS (SELECT NAME,MIN(YEAR) startyear,MAX(YEAR) ENDYEAR FROM a GROUP BY NAME,YEAR-rank) SELECT * FROM b WHERE startyear!=endyear可以再多些记录,然后测试一下
with nba as (select rownum id, '湖人' name, 2020 + rownum year from dual connect by rownum <= 2 union all select 3, '马刺', 2023 from dual union all select rownum + 3, '热火', rownum + 2023 from dual connect by rownum <= 3 union all select 7, '火箭', 2027 from dual union all select rownum + 7, '湖人', rownum + 2027 from dual connect by rownum <= 2) select name, min(year), max(year) from nba t start with not exists (select 1 from nba where name = t.name and year = t.year - 1) connect by prior name = name and prior year = year - 1 group by name, connect_by_root id having count (1) > 1 order by 2;数据量大的话在Year字段上建索引
with tmp as
(
select a.*,
rownum() as sid,
row_number() over (partition by name order by year) as rid
from nba
)select name,min(year) as startyear,max(year) as endyear
from tmp
group by name,rid-sid
FROM (SELECT a.*,
LEVEL lv,
MAX(LEVEL) OVER(PARTITION BY ROWID) max_lv,
connect_by_root(YEAR) root_year
FROM nba a
CONNECT BY (YEAR - 1 = PRIOR YEAR AND NAME = PRIOR NAME))
WHERE lv = max_lv
GROUP BY NAME, root_year
最初的想法是想把红框内的记录分为一组,但这需要一个标志位,将year-rank置为标志位,想了许久
WITH a AS (SELECT NAME,YEAR,dense_rank()over(PARTITION BY NAME ORDER BY YEAR) rank FROM nba),
b AS (SELECT NAME,MIN(YEAR) startyear,MAX(YEAR) ENDYEAR FROM a GROUP BY NAME,YEAR-rank)
SELECT * FROM b WHERE startyear!=endyear可以再多些记录,然后测试一下
(select rownum id, '湖人' name, 2020 + rownum year
from dual
connect by rownum <= 2
union all
select 3, '马刺', 2023
from dual
union all
select rownum + 3, '热火', rownum + 2023
from dual
connect by rownum <= 3
union all
select 7, '火箭', 2027
from dual
union all
select rownum + 7, '湖人', rownum + 2027 from dual connect by rownum <= 2)
select name, min(year), max(year)
from nba t
start with not exists (select 1
from nba
where name = t.name
and year = t.year - 1)
connect by prior name = name
and prior year = year - 1
group by name, connect_by_root id
having count (1) > 1
order by 2;数据量大的话在Year字段上建索引