无尽日期列表生成器

我有一个表中的列，其中有很多不同的日期。我知道如何从任何日期生成一个无休止的周数列表，但只有一个日期必须硬编码。

例如，如果我输入"2000年1月1日"的日期，并且我想要该日期之后的所有周，那么我会得到该日期之后每周的一列(见下文)：

输入字段：

1/1/2000

输出列：

1/1/2000
1/8/2000
1/15/2000
1/22/2000
1/29/2000

等等

然而，这只是一次约会。我所要求的是做与上面相同的事情，但显示所有日期的下一个X周数(在本例中为5周)(见下文)：

输入栏：

1/1/2000
1/1/2001
1/1/2002
1/1/2003
1/1/2004

输出列：

1/1/2000
1/8/2000
1/15/2000
1/22/2000
1/29/2000
1/1/2001
1/8/2001
1/15/2001
1/22/2001
1/29/2001
1/1/2002
1/8/2002
1/15/2002
1/22/2002
1/29/2002
1/1/2003
1/8/2003
1/15/2003
1/22/2003
1/29/2003
1/1/2004
1/8/2004
1/15/2004
1/22/2004
1/29/2004

这个例子的输入栏只有5个日期，但实际上我有数百个日期的栏，这不仅仅是未来5周，更像是未来的5年

使用上面的解释，如何为列中的所有日期生成此定期列表不仅是列中第一个日期的5周(我可以这样做)，而且是列中每个日期的接下来的5周。

使用递归cte:

with cte as (
select convert(date, v.dte) as dte, 1 as lev
from (values ('2000-01-01'), ('2001-01-01'), ('2002-01-01'), ('2003-01-01'), ('2004-01-01')
) v(dte)
union all
select dateadd(day, 7, dte), lev + 1
from cte
where lev < 5
)
select dte
from cte;

这是一个数据库<>不停摆弄

天哪，伙计们。。。不要对这样的事情(任何有增量的事情)使用rTE(递归CTE)。即使是小行数，它们也会因为资源使用率和较差的性能而令人讨厌。一个写得好的WHILE循环将击败它。

有一些方法可以打开rCTE、循环和其他形式的RBAR的大门。这篇文章太长了，不能在这里重复，但这里有一篇关于性能和资源使用情况的文章。

隐藏RBAR：用递归CTE的计数

首先，创建这个有用的函数

