More than 1 year has passed since last update.

SQL 徹底指南を受けて

Last updated at 2023-04-01Posted at 2022-08-18

始めに

「達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ」を読んで、個人的に大切だなと思ったことを整理
- SQLを使う際に何度も振り返りたい内容を、自分の理解も織り交ぜつつまとめてみた
大きく 抽象的な話 と 具体的な話 で分けている
- 本書も2つの章「SQLとは何ぞや」「SQLの便利機能紹介」から構成されていて、それに倣っている
まだ読んだことなくて、SQLに苦手意識ある人はぜひ読んでみて欲しい

抽象的な話

その技術の使い方から一段目線を上げて意識したいこと
本書の「第２部　リレーショナルデータベースの世界」を読んで要点を整理

背景（バックグラウンド）について学ぶ

ある技術が生まれた背景を知ることで、その技術の理解がより深まる

過去から現在にかけての歴史を振り返る

RDBの誕生から成長の歴史を振り返り、現在主流となった技術がどのような背景から登場し、なぜデータベースのスタンダードの地位を確立したのかについて

RDB以前のデータベース市場では、階層型と呼ばれるモデルに基づいた製品が主流だった
- データの間にある関係を階層関係として表わし、データの位置をプログラムで特定してデータを取得するというモデル
RDBが破壊的イノベーションを起こす上で取っ掛かりとなったのが、ユーザーフレンドリーの精神
- ユーザーが使いやすいデータ構造とインターフェイスにこだわったこと、つまり「テーブル」「SQL」の発明
- テーブルは見た目が「二次元表」に似ており、二次元表によるデータ管理は当時から一般的だったため受け入れやすかった
そして、より重要なことは、テーブルにおけるデータ表現において、「データの位置」という概念を一切排除したこと
- テーブルにおいては、あるデータが何列目・何行目であることは一切意味を持たない
- アドレスやポインタといった扱いの難しい位置表現、アドレスの化身とも言える「変数（および代入）」を使わなくても、データ操作できることを意味する
関係モデル以前のデータベースモデルである階層モデルやネットワークモデルは、アドレスまみれだった
- 「番地（＝アドレス）」は、ポインタとして扱われるアドレスだけでなく、配列の添え字など、位置に束縛されるデータ表現全般を指す
- データのアドレスをポインタや配列の添え字で操作したり、ループ処理を記述することは、バグを引き起こしやすい
RDBは「そもそもこれら問題が原理的に発生しないシステム」を作るアプローチを取った
- 徐々に問題が認識されるようになり、他言語では「きれいなコードを書くための方法論」も発達するようになった一方
それは人間が認識しやすい有意味な世界を作り上げることに繋がる
- 世の中の様々な業務において、究極的に欲しいのは「データ（例.翔泳　太郎）」であって、「データの在りかを示すアドレス（例.x002ab45）」ではない
- データの管理方法を、位置から内容へ変えることは、物理レベルから論理レベルへ（抽象化）、記号から名前へ（有意味化）の変化であり、システムから人間にとって無意味なものを追放すること

現在から将来にかけての展望を考えてみる

時代の移り変わりとともに生じた新たな課題に対して、RDBがどのような限界に突き当たっているか
そして、NoSQLに代表される新技術がどのようなアプローチを試みているかについて

1990年代後半から2000年代にかけて、インターネットの発展を主なトリガーとして、システムは多種多様な用途に使われるようになった
それに伴いRDBの限界、不便さがクローズアップされるようになった
- 具体的には、パフォーマンスのスケーラビリティや非構造化データの扱いといった問題
1つ目が、性能と信頼性のトレードオフ
- 「厳密なトランザクション制御（ACID実現）によるデータ整合性・耐久性」を担保するため、ストレージ共有する構成を取る必要があり、ストレージがシングルボトルネックポイントになってしまう
- 「SQLによる高度なデータ操作」が行えるため、場合によって複雑な重い処理を実行できてしまう
2つ目が、データモデルの限界
- テーブルでは表現することが苦手なデータの種類がいくつかある
- フラットな二次元表では、グラフの再帰的構造を表現するのが難しい
- 非構造化データは、事前に列の意味と数を決める必要があるテーブルとは相性が悪くなる
これらRDBの抱える課題に対して、大きく対処法は2通りある
- RDBの機能を高度化することで対応する
- RDB以外のデータベースを利用する
本稿では後者について言及している
- 具体的には、一般にNoSQLを利用するアプローチ
1つ目の課題に対しては、RDBが持つ利点を諦める代わりにパフォーマンス追求するというトレードオフを許容する
- KVS（Key-Value Store）では、データモデルを単純化し、複雑なデータ操作を制限することで、高速な検索性能を実現している（代わりに結合などは行えない）
- また、KVS含むNoSQLは、複数DBインスタンスでクラスタ構成することで、シングルボトルネックポイントをなくしてスケールアウト可能にしている
2つ目の課題に対しては、各データを扱うことに重点を置いたDBが登場した
- 「ドキュメント指向型DB」は、JSONやXMLのような自由度の高いドキュメントを、RDBのテーブルに変換することなくネイティブに扱う機能を持つ（例.MongoDB,CouchDB）
- また、グラフに対しても同様に、近年「グラフDB」の開発が進んでいる（例.Neo4j）
しばらくは、RDBとNoSQLは補完的関係として共存する可能性が高い
- 上述したように、NoSQLの多くがトレードオフを発生させており、現状においては、第二の破壊的イノベーションは起きない
- また、RDBのほうも、NoSQL的な機能をサポートするようになっており、両者の差が埋まり始めている（NoSQLというのは、将来的にはRDBの機能の1つを指す言葉になる可能性もある）

マインドセットを切り替える

ある技術に習熟するためには、その思想を理解する必要がある

全てを集合と述語でやろうとするのが、SQLの発想
SQLとリレーショナルデータベースを支える基礎理論は大きく2つあり、SQLはそれぞれの側面を併せ持つ

