时间戳

首页 > 解决方案 > Postgres 查找两个表之间的日期不重叠的位置

postgresql - Postgres 查找两个表之间的日期不重叠的位置

问题描述

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

物品表：

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

标签： postgresql

解决方案

分步演示：db<>fiddle

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 （这里发生了一个时间变化问题：这就是为什么我不要求一天的差异，而是 2 天的差异。之间2019-03-31只有2019-04-0123小时，因为夏令时）
  2. 这些0和1值是累积聚合的。如果有一个间隔大于一天，那就是一个新的间隔（之间的天被覆盖）
  3. 这会产生一个可分组列，可用于聚合和查找每个间隔的最大和最小日期

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

推荐阅读