sql-server - SQL 中的日期范围交集拆分

Question

我有一个 SQL Server 2005 数据库，其中包含一个名为 Memberships 的表。

表架构是:

PersonID int, Surname nvarchar(30), FirstName nvarchar(30), Description nvarchar(100), StartDate datetime, EndDate datetime

我目前正在开发一个网格功能，该功能可以按人显示成员(member)资格。其中一项要求是拆分存在日期范围交集的成员资格行。交集必须由 Surname 和 FirstName 绑定(bind)，即拆分仅发生在具有相同 Surname 和 FirstName 的成员记录中。

示例表数据:

18  Smith  John  Poker Club  01/01/2009  NULL18  Smith  John  Library     05/01/2009  18/01/200918  Smith  John  Gym         10/01/2009  28/01/200926  Adams  Jane  Pilates     03/01/2009  16/02/2009

预期结果集:

18  Smith  John  Poker Club                  01/01/2009  04/01/200918  Smith  John  Poker Club / Library        05/01/2009  09/01/200918  Smith  John  Poker Club / Library / Gym  10/01/2009  18/01/200918  Smith  John  Poker Club / Gym            19/01/2009  28/01/200918  Smith  John  Poker Club                  29/01/2009  NULL26  Adams  Jane  Pilates                     03/01/2009  16/02/2009

有没有人知道如何编写一个存储过程来返回具有上述分解的结果集。

Answer 1

您将遇到的问题是，随着数据集的增长，使用 TSQL 解决它的解决方案将无法很好地扩展。下面使用一系列动态构建的临时表来解决这个问题。它使用数字表将每个日期范围条目拆分为各自的日期。这是它无法缩放的地方，主要是由于您的开放范围 NULL 值似乎是无穷大，因此您必须在未来的固定日期交换，从而将转换范围限制在可行的时间长度内。通过构建包含适当索引的日期表或日历表以优化每一天的呈现，您可能会看到更好的性能。

一旦范围被拆分，描述就会使用 XML PATH 合并，这样范围系列中的每一天都有为其列出的所有描述。按 PersonID 和日期进行行编号允许使用两个 NOT EXISTS 检查找到每个范围的第一行和最后一行，以查找匹配的 PersonID 和描述集的前一行不存在的实例，或者下一行不存在的实例对于匹配的 PersonID 和 Description 集不存在。

此结果集然后使用 ROW_NUMBER 重新编号，以便它们可以配对以构建最终结果。

/*
SET DATEFORMAT dmy
USE tempdb;
GO
CREATE TABLE Schedule
( PersonID int, 
 Surname nvarchar(30), 
 FirstName nvarchar(30), 
 Description nvarchar(100), 
 StartDate datetime, 
 EndDate datetime)
GO
INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Poker Club', '01/01/2009', NULL)
INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Library', '05/01/2009', '18/01/2009')
INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Gym', '10/01/2009', '28/01/2009')
INSERT INTO Schedule VALUES (26, 'Adams', 'Jane', 'Pilates', '03/01/2009', '16/02/2009')
GO

*/

SELECT 
 PersonID, 
 Description, 
 theDate
INTO #SplitRanges
FROM Schedule, (SELECT DATEADD(dd, number, '01/01/2008') AS theDate
    FROM master..spt_values
    WHERE type = N'P') AS DayTab
WHERE theDate >= StartDate 
  AND theDate <= isnull(EndDate, '31/12/2012')

SELECT 
 ROW_NUMBER() OVER (ORDER BY PersonID, theDate) AS rowid,
 PersonID, 
 theDate, 
 STUFF((
  SELECT '/' + Description
  FROM #SplitRanges AS s
  WHERE s.PersonID = sr.PersonID 
    AND s.theDate = sr.theDate
  FOR XML PATH('')
  ), 1, 1,'') AS Descriptions
INTO #MergedDescriptions
FROM #SplitRanges AS sr
GROUP BY PersonID, theDate


SELECT 
 ROW_NUMBER() OVER (ORDER BY PersonID, theDate) AS ID, 
 *
INTO #InterimResults
FROM
(
 SELECT * 
 FROM #MergedDescriptions AS t1
 WHERE NOT EXISTS 
  (SELECT 1 
   FROM #MergedDescriptions AS t2 
   WHERE t1.PersonID = t2.PersonID 
     AND t1.RowID - 1 = t2.RowID 
     AND t1.Descriptions = t2.Descriptions)
UNION ALL
 SELECT * 
 FROM #MergedDescriptions AS t1
 WHERE NOT EXISTS 
  (SELECT 1 
   FROM #MergedDescriptions AS t2 
   WHERE t1.PersonID = t2.PersonID 
     AND t1.RowID = t2.RowID - 1
     AND t1.Descriptions = t2.Descriptions)
) AS t

SELECT DISTINCT 
 PersonID, 
 Surname, 
 FirstName
INTO #DistinctPerson
FROM Schedule

SELECT 
 t1.PersonID, 
 dp.Surname, 
 dp.FirstName, 
 t1.Descriptions, 
 t1.theDate AS StartDate, 
 CASE 
  WHEN t2.theDate = '31/12/2012' THEN NULL 
  ELSE t2.theDate 
 END AS EndDate
FROM #DistinctPerson AS dp
JOIN #InterimResults AS t1 
 ON t1.PersonID = dp.PersonID
JOIN #InterimResults AS t2 
 ON t2.PersonID = t1.PersonID 
  AND t1.ID + 1 = t2.ID 
  AND t1.Descriptions = t2.Descriptions

DROP TABLE #SplitRanges
DROP TABLE #MergedDescriptions
DROP TABLE #DistinctPerson
DROP TABLE #InterimResults

/*

DROP TABLE Schedule

*/

上述解决方案还将处理其他描述之间的间隙，因此如果您要为 PersonID 18 添加另一个描述并留下间隙:

INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Gym', '10/02/2009', '28/02/2009')

它将适本地填补空白。正如评论中所指出的，你不应该在这个表中有姓名信息，它应该被规范化为一个可以在最终结果中加入的 Persons 表。我通过使用 SELECT DISTINCT 构建一个临时表来创建该 JOIN 来模拟另一个表。