sql - 为来自同一时间戳列的记录创建日期间隔

Question

我有一个表“日期”，其中 2 列表示对象描述有效的日期，以及直到它有效的日期（valid_from & valid_to），如下所示：

 ID   valid_from      valid_to      removed

  1   2019-06-27         -           false
  1        -         2019-06-26      true  
  1   2019-05-16         -           false   
  1   2019-05-08         -           false
  1   2019-05-07         -           false

我需要做的是为所有这些行获取明确的时间间隔，为每一行定义一个 valid_from 和 valid_to。

Valid_to 日期对应于删除最接近 valid_from 日期的记录，因此这 2 行应合并为一行，保留 removed=true 值（尽管这不是优先级）。在这种情况下，例如，应折叠第 2 行和第 3 行。

然后，对于 valid_to 为 NULL (removed=false) 的每一行，应插入比该行的 valid_from 大的最接近的 valid_from 日期。最近的行（即第一行）后面不会有记录，因此 valid_to 日期将保持为 NULL。

因此，产生的结果将是：

 ID   valid_from      valid_to      removed

  1   2019-06-27         -           false
  1   2019-05-16     2019-06-26      true  
  1   2019-05-08     2019-05-16      false
  1   2019-05-07     2019-05-08      false

这不能通过硬编码值或条件限制来完成，因为这只是一个更大的表的一部分，并且应该为每个 ID 组执行操作。

---

我曾尝试在 INSERT INTO 语句中的子查询上使用 TOP 语句，但无法弄清楚如何对实现此结果所需的进程进行排序。

帮助将不胜感激。谢谢你。

标签： sql

Answer 1

我想你只是想要lead()：

select valid_from, next_valid_to,
       coalesce(next_result, result) as result
from (select t.*,
             lead(coalesce(valid_from, valid_to)) over (partition by id order by coalesce(valid_from, valid_to)) as next_valid_to,
             lead(removed) over (partition by id order by coalesce(valid_from, valid_to)) as next_result
      from t
     ) t
where valid_from is not null;