SQL Server 按动态日期范围分组

nqwrtyyt  于 2023-02-03  发布在  其他
关注(0)|答案(1)|浏览(129)

我有一个包含ID、日期和值的表。我希望始终根据ID合并90天窗口内的记录。在下面的示例中,这些行用相同的颜色标记。

最终结果应如下所示:

RowId为1的条目为ID 133741打开90天的窗口。RowId 2和3位于此窗口中,因此应与RowId 1聚合在一起。
RowId 4将与2和3一起处于90天窗口中,但由于它在1的窗口之外,因此不应再与它们聚集,而应将其视为新的90天窗口的开始。由于此窗口中没有其他条目,因此它仍为一行。
第5行的日期明显超出了其他条目的90天窗口,因此也单独汇总。就像第6行一样,因为这是一个不同的ID。
下面是一些示例代码:

create table #Table(RowId int, ID nvarchar(255) , Date date, Amount numeric(19,1));
insert into #Table values

(1,'133742',    '2021-07-30',   1.00    ),
(2,'133742',    '2021-08-05',   3.00    ),
(3,'133742',    '2021-08-25',   10.00   ),
(4,'133742',    '2021-11-01',   7.00    ),
(5,'133742',    '2022-08-25',   11.00   ),
(6,'133769',    '2021-11-13',   9.00    );

我尝试使用windowfunctions和CTE,但无法找到包含所有需求的方法

dfddblmv

dfddblmv1#

使用窗口函数first_value() over(),我们计算距离(以天为单位)除以90,以获得导出的Grp

    • 示例**
with cte as (
Select *
      ,Grp = datediff(day,first_value(Date) over (partition by id order by date) ,date) / 90
 from #Table
 )
 Select ID 
       ,Date = min(date)
       ,Amount = sum(Amount)
 From  cte
 Group By ID,Grp
 Order by ID,min(date)
    • 结果**
ID      Date        Amount
133742  2021-07-30  14.0
133742  2021-11-01  7.0
133742  2022-08-25  11.0
133769  2021-11-13  9.0

相关问题