クエリのアンチパターン

クリスマスイブ！？関係ねえ！！さあ行こうぜ！無限大の彼方へ！！

引き続き、 O'Reilly Japan - SQLアンチパターンを赤ちゃんのほっぺたくらいの柔らかさに噛み砕くシリーズです。

13. Fear of the Unknown

目的

欠けている値を区別する

データベースにおいて、「データがない」という状態をどう表現するか、というお話。

アンチパターン

NULLを一般値として使う、または一般値をNULLとして使う

NULL は 0 や false や空文字列とは異なる、特殊な値です。

select cuteness + 10 from Actors 
    where name = 'ガッキー';
--> NULL（ 0ではない ）

NULLがよくわからんからNULLを避けて 'NULL'という文字列をNULLみたいに使う、とかいうのが一番ダメです。0除算エラーが嫌だからゼロを禁止する、みたいな。

解決策

NULLを一意な値として使う

3値論理を理解しようぞ〜というかんじです。スカラ式、論理式での扱い。 = nullではなくis nullやで。SQLにおいて「欠けている値」を表現するのはNULL一択です。

14. Ambiguous Groups

目的

グループ内で最大値を持つ行を取得する

以下に、各人物と、それぞれの人物に対するコメントがあります。コメントには、 score がついています。

Persons

person_id	name
1	ガッキー
2	星野源
3	かばお

Comments

person_id	comment	score
1	可愛い	4
1	結婚したい	10
1	最高	2
2	かっこいい	5
2	うらやましい	9
2	ヘタレ	1
3	カス	5
3	クズ	8
3	ポンコツ	10

人物ごとに、最も高いスコアを取得するクエリは以下です。

select person_id, name, max(score) as max_score
    from Persons inner join Comments using (person_id)
    group by person_id

結果は以下。

person_id	name	max_score
1	ガッキー	10
2	星野源	9
3	かばお	10

ここで、以下のように、最大のスコアを獲得したコメントの内容も取得したいです。

person_id	name	comment_text	max_score
1	ガッキー	結婚したい	10
2	星野源	うらやましい	9
3	かばお	ポンコツ	10

アンチパターン

非グループ化列を参照する

以下のようなことはできません。

select person_id, name, comment_text, max(score) as max_score
    from Comments inner join Persons using (person_id)
    group by person_id

person_idに対応したcommentは複数あり、どの行のcommentを取得すればいいかが不定だからです。単一値の原則ってやつです。

解決策

曖昧でない列を使用する

相関サブクエリ

select p1.person_id, p1.name, c1.score as max_score, c1.comment_text
    from Comments c1 inner join Persons p1 using (person_id)
    where not exists
        (select * from Comments c2 inner join Persons p2 using (person_id)
            where p1.person_id = p2.person_id and c1.score < c2.score);

読みやすい。でもパフォーマンスあやしい。

導出テーブル

select m.person_id, m.name, m.max_score, c1.comment_text
from Comments c1 inner join Persons p1 using (person_id)
    inner join (select p2.person_id, p2.name, max(c2.score) as max_score
        from Comments c2 inner join Persons p2 using (person_id)
        group by p2.person_id) m
    on p1.person_id = m.person_id and c1.score = m.max_score;

ここでは掘り下げませんが、相関サブクエリよりはパフォーマンスよさそうなやつです。

JOIN

select p1.person_id, p1.name, c1.score as max_score, c1.comment_text
from Comments c1
inner join Persons p1 on c1.person_id = p1.person_id
left outer join (
    Comments c2 inner join Persons p2 on c2.person_id = p2.person_id
)
on (p1.person_id = p2.person_id and (c1.score < c2.score)) -- 内部表よりscoreが大きい場合のみjoinする
where p2.person_id is null;  -- joinされなかった行はscoreが最大

サブクエリを使わないので、対象のデータが大量な場合にいいかもです。

15. Random Selection

目的

サンプル行をフェッチする

対象のテーブルのうち、どこでもいいからランダムに一行を取得したい、というやつ。

アンチパターン

データをランダムにソートする

select * from Persons
order by rand()
limit 1;

1行だけでいいのに、全部ソートするのは愚か。愚かを極めし者ですよね。

解決策

特定の順番に依存しない

1と最大値の間のランダムなキーを選択する

select p1.*
from Persons p1
inner join (
    select ceil(rand() * (select  max(person_id) from Persons)) rand_id
) p2 on p1.person_id = p2.rand_id;

主キーが1から始まって、かつ連続であることを前提としています。

すべてのキーのリストからランダムに1つを選択する

SQLではなくアプリケーションでキー列のみを取得して1つを選択し、それを使ってデータベースから行を取得します。テーブル全体のソートは回避できますが、リストが大きくなる場合などはきついです。行数がある程度少ないときに使えます。

オフセットを用いてランダムに行を選択する

行数をカウントし（統計情報として持ってるならそれを使っても）、0と行数までの間の乱数を作り、SQLでoffsetとして使って取得します。

ベンダー依存の解決策

データベース製品にはSAMPLEみたいな句が酔いされてるものもあるのでそれを使います。

16. Poor Man's Search Engine

目的

全文検索を行う

はい。

アンチパターン

パターンマッチ述語を使用する

ワイルドカード（%）や正規表現でパターンマッチします。パフォーマンス的にダメです。インデックス使えません。

解決策

適切なツールを使用する

はい。いうことなし！

17. Spaghetti Query

目的

SQLクエリの数を減らす

やりたいことを達成するために複数のクエリを撃ちまくるのはかっこよくないです。って考えます。

アンチパターン

複雑な問題をワンステップで解決しようとする

複雑なクエリを書いてると、意図せぬデカルト積を爆誕させて一生クエリが返ってこないっていうの、あるよねぇ〜。

解決策

分割統治を行う

できるだけ単純なクエリにしたい。そういう、お気持ち。

ワンステップずつ

プログラミングにおいてもメソッドやクラスの単一責務って意識すると思うんですが、クエリでもそういうことを考えましょうねぇ〜。

UNIONを用いる

サブクエリの列が一致する場合には、union allでくっつけてしまえばいいじゃない。

18. Implicit Columns

目的

タイプ数を減らす

列数がいっぱいあるときに、selectのあとにつらつらと列名を書くのがめんどくさい。

アンチパターン

ショートカットの罠に陥る

select * from … 、あるよねぇ〜。必要のない列を無駄に取得すると無駄 of 無駄。あと、insert into Persons values (xxx, yyy, … とかやってると、カラムが増えたときにエラーになるで。

解決策

列名を明示的に指定する

サボるな！！！！という、お気持ち。YAGNI！！！！

まとめ

うすうす気づいていたんですけど、本に載っている例と説明がえげつないくらいわかりにくいです。ぼくの脳ではまったく理解できません。ブログを書くにあたり、結果的にかなり説明も例も変えています。今回の中だと 14. Ambiguous Groups のところとか、やりたいことすらよくわからず、小一時間悩みました。アホでごめんなさい。