SQL Server Assign group to rows by 7 days window with breaks

lh80um4z 于 2023-06-04 发布在其他

关注(0)|答案(1)|浏览(120)

I have a table filled with dates and I want to group them in 7 day lasting blocks. I also want to mind the gaps between the blocks and treat any date, that comes after preceding block end, as a start of a 7 day period.

Below is my desired output example

Date            Block  
---------------------
2023-03-02      1
2023-03-03      1
2023-03-04      1
2023-03-10      2
2023-03-16      2
2023-04-04      3
2023-05-02      4
2023-05-05      4

I have an algorithm using the while statement. But its not usable in batch situations as it runs way too slow. Is there some other way?

create table #dates_to_assign (
    [Date] date
)
insert into #dates_to_assign values 
('2023-03-02'), ('2023-03-03'), ('2023-03-04'), ('2023-03-10'),
('2023-03-16'), ('2023-04-04'), ('2023-05-02'), ('2023-05-05')

create table #dates_assigned (
    [Date] date,
    [Block] int
)

declare @curr_date date = (select min([Date]) from #dates_to_assign)
declare @block int = 1

WHILE EXISTS(SELECT TOP 1 * from #dates_to_assign)
BEGIN
    insert into #dates_assigned
    select [Date], [Block] = @block
    from #dates_to_assign 
    where DATEDIFF(DAY, @curr_date, [Date]) < 7

    delete from #dates_to_assign
    where DATEDIFF(DAY, @curr_date, [Date]) < 7

    set @curr_date = (select min([Date]) from #dates_to_assign)
    set @block = @block + 1
END

select *
from #dates_assigned

sql-server

来源：https://stackoverflow.com/questions/76379268/assign-group-to-rows-by-7-days-window-with-breaks

1条答案

按热度按时间

e0bqpujr1#

I suspect you need to use recursive cte for this:

with data as (
    select *
    , row_number() over(order by date) as counter
    from 
    (
        VALUES  (N'2023-03-02','1')
        ,   (N'2023-03-03','1')
        ,   (N'2023-03-04','1')
        ,   (N'2023-03-10','2')
        ,   (N'2023-03-16','2')
        ,   (N'2023-04-04','3')
        ,   (N'2023-05-02','4')
        ,   (N'2023-05-05','4')
        ,   (N'2023-05-06','4')
        ,   (N'2023-05-07','4')
        ,   (N'2023-05-09','4')
        ,   (N'2023-05-10','5')
    ) t (Date   ,Block  )
)
, blocks as (
        select date, counter, date as startblock, 1 as block
        from data
        where counter = 1
        union all
        select d.date, d.counter
        ,   case when datediff(day, startblock, d.date) < 7 then b.startblock else d.date end
        ,   case when datediff(day, startblock, d.date) < 7 then b.block else b.block + 1 end
        from blocks b
        inner join data d
            ON  d.counter = b.counter + 1
    )
select  *
from    blocks
option (maxrecursion 0);

This "loops" each date by counter and reset the start date each time you leave the 7 day period. For performance you'd do well to materialize the counter with index, but not sure if it's possible.

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Assign group to rows by 7 days window with breaks

1条答案

相关问题

热门标签

最新问答