SQLでloopを使わず連番の歯抜けの部分を探してみようという話

sql mysql

はじめに

例えば以下のような数字の列があるとする。下の例は連番を期待した数値なのだが一部歯抜けになっているという数字の列の例。

1, 2, 3, 6, 8 // 4,5,7がない

このような連番を期待している数字の列から歯抜けになっている部分(上の例では4,5,7)を見つけられないかという話。

連番テーブルを作ってそれとjoin

よくある話。省略。

差集合とかで頑張る

連番を期待している数字列の集合をNとする。このNのすべての要素に+1した集合をN+1と書くことにする。この時、N+1とNの差集合をそれぞれ取ると歯抜けの値が分かりそう。

例えば以下の様な数字の列が与えられたとして。

N =   {1,2,3,6,8}
N+1 = {2,3,4,7,9}

それぞれ差集合を取ると以下の様になる

N+1 - N = {4,7,9}
N - N+1 = {1,6,8}

ここで始端である1と終端である9を排除すると、それぞれ歯抜けの値の開始と終了の値になる。

1, 4..6, 7..8, 9

から歯抜けの値は、4,5,7。

このようにして連番から歯抜けの部分を探してみようという話。

SQLでやる

mysqlでやった。最初temporary tableを使おうとしたら自己結合を書こうとした辺りでエラーになってしまう。どうやらtemporary tableでは同じ式の間で2度使うことはできないらしい

ERROR 1137 (HY000): Can't reopen table: 'x'

テキトウにNというテーブルを作って、上の例と同じように 1, 2, 3, 6, 8 という値を追加しておく

CREATE TABLE N (v int(11) primary key);
INSERT N VALUES (1),(2),(3),(6),(8);

そして mysqlには full outer joinが存在しなかった。とりあえず差集合的なものはleft outer join, right outer joinを使って取り出すことにする。

-- N+1 - N
mysql>
SELECT x.v, y.v
  from N as x
  RIGHT OUTER JOIN (SELECT v + 1 as v from N) as y
  on x.v = y.v 
where x.v is NULL;
+------+---+
| v    | v |
+------+---+
| NULL | 4 |
| NULL | 7 |
| NULL | 9 |
+------+---+
3 rows in set (0.00 sec)

-- N - N+1
mysql>
SELECT x.v, y.v
  from N as x
  LEFT OUTER JOIN (SELECT v + 1 as v from N) as y
  on x.v = y.v
where y.v is NULL;
+---+------+
| v | v    |
+---+------+
| 1 | NULL |
| 6 | NULL |
| 8 | NULL |
+---+------+
3 rows in set (0.00 sec)

しょうが無いのでこれらのunionを取る。そしてどことなくそれっぽい感じの位置にorder byするためにあれこれする。

mysql>
SELECT * FROM 
  (SELECT x.v as x_v, y.v as y_v
     from N as x
     LEFT OUTER JOIN (SELECT v + 1 as v from N) as y
     on x.v = y.v
  where y.v is NULL
  UNION
  SELECT x.v as x_v, y.v as y_v
     from N as x
     RIGHT OUTER JOIN (SELECT v + 1 as v from N) as y
     on x.v = y.v
  where x.v is NULL
  ) as q order by COALESCE(q.x_v, 0) + COALESCE(q.y_v, 0);
+------+------+
| x_v  | y_v  |
+------+------+
|    1 | NULL |
| NULL |    4 |
|    6 | NULL |
| NULL |    7 |
|    8 | NULL |
| NULL |    9 |
+------+------+
6 rows in set (0.02 sec)

心の目で見て、"4,5","7" を見つけることができそう。という当たりで飽きた。