英文:
Assign group to rows by 7 days window with breaks
问题
以下是您提供的内容的中文翻译：
我有一张填满日期的表格，我想将它们分成持续7天的块。我还希望注意块之间的间隙，并将任何在前一个块结束之后出现的日期视为7天周期的开始。
以下是我期望的输出示例：
日期 块
---------------------
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
我有一个使用while语句的算法。但在批量情况下，它运行得太慢了。是否有其他方法？
create table #dates_to_assign (
[日期] 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,
[块] int
)
declare @curr_date date = (select min([日期]) from #dates_to_assign)
declare @block int = 1
WHILE EXISTS(SELECT TOP 1 * from #dates_to_assign)
BEGIN
insert into #dates_assigned
select [日期], [块] = @block
from #dates_to_assign
where DATEDIFF(DAY, @curr_date, [日期]) < 7
delete from #dates_to_assign
where DATEDIFF(DAY, @curr_date, [日期]) < 7
set @curr_date = (select min([日期]) from #dates_to_assign)
set @block = @block + 1
END
select *
from #dates_assigned
英文:
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
答案1
得分: 1
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);
这段代码通过递归公共表达式（CTE）按照日期和计数器将每个日期"循环"并在每次超出7天期间时重置起始日期。为了提高性能，最好将计数器与索引材料化，但不确定是否可行。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论