数学の一分野である集合論 → 集合指向言語
現代論理学の標準的な体系である述語論理 → 関数型言語

つまり、SQLの考え方を習得すためには、慣れ親しんだ手続き型言語の考え方を変える必要がある

手続き型言語の発想は還元論的
- 代入・分岐・ループという基本的な処理単位に分割していく
- 大量データをレコードという小さな単位に分割して扱うファイルシステム
SQLとリレーショナルデータベースの発想は全体論的
- SQLには代入やループなどの手続きは一切現われない
- データもレコードではなく、もっと複合的な集合の単位として扱う

集合指向言語としての要点

テーブルは集合である

リレーショナルデータベースのテーブルは、数学の「集合」の一種
- データが順序良く整理されたバインダーではなく、色々なものが雑多に放り込まれた入れ物に過ぎない
- ファイルはその記憶方法に緊密に結び付けられている一方、テーブルはそのメモリ上での扱いを（パフォーマンスを除いて）気にする必要はない
この抽象性によって、SQLの自由度は上がり、その分強力なツールになっている
- 例えば、自己結合は、集合という概念の抽象度の高さゆえに可能となった技術
- SQLの中では、同じテーブルに違う名前を与えて、それらが別のテーブルとして存在しているかのように扱うことができる
また、集合指向を身に付ける近道として、HAVING句の使い方を学ぶことがオススメ
- HAVING句は、WHERE句と違って、集合単位の条件を設定する場所であるため、HAVING句の練習をすることによって、データを集合の観点から把握できるようになる

閉包性を意識する

テーブルはただの集合ではなく、閉包性を持っている
閉包性は、平たく言うと「演算子の入力と出力が共にテーブルになる（テーブルの世界が閉じていることを保証する）」という性質
SELECT文とは、テーブルを引数にとってテーブルを返す関数として捉えられ、サブクエリやビュー利用の前提になっている

他にも閉包性の例として、UNIXのファイル・シェルコマンドが挙げられる
UNIXは、デバイスからコンソールまで全て「ファイル」として扱っている（プリンターやディスプレイのような物理的なデバイスも、/devディレクトリの下にある普通のファイルのように見える）
これらファイルが、様々なコマンドに対して入力・出力になることで、UNIXのシェルプログラミングに高い柔軟性を与えている（例. cat test.txt | sort +1 | more）

また、テーブルの閉包性は、代数構造のうち「体」の条件もクリアしているため、「自由に四則演算が可能な集合」と考えることが可能（和の射影や制限の差等が行える）
数学的に厳密な基礎付けを持つことで、集合論や群論など、すでに多くの実績の積み重ねがある分野の成果を、そのまま援用している

四角を描くな、円を描け

SQLは、ただ欲しいデータの条件を記述するだけで、そこに動的な処理は一切表われない
- 例えるなら、「35歳まで」「未経験可」等、色々な条件を付けて求人募集の広告を出して
- 実際に駆けずり回って条件に見合う人材を探すのはデータベースの仕事になる
このような静的データモデルを一番的確に表わす視覚図は、ベン図
- SQLにおいては、入れ子集合の使い方が1つの鍵になるため、入れ子の円を描くことが重要
  - GROUP BYやPARTITION BYによって、テーブルを部分集合に切り分ける等
手続き型言語においての視覚的なツールとしては、構造図やDFD等、手続きが箱でデータの流れが矢印で表現されるが、この伝統的な道具は、SQLには不向き
- ちなみに、手続き型言語においてループを使っていた処理は、GROUP BY句（集合演算）や相関サブクエリ、ウィンドウ関数を使って表現することができる
- つまり、一見手続き的にしか解けないと思うような問題も、上述の考え方に落とし込むことができる

関数型言語としての要点

処理の分岐は「式」で行う

手続き型言語では、処理の分岐は「文」の単位で行なうが、SQLでは、文の中の「式」の単位で行う
- 具体的には、IF文やCASE文は、CASE式で置き換える
CASE式は、最終的に1つの値に定まるために、他の式や関数の引数に取ることもできる

述語論理の「階」の導入

この概念は存在の階層を区別するものであり、SQLにおいては行と行集合の区別
述語論理は、複数の対象を一つの入力として扱う道具である「量化子」「全称量化子」が存在し、SQLに導入されている
SQLでは前者をEXISTS述語、後者をNOT EXISTSを使って表現する
- 行集合を一単位として扱える

具体的な話

いつでも引き出しから取り出したい便利機能について紹介
本書の「第１部　魔法のSQL」を読んで要点を整理

FROM句から書く

SQLを書く時に、どの句から書き始めると分かりやすいか

複雑なSQLを書く場合、いきなりSELECT句から書くより、実行順序に沿って、FROM句から書いたほうが自然にロジックを追える
- SQLの実行順序は、 FROM → WHERE → GROUP BY → HAVING → SELECT （→ ORDER BY）
SELECT句がやっていることというのは、表示用に見た目を整形したり、計算列を算出したりするだけ
より重要な役割を持っているWHERE、GROUP BY、HAVINGから考える

CASE式

CASE式のやっていることは、ある列値を読み替えること

CASE「式」は実行時に評価されて1つの値に定まるため、列名や定数を書ける場所にはどこでも書くことができる（SELECT句にもGROUP BY句にもWHERE句にもORDER BY句にも）
特に、集約関数（SUMやAVG）とGROUP BY句と一緒に使うと絶大な力を発揮する

コード体系を変換して集計

GROUP BY句でCASE式を使うことで、集約単位となるコードや階級を柔軟に設定できる

-- 「PopTblテーブル（県名・人口カラム）」を地方単位に再分類する
-- ※一部DBMSでは列名参照でエラーになるので、group byでもCASE式を書く
--  MySQLやPostgreはOK（SELECT句のリストを先にスキャンして列計算を事前に行うため）
-- 
-- pref_name population　　　→　　　district population
-- --------------------　　　　　　　　--------------------
-- 徳島       100　　　　　　　　　　　　　　　　　　　　　　四国　　　　　　　　　　　２００
-- 香川       200

