在t-sql中生成日期序列

h7appiyu  于 2021-07-26  发布在  Java
关注(0)|答案(3)|浏览(337)

在购买记录中,我想生成一个基准日期+/-12个月的序列

Client Date 
Joe    2020-03-15
Maria  2019-11-01

所以预期的结果是

Client Date 
Joe    2019-03-15
Joe    2019-04-15
.
.
.
Joe    2020-03-15
.
.
.
Joe    2021-02-15
Joe    2021-03-15
Maria  2018-11-01
Maria  2018-12-01
.
.
.
Maria  2019-11-01
.
.
.
Maria  2020-10-01
Maria  2020-11-01

如何做到这一点?

hwamh0ep

hwamh0ep1#

一个选项使用递归查询:

with cte as (
    select client, dateadd(month, -12, date) date, 0 lvl from mytbale
    union all
    select client, dateadd(month, 1, date), lvl + 1 from cte where lvl < 24
)
select * from cte order by client, date
lyr7nygr

lyr7nygr2#

另一个选择是一个特别的理货表与 CROSS JOIN 例子

Select A.Client
      ,[Date] = date,DateAdd(MONTH,N,[Date])
 From  YourTable A
 Cross Join  ( Select Top (12+1) N=-1+Row_Number() Over (Order By (Select Null)) From  master..spt_values n1)  B

退货

Client  Date
Joe     2020-03-15
Joe     2020-04-15
Joe     2020-05-15
Joe     2020-06-15
Joe     2020-07-15
Joe     2020-08-15
Joe     2020-09-15
Joe     2020-10-15
Joe     2020-11-15
Joe     2020-12-15
Joe     2021-01-15
Joe     2021-02-15
Joe     2021-03-15
Maria   2019-11-01
Maria   2019-12-01
Maria   2020-01-01
Maria   2020-02-01
Maria   2020-03-01
Maria   2020-04-01
Maria   2020-05-01
Maria   2020-06-01
Maria   2020-07-01
Maria   2020-08-01
Maria   2020-09-01
Maria   2020-10-01
Maria   2020-11-01

编辑:如果你想+/-12个月,只是一个小的调整是必需的

...
 Cross Join  ( Select Top (24+1) N=-13+Row_Number() Over (Order By (Select Null)) From  master..spt_values n1)  B
vs91vp4v

vs91vp4v3#

这里有一个稍微不同的解决方案,它使用了计数表的本质。我之所以发布它,是因为很多人的dba不允许他们使用主数据库中的东西,还因为需要一系列的值是一件非常常见的事情。下面的方法还导致在序列的生成中没有逻辑读取。
也就是说,“理货表”经常被称为t-sql的“瑞士军刀”。十年前,erlandsommarskog通过ms“connect”(feedback的旧名称)请求一个内置的序列生成器,这一点非常重要。不幸的是,十多年来,她除了保留请求外,什么也没做。
考虑到这一点,下面是我对伊兹克·本·甘(itzik ben gan)创造这种功能的优秀作品的演绎。它非常有意地只能从“0”或“1”开始,但可以上升到(实际上超过)您可能需要的任何正int值。如果您需要它从一个不同于“0”或“1”的数字开始,那么在函数之外的一个简单的整数数学就可以实现它。我之所以没有将该功能构建到函数中,是因为从“0”或“1”开始的序列是最常见的用法,我不想因为这个函数所经历的极端用法而对性能造成一到两毫秒的影响。
这是函数。。。每个人都应该在每个数据库中或者在普通公众可以使用的“实用程序”数据库中都有类似的东西。

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
;

p、 我通常不使用“匈牙利符号”的对象,但我也有一个理货表,你不能有两个同名的对象。
一旦你有了这样的东西,需要数字序列的问题就变得简单、快速、廉价。例如,下面是如何使用此函数编写op问题的解决方案。

--===== Solve the problem using the fnTally function as a numeric sequence generator starting
     -- at 0 and ending at the +/- 12 month differences for each Client's base date.
DECLARE @OffsetMonths INT = 12 --Just to make it a bit more flexible
;
 SELECT  d.Client
        ,[Date] = DATEADD(mm,t.N-@OffsetMonths,d.[Date])
   FROM #TestTable d
  CROSS APPLY dbo.fnTally(0,@OffsetMonths*2) t
  ORDER BY d.Client,[Date]
;

如果您想测试代码,下面是我为测试表构建的内容。

--===== Create and populate a test table.
     -- This is NOT a part of the solution.
     -- We're just creating test data here.
   DROP TABLE IF EXISTS #TestTable
;
 CREATE TABLE #TestTable
        (
         Client VARCHAR(20)
        ,[Date] DATE
        )
;
 INSERT INTO #TestTable
        (Client,[Date])
 VALUES  ('Joe','2020-03-15')
        ,('Maria','2019-11-01')
;

如果你想知道更多关于“理货表”和相关函数的工作原理,这里有一个链接到我的文章,这篇文章太长了,不能在这里发表。
“数字”或“计数”表:它是什么以及它如何替换循环

相关问题