読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

SQLクエリメモ - 整数型カラム中の欠番を見つけるクエリ

とあるプログラム(何)で、未処理のID(=欠番)を見つけ出して処理を実行するという必要性が生じたので、欠番を見つけ出すためのSQLクエリを作ってみた。(プログラムでループ回して1つずつチェックなんて、数万件のデータに対してはやってられないから)
プログラムが使用しているのがDB2だったので、DB2でのクエリ。

WITH temp(num, num2) AS (
    SELECT num, num-1
      FROM hoge
      WHERE num-1 NOT IN (SELECT num FROM hoge)
        AND num-1 >= (SELECT MIN(num) FROM hoge)
    UNION ALL
    SELECT num, num2-1
      FROM temp
      WHERE num2-1 NOT IN (SELECT num FROM hoge)
        AND num2-1 >= (SELECT MIN(num) FROM hoge)
)
SELECT temp.num2
  FROM temp
  ORDER BY temp.num2

たとえばこんなテーブル:

CREATE TABLE hoge(num INTEGER NOT NULL PRIMARY KEY)

INSERT INTO hoge(num)
    VALUES
          ( 2), ( 3), ( 4), ( 5), ( 6)
        , ( 7), (10), (11), (13), (14)
        , (17), (19), (22), (23), (26)
        , (29), (31), (34), (37), (38)
        , (41), (43), (46), (47)

を作って実行すると、

NUM2
-----------
          8
          9
         12
         15
         16
         18
         20
         21
         24
         25
         27
         28
         30
         32
         33
         35
         36
         39
         40
         42
         44
         45

また、上記のクエリだと、欠番すべてが返されるため、実際には既存の番号より1だけ小さい欠番だけを取得するクエリを使用している(最初に作ったのはこっち)。

WITH temp(num, num2) AS (
    SELECT num, num-1
      FROM hoge
      WHERE num-1 NOT IN (SELECT num FROM hoge)
        AND num-1 >= (SELECT MIN(num) FROM hoge)
)
SELECT temp.num2
  FROM temp
  ORDER BY temp.num2

こちらのクエリの場合、結果は次の通り。

NUM2
-----------
          9
         12
         16
         18
         21
         25
         28
         30
         33
         36
         40
         42
         45


ちなみに、全然関係ないが、テーブルhogeに含まれているのはどんな数値の列でしょう。(ヒント:2つの数列が混在しています)