作りたいものがありすぎる

40歳を過ぎてプログラミングを始めた人の顛末とこれからなど

【輪読会資料】達人に学ぶSQL徹底指南書第2版 第二部後半　読書メモ

MySQL SQL 輪読会 資料

以下の記事は2019/1/16　コワーキングスペース秋葉原Weeybleで行われる輪読会
[秋葉原] 達人に学ぶSQL徹底指南書 輪読会 第2部 魔法のSQL(第2部 RDBの世界) のための読書メモとなります。
以下の書籍の 第二部 9 18 GROUP BY と PARTITION BY ～ 23 SQLにおける存在の改装 のメモです。

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ (CodeZine BOOKS)

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ (CodeZine BOOKS)


前提として

私のSQLスキルはMySQLを前提として基本的な知識のみで、書籍としてSQL本を読むのは、リファレンスを除いてこれが2冊目となります。まだまだ知らない事だらけ、という事を前提に以下のメモを読んでいただければ幸いです。

18 GROUP BY と PARTITION BY

GROUP BY と PARTITION BYは似てる、ということだが、そもそもPARTITION BYを使った事がなった。

GOROUP BY は分けたあとに代表だけ表示
PARTITION BY は分けた後にそのまま全て表示

しかし、この2つはいずれも『指定されたキーで分割をしている』という事らしい、違いはGROUP BY はキーで分割後に『集約してまとめる』操作が入る。
これらで集合されたものは以下の特徴を持つ

  1. いずれも空集合でない
  2. ずべての部分集合の輪が分割する前の集合一致
  3. 互いにことなる任意の２つの部分集合が共通部分を持たない

集合論的にはパッキリ割れるし余りや重複も出ない。完全な割り当てをしてくれる。

この集合部分のひと塊を『類（るい）』と呼ぶ


群論とSQL

MODという除算の余りを出す関数がSQLにはある（PHPだと %）これを使って、多数のデータのランダムサンプリング、もしくは、ほぼ同等のデータに振り分けをすることが簡単にできるので、MODでの振り分けは覚えておくと良いかもしれない。

SELECT MOD(num, 3) AS modulo,
    num
FROM Natural
ORDER BY modulo, num;

3で割った際の余りの数で振り分けをした例

modulo     num
--------   -----
0             0
0             3
0             6
1             1
1             4
1             7
2             2
2             5
2             8
...

当たり前だが多数のレコードのあるテーブルのユニークID（抜け漏れなし）を特定の数の余りで振り分けると、ほぼ同じ数でのグルーピングが可能となる。


19 手続き型から宣言型・集合指向へ頭を切り替える7箇条

はじめに

SQLはプログラミングにある手続き型の言語ではなく、宣言・集合型言語である、ここはこの概念を理解し生かすための実践ガイドとする章。

1. IF文やCASE文は、CASE式で置き換える。SQLはむしろ関数型言語と考え方が近い

MySQLにあるIFはむしろ特殊、またCASEは文（手続き）ではなく、一種の関数（数式の方の意味）としてとらえる

2. ループはGROUP BY句とウィンドウ関数で置き換える

SQLには文単位のループも存在しない。

3. テーブルの行に順序はない

テーブルはファイルよりも抽象度が高い。テーブルのレコードを『ファイル』の様に捉えない、テーブルは数学の「集合（set）」の一種である。 ビューにORDER BY を入れても意味はない、Oracleのrownum の様な考えも特殊な仕様ととらえる。

4. テーブルを集合とみなそう

テーブルの抽象性を理解するには自己結合を使ってみると良い。自己結合を使うと、好きな数だけ、集合を追加し、操作することができる。

5. EXISTS述語と「量化」の概念を理解する

SQLを支える理論は集合の他にも『述語論理』がある。「複数行を一単位として」扱う際、述語理論では『量化子』だが、SQLではEXISTSとなる。 また、使いこなす必要があるのは NOT EXISTSの方が大事。読みづらいがパフォーマンスが格段に良い。

6. HAVING句の真価を学ぶ

WHEREと異なりSQLを集合論としてとらえる事が出来るHAVING, をもっと活用しよう HAVINGの練習をすると知らず知らずに集合論の本質の理解が進むでしょう。

7. 四角を描くな、円を描け

SQLは手続きではない四角と矢印の手順図とは概念がことなり、ベン図（〇の中に〇が現れる図）として集合でとらえる事が重要

感想

俄然なにが筆者をここまで執拗にSQL集合論を書かせるに至ったのかに興味がわいてきた。


20 神のいない理論

SQLの元になる論理学の話。従来は真と偽になる2値原理の古典論理が、1920年代に新たな3値論理が生まれSQLに生かされたらしい。

汝、場合により命題の真偽を捨てよ

3値論理学の体形をはじめて作ったのはポーランドの論理学者J.ウカシェヴィッツ、関数型言語のポーランド記法「3+2」を「+3 2」を考案した人
論理学で真偽の二つ以外にも、「わからない、未知」となる概念の存在を提案した。

論理学の革命

神、宗教が支配していた時代は神による論理が主となる、神は全てを知っており、全ての真偽を知っている。という思想から、2値原理を当然としていたが、人間は全ての真理を知らないし、神に全てを訪ねも答えてくれる訳ではないので、「知らない、未知」という状態を論理学として取り入れるべき、という流れが出て来た。そもそもこのような提案ができたのは、宗教による支配が弱くなった時代背景もある。

人間の為の論理

データベースを扱うのは神ではなく人間なので、人間の認知や知識を表現するのに適した論理である3価理論（NULL, unknown）が採用された。 しかし、皮肉にもそれによって、人間の直感に反する奇妙な論理計算を導入せざるを得なくなった。


21 SQLと再帰集合

集合のなかに集合を含む入れ子の集合、「再帰集合」の扱いを知るのは重要という話。

実務の中の再帰集合

そもそもノイマンは何故自然数を再帰集合で定義しようとしたのか？（P57）

ノイマンによる自然数の帰納的定義

0 = ∅
1 = {0}
2 = {0, 1}
3 ={0, 1, 2}
...

ノイマンの先輩たち

ノイマン以前に自然数を集合で定義した数学者、哲学者がいる。
ゴットローブ・フレーゲ　哲学者　述語理論をほぼ独力で創始した
エルンスト・ツェルメロ　数学者　集合論の体系整備、整列可能定理と選択公理

それぞれの自然数の機能的定義

自然数 ノイマン型 ツェルメロ型 フレーゲ型
0 ∅ ∅ {∅}
1 {∅} {∅} {∅,{∅}}
2 {∅,∅} {{∅}} {∅,{∅},{∅,{∅}}}
3 {∅,∅,∅} {{{∅}}} {∅,{∅},{∅,{∅}}{∅},{∅,{∅}}}}
...

疑問

  1. 自然数の定義がこんなにたくさんあっていいのか？定義というのは普通１つなのでは？
  2. 何で自然数の定義に「集合」を使おうと思ったのか？

数とは何か？

ペアノの自然数の5つの公理

  1. 最初の数が存在する
  2. 任意の自然数aはその後者が存在する
  3. 最初の数はいかなる自然数の後者でもない
  4. 異なる自然数は異なる後者を持つ
  5. 最初の数がある性質を満たし、aがある性質を満たせばその後者もその性質を満たすとき、すべての自然数はその性質を満たす

ある自然数の次を数える関数を、後者関数と呼び suc(x) とかく。

0 = 0
1 = suc(0)
2 = suc(1) = suc(suc(0))
3 = suc(2) = suc(suc(suc(0)))
...

ノイマンはペアノの自然数の定義に見合う構成方法を考えたといえる。 これにより、自然数を構成する材料として∅を使う必要もなくなった。

0: λfx.x
1: λfx.fx
2: λfx. f(fx)
3: λfx.f(f(fx))

正直この辺までくるとちょっとよくわからない…

SQLの魔術と科学

ランキング算出のクエリの理屈にこういうものがある、と把握することによって世界が広がり理解が深まるのではないだろうか？


22 NULL撲滅委員会

実務に置いて厄介なNULLにどう対処していけばよいかの指針を提案している

決意表明~スベテノDBエンジニアニ告グ~

（デザイナーにおけるIE死ね死ね団的なノリノリである） NULLは人間の感覚的にはわかりやすく設計段階でついついいれてしまうが、システムが複雑化するととても厄介なものになる。そこでここではより具体的な提案をまとめた章となっている。

なぜNULLがそんなに悪いのか　　　

  1. SQLのコーディングにあたり、人間の直感に反する3値論理を考慮しないといけない
  2. IS NULL , IS NOT NULL を指定する場合、インデックスの利用に制限が入りパフォーマンスが低下する
  3. 四則演算またはSQL関数の引数にNULLが含まれると「NULLの伝播」が起こる
  4. SQLの結果を受け取るホスト言語において、NULLの組み込み方が標準化されていない。また、DBMS間でもNULLの扱いに関する仕様が不統一
  5. 通常の列の値と異なり、NULLは行のどこかに余分なビットを持つことで実装されている。記憶領域の圧迫や検索パフォーマンス悪化の要因となる。
  6. NULLを含むカラムに作成するユニークインデックスの「ユニーク」の意味がRDBMSで異なる。例：複数のNULLを含む列にユニークインデックスを作成する際、エラーになったりならなかったり。
  7. NULLは値ではない為、ORDER BY 句によるソートの際のルールを意識する必要がある。NULLは定義含まれないが、実際は最大値か最小値として扱われ、実装によってデフォルトが異なるのでややこしい事になる。

もっとも忌むべき理由は1.
また3.も厄介　四則演算にNULL が入ると計算結果がNULLとなってしまう。
4~7は仕様違いによる厄介さ

しかしNULLを完全に排除することはできない

しかし、実務では『重要でない列にNULLが入ってくるのは目をつむる』位が実際の運用でのルールになっている。
カラムの制約でNOT NULL をしたとしても、外部結合や CUBE,ROLLUP付きのGROUP BY句を使うとNULLが入り込んでしまう。

コッドさんはNULL撲滅の最右翼、著者もそこに近い所に行きたい心情ではあるが、エンジニアの現実感覚として以下の方針を提案している

NULLは薬、用法容量を守って使う。使わざる得ない時のみに使う

次からは具体的なNULL排除の指針を提案してゆく

コードの場合ーー未コード化用コードを割り振る

true,false の様な2つの値のみ入るレコードは3つ目の値、未定義などに、NULLではなく、数字を当てはめると良い
例えば性別
1:男性 2: 女性 3:未知 9:適用不能 といった具合

例えばここに人ではなく、企業アカウントとしてレコード登録する際は　9:適用不能　を選択させる コッドの提案した『未知』と『適用不能』を値として設ける方法。

また、不明なレコードを入れなければならない場合、例えば数値として9999を入れるのではなく、そのカラムでは普段は使わない文字列　XXXX　などを使うと良いだろう。　9999　という数字を持つユーザーが実際に存在する可能性があるなら数値は避けるべき。

名前の場合ーー「名無しの権兵衛」を割り振る

不明を表すデフォルトの値を入れると良い『不明』『UNKNOWN』など開発チーム内で共通了解の取られたものを入れると良い

数値の場合ーー0で代替する

NULLを0に変換してDBへ登録すると良い。筆者が実務で困った事は経験上あまりないらしい。
しかし、どうしても0とNULLを区別したい時だけNULLを許可すると良い。

