ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 4982|回复: 10

[每日一题] PL/SQL Challenge 每日一题:2017-1-12 12C新特性:时间有效性(Temporal validity)

[复制链接]
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-1-17 04:34 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

注:本题执行环境为12c或更高

我们有一张物品价格表,定义着哪段时间内特定的价格是有效的:

create table plch_prices (
   item        varchar2(10) not null
, valid_from  date
, valid_up_to date
, price       number not null
, period for valid_price (valid_from, valid_up_to)
)
/

insert into plch_prices values (
   'Santabeard', date '2016-01-01', date '2016-12-11', 100
)
/
insert into plch_prices values (
   'Santabeard', date '2016-12-11', date '2016-12-27',  75
)
/
insert into plch_prices values (
   'Santabeard', date '2016-12-27', null             , 105
)
/
insert into plch_prices values (
   'Beachball' , null             , date '2016-12-01',  12
)
/
insert into plch_prices values (
   'Beachball' , date '2016-12-01', null             ,  15
)
/
insert into plch_prices values (
   'Chessgame' , null             , date '2016-12-16',  23
)
/
insert into plch_prices values (
   'Chessgame' , date '2016-12-16', null             ,  21
)
/
commit
/

VALID_FROM 列定义着价格有效的起始日期,包括该日期本身。VALID_UP_TO定义着价格有效的截止日期,但不包括日期本身。所以VALID_UP_TO的值为2016-12-17意味着价格有效至2016-12-16 23:59:59。

但是我们在数据中只用到“整”日期(没有时间部分),而且我们能确保给定物品的日期区间不会重叠。

我们需要得到一个发生于2016年12月的价格变动清单,需要指出物品名称,旧价格截止日期,旧价格,新价格起始日期,以及新价格。

哪些选项包含了一个查询能够给我们这样一个价格变动清单,返回这样的输出:

ITEM       VALID_UP_T  OLD_PRICE VALID_FROM  NEW_PRICE
---------- ---------- ---------- ---------- ----------
Chessgame  2016-12-16         23 2016-12-16         21
Santabeard 2016-12-11        100 2016-12-11         75
Santabeard 2016-12-27         75 2016-12-27        105

注意:输出的格式被设置为:

alter session set nls_date_format='YYYY-MM-DD'
/

(A)
select item, valid_up_to, old_price, valid_from, new_price
  from (
   select item, valid_up_to, price as old_price
        , lead(valid_from) over (
             partition by item order by valid_up_to
          ) as valid_from
        , lead(price) over (
             partition by item order by valid_up_to
          ) as new_price
     from plch_prices
             versions period for valid_price
             between date '2016-12-01' and date '2016-12-31'
  )
where new_price is not null
order by item, valid_up_to
/

(B)
select old_prices.item
     , old_prices.valid_up_to
     , old_prices.price as old_price
     , new_prices.valid_from
     , new_prices.price as new_price
  from (
   select item, valid_up_to, price
     from plch_prices
             as of period for valid_price date '2016-12-01'
  ) old_prices
  join (
   select item, valid_from, price
     from plch_prices
             as of period for valid_price date '2017-01-01'
  ) new_prices
      on new_prices.item = old_prices.item
where new_prices.price != old_prices.price
order by old_prices.item, old_prices.valid_up_to
/

(C)
select item, valid_up_to, old_price, valid_from, new_price
  from (
   select item, valid_up_to, price as old_price
        , lead(valid_from) over (
             partition by item order by valid_up_to
          ) as valid_from
        , lead(price) over (
             partition by item order by valid_up_to
          ) as new_price
     from plch_prices
    where (valid_from  is null or valid_from  <= date '2016-12-31')
      and (valid_up_to is null or valid_up_to >  date '2016-12-01')
  )
where new_price is not null
order by item, valid_up_to
/

(D)
select old_prices.item
     , old_prices.valid_up_to
     , old_prices.price as old_price
     , new_prices.valid_from
     , new_prices.price as new_price
  from (
   select item, valid_up_to, price
     from plch_prices
    where (valid_from  is null or valid_from  <= date '2016-12-01')
      and (valid_up_to is null or valid_up_to >  date '2016-12-01')
  ) old_prices
  join (
   select item, valid_from, price
     from plch_prices
    where (valid_from  is null or valid_from  <= date '2017-01-01')
      and (valid_up_to is null or valid_up_to >  date '2017-01-01')
  ) new_prices
      on new_prices.item = old_prices.item
where new_prices.price != old_prices.price
order by old_prices.item, old_prices.valid_up_to
/

论坛徽章:
254
秀才
日期:2016-09-27 15:16:21技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25水瓶座
日期:2016-03-24 22:16:36摩羯座
日期:2016-03-17 15:09:14巨蟹座
日期:2016-03-12 12:03:33双鱼座
日期:2016-02-27 21:51:59射手座
日期:2016-02-23 23:47:49双子座
日期:2016-01-27 13:01:30
发表于 2017-1-17 17:16 | 显示全部楼层
AC 执行学习了

使用道具 举报

