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

我在 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

etc.

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

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

etc.

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

在这个答案中，我假设“id”字段在按日期递增排序时对行进行连续编号，就像在示例数据中一样。 （如果不存在这样的列，则可以创建）。

这是所描述技术的示例here and here.

1) 将表与相邻的“id”值连接起来。这会将相邻行配对。选择“分配”字段已更改的行。将结果存储在临时表中，同时保留运行索引。

SET @idx = 0;
CREATE TEMPORARY TABLE boundaries
SELECT
   (@idx := @idx + 1) AS idx,
   a1.date AS prev_end,
   a2.date AS next_start,
   a1.allocation as allocation
FROM allocations a1
JOIN allocations a2
ON (a2.id = a1.id + 1)
WHERE a1.allocation != a2.allocation;

这将为您提供一个表格，每行包含“上一周期的结束时间”、“下一周期的开始时间”和“上一周期的“分配”值”：

+------+------------+------------+------------+
| idx  | prev_end   | next_start | allocation |
+------+------------+------------+------------+
|    1 | 2012-01-01 | 2012-01-02 |          0 |
|    2 | 2012-01-02 | 2012-01-03 |          2 |
|    3 | 2012-01-05 | 2012-01-06 |          0 |
+------+------------+------------+------------+

2）我们需要每个周期的开始和结束在同一行，因此我们需要再次组合相邻行。通过创建第二个临时表来完成此操作，例如boundaries但有一个idx字段 1 更大：

+------+------------+------------+
| idx  | prev_end   | next_start |
+------+------------+------------+
|    2 | 2012-01-01 | 2012-01-02 |
|    3 | 2012-01-02 | 2012-01-03 |
|    4 | 2012-01-05 | 2012-01-06 |
+------+------------+------------+

现在加入idx场，我们得到答案：

SELECT
  boundaries2.next_start AS start,
  boundaries.prev_end AS end,
  allocation
FROM boundaries
JOIN boundaries2
USING(idx);

+------------+------------+------------+
| start      | end        | allocation |
+------------+------------+------------+
| 2012-01-02 | 2012-01-02 |          2 |
| 2012-01-03 | 2012-01-05 |          0 |
+------------+------------+------------+

** 请注意，这个答案正确地获取了“内部”周期，但错过了两个“边缘”周期，其中开头的分配 = 0，结尾的分配 = 5。这些可以使用UNION但我想在不那么复杂的情况下呈现核心思想。