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

Question

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

实际上，我的场景比这更复杂一些，但我已简化为产品/日期/价格。

假设我们有这张表:

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 日，我需要一个 View 每天重复每个价格，直到发布新价格。像这样:

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 来完成此操作。

我认为这可以使用预言机分析来完成，但我对此并不熟悉。我试着读这个http://www.club-oracle.com/articles/analytic-functions-i-introduction-164/但我根本没明白。

Answer 1

您可以使用 CONNECT BY LEVEL 语法创建行生成器语句，与表中的不同产品交叉连接，然后将其外部连接到价格表。最后一步是使用 LAST_VALUE 函数和 IGNORE NULLS 重复价格，直到遇到新值，并且由于您想要一个 View ，因此使用 CREATE查看语句:

create view dense_prices_test as
select
    dp.price_date
  , dp.product
  , last_value(pt.price ignore nulls) over (order by dp.product, dp.price_date) price
from (
      -- Cross join with the distinct product set in prices_test
      select d.price_date, p.product
      from (
            -- Row generator to list all dates from first date in prices_test to today
            with dates as (select min(price_date) beg_date, sysdate end_date from prices_test)
            select dates.beg_date + level - 1 price_date 
            from dual
            cross join dates
            connect by level <= dates.end_date - dates.beg_date + 1
            ) d
      cross join (select distinct product from prices_test) p
     ) dp
left outer join prices_test pt on pt.price_date = dp.price_date and pt.product = dp.product;