oracle 每日一题-分析函数的RANGE/ROW窗口

作者：        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

/

本题考查分析函数中over()用法开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary）按照salary排序进行累计，orderby是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unboundedpreceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unboundedpreceding and unbounded following）

本题关键

  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

over (
             order by roam_date
             RANGE between interval '4' day preceding
                       and interval '1' day preceding
          )

   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

over (
             order by c.calendar_date
             ROWS between 4 preceding and 1 preceding
          ) 

答案 A D E
B:错误，数据类型不一致，rows是按物理位置排序
C:错误，因为rows是按物理位置排序，导致从第3个roam_days开始都是大于2
E:正确，进行左连接后非漫游时roam_date是空值，count(r.roam_date)不会计算空值