🧐

達人に学ぶSQL徹底指南書を読みました。

2022/12/13に公開約9,900字

概要

業務でsqlを書く機会が結構あったのですが、基本的なCURDはできるのですが複雑な仕様になると、途端に対応できなくなりました。

この本を読むことでかなり学習できたので、自分が参考になった部分で、実務的な部分を要約してまとめます。
そのため、かなり省略しています。
かなり面白い本なので、お手元にとることをおすすめします。

対象読者

基本的なCRUDは行えるレベルを想定しています。
また、サブクエリについても、使用できることを前提にしています。

環境構築

実際にSQLを動かしながらのほうが身につくと思います。
筆者は以下の記事を参考にdockerでpostgresqlを動かしています。

https://qiita.com/Teramonte4/items/d2ef0fad11a55c125d9f

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 BYORDER 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. 連番の降順が昇順でソートする
  2. 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_Atbl_Bの行数が同じということを前提に、row_cnttbl_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を使用すると、ソートが発生しません。

GitHubで編集を提案

Discussion

ログインするとコメントできます