问用于查找最长连胜的SQL查询

EN

select country, count(*) as streak,
       min(year) as from_year, max(year) as to_year
from (select year, country,
             row_number() over (partition by country order by year) as seqnum
      from t
     ) t
group by country, (year - seqnum);

select country, streak
from (select country, count(*) as streak,
             min(year) as from_year, max(year) as to_year,
             row_number() over (partition by country order by count(*) desc) as seqnum_2
      from (select year, country,
                   row_number() over (partition by country order by year) as seqnum
            from t
           ) t
      group by country, (year - seqnum)
     ) cy
where seqnum_2 = 1;

with t1 as 
(
select 2001 as year,'IND' as cntry from dual union
select 2002,'IND' from dual union
select 2003,'IND' from dual union
select 2004,'AUS' from dual union
select 2005,'AUS' from dual union
select 2006,'SA' from dual union
select 2007,'SA' from dual union
select 2008,'SA' from dual union
select 2009,'IND' from dual union
select 2010,'IND' from dual union
select 2011,'IND' from dual union
select 2012,'IND' from dual union
select 2013,'AUS' from dual union
select 2014,'AUS' from dual union
select 2015,'SA' from dual union
select 2016,'NZ' from dual union
select 2017,'SL' from dual union
select 2018,'IND' from dual) ,
t2 as (select year, cntry, year - row_number() over (partition by cntry order by year) as grpBy from t1 order by cntry),
t3 as (select cntry, count(grpBy) as consWins from t2 group by cntry, grpBy),
res as (select cntry, consWins, row_number() over (partition by cntry order by consWins desc) as rnk from t3)
select cntry, consWins from res where rnk=1;

create table temp_c (competition_year int ,winning_country varchar(4));
insert into temp_c (competition_year, winning_country)
values
(2001,'IND'),
(2002,'IND'),
(2003,'IND'),
(2004,'AUS'),
(2005,'AUS'),
(2006,'SA'),
(2007,'SA'),
(2008,'SA'),
(2009,'IND'),
(2010,'IND'),
(2011,'IND'),
(2012,'IND'),
(2013,'AUS'),
(2014,'AUS'),
(2015,'SA'),
(2016,'NZ'),
(2017,'SL'),
(2018,'IND')
;

create or replace function find_longest_streak(InputStr varChar)
  returns integer
stable
as $$
    MaxStreak=0
    ThisStreak=0
    ThisYearStr=''
    LastYear=0
    for ThisYearStr in InputStr.split(','):
        if int(ThisYearStr) == LastYear + 1:
            ThisStreak+=1
        else:
            if ThisStreak > MaxStreak:
                MaxStreak=ThisStreak
            ThisStreak=1
        LastYear=int(ThisYearStr)
    return max(MaxStreak,1)
$$ language plpythonu;

select winning_country,
       find_longest_streak(listagg(competition_year,',') within group (order by competition_year))
from temp_c
group by winning_country
order by 2 desc
;