10-269 model句で期間型のsumを求める

SQLパズル

LogData
ID  seihin  koutei  kaishi          syuryo            kubun
--  ------  ------  --------------  ----------------  -------
 1  AAAAAA  1st     2008/9/1 08:00  2008/09/01 08:50  MEASURE
 3  AAAAAA  1st     2008/9/1 08:15  2008/09/01 08:20  PAUSE
 5  AAAAAA  2nd     2008/9/1 08:55  2008/09/01 09:55  MEASURE
 6  AAAAAA  2nd     2008/9/1 09:20  2008/09/01 09:28  PAUSE
 8  AAAAAA  2nd     2008/9/1 09:42  2008/09/01 09:45  PAUSE
11  BBBBBB  1st     2008/9/1 10:05  2008/09/01 10:40  MEASURE
13  BBBBBB  1st     2008/9/1 10:15  2008/09/01 10:20  PAUSE
14  CCCCCC  1st     2008/9/1 10:45  2008/09/01 10:50  MEASURE

seihin,kouteiの組み合わせごとの
syuryo-kaishiの合計を求める。
ただし、
kubun = PAUSEなら syuryo-kaishiには、マイナスを掛けたものを加算の対象とする。

出力結果
seihin  koutei  minute
------  ------  ------
AAAAAA  1st     45
AAAAAA  2nd     49
BBBBBB  1st     30
CCCCCC  1st      5

データ作成スクリプト

create table LogData(
ID      number(2),
seihin  char(6),
koutei  char(3),
kaishi  date,
syuryo  date,
kubun   varchar2(7),
primary key (ID));

insert into LogData values( 1,'AAAAAA','1st',to_date('200809 08:00','yyyymm hh24:mi'),
                                             to_date('200809 08:50','yyyymm hh24:mi'),'MEASURE');
insert into LogData values( 3,'AAAAAA','1st',to_date('200809 08:15','yyyymm hh24:mi'),
                                             to_date('200809 08:20','yyyymm hh24:mi'),'PAUSE');
insert into LogData values( 5,'AAAAAA','2nd',to_date('200809 08:55','yyyymm hh24:mi'),
                                             to_date('200809 09:55','yyyymm hh24:mi'),'MEASURE');
insert into LogData values( 6,'AAAAAA','2nd',to_date('200809 09:20','yyyymm hh24:mi'),
                                             to_date('200809 09:28','yyyymm hh24:mi'),'PAUSE');
insert into LogData values( 8,'AAAAAA','2nd',to_date('200809 09:42','yyyymm hh24:mi'),
                                             to_date('200809 09:45','yyyymm hh24:mi'),'PAUSE');
insert into LogData values(11,'BBBBBB','1st',to_date('200809 10:05','yyyymm hh24:mi'),
                                             to_date('200809 10:40','yyyymm hh24:mi'),'MEASURE');
insert into LogData values(13,'BBBBBB','1st',to_date('200809 10:15','yyyymm hh24:mi'),
                                             to_date('200809 10:20','yyyymm hh24:mi'),'PAUSE');
insert into LogData values(14,'CCCCCC','1st',to_date('200809 10:45','yyyymm hh24:mi'),
                                             to_date('200809 10:50','yyyymm hh24:mi'),'MEASURE');
commit;

SQL

--■■■sum関数を使う方法■■■
select seihin,koutei,
sum(case kubun when 'MEASURE' then syuryo-kaishi
               when 'PAUSE'   then -(syuryo-kaishi)
    end*24*60*60)/60 as minute
  from LogData
group by seihin,koutei
order by seihin,koutei;

--■■■model句を使う方法(10g以降)■■■
col diff for a40

select seihin,koutei,diff
from (select seihin,koutei,diff,Rn,maxRn
        from test.LogData
      model
      partition by (seihin,koutei)
      dimension by (Row_Number() over(partition by seihin,koutei order by ID) as Rn)
      measures(to_timestamp(to_char(syuryo,'YYYY/MM/DD HH24:MI:SS'),
                                           'YYYY/MM/DD HH24:MI:SS')
              -to_timestamp(to_char(kaishi,'YYYY/MM/DD HH24:MI:SS'),
                                           'YYYY/MM/DD HH24:MI:SS') as diff,kubun,
      count(*) over(partition by seihin,koutei) as maxRn)
      rules(diff[any] order by Rn = presentv(diff[cv()-1],
                                             case kubun[cv()]
                                             when 'MEASURE' then diff[cv()-1]+diff[cv()]
                                             when 'PAUSE'   then diff[cv()-1]-diff[cv()]
                                             end,diff[cv()])))
 where Rn = maxRn
order by seihin,koutei;

解説

date型-date型は、24*60*60を分母とした、日数の差となります。
誤差を減らすには、なるべく秒で計算するといいと思われます。

Oracle10gの段階で、(11gは不明)
sum関数は期間型を使えませんが、model句で代用することはできます。
しかし、表関数を使ったほうがシンプルでしょうねぇ

10-52 Interval型の総合計を求める

---