達人に学ぶSQL徹底指南書を読みました。
概要
業務でsqlを書く機会が結構あったのですが、基本的なCURDはできるのですが複雑な仕様になると、途端に対応できなくなりました。
この本を読むことでかなり学習できたので、自分が参考になった部分で、実務的な部分を要約してまとめます。
そのため、かなり省略しています。
かなり面白い本なので、お手元にとることをおすすめします。
対象読者
基本的なCRUDは行えるレベルを想定しています。
また、サブクエリについても、使用できることを前提にしています。
環境構築
実際にSQLを動かしながらのほうが身につくと思います。
筆者は以下の記事を参考にdockerでpostgresqlを動かしています。
Case文
CASE文には単純CASE文と検索CASE文の2種類があるようです。
それぞれのSQLを見てみましょう。
-- 単純CASE文
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE 'その他' END
-- 検索CASE文
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE 'その他' -- else句を省略すると、暗黙的にnullが入ります
END
単純CASE文で書ける条件は検索CASE文でも書けるため、基本的には検索CASEを使うのが良さそうです。
使用例を見てみましょう。
以下のようなテーブル、データが入っていることを前提とします。
CREATE TABLE perf_population (
pref_name TEXT,
population int
);
INSERT INTO perf_population VALUES
('徳島', 100),
('香川', 200),
('愛媛', 150),
('高知', 200),
('福岡', 300),
('佐賀', 100),
('長崎', 200),
('東京', 400),
('群馬', 50);
以下のSQLはpref_nameを地域に振り分けて、地域ごとの人口の合計を算出するSQLです。
ただし、OracleなどではGROUP BY districtの記述はエラーになるため、GROUP BYのあとにSELECT句で記述したCASE文をもう一度記述してグループ化する必要があります。
SELECT
CASE
WHEN pref_name = '徳島' THEN '四国'
WHEN pref_name = '香川' THEN '四国'
WHEN pref_name = '愛媛' THEN '四国'
WHEN pref_name = '高知' THEN '四国'
WHEN pref_name = '福岡' THEN '九州'
WHEN pref_name = '佐賀' THEN '九州'
WHEN pref_name = '長崎' THEN '九州'
ELSE 'その他'
END AS district,
SUM(population)
FROM
perf_population
GROUP BY
district
CASE文はSELECT句以外の部分にも使用することができます。
使用例を見てみましょう。
以下のようなテーブル、データが入っていることを前提にします。
CREATE TABLE salaries (
name TEXT,
salary int
);
INSERT INTO salaries VALUES
('相田', 270000),
('神埼', 324000),
('木村', 220000),
('斎藤', 290000);
以下のSQLは次のような条件で更新を行います。
宣言的な記述になっており、かなり使いやすいと思います。
- 現在の給料が30万円以上の場合は、10%の減給を行う
- 現在の給料が25万以上28万未満の場合は、20%の昇給を行う
UPDATE salaries
SET salary = CASE
WHEN salary >= 300000 THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
ELSE salary
END;
ウィンドウ関数
早速ウィンドウ関数とはなにか、見てみましょう。
以下のSQLは商品idの昇順にソートし、2つ前までの商品を含む価格の移動平均を求めています。（※ここではWINDOWの定義を明示的に行っていますが、ウィンドウを定義せずにOVERの後にそのまま(ORDER BY ~)と記載しても動きます）
-- Products(商品テーブル)
SELECT
id,
name,
price,
AVG(price) OVER W AS moving_avg
FROM Products
WINDOW W AS (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
ウィンドウ関数は以下のような機能を行っています。
- PARTITION BY句によるレコード集合のカット
- ORDER BY句によるレコードの順序付け
- フレーム句によるカレントレコードを中心としたサブセットの定義(
ROWS ~の部分）
1つ目と2つ目はそれぞれGROUP BYとORDER BYの機能に似ているため理解するのは簡単です。
3つ目は上記のSQLのように自分の行を基準として、他の行を取得するような機能です。
以下はフレーム句で利用できるオプションです。
- 移動単位
- ROWS: 移動単位を行で設定する。
- RANGE: 移動単位を列の値で設定する。基準となる列は
ORDER BYで指定した列
- 行の移動
- n PRECEDING: n行前に移動する
- n FOLLOWING: n行後に移動する
- UNBOUNDED PRECEDING: 無制限に前に移動する
- UNBOUNDED FOLLOWING: 無制限に後ろに移動する
- CURRENT ROW: 現在行
自己結合
他のテーブルとjoinするときは抵抗なくできると思いますが、自分自身のテーブルへのjoinは途端に書き方がわからなくなったりしないでしょうか?
例えば以下のようなテーブルが存在し、すべての品物の組み合わせを取得したいときは、このようなSQLを使用します。
-- with句でテーブルの代用にしています。
WITH products AS (
SELECT
*
FROM
(
VALUES
('りんご', 100),
('みかん', 50),
('バナナ', 80)
) AS t (name, price)
)
SELECT
p1.name AS p1_name,
p2.name AS p2_name
FROM
products p1
CROSS JOIN
products p2
次に順列を取得するSQLの記載します。
例えば、りんごの場合はみかんとバナナを取得したいので、同じ名前であるりんごをJOIN句の条件で弾いています。
同じテーブルですが、aliasをつけると違う集合だとみなすようです。
そのため、通常の結合と同様に考えることができます。
WITH products AS (
SELECT
*
FROM
(
VALUES
('りんご', 100),
('みかん', 50),
('バナナ', 80)
) AS t (name, price)
)
SELECT
p1.name AS p1_name,
p2.name AS p2_name
FROM
products p1
INNER JOIN
products p2
ON p1.name <> p2.name
Exists
Existsはサブクエリを渡し、対象のレコードが存在するか確認する関数です。
Notをつけると反対に、テーブルに存在しないデータを探すことができます。
例えば、何回かの会合と出席者をもとに出席していない人物を抽出してみましょう。
まずは1回でも全員が出席した場合の集合を作成してみます
WITH meetings AS (
SELECT
*
FROM
(
VALUES
('第1回', '伊藤'),
('第1回', '水島'),
('第1回', '坂東'),
('第2回', '伊藤'),
('第2回', '宮田'),
('第3回', '坂東'),
('第3回', '水島'),
('第3回', '宮田')
) AS t (meeting, person)
)
SELECT
DISTINCT m1.meeting,
m2.person
FROM
meetings m1
CROSS JOIN meetings m2
次にNOT EXISTSを使って↑で求めた集合と比較をして参加していない人を求めます。
WITH meetings AS (
SELECT
*
FROM
(
VALUES
('第1回', '伊藤'),
('第1回', '水島'),
('第1回', '坂東'),
('第2回', '伊藤'),
('第2回', '宮田'),
('第3回', '坂東'),
('第3回', '水島'),
('第3回', '宮田')
) AS t (meeting, person)
)
SELECT
DISTINCT m1.meeting,
m2.person
FROM
meetings m1
CROSS JOIN meetings m2
WHERE
-- この部分を追加
NOT EXISTS (
SELECT
*
FROM
meetings m3
WHERE
m1.meeting = m3.meeting
AND m2.person = m3.person
);
HAVING句
HAVING句は、集合（例: テーブル全体、GROUP BYされた単位）に対して抽出を行うことができます。
下記のようなテーブルが存在することを前提に、seqに歯抜けがあるかどうかをチェック方法を手続き型とSQLで見てみましょう
Users
| seq(連番) | name(名前) |
|---|---|
| 1 | ディック |
| 2 | アン |
| 3 | ライル |
| 5 | カー |
| 6 | マリー |
| 8 | ベン |
手続き型
- 連番の降順が昇順でソートする
- 1行ずつ現在の行と次の行のseqの値を比較する
例(typescriptで記載)
type User = {
seq: number
name: string
};
const users: User[] = [
{ seq: 1, name: "ディック" },
{ seq: 2, name: "アン" },
{ seq: 3, name: "ライル" },
{ seq: 5, name: "カー" },
{ seq: 6, name: "マリー" },
{ seq: 8, name: "ベン"}
];
const existMissing = (users: User[]) => {
const sortedUsers = users.slice().sort((prev, current) => prev.seq - current.seq );
let missing = false;
for (let i = 0; i < sortedUsers.length; i++) {
if (i === sortedUsers.length - 1) break;
if (sortedUsers[i+1].seq !== sortedUsers[i].seq + 1) {
missing = true;
}
}
return missing;
}
// false
console.log(existMissing(users));
SQL
行数とカラムの最大数が一致していれば、最初から最後までカウントアップできたという判定方法を使っています。（一体一対応（全単射）が存在するかをテストしている）
SELECT '歯抜けあり' AS gap
FROM users u
HAVING
count(*) <> MAX(u.seq)
このように、「集合」を考えると簡単に記述することができます。
集合の計算を行う
外部結合でNULLを生成して、それを除外することでさまざまな集合の計算を行うことができます
以下のテーブルがあることを前提にします。
Class_A テーブル
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 3 | 伊集院 |
Class_B テーブル
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 3 | 西園寺 |
差集合
SELECT
A.id AS id, A.name AS name
FROM
Class_A A
LEFT JOIN -- LEFT JOINだとA-B、RIGHT JOINだとB-Aを取得できる
Class_B B ON A.id = B.id
WHERE
B.name IS NULL;
積集合
SELECT
*
FROM
Class_A A
INTERSECT
SELECT
*
FROM
Class_B B
集合に対しての演算
2つの集合が等しいことを検証する
tbl_Aとtbl_Bの行数が同じということを前提に、row_cntがtbl_Aの行数と一致すれば両者は等しいことになります。
SELECT
count(*) AS row_cnt
FROM
(
SELECT
*
FROM
tbl_A
UNION
SELECT
*
FROM tbl_B
) TMP;
2つの集合で除算を行う
SQLにはまだ除算用の演算子がないため、除算を行うためには、自前でクエリを書く必要があります。
今回は引き算（差集合演算）を使用して集合での除算を行う方法を説明します。
以下のようなテーブルが存在することを前提とします。
RequireSkills テーブル
| skill(技術) |
|---|
| Oracle |
| UNIX |
| Java |
EmpSkills テーブル
| emp(社員) | skill(技術) |
|---|---|
| 相田 | Oracle |
| 相田 | UNIX |
| 相田 | Java |
| 相田 | C# |
| 神崎 | Oracle |
| 神崎 | UNIX |
| 神崎 | Java |
| 平井 | UNIX |
| 平井 | Oracle |
| 平井 | PHU |
| 平井 | Perl |
| 平井 | C++ |
| 若田 | Perl |
| 渡辺 | Oracle |
今回は、「EmpSkills」テーブルから「Skills」テーブルすべての技術に精通した社員を求めます。
SELECT
DISTINCT emp
FROM
EmpSkills es1
WHERE
NOT EXISTS (
SELECT
skill
FROM
skills
EXCEPT
SELECT
skill
FROM
EmpSkills es2
WHERE
es1.emp = es2.emp
)
このクエリは、引き算を社員ごとに行うことで除算を行っています。
要求されるスキル - 社員が精通しているスキル の差集合を取ることで要求されるスキルに精通していない場合は、結果列ができ、NOT EXISTSで弾かれます。
簡単なパフォーマンスチューニング
WEBサービスのように高速なレスポンスが要求される業務では、SQLのパフォーマンスの良し悪しは死活問題です。
お手軽なパフォーマンスチューニングの技術を記載します。
効率のいい検索を利用する
SQLでは同じ結果を得るコードにも複数の書き方が存在します。
同じ結果でも異なるコードだと、パフォーマンスが変わってしまいます。
サブクエリを引数に取る場合、INよりもEXISTSを使用する
例えば、下記のようなテーブルが存在し、2つのテーブルに存在するnameを取得したいときは、EXISTSを使用したほうが早い可能性があります。
Class_A テーブル
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 3 | 伊集院 |
Class_B テーブル
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 3 | 西園寺 |
-- INを使用
SELECT
*
FROM
Class_A
WHERE
id IN (SELECT id FROM Class_B);
-- EXISTSを使用
SELECT
*
FROM
Class_A
WHERE
EXISTS (
SELECT
*
FROM
Class_B B
WHERE
A.id = B.id
);
EXISTSのほうが早いと期待できるのは以下の理由があります。
- id にindexがはられている場合は、インデックスを参照するだけですむ
- EXISTSは1行でも条件に合致する行を見つけたらそこで検索を終了する。INの場合は全て検索を行う
また、このような場合は結合に書き換えることもできます。
SELECT
A.id,
B.id
FROM
Class_A A
INNER JOIN
Class_B B ON A.id = B.id
このように記載することで、どちらかのindexが利用できる上、サブクエリがなくなったため、中間テーブルも作成されません。
ソートを回避する
DBMSにおいては、ソートは暗黙的に行われています。
以下はソートが発生する代表的な演算です。
- GROUP BY
- ORDER BY
- 集約関数(SUM, COUNT, AVG, MAX, MIN)
- DISTINCT
- 集合演算子(UNION, INTERSECT, EXCEPT)
- ウィンドウ関数(RANK, ROW_NUMBER)
ソートがメモリ上で行われているのはまだましですが、ストレージを使用したソートが行われるとパフォーマンスが大きく低下します。
無駄にソートしないようなSQLを作成しましょう
集合演算子のALLオプションを使う
UNION, INTERSECT, EXCEPTの集合演算子はそのまま使用すると、重複排除のためのソートを行います。
重複が気にならない場合や、重複が発生しないことが明らかな場合はUNIONの代わりにUNION ALLを使用すると、ソートが発生しません。
Discussion