「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

【完結】SQL中級者になる!(目次)

初めに

 データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
 この知識をより昇華させてSQL中級者になりたい!ため、同じ著者の

を参考に、更なるSQLの知識を拡充していこう。

1. CASE式

  • \mathrm{GROUP\ BY}句で\mathrm{CASE}式を用いることで集約単位となるコードや階級を柔軟に設定できる。これは非定形的な集計に有効である。
  • 集約関数の中に使うことで行持ちから列持ちへの水平展開も簡単にできる。
  • 集約関数を条件式に組み込むことで\mathrm{HAVING}句を使わずにクエリをまとめられる。
  • 実装依存の関数より表現力が強力かつ汎用性も高まる。
  • 以上ができるのは\mathrm{CASE}式が式であるからである。
  • \mathrm{CASE}式を駆使することで複数の\mathrm{SQL}分をつにまとめられ、可読性もパフォーマンスも向上する。

power-of-awareness.com

2. ウィンドウ関数

  • ウィンドウ関数の「ウィンドウ」は順序を持つ「範囲」の意味である。
  • ウィンドウ関数の構文上では\mathrm{PARTITION\ BY}句と\mathrm{ORDER\ BY}句で特徴づけられたレコード集合を意味し、一般的に簡略的の構文が使われるため、却ってウィンドウの存在を意識しにくい。
  • \mathrm{PARTITION\ BY}句は\mathrm{GROUP\ BY}句から集約の機能を引いて、カットの機能だけを残し、\mathrm{ORDER\ BY}句はレコードの順序を付ける。
  • フレーム句はカーソルの機能を\mathrm{SQL}の構文に持ち込むことで「カレントレコード」を中心にしたレコード集合の範囲を定義することができる。
  • フレーム句を使うことで、異なる行のデータを1つの行に持ってくることができるようになり、行間比較が簡単に行えるようになった。
  • ウィンドウ関数の内部動作としては、現在のところ、レコードのソートが行われている。将来的にハッシュが採用される可能性も零ではない。

power-of-awareness.com

3. 自己結合

  • 自己結合は非等値結合と組み合わせて用いるのが基本である。
  • \mathrm{GROUP\ BY}と組み合わせると再帰的集合を作ることができる。
  • 全くもって異なるテーブルを結合していると考えれば分かりやすい。

power-of-awareness.com

4. 3値論理

  • \mathrm{NULL}は値ではない。
  • 値ではないので述語も値の陽には適用できない。
  • 無理やり適用すると\mathrm{unknown}が生じる。
  • \mathrm{unknown}が論理演算に紛れ込むと\mathrm{SQL}が直観に反する動作をする。
  • これらに対処すべく、段落的なステップに分けて\mathrm{SQL}の動作を追うことが有効である。

power-of-awareness.com

5. EXISTS述語

  • \mathrm{SQL}では\mathrm{EXISTS}は重要な意味を持つ。
  • \mathrm{SQL}における述語は真理値を返す関数を指す。
  • \mathrm{EXISTS}のみが(行の)集合を引数に取れる。
  • その意味では\mathrm{EXISTS}高階関数の一種と見なせる。
  • \mathrm{SQL}には全称量化子に相当する演算子が無いため\mathrm{NOT\ EXISTS}で代用する。

power-of-awareness.com

6. HAVING句

  • \mathrm{HAVING}句の応用方法を見てきた。\mathrm{HAVING}句を用いるときには、何を以て集合と見なすかに注目する。何かで集計されたものが複数行で成り立つならばそれを集合と見なせ、そのときには\mathrm{HAVING}句が利用できるかを考える。
  • テーブルはファイルではなく、行も順序を持たない。そのため\mathrm{SQL}は原則としてソートを記述しない。
  • 代わりに\mathrm{SQL}は、求める集合にたどり着くまで次々に集合を作る。
  • \mathrm{GROUP\ BY}句は過不足なく同値類を作る。
  • \mathrm{WHERE}句が集合の要素の性質を調べるものであるのに対し、\mathrm{HAVING}句は集合自身の性質を調べるものである。
  • \mathrm{SQL}で検索条件を設定する場合、検索対象が集合なのか集合の要素なのかに応じてそれぞれ\mathrm{WHERE}句または\mathrm{HAVING}句を用いるかを考える。

power-of-awareness.com

7. ウィンドウ関数(再訪)

  • 昔は、\mathrm{SQL}で行同士の比較を行う場合、比較対象のテーブルを追加して相関サブクエリを行なっていた。
  • しかし相関サブクエリはパフォーマンスと可読性が悪く、\mathrm{SQL}ユーザーから不評だった。
  • ウィンドウ関数の登場により、相関サブクエリを使う必要はなくなり、可読性が高くパフォーマンスが改善できた。

power-of-awareness.com

8. 外部結合

  • \mathrm{SQL}は帳票作成のための言語ではないため、フォーマット成型には不向きである。
  • 必要に迫られた際には外部結合と\mathrm{CASE}式を駆使する。
  • 入れ子の表側を作成する際はマスタとなる直積を作ってから結合を一回することで済ませる。
  • 行数に着目した場合、結合は「掛け算」として考えることができる。そのため一対多の結合では結果の行数は増えない。
  • 外部結合は集合演算と類比的に感がられる。その観点から様々な集合演算を表現できる。

power-of-awareness.com

9. SQLでの集合演算

  • \mathrm{SQL}は集合演算機能の整備が遅れていた。今でも\mathrm{DB}によって実装状況が相違する。
  • 集合演算子\mathrm{ALL}オプションを付けないと重複排除を行ない、その際にソートも行なうため、パフォーマンスが悪い。
  • \mathrm{UNION},\mathrm{INTERSECT}は冪等性を持つ。他方で\mathrm{EXCEPT}は持たない。
  • 集合の除算は自前で作る必要がある。
  • 集合の相等性を調べるには、冪等性または全単射を用いる。
  • \mathrm{EXCEPT}を用いると補集合を簡単に表現できる。

power-of-awareness.com

10. SQLでの数列

  • \mathrm{SQL}でのデータの扱い方には2通りである。
  • 1つは、順序を無視した集合と見なす方法である。この場合、伝統的な\mathrm{SQL}の集合と述語による考え方に基づいて考える。
  • もう1つはおもにウィンドウ関数による直接的な順序の操作による、順序を持った集合と見なす方法である。
  • \mathrm{SQL}で全称文を記述したい場合、存在分の否定に同地変形して、\mathrm{NOT\ EXISTS}述語を用いる必要がある。

power-of-awareness.com

11. 高速なSQL

  • \mathrm{SQL}のチューニングでは、ボトルネックを見つけ、そこを重点的に解消すること。
  • \mathrm{IN}にサブクエリを取る場合、\mathrm{EXISTS}または結合に書き換える。
  • インデックスを利用するとき、「左辺は裸」とすることが基本である。
  • \mathrm{SQL}は暗黙のソートを行なうことが度々あるため注意する。
  • 余計な中間テーブルは可能な限り減らす。
  • レコード数を絞れる条件は早期段階で記述する。

power-of-awareness.com

12. SQLにおけるプログラミング作法

  • 読みにくいコードは誰にとっても望ましくない。

power-of-awareness.com

プライバシーポリシー お問い合わせ