SQL：从重叠日期推断有效定价

使用新起始价格窗口的逻辑可以赢得重叠.

离散日期版本:

with data(article,price,startdate,enddate) as (
    select * FROM VALUES
        (123, 10, '2022-02-02'::date, '2049-12-31'::date),
        (123, 8,  '2022-02-14'::date, '2022-09-14'::date),
        (123, 5,  '2022-03-14'::date, '2022-04-06'::date),
        (123, 4,  '2022-04-11'::date, '2022-04-27'::date)
), dis_times as (
    select article,
        date as startdate,
        lead(date) over(partition by article order by date)-1 as enddate
    from (
        select distinct article, startdate as date from data
        union
        select distinct article, enddate+1 as date from data
    )
    qualify enddate is not null
)
select 
    d1.article, 
    d1.price, 
    d2.startdate,
    d2.enddate
from data as d1
join dis_times as d2
    on d1.article = d2.article 
        and d2.startdate between d1.startdate and d1.enddate qualify row_number() over (partition by d1.article, s_startdate order by d1.startdate desc) = 1
order by 1,3;

给予:

连续时间戳版本:

with data(article,price,startdate,enddate) as (
    select * FROM VALUES
        (123, 10, '2022-02-02'::date, '2049-12-31'::date),
        (123, 8,  '2022-02-14'::date, '2022-09-14'::date),
        (123, 5,  '2022-03-14'::date, '2022-04-06'::date),
        (123, 4,  '2022-04-11'::date, '2022-04-27'::date)
), dis_times as (
    select article,
        date as startdate,
        lead(date) over(partition by article order by date) as enddate
    from (
        select distinct article, startdate as date from data
        union
        select distinct article, enddate as date from data
    )
    qualify enddate is not null
)
select 
    d1.article, 
    d1.price, 
    d2.startdate,
    d2.enddate
from data as d1
join dis_times as d2
    on d1.article = d2.article 
        and d2.startdate >= d1.startdate and d2.startdate < d1.enddate
qualify row_number() over (partition by d1.article, s_startdate order by d1.startdate desc) = 1
order by 1,3;

which 给予:

感谢MatBailie提出的更紧密的加入建议.

join dis_times as d2
    on d1.article = d2.article 
        and d2.startdate between d1.startdate and d1.enddate 

这是我通常在这段时间内做的连续范围

and d2.startdate between d1.startdate and d1.enddate and d2.startdate < d1.enddate

而不是这个表格

and d2.startdate >= d1.startdate and d2.startdate < d1.enddate

因为根据我的经验，它表现得更好.总是测试你的复杂性.