查找表中每个ID的最大连续年份(Oracle SQL) [英] Find the maximum consecutive years for each ID's in a table(Oracle SQL)

问题描述

我正在尝试解决一个问题，该问题是如何在一系列记录中查找连续年份的最大数量.在下面的示例中:

I am trying to solve a problem of how to find the maximum count of consecutive years in a series of records. In the following example:

ID  Year
1 1993
1 1994
1 1995
1 1995
1 2001
1 2002
2 1993
2 1995
2 1996
2 1996
2 1998
2 1999
2 2000
2 2001
2 2001

我的结果集应类似于

id   count
1      3
2      4

我必须用oracle SQL编写代码.

I have to write the code in oracle SQL.

推荐答案

这将产生您想要的结果:

This will produce your desired result:

select
  id,
  ayear,
  byear,
  yeardiff
from
(
  select
    a.id,
    a.year ayear,
    b.year byear,
    (b.year - a.year)+1 yeardiff,
    dense_rank() over (partition by a.id order by (b.year - a.year) desc) rank
  from
    years a
    join years b on a.id = b.id 
        and b.year > a.year
  where
    b.year - a.year = 
      (select count(*)-1
         from years a1
        where a.id = a1.id
             and a1.year between a.year and b.year)
)
where
  rank = 1

编辑已更新，以显示最长延伸的开始/结束年份.

EDIT updated to display start/end years of longest stretch.

SQLFiddle

这篇关于查找表中每个ID的最大连续年份(Oracle SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！