Sql query to Count Total Consecutive Years from la

问题:

I have a table Temp:

 CREATE TABLE Temp 
( 
  [ID]  [int],
  [Year]  [INT],
 )
**ID    Year**
1 2016
1   2016
1   2015
1   2012
1   2011
1   2010
2   2016
2   2015
2   2014
2   2012
2   2011
2   2010
2   2009
3   2016
3   2015
3   2004
3   1999
4   2016
4   2015
4   2014
4   2010
5   2016
5   2014
5   2013

I want to calculate the total consecutive years starting from the most recent Year. Result should look like this:

ID  Total Consecutive Yrs
1   2
2   3
3   2
4   3
5   1

回答1:

select ID,
   -- returns a sequence without gaps for consecutive years
   first_value(year) over (partition by ID order by year desc) - year +1 as x, 
   -- returns a sequence without gaps
   row_number() over (partition by ID order by year desc) as rn
from Temp

e.g. for ID=1:

1   2016    1   1
1   2015    2   2
1   2012    5   3
1   2011    6   4
1   2010    7   5

As long as there's no gap, both sequences increase the same.

Now check for equal sequences and count the rows:

with cte as 
 (
   select ID,
      -- returns a sequence without gaps for consecutive years
      first_value(year) over (partition by ID order by year desc) - year + 1 as x, 
      -- returns a sequence without gaps
      row_number() over (partition by ID order by year desc) as rn
   from Temp

 ) 
select ID, count(*)
from cte
where x = rn  -- no gap
group by ID

Edit:

Based on your year zero comment:

with cte as 
 (
   select ID, year,
      -- returns a sequence without gaps for consecutive years
      first_value(year) over (partition by ID order by year desc) - year + 1 as x, 
      -- returns a sequence without gaps
      row_number() over (partition by ID order by year desc) as rn
   from Temp

 ) 
select ID, 
   -- remove the year zero from counting
   sum(case when year <> 0 then 1 else 0 end)
from cte
where x = rn
group by ID

回答2:

You can use lead and get this counts as below:

Select top (1) with ties Id, RowN as [Total Consecutive Years] from (
    Select *, Num = case when ([year]- lead(year) over(partition by Id order by [Year] desc) > 1) then 0 else 1 end 
        , RowN = Row_Number() over (partition by Id order by [Year] desc)
    from temp
) a
where a.Num = 0
order by row_number() over(partition by Id order by RowN)

Output as below:

+----+-------------------------+
| Id | Total Consecutive Years |
+----+-------------------------+
|  1 |                       2 |
|  2 |                       3 |
|  3 |                       2 |
|  4 |                       3 |
|  5 |                       1 |
+----+-------------------------+

回答3:

You can do this using window functions:

select id, count(distinct year)
from (select t.*,
             dense_rank() over (partition by id order by year + seqnum desc) as grp
      from (select t.*,
                   dense_rank() over (partition by id order by year desc) as seqnum
            from temp t
           ) t
     ) t
where grp = 1
group by id;

This assumes that "most recent year" is per id.