SQLチューニングの条件分岐に関する備忘録
前回の記事の続きです。
SQLにおける分岐条件
CASE式を用いる方法と集合演算子UNIONを用いる方法があるが、基本は以下の二つの理由からCASE式を用いること。
- CASE文の方がパフォーマンスが良い
- SQL自体が簡潔に書ける
UNIONを使った条件分岐
SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year <= 2001
UNION ALL
SELECT item_name, year, price_tax_in AS price
FROM Items
WHERE year >= 2002;
CASE式を使った条件分岐
SELECT item_name, year,
CASE WHEN year <= 2001 THEN price_tax_ex
WHEN year >= 2002 THEN price_tax_in END AS price
FROM Items;
実行計画を確認するとUNIONのはテーブルに二回アクセスしているが、CASE文は一回しかアクセスしないので、二倍程度パフォーマンスに差が出る。
しかし、異なるテーブルの結果をマージするときはUNIONを使わなければならない。
UNION必須なケース
SELECT col_1
FROM Table_A
WHERE col_2 = 'A'
UNION ALL
SELECT col_3
FROM Table_B
WHERE col_4 = 'B';
また、UNIONを使ってうまく絞り込みの効くインデックスを利用できて、かつUNION以外の手段ではそうしたインデックスを利用できない場合、UNIONを利用したほうがパフォーマンスが良いこともある。
カットと集約
GROUP BY句：集約とカットの機能を持つ
GROUP BYを用いて集約操作を行った場合、SELECT句には以下の三つしか書くことができない。
- 定数
- GROUP BY句で指定した集約キー
- 集約関数
SELECT id,
CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END AS data_1,
CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END AS data_2
FROM NonAggTbl
GROUP BY id;
上記のようなクエリは構文エラーが発生します。正しくは下記のクエリです。
SELECT id,
MAX (CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END) AS data_1,
MAX (CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END) AS data_2
FROM NonAggTbl
GROUP BY id;
PARTITION BY句：カットの機能のみ持つウィンドウ関数
PARTITION BYは集約機能をもたないので、GROUP BYと違い、SELECT句に普通の列を指定できる。
SELECT name, age,
CASE WHEN age < 20 THEN '子供'
WHNE age BETWEEN 20 AND 69 THEN '成人'
WHNE age >= 70 THEN '老人'
ELSE NULL END AS age_class,
RANK() OVER (PARTITION BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 20 AND 69 THEN '成人'
WHEN age >= 70 THNE '老人'
ELSE NULL END
ORDER BY age) AS age_rank_in_class
FROM Persons
ORDER BY age_class, age_rank_in_class;
