根据时间窗口选择行

Question

我有一张物品表：

create table #items (
        ItemId int identity(1,1) primary key,
        GroupId int null,
        CreatedTimestamp datetime)

insert into #items values   (1, '2015-01-01'),
                            (2, '2015-02-02'),
                            (3, '2015-03-03'),
                            (1, '2015-06-01'),
                            (2, '2015-09-02'),
                            (2, '2015-10-02'),
                            (1, '2016-05-05'),
                            (1, '2016-07-16'),
                            (2, '2016-03-01')

我想检索这些项目，其规则是每个GroupId在每12个月内只能有一行(从每个GroupId的最小CreatedTimestamp开始)。

因此，对于上述数据集，将返回第1、2、3、6和8行：

ItemId GroupId CreatedTimestamp
------ ------- -----------
1      1       2015-01-01  - yes (1st in group 1)
2      2       2015-02-02  - yes (1st in group 2)
3      3       2015-03-03  - yes (1st in group 3)
4      1       2015-06-01  - no  (within 12 months of item in group 1)
5      2       2015-09-02  - no  (within 12 months of item in group 2)
6      2       2015-10-02  - no  (within 12 months of item in group 2)
7      1       2016-05-05  - yes (over 12 months since last returned item in group 1)
8      1       2016-07-16  - no  (within 12 months of item in group 1)
9      2       2016-03-01  - yes (over 12 months since last returned item in group 2)

我已经用游标(下面)找到了一个解决方案，但不知道是否有一个基于集合的解决方案，因为多年来，这个表将增长到数百万行。

declare @lastTable table (
    groupId int not null,
    lastItemDate datetime not null
)

declare @groupId int
declare @timestamp datetime
declare @lastgroupTime datetime

declare caseCursor CURSOR for select groupId, CreatedTimestamp from #items order by CreatedTimestamp

open caseCursor

fetch from caseCursor into @groupId, @timestamp

while @@FETCH_STATUS = 0
begin
    select  @lastGroupTime = max(lastItemDate)
    from    @lastTable
    where   groupId = @groupID

    if  (@lastgroupTime is null or  dateadd(yy, 1, @lastGroupTime) < @timestamp)
    begin
        insert into @lastTable values (@groupId, @timestamp)
    end

    fetch from caseCursor into @groupId, @timestamp
end

close caseCursor
deallocate caseCursor

select * from @lastTable

Answer 1

首先选择每个组id的最小创建日期。然后对年数进行编号，并从每个期间中选择一行。不幸的是，Server并不擅长计算两个日期之间的年数。大概365天/年就足够了：

select i.*
from (select i.*,
             row_number() over (partition by groupid, datediff(day, minct, createdtimestamp) / 365 
                                order by CreatedTimestamp
                               ) as seqnum
      from (select i.*, min(CreatedTimestamp) over (partition by groupid) as minct
            from #items i
           ) i
     ) i
where seqnum = 1;