提取多行连续期间所覆盖的天数

Question

我在Ingres 10表中有如下数据:-

ref, from_date, to_date
A, 01/04/2016, 30/04/2016
A, 30/04/2016, 20/05/2016
A, 25/05/2016, 30/05/2016
B, 01/04/2016, 01/09/2016
B, 01/10/2016, 20/02/2016

to_dates是排他的.

因此，有些线条表示没有间隙的连续周期，这些线可以跨越多条线，但是有时会有空隙。

我需要计算每一个连续期间所涵盖的天数。我想不出有什么办法能做到。

因此，对于参考文献A，我需要知道第一个连续的周期是01/04/16至20/05/16，这一期间有46天，第二个连续的周期是25/05/16 - 30/05/16，这是4天。

Answer 1

在一条SQL语句中，我想不出一种聪明的方法来做到这一点。下面的答案依赖于创建表的副本，并对其进行迭代更新，直到我们得到每个期间的“最早连续日期”为止。然后，这只是一个问题，选择最大的时间为每个参考和从日期组合。

在下面的示例中，我已经运行了两次更新，但是对于任意数据，您需要运行它，直到更新更新0行。

此外，我还冒昧地修改了我所假设的日期中的几个排字。

DECLARE GLOBAL TEMPORARY TABLE SESSION.test2 AS
SELECT ref AS ref,
  from_date AS from_date,
  to_date AS to_date,
  from_date AS cont_from
FROM test
ON COMMIT PRESERVE ROWS
WITH NORECOVERY
Executing . . .

(5 rows)
continue
* * SELECT * FROM SESSION.test2
Executing . . .


+------+-------------------------+-------------------------+-------------------------+
|ref   |from_date                |to_date                  |cont_from                |
+------+-------------------------+-------------------------+-------------------------+
|A     |01-apr-2016              |30-apr-2016              |01-apr-2016              |
|A     |30-apr-2016              |20-may-2016              |30-apr-2016              |
|A     |25-may-2016              |30-may-2016              |25-may-2016              |
|B     |01-apr-2016              |01-sep-2016              |01-apr-2016              |
|B     |01-oct-2016              |20-feb-2017              |01-oct-2016              |
+------+-------------------------+-------------------------+-------------------------+
(5 rows)
continue
* * * * * * * * * * *
/* repeat this update until 0 rows are updated */
UPDATE SESSION.test2 b
FROM SESSION.test2 a
SET cont_from = a.cont_from
WHERE b.cont_from <= a.to_date
  AND b.cont_from >  a.from_date
  AND b.ref = a.ref
  AND b.cont_from != a.cont_from;
SELECT * FROM SESSION.test2
Executing . . .

(1 row)

+------+-------------------------+-------------------------+-------------------------+
|ref   |from_date                |to_date                  |cont_from                |
+------+-------------------------+-------------------------+-------------------------+
|A     |01-apr-2016              |30-apr-2016              |01-apr-2016              |
|A     |30-apr-2016              |20-may-2016              |01-apr-2016              |
|A     |25-may-2016              |30-may-2016              |25-may-2016              |
|B     |01-apr-2016              |01-sep-2016              |01-apr-2016              |
|B     |01-oct-2016              |20-feb-2017              |01-oct-2016              |
+------+-------------------------+-------------------------+-------------------------+
(5 rows)
continue
*
/* repeat this update until 0 rows are updated */
UPDATE SESSION.test2 b
FROM SESSION.test2 a
SET cont_from = a.cont_from
WHERE b.cont_from <= a.to_date
  AND b.cont_from >  a.from_date
  AND b.ref = a.ref
  AND b.cont_from != a.cont_from;
SELECT * FROM SESSION.test2
Executing . . .

(0 rows)

+------+-------------------------+-------------------------+-------------------------+
|ref   |from_date                |to_date                  |cont_from                |
+------+-------------------------+-------------------------+-------------------------+
|A     |01-apr-2016              |30-apr-2016              |01-apr-2016              |
|A     |30-apr-2016              |20-may-2016              |01-apr-2016              |
|A     |25-may-2016              |30-may-2016              |25-may-2016              |
|B     |01-apr-2016              |01-sep-2016              |01-apr-2016              |
|B     |01-oct-2016              |20-feb-2017              |01-oct-2016              |
+------+-------------------------+-------------------------+-------------------------+
(5 rows)
continue
* * * * * SELECT ref,cont_from,MAX(to_date - cont_from)
FROM SESSION.test2
GROUP BY ref,cont_from
ORDER BY 1,2
Executing . . .


+------+-------------------------+-------------------------+
|ref   |cont_from                |col3                     |
+------+-------------------------+-------------------------+
|A     |01-apr-2016              |49 days                  |
|A     |25-may-2016              |5 days                   |
|B     |01-apr-2016              |153 days                 |
|B     |01-oct-2016              |142 days                 |
+------+-------------------------+-------------------------+

HTH