sql - 填补 SQL Server 日期范围中的空白

Question

在 SQL Server 2014 中，我有一个如下所示的 Periods 表:

| PeriodId | PeriodStart | PeriodEnd  |
---------------------------------------
| 202005   | 2020-05-01  | 2020-05-31 |
| 202006   | 2020-06-01  | 2020-06-30 |

句点并不总是从该月的第一天到最后一天。

然后我有一个 Activities 表，其中包含用户编程的一些事件:

| ActivityId | UserId | ActivityStart | ActivityEnd |
-----------------------------------------------------
| 1          | A      | 2020-05-20    | 2020-06-05  |
| 2          | A      | 2020-06-15    | 2020-06-18  |
| 3          | B      | 2020-06-10    | 2020-06-25  |

用户的事件之间可能存在间隔，但同一用户永远不会有重叠的事件。

现在我需要一个查询，将事件日期范围限制为期间的开始和结束，并填补空白以完成该期间。我将始终按 PeriodId 进行过滤，因此我将只放置 PeriodId = 202006 的示例结果:

| PeriodId | UserId | ActivityId | NewActivityStart | NewActivityEnd |
----------------------------------------------------------------------
| 202006   | A      | 1          | 2020-06-01       | 2020-06-05     |  --Part of ActivityId 1
| 202006   | A      | NULL       | 2020-06-06       | 2020-06-14     |  --Fill between activities 1 and 2
| 202006   | A      | 2          | 2020-06-15       | 2020-06-18     |
| 202006   | A      | NULL       | 2020-06-19       | 2020-06-30     |  --Fill until end of period
| 202006   | B      | NULL       | 2020-06-01       | 2020-06-09     |  --Fill from start of period
| 202006   | B      | 3          | 2020-06-10       | 2020-06-25     |
| 202006   | B      | NULL       | 2020-06-26       | 2020-06-30     |  --Fill until end of period

我已经能够通过以下查询包含该期间内的事件日期:

SELECT p.PeriodId, a.UserId, a.ActivityId
       IIF(p.PeriodStart > a.ActivityStart, p.PeriodStart, a.ActivityStart) AS NewActivityStart,
       IIF(p.PeriodEnd < a.ActivityEnd, p.PeriodEnd, a.ActivityEnd) AS NewActivityEnd
FROM Periods p
JOIN Activities a ON a.ActivityStart <= p.PeriodEnd AND a.ActivityEnd >= p.PeriodStart

但我无法填补范围内的空白。我尝试过使用相关日期表和/或像 LAG/LEAD 这样的窗口函数。

我觉得 Window Functions 可能是解决方案，我已经尝试遵循 examples关于差距/岛屿，但我还没有能够很好地理解它们以使其发挥作用。

有没有办法完成查询以填补缺失的空白？有没有其他方法可以在查询中实现这一点？

Answer 1

您可以使用各种技术解决此问题。在下面的示例中，我使用了一种方法，因为代码是 SQL 例程的主体。

那么，这是你的约会对象:

DECLARE @Periods TABLE
(
    [PeriodId] INT
   ,[PeriodStart] DATE
   ,[PeriodEnd] DATE
);

INSERT INTO @Periods ([PeriodId], [PeriodStart], [PeriodEnd])
VALUES ('202005', '2020-05-01', '2020-05-31')
      ,('202006', '2020-06-01', '2020-06-30');

DECLARE @Activities  TABLE
(
    [ActivityId] INT
   ,[UserId] CHAR(1)
   ,[ActivityStart] DATE
   ,[ActivityEnd] DATE
);

INSERT INTO @Activities ([ActivityId], [UserId], [ActivityStart], [ActivityEnd])
VALUES (1, 'A', '2020-05-20', '2020-06-05')
      ,(2, 'A', '2020-06-15', '2020-06-18')
      ,(3, 'B', '2020-06-10', '2020-06-25');

然后，假设我们有一个输入参数 @PeriodID，我们通过它提取相应的开始和结束日期期间:

DECLARE @PeriodID INT
       ,@PeriodDateStart DATE
       ,@PeriodDateEnd DATE;

