Oracle SQLパズル2-3-28

programming sql 下書き

某所でコメント書いたけど特に反応がなかったので、自分の所でちゃんとやってみる。

問題篇

1から開始される連番の、空き番号の最小値を求める。
ということで以下のようなルールになります。

  1. 1,2,4,5,6なら3が返る。
  2. 2,3,4,5,6なら1が返る。
  3. 1,2,3,4,5,6なら7が返る。

実際は、Sequenceの代替を想定するなら、テーブルが空なら1が返る、というルールも必要な気がします。

で、これをMySQL4.1で動くようなSQLとして書けるか？

回答篇

ということで書いてみたのが下記。
Row_Number関数を使う方法2と同様の理屈をウィンドウ関数無しで実装しています。

SELECT
    ifnull(ifnull(min(cnt), max(No) + 1), 1)
FROM
    (SELECT a.No, nullif(count(*), a.No) as cnt
    FROM GetNo a, GetNo b
    WHERE a.No >= b.No
    GROUP BY a.No) as x

IFNULLのネストは本当はCOALESCE使うべきなんですが、MySQL4.1のマニュアルには無かったのでやむなく。
そもそも、上で勝手に追加した、「テーブルが空なら1が返る」が不要なら、外側のIFNULLは不要です。

導出表のθ結合は、上記ルール1の場合、こんな感じになります。

a.No b.No count(*)
1 1 1
2 1,2 2
4 1,2,4 3
5 1,2,4,5 4
6 1,2,4,5,6 5

で、NULLIF(expr1, expr2)はexpr1とexpr2の結果が一致するとNULLを返し、異なる場合はexpr1を返すので、先頭からきっちり揃ってる部分はNULLになります。

a.No cnt
1 NULL
2 NULL
4 3
5 4
6 5

で、MIN(cnt)すると、この場合は3が返ります。
1が無ければ、Noが何番から始まっていようとcntは1から始まるため、結果は1でOK。
空き番号が無い場合、MIN(cnt)がNULLになるので、MAX(No) + 1を適用。
導出表が空なら、MIN(cnt)もMAX(No) + 1もNULLなので、結果は1。

検証

未実施。
一応、Oracle上での動作は確認済み。

教訓

ROW_NUMBERウィンドウ関数は、自己θ結合でcount(*)を使う事で代替可能。
H2のようなウィンドウ関数がないRDBMSでは有効なのかも。