日付の場合ーー最大値・最小値で代替する

日付が開始日や終了日を意味する場合は「0001-01-01」「9999-12-31」等の最大値、最小値を使うと良い。
しかし、デフォルト値がそもそもわからない誕生日など「未知」のNULLに相当する場合はNULLを許可しても良いでしょう。

指針のまとめ

  1. まずデフォルト値を入れられないか検討する。
  2. どうしようもない場合だけNULLを許可する。

というのが筆者の指針となる。


23 SQLにおける存在の階層

GROUP BY を使って集約をすると、集約キーを除いて、元のテーブルの列をそのまま参照できなくなるが、これは存在の階層を厳密に区別するSQL理論の現れ、ここからSQLの本質にせまる。

述語論理における階層、集合論における階層

P84 EXISTS述語の使い方を復習 EXISTSは高階関数である
2階 テーブルの集合
1階 テーブル（行集合）
0階 行

実はGROUP BY 句はEXISTS同様に階層がある。

なぜ集約すると、もとのテーブルの列を参照できなくなるのか？

以下の様なテーブルがあり、チームごとの平均年齢を出すクエリを考える

Teams

member team age
大木 A 28
逸見 A 19
新藤 A 23
山田 B 40
久本 B 29
橋田 C 30
... 
SELECT team, AVG(age)
 FROM Tems
GROUP BY team;

これはちゃんと出力されるが、 次のはエラーになる。

-- チーム単位に集約するクエリ？
SELECT team, AVG(age), age
 FROM Tems
GROUP BY team;

理由はSELECT句に追加されたage 列を選択する事が出来ない為。 標準SQLではテーブル集約をした際、SELECT句に書ける要素は以下となる。

  1. GROUP BY句で指定した集約キー
  2. 集約関数（SUM, AVGなど）
  3. 定数

上記のエラーになったクエリの場合、 age は集約できない個人の年齢を差している。 個人の年齢は集約された際の「集団についての属性」ではない、当たり前だが、集団の統計属性なら出力できるが、個人は出力できない。と考えると良い。 過去のMySQLでは特別に可能だったが8.0以降ではやはりエラーになる様になった

GROUP BY で集約を行うと、SQLが扱う「行」という０階の存在から、「行の集合」という１階の存在に変化するため、「行」の属性は参照不能となる。

つまり、GOROUP BY で集団化されたものに、個人の〇〇を訪ねても駄目、という風に解釈すると良い。

もし、上記のクエリで名前を尋ねたい、とするなら仮に以下の様に　MAXのmemberを求めるしかない

-- 正常
SELECT team, AVG(age), MAX(member)
 FROM Tems
GROUP BY team;

このクエリを応用すると「チームでの最高齢の年齢の人物」を求めるクエリも書ける。

-- チーム最高齢者も出力
SELECT team, MAX(age)
    (SELECT MAX(member)
      FROM Teams T2
      WHERE T2.team = T1.team
      AND T2.age = MAX(T1.age)
    ) AS oldest
FROM Tems T1
GROUP BY team;

team   max(age)    oldest
------   ----------   ---------
A         28              大木
B         40              山田
...

通常、WHERE句で集約関数は使えないが、このサブクエリ内では使える。MAX(T1.age) 理由は外側のT1テーブルを集約（１行目の MAX(age)かな？ ）したことによって、SELECT句集約関数が参照可能になる為。 その代わり今度は逆にサブクエリ内で age を裸で利用することはできない。つまり集約したルールはサブクエリと外のクエリで合わせなら利用できる。という事らしい。

単元集合も立派な集合です

GROUP BYをする際、そもそも、元のレコードでも１要素しかないものを集約した場合、どう動くか？ （例となるTeamテーブルにはCチームに１メンバーのみのレコードがある）

実はこれも１単位でも１グループとして扱う。やはり階が0階から１階上がるという事、なので注意しましょう。ということ。