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',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检索数据集中最早的可能日期 演示:http://www.sqlfiddle.com/#!4/e528f/126 (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |