Oracle de Fizzbuzz #2 Tweet

前回はOracle上にFizzbuzz用基本的な環境を作成したところまでだった。
今回は、幾つかの前提を設け、なるべくこちらの意図した解答を引き出す工夫も含めて..

事前にnullを設定した１０行を登録した表を用意しておき、SQL文だけでFizzbuzzを解かせるなんてのもおもしろそう。

SYS> create table fizzbuzz.x (x number(1));

表が作成されました。

SYS> l
  1  begin
  2    for i in 1..10 loop
  3      insert into fizzbuzz.x values(null);
  4    end loop;
  5* end;
SYS> /

PL/SQLプロシージャが正常に完了しました。

SYS> commit;

コミットが完了しました。

SYS> set null NULLだ！
SYS> select * from fizzbuzz.x;

         X
----------
NULLだ！
NULLだ！
NULLだ！
NULLだ！
NULLだ！
NULLだ！
NULLだ！
NULLだ！
NULLだ！
NULLだ！

10行が選択されました。

上記のような前提なら、以下のような答えを期待しちゃう。ポイントは直積するか、という点と、rownum疑似列を使っうかどうか、ってところ。。

FIZZBUZZ> l
  1  select
  2    case 0
  3      when mod(rownum,15) then 'fizzbuzz'
  4      when mod(rownum,3) then 'fizz'
  5      when mod(rownum,5) then 'buzz'
  6      else to_char(rownum)
  7    end as results
  8  from
  9*   x a cross join x b
FIZZBUZZ>

事前にデータを準備せず、１００行の生成もSQLで行うことを期待するのであれば、オラクルだと Oracle9i以降であれば、cube又はrollup、Oracle10g以降であれば階層問合せのいずれかというところか。
case式の変わりに、decodeを利用したり、with句を利用しないで単純に副問い合せにしたりといういくつかのバリエーションはあるとおもうが・・・・・。
（いまさら、Oracle8iを利用することもないだろうから。。。まだ、利用している方、すみません。。。）

部分的cubeがいくつの小計を計算するかさえ知っていれば。。。以下のようなクエリで実現できる。以下の方法であれば、cubeがサポートされたOracle9i以降で実行できるクエリ版、fizzbuzzが作れる。

日本語のマニュアルはこちら
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19217-02/aggreg.htm#sthref1627

英語版のマニュアルはこちら
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1627

FIZZBUZZ> edit
file afiedt.bufが書き込まれました。

  1  with
  2    t as (
  3      select 1 from dual group by cube(1,1,1,1)
  4    )
  5  select
  6    case 0
  7      when mod(rownum,15) then 'fizzbuzz'
  8      when mod(rownum,3) then 'fizz'
  9      when mod(rownum,5) then 'buzz'
 10      else to_char(rownum)
 11    end as results
 12  from
 13    t a cross join t b
 14  where
 15*  rownum <= 100

尚、cubeがサポートされてないリリースだと、、ちと面倒だが、、、Oracle9i以降であれば使える方法ですよん。

階層問い合せで行う方法もあるが、個人的にはcubeを使ったクエリのほうが好き。以下の例で利用している階層問合せは、Oracle9iでは１行しか返してこないので将来的にもそのような挙動をするのか多少不安だし。。

FIZZBUZZ> l
  1    with
  2      t as
  3      (
  4        select level
  5        from dual
  6        connect by level <= 10
  7      ) 
  8    select
  9      case 0
 10        when mod(rownum,15) then 'fizzbuzz'
 11        when mod(rownum,3)  then 'fizz'
 12        when mod(rownum,5)  then 'buzz'
 13        else to_char(rownum)
 14      end as results
 15    from
 16*     t a cross join t b
FIZZBUZZ> 

といったところだろうか。