SQL 查询，用于计算最近一年的连续年份总数

Question

我有一个表温度：

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

我想计算从最近一年开始的连续年份总数。 结果应如下所示：

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

Answer 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

例如，对于 ID=1：

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

只要没有间隙，两个序列的增加就相同。

现在检查相等的序列并计算行数：

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

编辑：

根据你当年的零评论：

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

Answer 2

您可以使用铅并按以下方式获取此计数：

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)

输出如下：

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

Answer 3

您可以使用窗口函数执行此操作：

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;

这假定"最近一年"是每个 id。

Answer 4

戈登·利诺夫， 你的代码很棒！ 您的代码从最近一年中提取连续年份。 我修改了它以拉出连续年份的总最大。 张贴在这里，以防其他人需要它：

--overall max consecutive years
select id,max(yr_cnt) max_consecutive_years
from (
select id, grp,count(seqnum) yr_cnt
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
group by id,grp) t2
group by id;