复制记录以填补日期之间的空白 [英] Duplicating records to fill gap between dates

问题描述

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

I need to do something really weird, which is to create fake records in a view to fill the gap between posted dates of product prices.

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

Actually, my scenario is a little bit more complicated than that, but I've simplified to products/dates/prices.

假设我们有这张桌子:

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日，我需要一个视图，该视图将每天重复每个价格，直到发布新价格为止.像这样:

Assuming today is Apr 21 2012, I need a view that shall repeat each price every day until a new price is posted. Like this:

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编程，我确实需要使用视图来做到这一点.

Any ideas how to do that? I cannot really use other auxiliary tables, triggers nor PL/SQL programming, I really need to do this using a view.

我认为可以使用Oracle Analytics(分析)完成此操作，但是我对此并不熟悉.我试图阅读此 http://www.club-oracle .com/articles/analytic-functions-i-introduction-164/，但我完全不了解.

I think this can be done using oracle analytics, but I'm not familiar with that. I tried to read this http://www.club-oracle.com/articles/analytic-functions-i-introduction-164/ but I didn't get it at all.

推荐答案

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

I think I have a solution using an incremental approach toward the final result with CTE's:

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条件过滤出没有相关价格日期的日期
• mindate retrieves the earliest possible date in the data set
• dates generates a calendar of dates from earliest possible date to today.
• productdates cross joins all possible products with all possible dates
• ranges determines which price date applied at each date
• the final query links which price date applied to the actual price and filters out dates for which there are no relevant price dates via the inner join condition

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

这篇关于复制记录以填补日期之间的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！