SELECT
  CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '福岡' THEN '九州'
    WHEN '佐賀' THEN '九州'
    WHEN '長崎' THEN '九州'
    ELSE 'その他'
  END AS district,
  SUM(population)
FROM
  PopTbl
GROUP BY
  district
;

行持ちから列持ちへの水平展開

集約関数の中にCASE式を使うことで、異なる条件の集計を１つのSQLで行うことができる

-- 「PopTbl2テーブル（県名・人口・性別カラム）」から、県別・男女別のクロス表を作る
--
-- pref_name sex population　　　→　　　pref_name cnt_m cnt_f
-- --------------------　　　　　　    　--------------------
-- 徳島       1   100　　　　　　　　　　　　　　　　　　　　　　徳島　　　　　　　　　　　 100    200
-- 徳島       2   200
-- ...

SELECT
  pref_name,
  SUM(
    CASE WHEN sex = '1' THEN population ELSE 0 END
  ) AS cnt_m,
  SUM(
    CASE
      WHEN sex = '2' THEN population ELSE 0 END
  ) AS cnt_f
FROM
  PopTbl2
GROUP BY
  pref_name
;

WHERE句で条件分岐させるのではなく、SELECT句で分岐させることで、クロス表形式で結果を出力できる

-- 「CourseMasterテーブル（講座ID・講座名カラム）」と「OpenCoursesテーブル（開講月・講座IDカラム）」から、講座と開催月のクロス表を作る
--
-- course_id course_name　　　+ 　　month  course_id
-- ---------------------    ----------------
-- 1         SQL入門         202301 1
-- 2         Rails入門       202302 2
-- ↓
-- course_name 1月 2月
-- ------------------
-- 1            o  x
-- 2            x  o

SELECT
  CM.course_name,
  CASE
    WHEN EXISTS(
      SELECT course_id FROM OpenCourses OC WHERE MONTH = 201806 AND OC.course_id = CM.course_id
    ) THEN '○'
    ELSE '×'
  END AS "6 月",
  CASE
    WHEN EXISTS(
      SELECT course_id FROM OpenCourses OC WHERE MONTH = 201807 AND OC.course_id = CM.course_id
    ) THEN '○'
    ELSE '×'
  END AS "7 月",
  CASE
    WHEN EXISTS(
      SELECT course_id FROM OpenCourses OC WHERE MONTH = 201808 AND OC.course_id = CM.course_id
    ) THEN '○'
    ELSE '×'
  END AS "8 月"
FROM
  CourseMaster
;

条件を分岐させてUPDATE

CASE式を駆使することで、複数のSQL文を1つにまとめられ、可読性もパフォーマンスも向上する

-- 「Personnelテーブル（名前・給料カラム）」に対して、以下を行う
-- 1．現在の給料が30万以上の社員は、10％の減給とする
-- 2．現在の給料が25万以上28万未満の社員は、20％の昇給
--
-- name salary　　　→   name salary
-- -----------　　　　　　　　　　　-----------
-- tomi 300000　　　　　　　　　　　tomi 270000
-- taka 250000　　　　　　　　　　　taka 300000

UPDATE
  Personnel
SET
  salary = CASE
    WHEN salary >= 300000 THEN salary * 0.9
    WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
    ELSE salary
  END
;

CASE式の評価は、真になるWHEN句が見つかった時点で打ち切られる
最後の行のELSE句がないと、「ELSE NULL」と見なされるので気を付ける

集計結果に対する分岐を行う

集計結果に対する条件はHAVING句を使って設定する以外に、CASE式の中で集約関数を使っても可能

-- 「StudentClubテーブル（学生ID・クラブID・主クラブフラグカラム）」から以下を求める
-- 1．1つだけのクラブに所属している学生については、そのクラブIDを取得する 
-- 2．複数のクラブをかけ持ちしている学生については、主クラブのIDを取得
--
-- std_id club_id club_name main_club_flag
-- ---------------------------------------
-- 1      1       テニス      Y
-- 1      2       バスケ      N
-- 2      1       テニス      Y
-- ↓
-- std_id main_club
-- ----------------
-- 1      1
-- 2      1

SELECT
  std_id,
  CASE
    WHEN COUNT(*) = 1 THEN MAX(club_id) -- 1つだけのクラブに専念している
    ELSE MAX( -- 複数のクラブをかけ持ちしている
      CASE
        WHEN main_club_flg = 'Y' THEN club_id
        ELSE NULL
      END
    )
  END AS main_club
FROM
  StudentClub
GROUP BY
  std_id
;

Window関数

Window関数の「ウィンドウ」とは、PARTITION BY句・ORDER BY句・フレーム句により加工されたレコードの集合を意味する

PARTITION BY句によるレコード集合のカット
- GROUP BY句の集約しない版
ORDER BY句によるレコードの順序付け
- 普通のORDER BY句と同じ
フレーム句によるレコード集合の定義
- 「カレントレコード」を中心にしたサブセット

無名と名前付き

2通りの方法でウィンドウを定義することができる

