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

 找回密码
 注册
查看: 14990|回复: 5

[每日一题] PL/SQL Challenge 每日一题:2017-2-10 分析函数的RANGE/ROW窗口

[复制链接]
论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
发表于 2017-2-15 05:21 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者:        Kim Berg Hansen  

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

我有一张表,登记着我在哪些日期有手机漫游(漫游的意思是我在国外)。我还有一个视图,生成了特定期间的所有日期:

create table plch_roaming (
   roam_date   date primary key
)
/

insert into plch_roaming values (date '2016-01-03')
/
insert into plch_roaming values (date '2016-01-04')
/
insert into plch_roaming values (date '2016-01-05')
/
insert into plch_roaming values (date '2016-01-06')
/
insert into plch_roaming values (date '2016-01-09')
/
insert into plch_roaming values (date '2016-01-10')
/
insert into plch_roaming values (date '2016-01-11')
/
insert into plch_roaming values (date '2016-01-12')
/
insert into plch_roaming values (date '2016-01-14')
/
commit
/

create view plch_calendar as
select date '2016-01-01' + level - 1 as calendar_date
  from dual
connect by level <= 14
/

我的手机费是统一费率(相当于包月),当我在漫游也是如此。但仅仅是在漫游不太多的时候。 规则如下:

当我不在漫游(我在国内),通话包含在统一费率中。
当我在漫游时,如果我之前四天中漫游不多于两天,则通话仍然包含在统一费率中。

当我在漫游时,如果我之前四天中漫游多于两天,则在统一费率之上有附加费。

例如,一月份的前几天我不在漫游,那些天是统一费率的。1月3号我在漫游,但是之前的四天没有一天是在漫游,所以它仍然是统一费率。在1月4号,之前的四天中有一天漫游,所以1月4号仍然是统一费率。1月5号是统一费率,因为前四天之中有两天是在漫游。但是1月6号要收费,因为之前的四天中有三天在漫游。

对于一月份的前14天(日期由上述视图生成),我想要一个清单,每天显示我的所在地(在家(Home)或者漫游(Roaming)), 以及当天的通话是包含在统一费率(Flat)或者是要额外收费(Fee)。

哪些选项包含了一个查询能够给我这样的一个清单:

CALENDAR_D LOCATIO CHAR
---------- ------- ----
2016-01-01 Home    Flat
2016-01-02 Home    Flat
2016-01-03 Roaming Flat
2016-01-04 Roaming Flat
2016-01-05 Roaming Flat
2016-01-06 Roaming Fee
2016-01-07 Home    Flat
2016-01-08 Home    Flat
2016-01-09 Roaming Flat
2016-01-10 Roaming Flat
2016-01-11 Roaming Flat
2016-01-12 Roaming Fee
2016-01-13 Home    Flat
2016-01-14 Roaming Fee

(A)
select c.calendar_date
     , case
         when r.roam_days is null then 'Home'
         else 'Roaming'
       end as location
     , case
          when r.roam_days > 2 then 'Fee'
          else 'Flat'
       end as charge
  from plch_calendar c
  left outer join (
   select roam_date
        , count(*) over (
             order by roam_date
             RANGE between interval '4' day preceding
                       and interval '1' day preceding
          ) as roam_days
     from plch_roaming
  ) r
   on r.roam_date = c.calendar_date
order by c.calendar_date
/

(B)
select c.calendar_date
     , case
         when r.roam_days is null then 'Home'
         else 'Roaming'
       end as location
     , case
          when r.roam_days > 2 then 'Fee'
          else 'Flat'
       end as charge
  from plch_calendar c
  left outer join (
   select roam_date
        , count(*) over (
             order by roam_date
             ROWS between interval '4' day preceding
                       and interval '1' day preceding
          ) as roam_days
     from plch_roaming
  ) r
   on r.roam_date = c.calendar_date
order by c.calendar_date
/

(C)
select c.calendar_date
     , case
         when r.roam_days is null then 'Home'
         else 'Roaming'
       end as location
     , case
          when r.roam_days > 2 then 'Fee'
          else 'Flat'
       end as charge
  from plch_calendar c
  left outer join (
   select roam_date
        , count(*) over (
             order by roam_date
             ROWS between 4 preceding and 1 preceding
          ) as roam_days
     from plch_roaming
  ) r
   on r.roam_date = c.calendar_date
order by c.calendar_date
/


(D)
select calendar_date
     , case
         when roam_date is null then 'Home'
         else 'Roaming'
       end as location
     , case
          when roam_date is not null and roam_days > 2 then 'Fee'
          else 'Flat'
       end as charge
  from (
   select c.calendar_date
        , r.roam_date
        , count(r.roam_date) over (
             order by c.calendar_date
             RANGE between interval '4' day preceding
                       and interval '1' day preceding
          ) as roam_days
     from plch_calendar c
     left outer join plch_roaming r
      on r.roam_date = c.calendar_date
  )
order by calendar_date
/

