时间戳

首页 > 解决方案 > 用于查找最长连胜的 SQL 查询

sql - 用于查找最长连胜的 SQL 查询

问题描述

我有如下数据-

Year,winning_country
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

这里的问题是找出每个国家最长的连胜纪录，期望的输出将如下所示 -

Country,no_of_wins
IND,4
AUS,2
SA,3
SL,1
NZ,1

有人可以在这里帮忙。

标签： sqlamazon-redshift

解决方案

这是一个间隙和孤岛问题，但最简单的方法是从年份中减去一个序列。因此，要获取所有序列：

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;

我更喜欢用row_number()它来获得最长的连胜记录，因为它可以让你也得到它发生的年份。

推荐阅读