-- 無名ウィンドウ関数
-- ウィンドウ定義が暗黙的に行われるため、ウィンドウの存在を意識しにくい
-- 一般的によく使われる方法
SELECT
  shohin_id,
  shohin_mei,
  hanbai_tanka,
  AVG(hanbai_tanka) OVER(
    ORDER BY shohin_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM
  Shohin
;

-- 名前付きウィンドウ関数
-- ウィンドウを明示的に定義した上で、それに対してAVG関数を適用している
-- ウィンドウの使い回しが可能
-- ※ DBMSによってはエラーになる
SELECT
  shohin_id,
  shohin_mei,
  hanbai_tanka,
  AVG(hanbai_tanka) OVER W AS moving_avg
FROM
  Shohin
WINDOW W AS(
  ORDER BY shohin_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
;

フレーム句について理解する

フレーム句を使うことで「異なる行を自分の行に持ってくる」ことができる

カレントレコードを基点として、集計対象とするレコードの範囲を制限する機能
- 「カレントレコード」の概念を明示的に利用しており、手続き型言語の考え方をSQLに輸入した機能と言える
- 存在しないデータを指定されるとNULLを返す
その結果、従来SQLでは難しかった行間比較を自在に行えるようになった

参考として、以下フレーム句で範囲定義する際に利用できるオプション

オプション	意味
ROWS	移動単位を行で設定する（例. ~ ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING）
RANGE	移動単位を列の値で設定する。基準となる列はORDER BY句で指定された列（例. ~ RANGE BETWEEN interval '1' day PRECEDING AND interval '1' day PRECEDING）
n PRECEDING	nだけ前へ（小さいほう）へ移動する。nは正の整数
n FOLLOWING	nだけ後へ（大きいほう）へ移動する。nは正の整数
UNBOUNDED PRECEDING	無制限にさかのぼるほうへ移動する
UNBOUNDED FOLLOWING	無制限に下るほうへ移動する
CURRENT ROW	現在

行間比較を行う

ウィンドウ関数の登場によって、相関サブクエリを使わずに、行間比較を行えるようになった
- 可読性が高くコードも簡潔に記述でき、またテーブルに対するスキャンも一度で済むためパフォーマンスが良い
この違いが生じる理由は、ウィンドウ関数は「行の順序」に基づいた操作を行なうことで、手続き型言語のループ動作をよりダイレクトに行えるようになったため
- 相関サブクエリが複数のテーブルを結合するという、SQLの古い機能によって実現していたのに対して

まずは相関サブクエリを使ったケースを見てみる

-- 「Salesテーブル（年度・年商カラム）」から、前年と年商が同じ年度を求める
--
-- year sale   →   year sale
-- ----------     -----------
-- 1990 50         1992 51
-- 1991 51
-- 1992 51

SELECT
  year,
  sale
FROM
  Sales S1
WHERE
  sale = (
    SELECT sale FROM Sales S2 WHERE S2.year = S1.year - 1
  )
ORDER BY
  year
;

相関サブクエリは、2つのテーブルにおける比較対象の行を「ずらす」ことで、手続き型言語におけるループの代役を果たしている
- 相関サブクエリはループクエリとも呼ばれる
この相関サブクエリは、年度ごとに前年の年商を確認するシンプルなSELECT文を1行ずつ（＝ループさせながら）繰り返し実行していると言える

-- 1990
SELECT year,sale FROM Sales S1
WHERE 50（1990年度の年商） = (SELECT sale FROM Sales S2 WHERE S2.year = 1989（1990年度の前年）) ;

-- 1991
SELECT year,sale FROM Sales S1
WHERE 51（1991年度の年商） = (SELECT sale FROM Sales S2 WHERE S2.year = 1990（1991年度の前年）) ;

...

一方、ウィンドウ関数を使った場合は以下のようになる

-- 「Salesテーブル（年度・年商カラム）」から、前年と年商が同じ年度を求める
SELECT
  year,
  current_sale
FROM
  (
    SELECT
      year,
      sale AS current_sale,
      SUM(sale) OVER(
      -- 直近（前年ではなく）と年商が同じ年度を求める場合は、
      -- ORDER BY YEAR ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING に置き換えるだけ
      -- オプションを変更することで、手軽かつ柔軟に対象範囲を変更することができる
        ORDER BY YEAR RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
      ) AS pre_sale
    FROM
      Sales
  ) TMP
WHERE
  current_sale = pre_sale
ORDER BY
  year
;

自己結合

同一のテーブルを対象に行う結合のことを自己結合と言う

重複順列・順列・組み合わせ

-- Productsテーブル
-- 
-- name price
-- ----------
-- バナナ 100
-- パイン 200

-- 重複順列を得るSQL 
-- クロス結合の構文の特徴は、結合条件が存在しないこと（2つのテーブルを「総当たり」）
SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  CROSS JOIN Products P2
;

-- 順列を得るSQL
SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  INNER JOIN Products P2 ON
    P1.name <> P2.name
;

-- 組み合わせを得るSQL
SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  INNER JOIN Products P2 ON
    P1.name > P2.name
;

自己非等値結合を使う

基本的には、自己結合は自己非等値結合として使うことが多い
- 等号「=」以外の比較演算子である>や<、<>を使って行なう結合を非等値結合と呼ぶ
- それを自己結合と組み合わせたのが、自己非等値結合

-- 「Productsテーブル（商品名・値段カラム）」から、「同じ値段だけど、商品名が違う商品」を取得する
-- 
-- name  price
-- ----------
-- バナナ   100
-- バイン   100
-- パパイヤ 　200

SELECT DISTINCT
  P1.name,
  P1.price
FROM
  Products P1
  INNER JOIN Products P2 ON
    P1.price = P2.price AND
    P1.name <> P2.name
ORDER BY
  P1.price
;

自己相関サブクエリを使う

-- 重複行を削除するSQL
--
-- name  price  →  name  price
-- ----------      ----------
-- バナナ   100     バナナ   100
-- バナナ   100

DELETE
FROM
  Products P1
WHERE
  EXISTS(
    SELECT
      *
    FROM
      Products P2
    WHERE
      P1.name = P2.name AND
      P1.price = P2.price AND
      P1.rowid < P2.rowid
  )
;

3値論理とNULL

SQLの論理体系は3値論理であり、true、falseに加えて、unknownという第三の値を持つ

3つの真理値の優先順位は以下となる
- ANDの場合： false > unknown > true
- ORの場合： true > unknown > false
例えば true AND unknown なら、 unknown のほうが強いので、結果も unknown になる
SQLで3値論理が採用されている理由は、NULLにある
- NULLは「値がない」ことを示す目印であり、値ではないため、述語をまともに適用することができない
- そこで持ち込まれたのが第三の真理値であり、NULLに述語を適用した結果は常にunknownになる
NULLによる弊害はいくつかあるため、基本的には以下の順番で検討する
- NOT NULL制約を付けて、極力NULLを排除する
  - 外部結合等でNULLになることはあり得るため、完全排除はできない
- 代変え値を入れられないか検討する
  - 例えば、コードの場合、未コード化用コードを割り振る
  - 日付の場合は、最大値・最小値で代替する
- NULLを許可する
  - 「値がない」ことを明示的に示したいとき

NULLの伝播

評価途中でNULLが含まれると「NULLの伝播」が起こる
- 意図せず条件評価にNULLが入り込むと、想定外のクエリ結果となることがある
- クエリの結果として選択されるのは、WHERE句の条件評価が true になる行のみであり、false や unknown の行は選択されない
例えば、NOT INをNOT EXISTSで書き換える場合には、必ずしも結果が一致しない
- NOT INのサブクエリで使用されるテーブルの選択列にNULLが存在する場合、そのSQL結果は空になる
- 何故なら、NOT INは実行時に (col1 <> val1) AND (col2 <> val2) AND ... として評価され、該当カラムにNULLが存在する場合 ... AND unknown AND ...となり、結果 unknown を返すため
これに対処するには、条件評価時の動作を段階的なステップに分けて確認することが大切
- 正しい結果を得るには、EXISTS述語を使う（EXISTS述語は、unknownを返さず、true と false のみ返す）

パフォーマンス悪化

IS NULL、IS NOT NULLを指定する場合、インデックスの利用に制限が入りパフォーマンスが悪くなる
多くのDBMSではインデックスにNULLが多いとインデックスが参照されなかったり、Oracleのようにそもそもインデックスが使用されない実装もある

仕様の不明瞭さ

SQLの結果を受け取るホスト言語において、NULLの組み込み方が標準化されていない
- また、DBMS間でもNULLの扱いに関する仕様が統一されていない
NULLを含むカラムに作成するユニークインデックスの「ユニーク」の意味が各RDBMSで違う
- 例えば、複数のNULLを含む列にユニークインデックスを作成するとき、NULLの重複によってエラーになるかは統一されていない
NULLは値ではないため、ORDER BY句によるソートの際のルールが統一されていない
- NULLは本来は順序を付けられないが、実装上は、一般に最大値か最小値として扱われる

EXISTS述語

EXISTS述語は、述語論理の存在量化子を実装したもの

述語論理には、量化子という特別な述語が存在する（述語とは、真理値を返す関数のこと）

全称量化子
- 「すべてのxが条件Pを満たす」を確認する
存在量化子
- 「条件Pを満たすxが（少なくとも1つ）存在する」を確認する

SQLは「一階述語論理」に対応する

EXISTSだけが、他の述語と違って（行の）集合を引数に取ることができる（高階関数の一種と見なせる）
ただ、SQLが採用している述語論理は、少し範囲を限定した「一階述語論理」のため、EXISTS述語は、一階の存在までしか引数に取れない
- リレーショナルデータベースにおける存在の階層は、0階が行、一階がテーブル（行集合）、二階がテーブルの集合（SQLには三階以上の述語は出てこない）
もし将来、SQLが二階述語論理を備えたら、テーブルに対する量化が可能になる
- 今のSQLでは「供給業者S1を含む行は存在するか？」というクエリしか表現できないのに対し、「供給業者S1を含むテーブルは存在するか？」という一段レベルの高いクエリを表現できるようになる

テーブルに存在しないデータを探す

存在するデータについて、「こういう性質を満たす」という条件を設定するのではなく、
そもそも「データが存在するか否か」という、次数の1つ高い問題にはEXISTSを使う

-- 「Meetingsテーブル（会合名・出席者カラム）」から「会合に出席しなかった人物」を求める
-- 考え方としては、全員が皆勤したと仮定した場合の集合を作り、そこから現実に出席した人々を引き算する
-- 
-- meeting person  →  meeting person
-- --------------     --------------
-- 1回      tomi      2回      taka
-- 1回      taka
-- 2回      tomi

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
  )
;

-- 差集合演算を利用しても良い
SELECT
  M1.meeting,
  M2.person
FROM
  Meetings M1,
  Meetings M2
EXCEPT
  SELECT
    meeting,
    person
  FROM
    Meetings
;

全称量化子を使う

SQLには全称量化子に相当する演算子がないので、NOT EXISTSで代用する

「すべての行が条件Pを満たす」を「条件Pを満たさない行が存在しない」へ変換してクエリで表現する

-- 「TestScoresテーブル（学生ID・教科・点数カラム）」から、以下の条件をともに満たす学生を選択する
-- 1．算数の点数が80点以上
-- 2．国語の点数が50点以上
-- 二重否定に同値変換すると、「算数の点数が80点未満または国語の点数が50点未満でない学生」になる
--
-- student_id subject score  →  student_id
-- ------------------------     ----------
-- 1          算数     80        1
-- 1          国語     50
-- 1          理科     100
-- 2          算数     79

SELECT DISTINCT
  student_id
FROM
  TestScores TS1
WHERE
  subject IN('算数', '国語') -- 対象の教科に限定する
AND
  NOT EXISTS(
    SELECT
      *
    FROM
      TestScores TS2
    WHERE
      TS2.student_id = TS1.student_id -- 生徒ごとに条件を調べる
      AND
        1 = CASE -- 特性関数（条件を満たす行については1、そうでない行については0を返す関数）
              WHEN subject = '算数' AND score < 80 THEN 1
              WHEN subject = '国語' AND score < 50 THEN 1
              ELSE 0
            END
  )
GROUP BY
  student_id
HAVING COUNT(*) = 2 -- 算数・国語のデータが存在しない生徒を除外する
;

HAVING句

HAVING句を活用する際のポイントは、何を集合と見なすかに着目すること
そして、その集合の性質を調べたり、求める集合にたどりつくまで次々に集合を作っていく

処理対象を集合として捉える

以下テーブルについて歯抜けデータを探すことを考える

seq	name
1	ichiro
2	ziro
4	siro

仮にこのテーブルがファイルで、手続き型言語を使って調べるなら、次のような手順になる

連番の昇順か降順にソートする
ソートキーの昇順（または降順）にループさせて、1行ずつ次の行とseq列の値を比較する

ファイルのレコードは順序を持ち、それを扱うためにプログラミング言語はソートを行なう

代わりにSQLは、処理対象を集合として捉える

-- 欠番があれば'歯抜けあり'を返す
-- 考え方としては、SeqTbl集合（SeqTblの要素数）と自然数の集合（歯抜けのない連番）との間に、一対一対応（集合論の全単射）が存在するかどうかを確認する
SELECT
  CASE
    WHEN COUNT(*) = 0 THEN 'テーブルが空です'
    WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1 THEN '歯抜けあり' -- 一対一対応しているかチェック
    ELSE '連続'
  END AS gap
FROM
  SeqTbl
;

なお、GROUP BY句が存在しない場合でも、HAVING句は問題なく使える
- その場合、テーブル全体が1行に集約される
ただし、SELECT句で元テーブルの列を参照できなくなるため、定数を指定するか、または集約関数を使う必要がある

集合の性質を調べるための代表的な条件を以下にまとめておく

性質	条件
colの値が一意	COUNT(DISTINCT col) = COUNT(col)
colにNULLが存在しない	COUNT(*) = COUNT(col）
colは連番（開始値は1）	COUNT(*) = MAX(col)
colは連番（開始値は任意の整数）	COUNT(*) = MAX(col) - MIN(col) + 1
colは1つだけの値 or NULL	MAX(col) = MIN(col)
全てのcolの符号が同じ	MAX(col) * MIN(col) > 0
最大値の符号が正、最小値の符号が負	MAX(col) * MIN(col) < 0
colは少なくとも1つのゼロを含む	MIN(ABS(col)) = 0

特性関数と組み合わせる

HAVING句は集合の性質を調べる道具であり、集約関数やCASE式と組み合わせると複雑な条件を表現できる

特に、CASE式による特性関数と組み合わせると応用が効く
特性関数とは、各要素（＝行）が特定の条件を満たすかどうかを決める関数のことを言う

-- 「TestResultsテーブル（学生ID・クラス・性別・得点カラム）」に対して、各条件を満たすクラスを選択する
--
-- student_id class sex score
-- ---------------------------
-- 1          A     男   100
-- 2          A     女   10
-- 3          B     女   100


-- クラスの75％以上の生徒が80点以上のクラスを選択
SELECT
  class
FROM
  TestResults
GROUP BY
  class
HAVING COUNT(*) * 0.75 <= SUM(
  CASE
    WHEN score >= 80 THEN 1
    ELSE 0
  END
)
;

-- 50点以上を取った生徒のうち、男子の数が女子の数より多いクラスを選択
SELECT
  class
FROM
  TestResults
GROUP BY
  class
HAVING SUM(
  CASE
    WHEN score >= 50 AND sex = '男' THEN 1
    ELSE 0
  END
) > SUM(
  CASE
    WHEN score >= 50 AND sex = '女' THEN 1
    ELSE 0
  END
)
;

-- 女子の平均点が、男子の平均点より高いクラスを選択
SELECT
  class
FROM
  TestResults
GROUP BY
  class
HAVING AVG(
  CASE
    WHEN sex = '男' THEN score
    ELSE NULL -- 片方が空集合の場合、選択対象外とするためNULLを返す
  END
) < AVG(
  CASE
    WHEN sex = '女' THEN score
    ELSE NULL -- 片方が空集合の場合、選択対象外とするためNULLを返す
  END
)
;

関係除算によるバスケット解析

関係除算は、数の演算でいう割り算にあたる（対して掛け算にあたるのがクロス結合）

-- itemsテーブル（商品カラム）、ShopItemsテーブル（店舗・商品カラム）に対して、以下を満たす店舗を検索する
--
-- item    +   shop item     →    shop
-- -----       ----------         -----
-- ビール        東京  ビール         東京
-- 紙オムツ      東京  紙オムツ
-- 自転車       東京  自転車
--             京都  ビール

-- itemsテーブルの全商品を置いている店舗を検索する
SELECT
  SI.shop
FROM
  ShopItems SI
  INNER JOIN Items I ON
    SI.item = I.item
GROUP BY
  SI.shop
HAVING COUNT(SI.item) = (
  SELECT
    COUNT(item)
  FROM
    Items
)
;

-- 厳密な関係除算（過不足なく割り切れる店舗のみを選択する）
SELECT
  SI.shop
FROM
  ShopItems SI
  LEFT OUTER JOIN Items I ON
    SI.item = I.item
GROUP BY
  SI.shop
HAVING COUNT(SI.item) = ( -- itemsテーブル以外の商品を置いていない
  SELECT
    COUNT(item)
  FROM
    Items
)
AND
COUNT(I.item) = ( -- itemsテーブルの全商品を置いている
  SELECT
    COUNT(item)
  FROM
    Items
)
;

集合演算

集合演算を利用することで、集合論に基づいたクエリ実行が行える

集合の相等性を調べる

集合演算の実践的な応用として、2つのテーブルが等しいか否かを調べたい
ここで言う「等しい」とは、行数も列数もデータ内容も同じことを指す

-- 集合論で集合の相等性を調べる公式をSQLで表現する
-- (AuB)=(A^B)であれば、(A=B)が成り立つ
SELECT
  CASE
    WHEN COUNT(*) = 0 THEN '等しい'
    ELSE '異なる'
  END AS result
FROM
  ((
      SELECT * FROM tbl_A
      UNION
      SELECT * FROM tbl_B
    ) EXCEPT(
      SELECT * FROM tbl_A
      INTERSECT
      SELECT * FROM tbl_B
    )) TMP
;

関係除算によるバスケット解析

HAVING句を使う方法は上述した通り、今回は差集合を使って解決する

-- 「Skillsテーブル（技術カラム）と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 -- 引き算を社員毎に行う
  )
;

上記クエリは、見方によっては非常に手続き型に近い発想をする

以下の作業を全社員について繰り返す（相関サブクエリによるループ）
「求められているスキル」から「社員のスキル」を引き算し、その結果が空であれば、全スキルを持っていると判定する

等しい部分集合を見つける

集合同士の比較を行なう問題について考える
- 関係除算では、比較対象の一方の集合は固定されていたのに対し、今回はどちらの集合も固定せずに、部分集合同士の組み合わせについて比較を行う
ポイントとしては、SQLでは、2つの集合を比較するときは、行単位で比較するのではなく、あくまで集合全体として扱うということ

-- 「SupPartsテーブル（供給業者・部品カラム）」から「個数と種類が同じ部品を取り扱う供給業者のペア」を求める
-- 考え方としては、まず業者ペアの組み合わせを作り、その業者ペアについて、 `(A ⊆ B) and (A ⊇ B) => (A = B)` の公式を当てはめる
-- 具体的には、以下を満たせば真といえる
-- 条件1．どちらの業者も同じ種類の部品を扱っている
-- 条件2．同数の部品を扱っている（すなわち全単射が存在する）
--
-- sup part  →  s1 s2
-- --------     ------
-- A   ボルト     A  B
-- A   チップ
-- B   ボルト
-- B   チップ
-- C   チップ

SELECT
  SP1.sup AS s1,
  SP2.sup AS s2
FROM
  SupParts SP1,
  SupParts SP2
WHERE
  SP1.sup < SP2.sup -- 業者の組み合わせを作る 
AND
  SP1.part = SP2.part -- 条件1
GROUP BY
  SP1.sup,
  SP2.sup
HAVING COUNT(*) = ( -- 条件2
  SELECT
    COUNT(*)
  FROM
    SupParts SP3
  WHERE
    SP3.sup = SP1.sup
) AND
  COUNT(*) = (
    SELECT
      COUNT(*)
    FROM
      SupParts SP4
    WHERE
      SP4.sup = SP2.sup
  )
;

結合による集合演算

少し視点を変えてみると、結合を集合演算と見なすことができる
- 内部結合が積集合（INTERSECT）、完全外部結合が和集合（UNION）に相当する
集合演算子はソートを発生させるので、パフォーマンス上の問題を引き起こす可能性があるため、集合演算子の代用を検討する

-- Class_A と Class_B
--
-- id name   id name
-- -------   --------
-- 1  tomi    1  tomi
-- 2  taka    2  taku

-- 和集合を求める A+B
SELECT
  COALESCE(A.id, B.id) AS id,
  A.name AS A_name,
  B.name AS B_name
FROM
  Class_A A
FULL OUTER JOIN Class_B B ON
  A.id = B.id
;

-- 差集合を求める A-B
SELECT
  A.id AS id,
  A.name AS A_name
FROM
  Class_A A
LEFT OUTER JOIN Class_B B ON
  A.id = B.id
WHERE
  B.name IS NULL
;

-- 積集合を求める A*B
SELECT
  A.id AS id,
  A.name AS A_name
FROM
  Class_A A
CROSS JOIN Class_B B ON
  A.id = B.id
WHERE
  B.name IS NULL
;

-- 排他的和集合を求める
SELECT
  COALESCE(A.id, B.id) AS id,
  COALESCE(A.name, B.name) AS name
FROM
  Class_A A
FULL OUTER JOIN Class_B B ON
  A.id = B.id
WHERE
  A.name IS NULL OR B.name IS NULL -- 完全外部結合から不要な結果を除く
;

-- 商集合を求める A/B（関係除算）
-- 「Itemsテーブル（商品カラム）」「ShopItemsテーブル（店舗・商品カラム）」から「Itemsテーブルの全商品を扱っている店舗」を取得する
-- 考え方としては、対象商品から店舗ごとの商品を引き算した結果が空集合であれば、その店舗は対象商品をすべて揃えていると言える
--
-- item    +   shop item     →    shop
-- -----       ----------         -----
-- ビール        東京  ビール         東京
-- 紙オムツ      東京  紙オムツ
-- 自転車       東京  自転車
--             京都  ビール

SELECT
  DISTINCT shop
FROM
  ShopItems SI1
WHERE
  NOT EXISTS(
    SELECT
      I.item
    FROM
      Items I
      LEFT OUTER JOIN ShopItems SI2 ON
        I.item = SI2.item AND
        SI1.shop = SI2.shop -- 「店舗ごと」にチェックする
    WHERE
      SI2.item IS NULL
  )
;

パフォーマンスチューニング

チューニングにおいて大事なことは、ボトルネックを見つけ、そこを重点的に解消すること

データベースとSQLにおいて、最大のボトルネックになるのはストレージ（典型的にはハードディスク）へのアクセスになる
なので、低速ストレージへのアクセスを減らすことが有効であり、本稿で紹介する対応もそれを目的にしている
本格的なパフォーマンスチューニングを行なうには、使用しているハードウェアやDBMSが持つ機能・特徴についての知識が不可欠
今回は、手軽にできるパフォーマンスチューニング方法について紹介

INをEXISTSまたは結合に書き換える

IN述語はパフォーマンス面から見るとボトルネックになる可能性がある
具体的には、INの引数にサブクエリを取る場合になる

-- 遅い 
SELECT * FROM Class_A WHERE id IN (SELECT id FROM Class_B);

-- 速い 
SELECT * FROM Class_A A WHERE EXISTS (SELECT * FROM Class_B B WHERE A.id = B.id);

-- 結合で代用してもOK
SELECT A.id, A.name FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;

EXISTSのほうが速いと期待できる理由は以下の2つ

最低限必要な（サブクエリ）検索
- もし結合キー（この場合はid）にインデックスが張られていれば、インデックスを参照するのみで済む
  - Class_Bテーブルの実表は見にいかない
- また、EXISTSならワークテーブルは作成されない
  - INの引数にサブクエリを与える場合、DBはまずサブクエリから実行し、その結果を一時的なワークテーブル（インラインビュー）に格納し、その後、ビューを全件走査する
  - 一般にワークテーブルにはインデックスが存在しない
最低限必要なチェック
- EXISTSは1行でも条件に合致する行を見つけたらそこで検索を打ち切る
  - INのように全表検索の必要がない

インデックスが本当に効いているか確認する

基本的には、インデックスを利用するときは「列はそのまま使う」

インデックスを使うつもりが、実のところテーブルを全件検索してしまうケースがある

-- 索引列に加工を行なっている 
SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;

-- 関数を適用している
SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';

-- 否定形（<>, !=, NOT IN）
SELECT * FROM SomeTable WHERE col_1 <> 100;

-- ORを使っている
-- インデックスが利用できなくなるか、使えたとしてもANDに比べれると非効率的な検索になる
SELECT * FROM SomeTable WHERE col_1 > 100 OR col_2 = 'a';

-- 複合索引の場合に、列の順番を間違えている
-- 複合インデックス `（col_1, col_2, col_3）` の場合、必ず最初の列（col_1）を先頭に書き、かつ順番も崩してはいけない
-- 順番が崩れていてもインデックスを利用できる場合もあるが、順番が正しい場合に比べて非効率的な検索になる
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 1000;
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 1000;

-- 後方一致、または中間一致のLIKE述語を用いている
SELECT * FROM SomeTable WHERE col_1 LIKE '%a'; 

-- 暗黙の型変換を行なっている
-- 文字列型で定義されたcol_1に対する条件を書く場合
SELECT * FROM SomeTable WHERE col_1 = 10;

また、インデックスにおけるNULLの扱いは難しく、実装によっても異なる

IS NULLやIS NOT NULLを使用するとインデックスが使用されなかったり、
NULLが多い列ではインデックスが利用されなかったりという制限を受けることがある

暗黙に行われるソートに注意する

ソートは頻繁に暗黙に行なわれているため、どの演算でソートが発生するのか意識する必要がある
ソートに必要なリソースがメモリ上では足りずに、ストレージを使って行なわれる場合、パフォーマンスが大きく低下するため、無駄なソートは極力回避する

ソートが発生する代表的な演算は以下

GROUP BY句
ORDER BY句
集約関数（SUM、COUNT、AVG、MAX、MIN）
DISTINCT
集合演算子（UNION、INTERSECT、EXCEPT）
- ALLオプションを付けないと、重複排除のために暗黙のソートを発生させる
ウィンドウ関数

ソート自体をなくせない場合、ソートにインデックスを使うことで高速化することは可能
例えば、極値関数の引数の列にインデックスが存在する場合、そのインデックスのスキャンだけで済むため、実表への検索を回避できる（複合インデックスの場合でも、先頭列であれば有効）

-- 前提として、item_idインデックスあり、item_nameインデックスなしの場合

-- これは全表検索が必要
SELECT MAX(item_name) FROM Items;

-- これはインデックスを利用できる
SELECT MAX(item_id) FROM

余計な中間テーブルを減らす

SQLでは、サブクエリの結果を中間テーブルとして、他テーブルと同じように扱える
中間テーブルの問題点は、

データを展開するためにメモリ（場合によってはストレージ）を消費すること
元テーブルに存在したインデックスを使うのが難しくなる（特に集約した場合）

-- 「itemsテーブル（商品ID・商品名カラム）」「SalesHistoryテーブル（売上日・商品ID・売上量カラム）」から「itemsテーブルの商品毎の売上総量」を求める
--
-- item_no item_name   +    saled_date item_no quantity
-- -----------------        ---------------------------
-- 1       PC               20230101   1       10
-- 2       机               　20230102   2       10 


-- 結合の前に集約する場合、以下の問題がある
-- 中間ビューSHのデータを一度メモリ上に保持する必要がある
-- SH自身には主キーのインデックスが存在しないため、その分結合が非効率になる
SELECT
  I.item_no,
  SH.total_qty
FROM
  Items I
  LEFT OUTER JOIN(
      SELECT
        item_no,
        SUM(quantity) AS total_qty
      FROM
        SalesHistory
      GROUP BY
        item_no
    ) SH ON
    I.item_no = SH.item_no
;

-- 集約の前に結合する場合
-- 「結合を掛け算としてみる」視点から、クエリを改良することで、
-- コードを簡潔に記述でき、中間ビューをなくすことによってパフォーマンスが向上する
SELECT
  I.item_no,
  SUM(quantity) AS total_qty
FROM
  Items I
LEFT OUTER JOIN SalesHistory SH ON
  I.item_no = SH.item_no
GROUP BY
  item_no
;

レコード数は早い段階で絞り込む

具体的には、WHERE句で書ける条件はHAVING句には書かない

ソート負荷の軽減
- GROUP BY句による集約はソートやハッシュの演算を行なうので、事前に母数を絞り込めればその分処理量が減る
インデックス利用
- HAVING句は、集約した後のビューに対する条件を設定するが、集約後のビューは元テーブルのインデックスまでは引き継がないケースが多い
- WHERE句の条件にインデックスがあれば絞り込みも効率的に行える

終わりに

SQLを書く際、どういう書き方するんだっけとなる度に、繰り返し読んでる
- そして、読む度に「SQLって奥が深い」と思わせてくれる
SQLに限らず他技術にも自分の知らない世界があって、そう思うと気が遠くなるような嬉しいような
後、この本をきっかけにしてSQLについて学んでいく過程で、サピア＝ウォーフの仮説的なことを体感していて、それも新しい発見だった

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up