(E)
select calendar_date
     , case
         when roam_date is null then 'Home'
         else 'Roaming'
       end as location
     , case
          when roam_date is not null and roam_days > 2 then 'Fee'
          else 'Flat'
       end as charge
  from (
   select c.calendar_date
        , r.roam_date
        , count(r.roam_date) over (
             order by c.calendar_date
             ROWS between 4 preceding and 1 preceding
          ) as roam_days
     from plch_calendar c
     left outer join plch_roaming r
      on r.roam_date = c.calendar_date
  )
order by calendar_date
/
论坛徽章:
0
发表于 2017-2-15 09:07 | 显示全部楼层
ACDE 本办法 跑一遍

B的rows没这种用法吧。。

使用道具 举报

回复
论坛徽章:
6
秀才
日期:2017-01-20 11:04:31秀才
日期:2017-01-20 11:00:36秀才
日期:2016-11-09 14:47:05技术图书徽章
日期:2016-11-09 14:47:05秀才
日期:2016-11-09 14:46:36秀才
日期:2017-03-02 10:35:32
发表于 2017-2-15 11:17 | 显示全部楼层
答案 A D E
B:错误,数据类型不一致,rows是按物理位置排序
C:错误,因为rows是按物理位置排序,导致从第3个roam_days开始都是大于2
E:正确,进行左连接后非漫游时roam_date是空值,count(r.roam_date)不会计算空值

使用道具 举报

回复
论坛徽章:
0
发表于 2017-2-15 11:45 | 显示全部楼层
本题考查分析函数中over()用法,3楼说得对

使用道具 举报

回复
论坛徽章:
263
乌索普
日期:2016-07-29 01:46:29射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25
发表于 2017-2-15 15:19 | 显示全部楼层
第一映像

SQL> select calendar_date,
  2         location,
  3         case when cnt > 2 and location ='Roaming' then 'Fee' else 'Flat' end charge
  4  from (
  5  select calendar_date,
  6         location,
  7         nvl(sum(case when location ='Roaming' then 1 else 0 end) over(order by calendar_date rows between 4 preceding and 1 preceding),0) cnt
  8   from (
  9  select a.calendar_date,
10         case
11           when t.roam_date is null then
12            'Home'
13           else
14            'Roaming'
15         end location
16    from PLCH_ROAMING t, plch_calendar a
17   where a.calendar_date = t.roam_date(+)
18   )
19  )
20  order by calendar_date;
CALENDAR_DATE LOCATION CHARGE
------------- -------- ------
2016/1/1      Home     Flat
2016/1/2      Home     Flat
2016/1/3      Roaming  Flat
2016/1/4      Roaming  Flat
2016/1/5      Roaming  Flat
2016/1/6      Roaming  Fee
2016/1/7      Home     Flat
2016/1/8      Home     Flat
2016/1/9      Roaming  Flat
2016/1/10     Roaming  Flat
2016/1/11     Roaming  Flat
2016/1/12     Roaming  Fee
2016/1/13     Home     Flat
2016/1/14     Roaming  Fee
14 rows selected

使用道具 举报

回复
论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
 楼主| 发表于 2017-2-16 06:07 | 显示全部楼层
答案ADE, 3楼得奖。

A: 在内联视图R中,我们用了分析函数COUNT的RANGE子句来找出之前四天之内的漫游天数。因为我们只计算PLCH_ROAMING中的行数,在那些没有漫游的日子里,CASE语句中的 R.ROAM_DAYS 将会是NULL(因为是外连接),这和正确地使得这些天的值为'Flat'。
B: ROWS BETWEEN 不支持天数间隔,这个选项会报错:
ORA-00932: inconsistent datatypes: expected - got INTERVAL DAY TO SECOND.
C: 此处我们正确使用了ROWS BETWEEN, 但是这意味着我们不是计算前四天的计数,而是前四行的计数,这导致在前两个漫游日之后,所有的漫游天数都被显示为'Fee', 错误输出如下:

CALENDAR_D LOCATIO CHAR
---------- ------- ----
2016-01-01 Home    Flat
2016-01-02 Home    Flat
2016-01-03 Roaming Flat
2016-01-04 Roaming Flat
2016-01-05 Roaming Flat
2016-01-06 Roaming Fee
2016-01-07 Home    Flat
2016-01-08 Home    Flat
2016-01-09 Roaming Fee
2016-01-10 Roaming Fee
2016-01-11 Roaming Fee
2016-01-12 Roaming Fee
2016-01-13 Home    Flat
2016-01-14 Roaming Fee

D: 此处我们在内联视图中对PLCH_CALENDAR 和 PLCH_ROAMING做了外连接,我们对ROAM_DATE的计数是在日历的所有天数上进行的。这意味着ROAM_DAYS在非漫游日不再为NULL,所以我们需要在CASE语句中对此特殊处理一下。

E: 既然对于期间中的每一天,在PLCH_CALENDAR恰好有一行(不多也不少),这意味着在此情况下ROWS BETWEEN 和 RANGE BETWEEN会得到相同结果,所以这个结果和前一选项相同。要注意的是,既然知道了ROWS 和 RANGE 在PLCH_CALENDAR中得到相同结果,使用ROWS会使得优化器对分析函数窗口的性能略为改善。

使用道具 举报

回复

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

本版积分规则

SACC2017购票8.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备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表