SQLパズル 解答と解説

 それでは、「文字列中の重複する文字を削除する 」の解答と解説です。

 まずはセルコのオリジナルの解からご紹介しましょう。
 最初に、対象となる文字列の文字数をカバーできる適当な大きさの連番テーブルSequenceを作ります。この方法は、『指南書』の「1-9 数列を扱う」や「SQLで数列パズルを解く」などを参照してください。

 それが出来たら、各文字列について、残す文字列と削除する文字列を検査して、残す文字集合だけを保存します。


/* 残す文字集合Keeper */
CREATE VIEW Keeper (word_key, seq, letter) AS
SELECT word_key, seq,
SUBSTRING(word_txt FROM S.seq FOR 1) AS letter
FROM WordList W, Sequence S
WHERE S.seq >= 1
AND S.seq <= CHAR_LENGTH(word_txt)
AND POSITION(SUBSTRING(word_txt FROM S.seq FOR 1)
IN SUBSTRING(word_txt FROM 1 FOR S.seq -1)) = 0;

word_key | seq | letter

                                            • -

1 | 1 | a
2 | 1 | a
2 | 2 | b
3 | 1 | a
3 | 3 | c
3 | 5 | e
3 | 4 | d
3 | 2 | b
4 | 5 | d
4 | 6 | e
4 | 2 | b
4 | 1 | a
4 | 4 | c
5 | 7 | g
5 | 1 | a
5 | 2 | b
5 | 3 | c
5 | 4 | d
5 | 5 | e
5 | 6 | f

 連番テーブルは、各文字列中の文字位置を示すインデックスを取得する役割を果たしています。このインデックスを使って、ループ処理を代替しています。ループを連番テーブルで代用するこの方法は、セルコが頻繁に使う方法で、『SQLパズル 第2版』の「パズル60 バーコード」でも使われています。

 この残す文字集合が分かれば、後は、元の文字列から残す文字のインデックスを頼りに文字を切り出せば完成です。


SELECT W.word_key,
SUBSTRING(word_txt FROM 1 FOR 1)
|| CASE WHEN EXISTS
(SELECT *
FROM Keeper K
WHERE K.word_key = W.word_key
AND K.seq = 2)
THEN SUBSTRING(word_txt FROM 2 FOR 1)
ELSE '' END
|| CASE WHEN EXISTS
(SELECT *
FROM Keeper K
WHERE K.word_key = W.word_key
AND K.seq = 3)
THEN SUBSTRING(word_txt FROM 3 FOR 1)
ELSE '' END
|| CASE WHEN EXISTS
(SELECT *
FROM Keeper K
WHERE K.word_key = W.word_key
AND K.seq = 4)
THEN SUBSTRING(word_txt FROM 4 FOR 1)
ELSE '' END
......
FROM WordList W;

 動作確認はPostgreSQL8.3で行いました。文字列関数は実装によってけっこう違いがあるので、他のDBではSUBSTRINGや||を変える必要があるかもしれません。

 この方法の評価なのですが、まずすぐに分かる欠点は拡張性がないことです。文字長があらかじめ分かっていないと使えないし、CASE式をずらずら並べるのも冗長です。簡単な処理を簡単に記述できない、というのはプログラミング言語として誉められない(これは、繰り返しになりますがSQLが文字列操作を自分の仕事だと思っていないからです)。

 その点で、明智さんとkamataroさんの解は、実装依存であるものの、拡張性に富む点で優れています。

 私は、Keeperから累積的に文字列を作る方法がないかなー、と思って探していたのですが、見つけられなかった。ちょうどSUM() ... OVER() で累計を求めるようなイメージで文字列を順に結合できればいいんだけど、文字列結合(||)の演算子にOVER句は使えないのでこれは無理。もどかしい。

 さて、それでは理事長は明日からタヒチボラボラ島にバカンスへ出かけます。皆さんも労働者の権利である有休はしっかり取得しましょう。