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

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

等等。

我需要做的是获取所有连续的日子，其中Allocation = 0，并采用以下形式：

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

等等。

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

Answer 1

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

这是此处和此处描述的技术示例。

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;

这将为您提供一个表，在每行中具有“上一个期间的结束”，“下一个期间的开始”和“上一个期间的’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。可以在usingUNION子句中加入这些内容，但我想提出的核心思想是没有复杂性。