编辑:这是一个好消息,您的DBA同意升级到较新版本的Postgresql。单独的窗口功能使升级成为值得的投资。
我的原始答案是一个主要缺陷:每行限制为一行id
。 下面是没有这种限制的更好的解决方案。 我已经使用系统上的测试表(8.4)对其进行了测试。
如果您有时间,我想知道它如何对您的数据执行。 我还在这里写了一个解释:https ://www.mechanical-meat.com/1/detail
WITH RECURSIVE t1_rec ( id, "begin", "end", n ) AS (
SELECT id, "begin", "end", n
FROM (
SELECT
id, "begin", "end",
CASE
WHEN LEAD("begin") OVER (
PARTITION BY id
ORDER BY "begin") <= ("end" + interval '2' day)
THEN 1 ELSE 0 END AS cl,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY "begin") AS n
FROM mytable
) s
WHERE s.cl = 1
UNION ALL
SELECT p1.id, p1."begin", p1."end", a.n
FROM t1_rec a
JOIN mytable p1 ON p1.id = a.id
AND p1."begin" > a."begin"
AND (a."begin", a."end" + interval '2' day) OVERLAPS
(p1."begin", p1."end")
)
SELECT t1.id, min(t1."begin"), max(t1."end")
FROM t1_rec t1
LEFT JOIN t1_rec t2 ON t1.id = t2.id
AND t2."end" = t1."end"
AND t2.n < t1.n
WHERE t2.n IS NULL
GROUP BY t1.id, t1.n
ORDER BY t1.id, t1.n;
原始(已弃用)答案如下; 注意:每个限制为一行id
。
Denis对于使用lead()
and可能是正确的lag()
,但还有另一种方法! 您还可以使用所谓的递归sql解决此问题。 该重叠功能也派上用场了。
我已经在系统上完全测试了该解决方案(8.4)。 它运作良好。
WITH RECURSIVE rec_stmt ( id, begin, end ) AS (
/* seed statement:
start with only first start and end dates for each id
*/
SELECT id, MIN(begin), MIN(end)
FROM mytable seed_stmt
GROUP BY id
UNION ALL
/* iterative (not really recursive) statement:
append qualifying rows to resultset
*/
SELECT t1.id, t1.begin, t1.end
FROM rec_stmt r
JOIN mytable t1 ON t1.id = r.id
AND t1.begin > r.end
AND (r.begin, r.end + INTERVAL '1' DAY) OVERLAPS
(t1.begin - INTERVAL '1' DAY, t1.end)
)
SELECT MIN(begin), MAX(end)
FROM rec_stmt
GROUP BY id;