5-17 nvl関数による分岐

SQLパズル

NumberDataテーブル
dateVal          numberVal
---------------  ---------
2007/1/27  5:00          1
2007/1/27  6:00          3
2007/1/27  6:30          4
2007/1/27  8:30          8
2007/1/27  9:00          9
2007/1/27  9:30         10
2007/1/27 10:00         11
2007/1/27 11:00         13
2007/1/27 12:00         15
2007/1/27 12:30         16

5:00, 5:30, 6:00, 6:30, 7:00〜という
30分おきのデータが、NumberDataテーブルにあります。

dateValの最小値以上の、date型の値を指定して、
直近の30分単位の時間で、過去の3件のデータを出力します。
ただし、本来あるべきはずのデータが欠けてる場合は、0とします。

出力結果
2007/1/27  8:30を指定した場合
val1  Val2  Val3
----  ----  ----
   8     0     0

2007/1/27 11:10を指定した場合
val1  Val2  Val3
----  ----  ----
  13     0    11

2007/1/27 12:30を指定した場合
val1  Val2  Val3
----  ----  ----
  16    15     0

データ作成スクリプト

create table NumberData(
dateVal   date,
numberVal number(2));

insert into NumberData values(to_date('2007/1/27 05:00','YYYY/MM/DD HH24:MI'), 1);
insert into NumberData values(to_date('2007/1/27 06:00','YYYY/MM/DD HH24:MI'), 3);
insert into NumberData values(to_date('2007/1/27 06:30','YYYY/MM/DD HH24:MI'), 4);
insert into NumberData values(to_date('2007/1/27 08:30','YYYY/MM/DD HH24:MI'), 8);
insert into NumberData values(to_date('2007/1/27 09:00','YYYY/MM/DD HH24:MI'), 9);
insert into NumberData values(to_date('2007/1/27 09:30','YYYY/MM/DD HH24:MI'),10);
insert into NumberData values(to_date('2007/1/27 10:00','YYYY/MM/DD HH24:MI'),11);
insert into NumberData values(to_date('2007/1/27 11:00','YYYY/MM/DD HH24:MI'),13);
insert into NumberData values(to_date('2007/1/27 12:00','YYYY/MM/DD HH24:MI'),15);
insert into NumberData values(to_date('2007/1/27 12:30','YYYY/MM/DD HH24:MI'),16);
commit;

SQL

def 指定日 = to_date('2007/01/27-08:30','YYYY/MM/DD-HH24:MI');

--■■■nvl関数を使う方法■■■
select
nvl((select numberVal from NumberData
      where dateVal = trunc(&指定日,'mi')
      -(mod(to_number(to_char(&指定日,'MI')),30)+30*0)/24/60),0) as Val1,
nvl((select numberVal from NumberData
      where dateVal = trunc(&指定日,'mi')
      -(mod(to_number(to_char(&指定日,'MI')),30)+30*1)/24/60),0) as Val2,
nvl((select numberVal from NumberData
      where dateVal = trunc(&指定日,'mi')
      -(mod(to_number(to_char(&指定日,'MI')),30)+30*2)/24/60),0) as Val3
from dual;

def 指定日 = to_date('2007/01/27-11:10','YYYY/MM/DD-HH24:MI');
/

def 指定日 = to_date('2007/01/27-12:30','YYYY/MM/DD-HH24:MI');
/

--■■■Lag関数とcase式を使う方法■■■
def 指定日 = to_date('2007/01/27-08:30','YYYY/MM/DD-HH24:MI');

select
case trunc(&指定日,'mi') -(mod(to_number(to_char(&指定日,'MI')),30)+30*0)/24/60
when dateVal     then numberVal
when Lag1dateVal then Lag1numberVal
when Lag2dateVal then Lag2numberVal
when Lag3dateVal then Lag3numberVal else 0 end as Val1,
case trunc(&指定日,'mi') -(mod(to_number(to_char(&指定日,'MI')),30)+30*1)/24/60
when dateVal     then numberVal
when Lag1dateVal then Lag1numberVal
when Lag2dateVal then Lag2numberVal
when Lag3dateVal then Lag3numberVal else 0 end as Val2,
case trunc(&指定日,'mi') -(mod(to_number(to_char(&指定日,'MI')),30)+30*2)/24/60
when dateVal     then numberVal
when Lag1dateVal then Lag1numberVal
when Lag2dateVal then Lag2numberVal
when Lag3dateVal then Lag3numberVal else 0 end as Val3
from (select dateVal,numberVal,
      Lag(dateVal)   over(order by dateVal) as Lag1dateVal,
      Lag(dateVal,2) over(order by dateVal) as Lag2dateVal,
      Lag(dateVal,3) over(order by dateVal) as Lag3dateVal,
      Lag(numberVal)   over(order by dateVal) as Lag1numberVal,
      Lag(numberVal,2) over(order by dateVal) as Lag2numberVal,
      Lag(numberVal,3) over(order by dateVal) as Lag3numberVal,
      max(dateVal) over() as maxdateVal
        from NumberData
       where &指定日 >= dateVal)
where dateVal = maxdateVal;

def 指定日 = to_date('2007/01/27-11:10','YYYY/MM/DD-HH24:MI');
/

def 指定日 = to_date('2007/01/27-12:30','YYYY/MM/DD-HH24:MI');
/

解説

nvl関数の引数に、スカラー問い合わせを使用してます。
Lag関数とcase式を使う方法もあります。

---

SQLを使った関係データベースアプリケーション

---