我需要做一些非常奇怪的事情,即在视图中创建虚假记录以填补产品价格的发布日期之间的差距.
实际上,我的情况比这复杂一点,但我已经简化为产品/日期/价格.
假设我们有这张表：
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',15); insert into PRICES_TEST values (date'2012-04-13','Hammer',10); insert into PRICES_TEST values (date'2012-04-16',15); insert into PRICES_TEST values (date'2012-04-19',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编程,我真的需要使用视图来做到这一点.
我认为这可以使用oracle分析完成,但我对此并不熟悉.我试着阅读这个http://www.club-oracle.com/articles/analytic-functions-i-introduction-164/,但我根本没有得到它.
解决方法
我想我有一个解决方案,使用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检索数据集中最早的可能日期
>日期生成从最早可能日期到今天的日期日历.
> productdates cross加入所有可能的产品和所有可能的日期
>范围确定在每个日期应用的价格日期
>最终查询链接应用于实际价格的价格日期,并通过内部连接条件筛选出没有相关价格日期的日期
