合同在给定日期处于活动状态

Question

在我的表contracts中，我有所有的合同和订单(订单属于父idpid定义的特定合同(。合同和订单通过id_type进行区分；

• 1=合同(开始时有效(
• 2=停用订单(合同失效(
• 3=重新激活订单(合同再次生效(

合同可以多次停用或重新激活。此外，合同可以被停用，再也不会被重新激活。

记录表：

id | pid | id_type | start_date
===+=====+=========+===========
20 |     |       1 | 2021-01-01 --> contract 20 started and active
38 |  20 |       2 | 2021-02-15 --> contract 20 temporarily deactivated
42 |  20 |       3 | 2021-02-25 --> contract 20 activated again
54 |  20 |       2 | 2021-04-01 --> contract 20 temporarily deactivated
95 |  20 |       3 | 2021-04-15 --> contract 20 activated again
30 |     |       1 | 2021-01-12 --> contract 30 started and active

我需要SQL查询，它将返回合同在给定日期是活动的还是停用的。

例如，对于日期2021-02-20，我应该得到合同20是无效的。

我尝试了LAG/LEAD功能，但没有成功。

Answer 1

您可以使用获取特定日期当天或之前的最近一行

select t.*
from (select t.*,
row_number() over (partition by coalesce(pid, id) order by start_date desc) as seqnum
from t
where start_date <= date '2021-02-20'
) t
where seqnum = 1;

如果你只想要状态和日期，那么你也可以使用group by和keep:

select coalesce(pid, id), max(start_date),
max(id_type) keep (dense_rank first order by start_date desc) as id_type
from t
where start_date <= date '2021-02-20'
group by coalesce(pid, id)