SET @PeriodID = 202006;

SELECT @PeriodDateStart = [PeriodStart]
      ,@PeriodDateEnd = [PeriodEnd]
FROM @Periods 
WHERE [PeriodId] = @PeriodID;

然后，让我们创建一个缓冲表，我们将在其中计算 activity 和 period 表之间的匹配，并添加 start 和 end 需要时记录:

DECLARE @Buffer TABLE
(
    [ActivityId] INT
   ,[UserId] CHAR(1)
   ,[ActivityStart] DATE
   ,[ActivityEnd] DATE
);

WITH DataSource AS
(
    SELECT A.[ActivityId]
          ,A.[UserId]
          ,A.[ActivityStart]
          ,A.[ActivityEnd]
    FROM @Activities A
    INNER JOIN @Periods P
        ON A.[ActivityStart] <= P.[PeriodEnd]
        AND A.[ActivityEnd] >= P.[PeriodStart]
    WHERE P.PeriodId = @PeriodID
)
INSERT INTO @Buffer ([ActivityId], [UserId], [ActivityStart], [ActivityEnd])
SELECT [ActivityId]
      ,[UserId]
      ,IIF([ActivityStart] < @PeriodDateStart, @PeriodDateStart, [ActivityStart]) AS [ActivityStart]
      ,[ActivityEnd]
FROM DataSource 
UNION ALL
SELECT NULL
      ,[UserId]
      ,DATEADD(DAY, 1, MAX([ActivityEnd]))
      ,@PeriodDateEnd
FROM DataSource
GROUP BY [UserId]
HAVING DATEADD(DAY, 1, MAX([ActivityEnd])) < @PeriodDateEnd
UNION ALL
SELECT NULL
      ,[UserId]
      ,@PeriodDateStart
      ,DATEADD(DAY, -1, MIN([ActivityStart]))
FROM DataSource
GROUP BY [UserId]
HAVING DATEADD(DAY, -1, MIN([ActivityStart])) > @PeriodDateStart;

很简单。在公用表表达式中，我使用了您的代码。然后，我们只是简单地检查是否需要在特定用户的时间段之前或/和之后添加一条记录。

现在，我们可以计算差距了，对吗？这里有很多变体。我正在使用 LEAD 函数来计算每一行的 missing 周期。声明如下:

SELECT *
      ,DATEADD(DAY, 1, [ActivityEnd]) AS [MissingPeriodStart]
      ,DATEADD(DAY, -1, LEAD([ActivityStart]) OVER (PARTITION BY [UserID] ORDER BY [ActivityStart] ASC)) AS [MissingPeriodEnd]
FROM @Buffer
ORDER BY USERID, ActivityStart;

输出是这样的:

因此，您可能会看到我们如何为除最后一行之外的每一行生成缺失句点 日期。现在，我们只需要获取其中的一些缺失句点。是这样的:

WITH DataSource AS
(
    SELECT *
          ,DATEADD(DAY, 1, [ActivityEnd]) AS [MissingPeriodStart]
          ,DATEADD(DAY, -1, LEAD([ActivityStart]) OVER (PARTITION BY [UserID] ORDER BY [ActivityStart] ASC)) AS [MissingPeriodEnd]
    FROM @Buffer
)
SELECT @PeriodID AS [PeriodID]
      ,[UserId]
      ,[ActivityId]
      ,[ActivityStart]
      ,[ActivityEnd]
FROM DataSource
UNION ALL 
SELECT @PeriodID AS [PeriodID]
      ,[UserId]
      ,NULL
      ,[MissingPeriodStart]
      ,[MissingPeriodEnd]
FROM DataSource
WHERE NOT EXISTS 
(
    SELECT 1 
    FROM DataSource DS
    WHERE [MissingPeriodStart] = DS.[ActivityStart]
        AND [UserID] = DS.[UserID]
)
    AND [MissingPeriodStart] < [MissingPeriodEnd]
ORDER BY [UserId]
        ,[ActivityStart];

结果是:

当然，这是一个想法。您可能需要更改或调整它以便与您的真实数据一起使用。我希望它能给你一个开始。