回复
论坛徽章:
25
ITPUB官方微博粉丝徽章
日期:2011-08-17 10:35:36秀才
日期:2017-03-02 10:35:322016猴年福章
日期:2016-02-18 09:31:302016猴年福章
日期:2016-02-23 09:58:342015年新春福章
日期:2015-03-06 11:57:312014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:31ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262012新春纪念徽章
日期:2012-01-04 11:53:29
发表于 2017-1-17 18:28 | 显示全部楼层
最近的题好多新知识啊看着各种迷糊

使用道具 举报

回复
认证徽章
论坛徽章:
167
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
发表于 2017-1-17 20:19 | 显示全部楼层
A.Beachball的无效数据看不见了,因为有效期是 period for valid_price
             between date '2016-12-01' and date '2016-12-31',A正确
B.包含as of period for valid_price date '2016-12-01'这个数据只有3条满足,另一个子表也是,不可能出现Santabeard 两条的情况
C.D其实就是想表达AB的意思。只不过是传统写法。
选AC

坐等大叔讲讲这个功能,顺便给我们说说使用场景。

使用道具 举报

回复
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-1-18 04:54 | 显示全部楼层
答案AC, 2楼得奖。

A:(推荐)
VERSIONS PERIOD FOR 给了我们价格在12月部分有效的那些数据行,所以Beachball的第一个价格被过滤掉了。然后我们在结果上用了分析函数LEAD来在有变动的地方得到“新”价格。最后,通过过滤掉LEAD不返回数据(分区中的最后一行)的那些行,我们就跳过了在12月底依然有效的那些价格,这样剩下来的就仅仅是想要的那些变动记录。

B:
这里的方法是不用VERSIONS PERIOD FOR, 而是用AS OF PERIOD FOR来得到12月起止12月底(等于1月起始日)的有效价格。然后我们比较并查看哪里发生变化。这个方法忽略了Santabeard在12月变动两次的事实,所以它显示了这个错误结果:

ITEM       VALID_UP_T  OLD_PRICE VALID_FROM  NEW_PRICE
---------- ---------- ---------- ---------- ----------
Chessgame  2016-12-16         23 2016-12-16         21
Santabeard 2016-12-11        100 2016-12-27        105

C: 这和A很类似,只是用了一个DIY的WHERE子句来找出12月有效的价格,而不是用 VERSIONS PERIOD FOR,这样的话本选项在12.1之前的老版本中也能工作。
D: 这和B很类似,只是用了一个DIY的WHERE子句而不是AS OF PERIOD FOR 来找出两个给定日期的有效价格,这样的话本选项在12.1之前的老版本中也能执行,并且返回和B一样的错误结果。
----------------------
我没有在生产系统上用过这个功能,具体使用参见汤姆大叔的博客:
http://tkyte.blogspot.ca/2013/07 ... k-or-see-it-as.html

其实就是写起来简洁易读一些,在底层优化器还是把它改写成传统的WHERE条件了。

使用道具 举报

回复
认证徽章
论坛徽章:
167
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
发表于 2017-1-18 10:33 | 显示全部楼层
newkid 发表于 2017-1-18 04:54
答案AC, 2楼得奖。

A:(推荐)

我举报,猫的回复太简单了,章归我了。

使用道具 举报

回复
论坛徽章:
254
秀才
日期:2016-09-27 15:16:21技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25水瓶座
日期:2016-03-24 22:16:36摩羯座
日期:2016-03-17 15:09:14巨蟹座
日期:2016-03-12 12:03:33双鱼座
日期:2016-02-27 21:51:59射手座
日期:2016-02-23 23:47:49双子座
日期:2016-01-27 13:01:30
发表于 2017-1-18 10:42 | 显示全部楼层
Naldonado 发表于 2017-1-18 10:33
我举报,猫的回复太简单了,章归我了。

 KAO KAO 

使用道具 举报

回复
论坛徽章:
27
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-23 23:08:49马上有对象
日期:2014-11-21 22:36:46暖羊羊
日期:2015-03-04 14:50:372015年新春福章
日期:2015-03-06 11:57:31秀才
日期:2016-03-24 09:10:24秀才
日期:2016-03-24 09:20:52秀才
日期:2016-04-21 14:08:53秀才
日期:2016-06-23 14:15:06
发表于 2017-1-18 13:56 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-1-19 04:19 | 显示全部楼层
Naldonado 发表于 2017-1-18 10:33
我举报,猫的回复太简单了,章归我了。

你已经拿太多章了,最近又调为本版版主,所以应该把机会让给别人,吸引更多人参与答题。我把你放入备胎库,如果同一题没有别人答对才会考虑你。

使用道具 举报

回复
认证徽章
论坛徽章:
167
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
发表于 2017-1-19 08:03 | 显示全部楼层
newkid 发表于 2017-1-19 04:19
你已经拿太多章了,最近又调为本版版主,所以应该把机会让给别人,吸引更多人参与答题。我把你放入备胎库 ...

我尽然成了备胎
来自苹果客户端来自客户端

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票7.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表