时间戳

首页 > 解决方案 > 在没有日历表的情况下，在 SQL 中提取两个日期之间的所有日期

sql - 在没有日历表的情况下，在 SQL 中提取两个日期之间的所有日期

问题描述

我有这样一张桌子：

CREATE table #tableTest
        (
        ID int,
        SumVisits int,
        Domain nvarchar(255),
        LoadDate int
        )

insert into #tableTest (ID,SumVisits ,Domain,LoadDate) values (1,67,'cnn.com',20180617),(2,58,'cnn.com',20180624),(3,52,'cnn.com',20180701)
select * from #tableTest order by LoadDate

我想有这样的结构：

  | SumVisits | date
1   67       20180617
2   67       20180618
3   67       20180619
4   67       20180620
5   67       20180621
6   67       20180622
7   67       20180623
8   58       20180624
9   58       20180625
10  58       20180626
11  58       20180627
12  58       20180628
13  58       20180629
14  58       20180630
15  52       20180701
...

我的第一个想法是使用递归 CTE：

;WITH GeneratedCalendar AS
(
SELECT
        CAST(convert(nvarchar(255),[LoadDate]) as date) as EndDate
       ,lead(cast(convert(nvarchar(255),[LoadDate]) as date) , 1,NULL) OVER(PARTITION BY [domain] order by [LoadDate] desc) as StartDate
      From Table
      UNION ALL
      SELECT

        EndDate
        ,StartDate = DATEADD(DAY, 1, G.StartDate)
      FROM
        GeneratedCalendar AS G
      WHERE
        G.StartDate < EndDate
)
select *  from GeneratedCalendar

但实际上，使用这个 sql 代码我无法生成我想要的结构。你对我有什么想法吗？

标签： sqlsql-server

解决方案

我相信这可以满足您的需求。将您的列存储LoadDate为int明确的日期意味着我必须使用大量转换。将您的日期按原样存储，一个date.

我没有使用递归 CTE，而是使用了 Tally。一个 rCTE 很可能在这里使用 RBAR，如果你有一个大数据集，它会慢得多。不过，Tally 不是 RBAR，因此扩展性要好得多。我使用的 Tally 最多可以满足 10,000 天（超过 27 年）的需求，远远超过您的需求（我本可以使用 1,000 天，但这只是几年，可能无法满足要求）。

USE Sandbox;
GO

CREATE TABLE #tableTest (ID int,
                         SUMV int,
                         Domain nvarchar(255),
                         LoadDate int --Why is this a int????
);

INSERT INTO #tableTest (ID,
                        SUMV,
                        Domain,
                        LoadDate)
VALUES (1, 67, 'cnn.com', 20180617),
       (2, 58, 'cnn.com', 20180624),
       (3, 52, 'cnn.com', 20180701);
SELECT *
FROM #tableTest
ORDER BY LoadDate;

GO

WITH N AS
    (SELECT *
     FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) V (N)),
Tally AS
    (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
     FROM N N1
          CROSS JOIN N N2
          CROSS JOIN N N3
          CROSS JOIN N N4),
DateTally AS
    (SELECT CONVERT(int,CONVERT(varchar(8), DATEADD(DAY, T.I, TT.MinDate), 112)) AS DateValue
     FROM Tally T
          CROSS JOIN (SELECT MIN(CONVERT(date, CONVERT(varchar(8), LoadDate))) AS MinDate,
                             MAX(CONVERT(date, CONVERT(varchar(8), LoadDate))) AS MaxDate
                      FROM #tableTest) TT
     WHERE DATEADD(DAY, T.I, TT.MinDate) <= TT.MaxDate)
SELECT TT.ID,
       TT.SUMV,
       DT.DateValue
FROM DateTally DT
     CROSS APPLY (SELECT TOP 1
                         *
                  FROM #tableTest TT
                  WHERE TT.LoadDate <= DT.DateValue
                  ORDER BY TT.LoadDate DESC) TT;

GO
DROP TABLE #tableTest;

推荐阅读