sql - 创建满足给定条件的连续天数组

Question

我在 SQL Server 中有以下数据结构表:

ID  Date        Allocation
 1, 2012-01-01, 0
 2, 2012-01-02, 2
 3, 2012-01-03, 0
 4, 2012-01-04, 0
 5, 2012-01-05, 0
 6, 2012-01-06, 5

等等。

我需要做的是获取分配 = 0 的所有连续日期间，并采用以下形式:

Start Date    End Date     DayCount
2012-01-01    2012-01-01   1
2012-01-03    2012-01-05   3

等等。

是否可以在 SQL 中执行此操作，如果可以，如何执行？

Answer 1

以下将是一种方法。这个解决方案的要点是

使用 CTE使用 Allocation = 0 获取所有连续开始和结束日期的列表

使用 ROW_NUMBER窗口函数根据开始日期和结束日期分配行号。

仅选择同时满足 ROW_NUMBERS 的记录等于 1。

使用 DATEDIFF计算 DayCount

SQL 语句

;WITH r AS (
  SELECT  StartDate = Date, EndDate = Date
  FROM    YourTable
  WHERE   Allocation = 0
  UNION ALL
  SELECT  r.StartDate, q.Date
  FROM    r
          INNER JOIN YourTable q ON DATEDIFF(dd, r.EndDate, q.Date) = 1
  WHERE   q.Allocation = 0          
)
SELECT  [Start Date] = s.StartDate
        , [End Date ] = s.EndDate
        , [DayCount] = DATEDIFF(dd, s.StartDate, s.EndDate) + 1
FROM    (
          SELECT  *
                  , rn1 = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY EndDate DESC)
                  , rn2 = ROW_NUMBER() OVER (PARTITION BY EndDate ORDER BY StartDate ASC)
          FROM    r          
        ) s
WHERE   s.rn1 = 1
        AND s.rn2 = 1
OPTION  (MAXRECURSION 0)

测试脚本

;WITH q (ID, Date, Allocation) AS (
  SELECT * FROM (VALUES
    (1, '2012-01-01', 0)
    , (2, '2012-01-02', 2)
    , (3, '2012-01-03', 0)
    , (4, '2012-01-04', 0)
    , (5, '2012-01-05', 0)
    , (6, '2012-01-06', 5)
  ) a (a, b, c)
)
, r AS (
  SELECT  StartDate = Date, EndDate = Date
  FROM    q
  WHERE   Allocation = 0
  UNION ALL
  SELECT  r.StartDate, q.Date
  FROM    r
          INNER JOIN q ON DATEDIFF(dd, r.EndDate, q.Date) = 1
  WHERE   q.Allocation = 0          
)
SELECT  s.StartDate, s.EndDate, DATEDIFF(dd, s.StartDate, s.EndDate) + 1
FROM    (
          SELECT  *
                  , rn1 = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY EndDate DESC)
                  , rn2 = ROW_NUMBER() OVER (PARTITION BY EndDate ORDER BY StartDate ASC)
          FROM    r          
        ) s
WHERE   s.rn1 = 1
        AND s.rn2 = 1
OPTION  (MAXRECURSION 0)