将行分组为7天的窗口，中间有间断。

huangapple
go评论150阅读模式
英文:

Assign group to rows by 7 days window with breaks

问题

以下是您提供的内容的中文翻译：

我有一张填满日期的表格，我想将它们分成持续7天的块。我还希望注意块之间的间隙，并将任何在前一个块结束之后出现的日期视为7天周期的开始。

以下是我期望的输出示例：

  1. 日期          	块  
  2. ---------------------
  3. 2023-03-02		1
  4. 2023-03-03		1
  5. 2023-03-04		1
  6. 2023-03-10		2
  7. 2023-03-16		2
  8. 2023-04-04		3
  9. 2023-05-02		4
  10. 2023-05-05		4

我有一个使用while语句的算法。但在批量情况下，它运行得太慢了。是否有其他方法？

  1. create table #dates_to_assign (
  2. 	[日期] date
  3. )
  4. insert into #dates_to_assign values 
  5. ('2023-03-02'), ('2023-03-03'), ('2023-03-04'), ('2023-03-10'),
  6. ('2023-03-16'), ('2023-04-04'), ('2023-05-02'), ('2023-05-05')
  8. create table #dates_assigned (
  9. 	[日期] date,
  10. 	[块] int
  11. )
  13. declare @curr_date date = (select min([日期]) from #dates_to_assign)
  14. declare @block int = 1
  16. WHILE EXISTS(SELECT TOP 1 * from #dates_to_assign)
  17. BEGIN
  18. 	insert into #dates_assigned
  19. 	select [日期], [块] = @block
  20. 	from #dates_to_assign 
  21. 	where DATEDIFF(DAY, @curr_date, [日期]) < 7
  23. 	delete from #dates_to_assign
  24. 	where DATEDIFF(DAY, @curr_date, [日期]) < 7
  26. 	set @curr_date = (select min([日期]) from #dates_to_assign)
  27. 	set @block = @block + 1
  28. END
  30. select *
  31. 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

  1. Date          	Block  
  2. ---------------------
  3. 2023-03-02		1
  4. 2023-03-03		1
  5. 2023-03-04		1
  6. 2023-03-10		2
  7. 2023-03-16		2
  8. 2023-04-04		3
  9. 2023-05-02		4
  10. 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?

  1. create table #dates_to_assign (
  2. 	[Date] date
  3. )
  4. insert into #dates_to_assign values 
  5. (&#39;2023-03-02&#39;), (&#39;2023-03-03&#39;), (&#39;2023-03-04&#39;), (&#39;2023-03-10&#39;),
  6. (&#39;2023-03-16&#39;), (&#39;2023-04-04&#39;), (&#39;2023-05-02&#39;), (&#39;2023-05-05&#39;)
  8. create table #dates_assigned (
  9. 	[Date] date,
  10. 	[Block] int
  11. )
  13. declare @curr_date date = (select min([Date]) from #dates_to_assign)
  14. declare @block int = 1
  16. WHILE EXISTS(SELECT TOP 1 * from #dates_to_assign)
  17. BEGIN
  18. 	insert into #dates_assigned
  19. 	select [Date], [Block] = @block
  20. 	from #dates_to_assign 
  21. 	where DATEDIFF(DAY, @curr_date, [Date]) &lt; 7
  23. 	delete from #dates_to_assign
  24. 	where DATEDIFF(DAY, @curr_date, [Date]) &lt; 7
  26. 	set @curr_date = (select min([Date]) from #dates_to_assign)
  27. 	set @block = @block + 1
  28. END
  30. select *
  31. from #dates_assigned

答案1

得分: 1

I suspect you need to use recursive CTE for this:

  1. with data as (
  2. 	select *
  3. 	, row_number() over(order by date) as counter
  4. 	from 
  5. 	(
  6. 		VALUES	(N'2023-03-02','1')
  7. 		,	(N'2023-03-03','1')
  8. 		,	(N'2023-03-04','1')
  9. 		,	(N'2023-03-10','2')
  10. 		,	(N'2023-03-16','2')
  11. 		,	(N'2023-04-04','3')
  12. 		,	(N'2023-05-02','4')
  13. 		,	(N'2023-05-05','4')
  14. 		,	(N'2023-05-06','4')
  15. 		,	(N'2023-05-07','4')
  16. 		,	(N'2023-05-09','4')
  17. 		,	(N'2023-05-10','5')
  18. 	) t (Date   ,Block  )
  19. )
  20. , blocks as (
  21. 		select date, counter, date as startblock, 1 as block
  22. 		from data
  23. 		where counter = 1
  24. 		union all
  25. 		select d.date, d.counter
  26. 		,	case when datediff(day, startblock, d.date) < 7 then b.startblock else d.date end
  27. 		,	case when datediff(day, startblock, d.date) < 7 then b.block else b.block + 1 end
  28. 		from blocks b
  29. 		inner join data d
  30. 			ON	d.counter = b.counter + 1
  31. 	)
  32. select	*
  33. from	blocks
  34. option (maxrecursion 0);

这段代码通过递归公共表达式（CTE）按照日期和计数器将每个日期"循环"并在每次超出7天期间时重置起始日期。为了提高性能，最好将计数器与索引材料化，但不确定是否可行。

英文:

I suspect you need to use recursive cte for this:

  1. with data as (
  2. 	select *
  3. 	, row_number() over(order by date) as counter
  4. 	from 
  5. 	(
  6. 		VALUES	(N&#39;2023-03-02&#39;,&#39;1&#39;)
  7. 		,	(N&#39;2023-03-03&#39;,&#39;1&#39;)
  8. 		,	(N&#39;2023-03-04&#39;,&#39;1&#39;)
  9. 		,	(N&#39;2023-03-10&#39;,&#39;2&#39;)
  10. 		,	(N&#39;2023-03-16&#39;,&#39;2&#39;)
  11. 		,	(N&#39;2023-04-04&#39;,&#39;3&#39;)
  12. 		,	(N&#39;2023-05-02&#39;,&#39;4&#39;)
  13. 		,	(N&#39;2023-05-05&#39;,&#39;4&#39;)
  14. 		,	(N&#39;2023-05-06&#39;,&#39;4&#39;)
  15. 		,	(N&#39;2023-05-07&#39;,&#39;4&#39;)
  16. 		,	(N&#39;2023-05-09&#39;,&#39;4&#39;)
  17. 		,	(N&#39;2023-05-10&#39;,&#39;5&#39;)
  18. 	) t (Date   ,Block  )
  19. )
  20. , blocks as (
  21. 		select date, counter, date as startblock, 1 as block
  22. 		from data
  23. 		where counter = 1
  24. 		union all
  25. 		select d.date, d.counter
  26. 		,	case when datediff(day, startblock, d.date) &lt; 7 then b.startblock else d.date end
  27. 		,	case when datediff(day, startblock, d.date) &lt; 7 then b.block else b.block + 1 end
  28. 		from blocks b
  29. 		inner join data d
  30. 			ON	d.counter = b.counter + 1
  31. 	)
  32. select	*
  33. from	blocks
  34. 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.

huangapple
  • 本文由 发表于 2023年6月1日 14:39:26
  • 转载请务必保留本文链接：https://go.coder-hub.com/76379268.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定