时间戳

首页 > 解决方案 > Oracle 排序间隙和孤岛查询

sql - Oracle 排序间隙和孤岛查询

问题描述

与其写长句和段落，不如让我展示数据和我想要实现的目标：

create table ssb_price (itm_no varchar2(10), price number, price_code varchar2(10), valid_from_dt date, valid_to_dt date);

insert into ssb_price values ('A001', 83, 'AB', '01-JAN-21', '05-JAN-21');
insert into ssb_price values ('A001', 83, 'AB', '06-JAN-21', '12-JAN-21');
insert into ssb_price values ('A001', 98, 'SPQ', '13-JAN-21', '17-JAN-21');
insert into ssb_price values ('A001', 83, 'AB', '19-JAN-21', '24-JAN-21');
insert into ssb_price values ('A001', 83, 'DE', '25-JAN-21', '30-JAN-21');
insert into ssb_price values ('A001', 83, 'DE', '31-JAN-21', '04-FEB-21');
insert into ssb_price values ('A001', 77, 'XY', '07-FEB-21', '12-FEB-21');
insert into ssb_price values ('A001', 77, 'XY', '15-FEB-21', '20-FEB-21');
insert into ssb_price values ('A001', 62, 'SD', '23-FEB-21', '26-FEB-21');
insert into ssb_price values ('A001', 59, 'SD', '26-FEB-21', '03-MAR-21');

对于特定的 itm_no 和 price，如果 from 和 to 日期是连续的，那么我应该得到那个值。对于价格 77，从日期到下一个日期之间有 2 天（第 13 和 14 天）的差距，因此它不是连续的。让我粘贴所需的输出应该是什么样子：（从excel中截取）

预期输出

我已经在另一个帖子中问过这个问题。但是那个帖子很旧，没有任何反馈，所以创建了这个。请让我知道我是否应该将这篇文章与上一篇文章合并。

标签： sqloraclegaps-and-islands

解决方案

这基本上是一个差距和孤岛问题。但是，您不想通过聚合来减少行数，而是希望在最后一步使用窗口函数。

在您的数据中，时间框架整齐地平铺。这建议使用lag()和 累积和来定义组：

select p.*,
       min(valid_from_dt) over (partition by itm_no, price, price_code, grp) as new_valid_from_dt,
       max(valid_to_dt) over (partition by itm_no, price, price_code, grp) as new_valid_to_dt
from (select p.*,
             sum(case when valid_from_dt = prev_valid_to_dt + interval '1' day then 0 else 1 end) over 
                   (partition by itm_no, price, price_code order by valid_from_dt) as grp
      from (select p.*,
                   lag(valid_to_dt) over (partition by itm_no, price, price_code order by valid_from_dt) as prev_valid_to_dt
            from ssb_price p 
           ) p
     ) p
order by itm_no, valid_from_dt;

这是一个 db<>fiddle。

推荐阅读