首先，我们将需要一个被称为"T-SQL的瑞士军刀"的工具的帮助。它是一个iTVF(内联表值函数)形式的"计数表"，以Itzik Ben Gan首次规定和描述的方式构建。这是函数(fnTally)的代码。文档比代码长得多。

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 10 Quadrillion.
Usage:
--===== Syntax example
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
@ZeroOrOne will internally conver to a 1 for any number other than 0 and a 0 for a 0.
@MaxN has an operational domain from 0 to 4,294,967,296. Silent truncation occurs for larger numbers.
Please see the following notes for other important information
Notes:
1. This code works for SQL Server 2008 and up.
2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works.
https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
3. To start a sequence at 0, @ZeroOrOne must be 0. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
4. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 4,294,967,296. If a larger
number is used, the function will silently truncate after that max. If you actually need a sequence with that many
or more values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending sort is
required, use code similar to the following. Performance will decrease by about 27% but it's still very fast 
especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT; 
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1 
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is implicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
9. This will return 1-10,000,000 to a bit-bucket variable in about 986ms.
This will return 0-10,000,000 to a bit-bucket variable in about 1091ms.
This will return 1-4,294,967,296 to a bit-bucket variable in about 9:12( mi:ss).
Revision History:
Rev 00 - Unknown     - Jeff Moden 
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden 
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden 
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 07 Sep 2013 - Jeff Moden 
- Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment. 
This will also make it much more difficult for someone to actually get silent truncation in the future.
Rev 04 - 04 Aug 2019 - Jeff Moden
- Enhance performance by making the first CTE provide 256 values instead of 10, which limits the number of
CrossJoins to just 2. Notice that this changes the maximum range of values to "just" 4,294,967,296, which
is the entire range for INT and just happens to be an even power of 256. Because of the use of the VALUES
clause, this code is "only" compatible with SQLServer 2008 and above.
- Update old link from "SQLMag" to "ITPro". Same famous original article, just a different link because they
changed the name of the company (twice, actually).
- Update the flower box notes with the other changes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN WITH
H2(N) AS ( SELECT 1 
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
GO

创建10000行测试数据

现在，让我们创建一点测试数据。。。只有10000次约会。除了日期(只是为了让它变得有趣)，我还添加了一个以周为单位的合同长度专栏。

--===== Create the test data
DROP TABLE IF EXISTS #TestTable
;
SELECT SomeDate = CONVERT(DATE,DATEADD(dd,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2000','2021')),'2000'))
,ContractWeeks = ABS(CHECKSUM(NEWID())%53)*5
INTO #TestTable
FROM dbo.fnTally(1,10000)
;

使用该功能使变得简单快捷

在那之后，这个问题的代码就很简单了(我添加了一些列只是为了演示……删除您不想要或不需要的内容)。

SELECT  SomeDate
,Week#             = wk.N+1
,WeekStartDate     = DATEADD(wk,wk.N  ,tt.SomeDate)
,NextWeekStartDate = DATEADD(wk,wk.N+1,tt.SomeDate)
INTO #Results
FROM #TestTable tt
CROSS APPLY dbo.fnTally(0,ContractWeeks) wk
;

性能

以下是性能统计数据。。。

Table 'Worktable'. Scan count 52, logical reads 33433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________0000000001E8'. 
Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 562 ms,  elapsed time = 576 ms.
(1320535 rows affected)

rCTE示例

这里有一个rCTE版本，可以将开始日期乘以合同周数，并将其转储到表中。

with cte as (
select SomeDate as dte, 1 as lev
,ContractWeeks
from #TestTable
union all
select dateadd(day, 7, dte), lev + 1
,ContractWeeks
from cte
where lev <= ContractWeeks
)
select dte
into #Results1                            
from cte
order by 1
OPTION (MAXRECURSION 0)
;

性能以下是该次跑步的统计数据。。。比较逻辑读取、CPU和持续时间。这只适用于10000行输入和130万行输出。。。与fnTally方法相同。。。

Table 'Worktable'. Scan count 2, logical reads 7903617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________0000000001E8'.
Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6391 ms,  elapsed time = 6388 ms.
(1320535 rows affected

这里的底线是rCTE

使用~235次或读取次数增加23500%
CPU使用量增加约10倍或1000%
耗时约10倍或延长1000%

只需对rTE的增量(计数)说"否"。

好的，各位。OP在评论我之前的帖子时说了如下。。。

然而，尽管我很想使用这个解决方案，但遗憾的是，我只能只读访问我们的服务器，因此我无法创建临时表或自定义函数，因此rCTE选项最适合我们。

不。。。即使在这种情况下，rTE也不能为您提供最佳服务。

首先，使用临时表只是为了构建测试数据，因为我无法访问OP发布的原始表。它们只是为了测试。。。它们不是解决方案的一部分。以这种方式创建测试数据在受访者中是一种非常常见的做法，您只需要认识到这一事实，并用实际表的实际名称替换临时表的名称。

OP还写了以下内容。。。

然而，我们的服务器一直存在性能问题，因此我很高兴你给了我这个更好的替代方案，希望我们可以在我们真正需要优化的时候使用。

我对此的看法是……为什么要等待？现在开始。将fnTally函数的代码提交给DBA，并说明为什么它应该成为每个数据库的一部分，或者至少包含在可访问的实用程序数据库中。如果DBA真的很有把握，他可能已经有了类似的东西(希望不是基于rTE或While Loop)，你只需要询问一下

如果这不可能，那么这个问题仍然可以达到同样的效果(高性能、短代码、正确答案)，但是，由于你不能做"跳出框框思考"的事情，你必须花一些时间"跳出框格思考"。

话虽如此，并且您需要将Temp表的名称更改为真实的表(我使用了之前回复中的Temp表来测试它)，下面稍长的代码使用类似的CTE(级联CTE)代码来替换您无法生成的fnTally函数。

WITH 
H1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))H0(N)) --16^1 or 16 rows
,    H8(N) AS (SELECT 1 FROM H1 a, H1 b, H1 c, H1 d, H1 e, H1 f, H1 g, H1 h) --16^8 or 4,294,967,296 rows
, Tally(N) AS (SELECT N = ROW_NUMBER() OVER (ORDER BY N) - 1 FROM H8)
SELECT  SomeDate
,Week#             = wk.N+1
,WeekStartDate     = DATEADD(wk,wk.N  ,tt.SomeDate)
,NextWeekStartDate = DATEADD(wk,wk.N+1,tt.SomeDate)
FROM #TestTable tt
CROSS APPLY (SELECT TOP (tt.ContractWeeks+1) N FROM Tally) wk
;

底线是，即使对于这样的事情，也很容易避免rCTE产生增量计数的恐怖。你只需要教会自己如何"深入"思考你所局限的盒子。：D

相关内容

最新更新

热门标签：