复制记录以填补日期之间的空白

小编典典

复制记录以填补日期之间的空白

sql

我需要做一些非常奇怪的事情，那就是创建虚假记录，以填补产品价格发布日期之间的空白。

实际上，我的情况比这要复杂一些，但是我已经简化为产品/日期/价格。

假设我们有此表：

create table PRICES_TEST
(
   PRICE_DATE    date          not null,
   PRODUCT       varchar2(13) not null,
   PRICE         number
);

alter table PRICES_TEST 
  add constraint PRICES_TEST_PK
    primary key (PRICE_DATE, PRODUCT);

有了这些记录：

insert into PRICES_TEST values (date'2012-04-15', 'Screw Driver', 13);
insert into PRICES_TEST values (date'2012-04-18', 'Screw Driver', 15);

insert into PRICES_TEST values (date'2012-04-13', 'Hammer', 10);
insert into PRICES_TEST values (date'2012-04-16', 'Hammer', 15);
insert into PRICES_TEST values (date'2012-04-19', 'Hammer', 17);

选择记录将向我返回以下内容：

PRICE_DATE                PRODUCT       PRICE                  
------------------------- ------------- ---------------------- 
13-Apr-2012 00:00:00      Hammer        10                     
16-Apr-2012 00:00:00      Hammer        15                     
19-Apr-2012 00:00:00      Hammer        17                     
15-Apr-2012 00:00:00      Screw Driver  13                     
18-Apr-2012 00:00:00      Screw Driver  15

假设今天是2012年4月21日，我需要 一个视图 ， 该视图 将每天重复每个价格，直到发布新价格为止。像这样：

PRICE_DATE                PRODUCT       PRICE                  
------------------------- ------------- ---------------------- 
13-Apr-2012 00:00:00      Hammer        10                     
14-Apr-2012 00:00:00      Hammer        10                     
15-Apr-2012 00:00:00      Hammer        10                     
16-Apr-2012 00:00:00      Hammer        15                     
17-Apr-2012 00:00:00      Hammer        15                     
18-Apr-2012 00:00:00      Hammer        15                     
19-Apr-2012 00:00:00      Hammer        17                     
20-Apr-2012 00:00:00      Hammer        17                     
21-Apr-2012 00:00:00      Hammer        17                     
15-Apr-2012 00:00:00      Screw Driver  13                     
16-Apr-2012 00:00:00      Screw Driver  13                     
17-Apr-2012 00:00:00      Screw Driver  13                     
18-Apr-2012 00:00:00      Screw Driver  15                     
19-Apr-2012 00:00:00      Screw Driver  15                     
20-Apr-2012 00:00:00      Screw Driver  15                     
21-Apr-2012 00:00:00      Screw Driver  15

任何想法如何做到这一点？我不能真正使用其他辅助表，触发器或PL / SQL编程，我确实需要使用 view 来做到这一点。

我认为可以使用Oracle Analytics（分析）完成此操作，但我对此并不熟悉。我尝试阅读此 http://www.club-
oracle.com/articles/analytic-functions-i-introduction-164/，但我一无所获。

阅读 179

2021-03-17

共1个答案

小编典典

我认为我有一个解决方案，使用增量方法来实现CTE的最终结果：

with mindate as
(
  select min(price_date) as mindate from PRICES_TEST
)
,dates as
(
  select mindate.mindate + row_number() over (order by 1) - 1 as thedate from mindate,
    dual d connect by level <= floor(SYSDATE - mindate.mindate) + 1
)
,productdates as
(
  select p.product, d.thedate
  from (select distinct product from PRICES_TEST) p, dates d
)
,ranges as
(
  select
    pd.product,
    pd.thedate,
    (select max(PRICE_DATE) from PRICES_TEST p2
     where p2.product = pd.product and p2.PRICE_DATE <= pd.thedate) as mindate
    from productdates pd
)
select 
    r.thedate,
    r.product,
    p.price
from ranges r
inner join PRICES_TEST p on r.mindate = p.price_date and r.product = p.product
order by r.product, r.thedate

mindate 检索数据集中最早的日期
dates 生成从最早的日期到今天的日期日历。
productdates 将所有可能的日期与所有可能的产品交叉连接
ranges 确定在每个日期应用哪个价格日期
最终查询会链接哪个价格日期应用于实际价格，并通过inner join条件过滤掉没有相关价格日期的日期

演示：http :
//www.sqlfiddle.com/#!4/ e528f/
126

2021-03-17