从Postgres中的非结束日期范围列表中查找未覆盖的日期范围

Question

我有一个表来存储从给定日期开始的sku的价格：start_date。此表中可能有多个条目用于sku和新start_date的新价格。每个新条目将覆盖从添加此新价格的日期起的前一组行中的价格。

表格结构如下：

sku_id | start_date   | price
100    | "2020-01-10" | 100
100    | "2020-01-20" | 200
100    | "2020-01-30" | 300

有了这些条目，10th Jan、21st Jan和31st Jan的价格将分别显示为100、200和300。

现在，如果我们在这个表中做另一个条目，起始日期为1st Jan，价格为500。然后，直到推翻所有这3个价格。然后之前提取的所有3个日期的价格现在将为500。

sku_id | start_date   | price
100    | "2020-01-10" | 100
100    | "2020-01-20" | 200
100    | "2020-01-30" | 300
100    | "2020-01-01" | 500    -> This row overrides prices of all 3 rows before it. Since start date `2020-01-01` is less then all their start date.

鉴于这种表格结构，以下是我的要求：

我想获取所有仍处于活动状态的记录(也就是说，它们不会被任何新创建的行完全覆盖(。我想到了使用LEAD函数，它可以获得每行的end_date。

SELECT sku_id, start_date, price,
LEAD(start_date, 1) OVER (
PARTITION BY sku_id ORDER BY created_at
) - INTERVAL '1 day' AS end_date
FROM rate;

这个查询给我的结果是：

sku_id | start_date   | price  | end_date
100    | "2020-01-10" | 100    | "2020-01-19"
100    | "2020-01-20" | 200    | "2020-01-29"
100    | "2020-01-30" | 300    | "2019-12-31"
100    | "2020-01-01" | 500    | 

在这之后，我需要一些查询，它可以拒绝第1、2和3行，因为它们的开始日期小于第3行的结束日期。这是我无法理解如何实现的。

如果问题的标题没有意义，我真的很抱歉，因为我想不出该给什么问题标题。

我的另一种方法是按降序保存加载记录。然后使用该记录作为光标，获取start_date小于该记录开始日期的前几行。但这将导致大量的数据库往返，我想避免这种情况。

如果有一个解决方案只需要一个SQL查询就可以得到我想要的东西，那就太好了。

Answer 1

下面的查询可以提供所需的输出。

select t.sku_id, t.start_date, t.price, t.created_at from
(select rate.*, min(start_date) over (partition by sku_id order by created_at desc) as calculated_date
from rate) t
where t.calculated_date = t.start_date

解释：

• 使用windows函数，为在当前记录之后创建的条目确定min start date
• 如果记录的开始日期大于计算的min start date，则过滤掉记录

参考：https://www.db-fiddle.com/f/uZgWjur4cmqwUjuLPcCNP5/1