Postgres发现两个表之间的日期不重叠

Question

我有两个表，我正试图在它们中找到日期不重叠的数据间隙。

项目表：

id    unique      start_date     end_date     data
1         a        2019-01-01    2019-01-31     X
2         a        2019-02-01    2019-02-28     Y
3         b        2019-01-01    2019-06-30     Y

计划表：

id     item_unique   start_date     end_date 
1         a         2019-01-01    2019-01-10
2         a         2019-01-15    'infinity'

我正试图找到一种方法来生产以下

缺少：

item_unique   from             to
a        2019-01-11    2019-01-14     
b        2019-01-01    2019-06-30     

Answer 1

逐步演示：db<>小提琴

WITH excepts AS (
SELECT 
item,
generate_series(start_date, end_date, interval '1 day') gs
FROM items
EXCEPT
SELECT
item,
generate_series(start_date, CASE WHEN end_date = 'infinity' THEN ( SELECT MAX(end_date) as max_date FROM items) ELSE end_date END, interval '1 day')
FROM plan
)
SELECT
item,
MIN(gs::date) AS start_date,
MAX(gs::date) AS end_date
FROM (
SELECT
*,
SUM(same_day) OVER (PARTITION BY item ORDER BY gs)
FROM (
SELECT
item,
gs,
COALESCE((gs - LAG(gs) OVER (PARTITION BY item ORDER BY gs) >= interval '2 days')::int, 0) as same_day
FROM excepts
) s
) s
GROUP BY item, sum
ORDER BY 1,2

寻找失踪的日子很简单。这是在WITH子句中完成的：

生成日期范围内的所有日期，并从第二个表的扩展列表中减去此结果。未出现在第二个表中的所有日期都将保留。infinity的结尾有点棘手，所以我用第一个表的最大日期替换了infinity的出现。这样可以避免扩展无限的日期列表。

更有趣的部分是重新聚集这个列表，这是WITH子句之外的部分：

1. lag()窗口函数采用上一个日期。如果列表中的前一天是最后一天，则给出true(这里出现了一个时间变化问题：这就是为什么我不要求一天的差异，而是要求两天的差异。由于夏令时，2019-03-31和2019-04-01之间只有23个小时(
2. 这些CCD_ 8和CCD_。如果有一个间隔大于一天，则这是一个新的间隔(包含间隔的天数(
3. 这会产生一个可分组的列，该列可用于聚合并查找每个间隔的最大和最小日期

---

尝试了一些具有日期范围的方法，这似乎是一种更好的方法，尤其是对于避免扩展长日期列表。但没有想出一个合适的解决方案。也许是其他人？