Requête SQL pour trouver la plus longue séquence de victoires

Si Redshift prend en charge la fonction analytique, ci-dessous serait la requête.

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;

J'espère que cela vous aidera.

Voici une solution qui tire parti de l'utilisation de Redshift Python UDF.

Il peut y avoir des moyens plus simples d'y parvenir, mais c'est un bon exemple de la façon de créer un simple UDF.

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
;

On dirait un problème de lacunes et d'îles.

Le SQL ci-dessous calcule un classement basé sur 2 row_number a >.

Ensuite, c'est juste une question de regroupement.

SELECT q2.Country, MAX(q2.no_of_wins) AS no_of_wins
FROM
(
  SELECT q1.winning_country as Country, 
  COUNT(*) AS no_of_wins
  FROM
  (
    SELECT t.Year, t.winning_country,
     (ROW_NUMBER() OVER (ORDER BY t.Year ASC) -
      ROW_NUMBER() OVER (PARTITION BY t.winning_country ORDER BY t.Year)) AS rnk
    FROM yourtable t
  ) q1
  GROUP BY q1.winning_country, q1.rnk
) q2
GROUP BY q2.Country
ORDER BY MAX(q2.no_of_wins) DESC

Il s'agit d'un problème de lacunes et d'îlots, mais la méthode la plus simple consiste à soustraire une séquence de l'année. Donc, pour obtenir toutes les séquences:

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;

Pour obtenir la plus longue par pays, agréger à nouveau ou utiliser les fonctions de fenêtre:

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);

Je préfère en utilisant row_number () pour obtenir la plus longue séquence, car cela vous permet également d'obtenir les années où cela s'est produit.

Que diriez-vous de quelque chose comme ...

SELECT
   winning_country,
   COUNT(*)
GROUP BY winning_country
HAVING MAX(year) - MIN(year) = COUNT(year) - 1

Cela suppose qu'il n'y a pas d'entrées en double.

La création d'une abstraction de session fait l'affaire:

WITH winning_changes AS (
  SELECT *,
         CASE WHEN LAG(winning_country) OVER (ORDER BY year) <> winning_country THEN 1 ELSE 0 END AS same_winner
  FROM winners
),

sequences AS (
  SELECT *,
         SUM(same_winner) OVER (ORDER BY year) AS winning_session
  FROM winning_changes
),

streaks AS (
  SELECT winning_country AS country,
         winning_session,
         COUNT(*) streak
  FROM sequences
  GROUP BY 1,2
)

SELECT country,
       MAX(streak) AS no_of_wins
FROM streaks
GROUP BY 1;