3-16 case式とignore nullsその1

SQLパズル

テーブル
Val
---
 10
 15
 20
 25
 30
 32
 34
 40

Val以下で最大の10の倍数と、
Val以上で最小の10の倍数を出力する。

出力結果
Val  以下で最大の10の倍数  以上で最小の10の倍数
---  -------------------  -------------------
 10                   10                   10
 15                   10                   20
 20                   20                   20
 25                   20                   30
 30                   30                   30
 32                   30                   40
 34                   30                   40
 40                   40                   40

データ作成スクリプト

create table NumTable(Val) as
select 10 from dual union
select 15 from dual union
select 20 from dual union
select 25 from dual union
select 30 from dual union
select 32 from dual union
select 34 from dual union
select 40 from dual;

SQL

--■■■case式とignore nullsを使う方法(10g以降)■■■
select Val,
Last_Value(case when mod(Val,10) = 0 then Val end ignore nulls)
over(order by Val)
as "以下で最大の10の倍数",
First_Value(case when mod(Val,10) = 0 then Val end ignore nulls)
over(order by Val Rows between Current Row and Unbounded Following)
as "以上で最小の10の倍数"
from NumTable;

--■■■decode関数とignore nullsを使う方法(10g以降)■■■
select Val,
Last_Value(decode(mod(Val,10),0,Val) ignore nulls)
over(order by Val)
as "以下で最大の10の倍数",
First_Value(decode(mod(Val,10),0,Val) ignore nulls)
over(order by Val Rows between Current Row and Unbounded Following)
as "以上で最小の10の倍数"
from NumTable;

--■■■相関サブクエリを使う方法■■■
select Val,
(select max(b.Val) from NumTable b
  where b.Val <= a.Val
    and mod(b.Val,10) = 0) as "以下で最大の10の倍数",
(select min(b.Val) from NumTable b
  where b.Val >= a.Val
    and mod(b.Val,10) = 0) as "以上で最小の10の倍数"
from NumTable a;

解説

Oracle10gから、First_Value関数とLast_Value関数で、
ignore nullsが指定できます。

case式とignore nullsを組み合わせると、
条件を満たす値を、探索できます。

decode関数、nullif関数、coalesce関数と
ignore nullsを組み合わせることもできます。

また、文字列関数の
RegExp_Substr関数、RegExp_Replace関数、Replace関数、translate関数と
ignore nullsを組み合わせることもできます。

分析関数の衝撃6 (応用編)

---