SQL
- 第１部 魔法のSQL 1-7 SQLで集合演算｜達人に学ぶSQL徹底指南書 (27 Jul 2016 | Tags:
第１部 魔法のSQL 1-7 SQLで集合演算｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-7 SQLで集合演算｜達人に学ぶSQL徹底指南書
1-7 SQLで集合演算
導入ー集合演算に関するいくつかの注意点
- 注意１：SQLの扱う集合は重複行を許す多重集合のため、それに対応するALLオプションが存在する
ex.) UNION ALL
集合演算は重複排除のために暗黙のソートを発生させるが、ALLオプションを付けるとソートが行われないのでパフォーマンスが向上する。
- 注意２：演算の順番に優先順位がある UNIONとEXCEPTに対して、INTERSECTのほうが先に実行される。
併用する場合、INTERSECT以外を優先的に実行したい場合は、括弧で明示的に演算の順番を指定する必要がある。
-
注意３：DBMSごとに集合演算子の実装状況にバラツキがある
-
注意４：除算の標準的な定義がない
テーブル同士のコンペアー
1. 集合の相性チェック：基本編
ALLオプションを付けなければ重複行を排除する。テーブル同士が同じなら、重複が排除されきれいに重なり合う。
2. 集合の相性チェック：応用編
INTERSECTにも冪等姓が成り立つ。
A INTERSECT B = A = B
A≠Bの場合、UNIONとINTERSECTの結果は異なり、UNIONの方が絶対に行数が多くなる。
(A UNION B) EXCEPT (A INTERSECT B)が空集合かどうかを判定する。差分は排他的和集合を選択すればよい。
3. 差集合で関係除算を表現する
要求される集合から引き算して、結果が空集合なら全部備えていた、と判定できる。
4. 等しい部分集合を見つける
集合の相等性を調べる方法には、冪等性か全単射を利用する２通りがある。
5. 重複行を削除する高速なクエリ
極値関数を使って残したいrowidを１つだけ取得し、その集合をテーブル算対から引き算する。
EXCEPTを使うと、補集合を簡単に表現できる。
LinkLatest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
- 第１部 魔法のSQL 1-6 相関サブクエリで行と行を比較する｜達人に学ぶSQL徹底指南書 (21 Jul 2016 | Tags:
第１部 魔法のSQL 1-6 相関サブクエリで行と行を比較する｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-6 相関サブクエリで行と行を比較する｜達人に学ぶSQL徹底指南書
1-6 相関サブクエリで行と行を比較する
成長・後退・現状維持
相関サブクエリと自己結合は同値変換可能な場合が多い。
パフォーマンスは、環境によりけり。
前年の比較結果を一覧表示する
相関サブクエリをSELECT句に移して、前年と今年の比較をする。
時系列の歯抜けがある場合：直近と比較
内部結合だと、比較対象のない最初の年度が除外される。 除外しない場合、自己外部結合を使う。
極地関数はソートを発生させるので、パフォーマンスが落ちる。
移動累計と移動平均
相関サブクエリでは、スカラ・サブクエリで行数のカウントを取れる。
オーバーラップする期間を調べる
おわりに
相関サブクエリを使うと、
- 可読性が低くなりやすい
- パフォーマンスがよくない
Latest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
- 第１部 魔法のSQL 1-5 外部結合の使い方｜達人に学ぶSQL徹底指南書 (20 Jul 2016 | Tags:
第１部 魔法のSQL 1-5 外部結合の使い方｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-5 外部結合の使い方｜達人に学ぶSQL徹底指南書
1-5 外部結合の使い方
外部結合で行列変換 その１（行→列）：クロス表を作る
外部結合はスカラ・サブクエリで代用できる。
外部結合で行列変換 その２（列→行）：繰り返し項目を１列にまとめる
UNION ALLで、行持ちに変換。UNION ALLは重複行を削除しない。
クロス表で入れ子の表側を作る
外部結合でも、結合条件がNULLとなり、結果がunknownになる。
表側を入れ子にする場合、その形のマスタをあらかじめ用意する。
掛け算としての結合
結合は、一対一でなくとも一対多ならべ行数は（不当には）増えない。
完全外部結合
完全外部結合は「両方をマスタに使う結合」
完全外部結合を使えない環境で同じ結果を得るには、左外部結合の結果と右外部結合の結果をUNIONする。
内部結合 = 積集合（INTERSECT）
完全外部結合 = 和集合（UNION）
外部結合で集合演算
集合演算子はソートを発生させるので、パフォーマンス上の問題を引き起こす可能性がある。
完全外部結合で排他的和集合を求める
関係除算を外部結合で書ける。
まとめ
「OUTER」は省略可能だが、内部結合に対して外部結合であることを明治するために、省略しないほうが良い。
Latest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
- 第１部 魔法のSQL 1-4 HAVING句の力｜達人に学ぶSQL徹底指南書 (19 Jul 2016 | Tags:
第１部 魔法のSQL 1-4 HAVING句の力｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-4 HAVING句の力｜達人に学ぶSQL徹底指南書
1-4 HAVING句の力
データの歯抜けを探す
現在の標準SQLでは HAVING句を単独で使える。 ただしその場合、SELECT句で元テーブルの列を参照できなくなるので、定数を指定するか集約関数を使う必要がある。
HAVING句でサブクエリ：最頻値を求める
GROUP BYは、もとの集合から部分集合を作る働きをする。
HAVING句で自己結合：メジアンを求める
大小関係に基づいて部分集合を作るなら、自己非等値結合。
NULLを含まない集合を探す
COUNT(*)はNULLを数える。COUNT(列名)はNULLを除外して集計する。
特定の条件を満たす集合に含まれるかどうかを決める関数。 特性関数
関係除算でバスケット解析
ひとつの実体についての情報が複数行に分散して存在する場合、WHERE句で単純にORやINで条件を指定しても正しい結果は得られない。 WHERE句で指定する条件は、あくまで１行について適用されるから。
まとめ
WHERE句は 集合の要素 の性質を調べる道具、対してHAVING句は 集合自身 の性質を調べる道具。
Latest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
- 第１部 魔法のSQL 1-3 3値理論とNULL｜達人に学ぶSQL徹底指南書 (19 Jul 2016 | Tags:
第１部 魔法のSQL 1-3 3値理論とNULL｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-3 3値理論とNULL｜達人に学ぶSQL徹底指南書
1-3 3値理論とNULL
1. 比較述語とNULL その１：排中律が成立しない
「命題とその否定を「または」でつなげてできる命題はすべて真である」というのが排中律。
2. 比較述語とNULL その２：CASE式とNULL
単純CASE式の
CASE col_1 WHEN NULLは、col_1 = NULLの省略形なので、trueにならず思ったような動作にならない。3. NOT INとNOT EXISTSは同値ではない
NOT INのサブクエリで使用されるテーブルの選択列にNULLが存在する場合、 SQL全体の結果は常に空になる。
EXITSはtrue とfalse しか返さない。そのため、INとEXISTSは同値変換が可能だが、NOT INとNOT EXISTSは同値ではない。
4. 限定述語とNULL
ALL述語は条件をANDで連結した論理式の省略形。
5. 限定述語と極値関数は同値ではない
極値関数は 集計の際にNULLを排除する、 入力が空集合だった場合はNULLを返す。
比較対象が存在しない場合、全行を返すべきか１行も返さないべきか、判断が必要。
6. 集約関数とNULL
COUNT関数以外の集約関数も、入力が空の場合NULLを返す。
NULLを何らかの値に変換するか、帰ってきたNULLに対処するのかのどちらかを選ぶ必要がある。
まとめ
NULLみな死すべし、慈悲はない
Latest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
- 第１部 魔法のSQL 1-2 自己結合の使い方｜達人に学ぶSQL徹底指南書 (17 Jul 2016 | Tags:
第１部 魔法のSQL 1-2 自己結合の使い方｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-2 自己結合の使い方｜達人に学ぶSQL徹底指南書
1-2 自己結合の使い方
重複順列・順列・組み合わせ
順列対
クロス結合して単純に直積を作れば得られる。
順序対から重複を排除
結合条件によって、同一要素の組み合わせを排除。 ２つのテーブルが実際に存在すると想像することがポイント。
組み合わせ
等号「=」以外の比較演算子である<や>, <>を使って行う結合を「 **非等値結合** 」と言う。
重複行を削除する
自己相関サブクエリを使って重複を削除。 SQLの動作を考える際に、データの記憶方法（テーブル、ビュー）を考慮する必要はない。どちらも等しく「集合」（関係）。
部分的に不一致なキーの検索
自己非等値結合を使う。 結合の代わりに、相関サブクエリを使っても書ける。この場合、DISTINCTが不要になる。
ランキング
自己非等値結合を使う。 「同心円的な」（セルコ）再帰的集合を作る。
※ 結合条件に合致しない場合も結果に反映させるために、内部結合ではなく外部結合を使っている。
同順位が続いた後に順位を飛ばす
COUNTで件数をだし、順位に使う。
連続的に出力する
COUNTの対象をDISTINCTにすることで対応。
まとめ
自己結合はとても強力だけど、異なるテーブル同士を対象に行う結合に比べてコスト高。 ※ 特に非等値結合と組み合わせる場合。
自己結合で使用する結合キーは、なるべく主キーを使うか、インデックスを作成しておく。
本当に異なるテーブルを結合していると理解しやすい。
Latest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
- 第１部 魔法のSQL 1-1 CASE式のススメ｜達人に学ぶSQL徹底指南書 (17 Jul 2016 | Tags:
第１部 魔法のSQL 1-1 CASE式のススメ｜達人に学ぶSQL徹底指南書 )第１部 魔法のSQL 1-1 CASE式のススメ｜達人に学ぶSQL徹底指南書
1-1 CASE式のススメ
既存のコード体系を新しい体系に変換して集計する
GROUP BY句でCASE式を使うことで、集計単位を自由に設定できる。動的な変更もできる。
PostgreSQL,MySQLでは、SELECT句のCASE式に別名をつけて、GROUP BY句で参照できる。※標準違反
異なる条件の集計を1つのSQLで行う
SQLの結果を二次元表形式に整形できる。クロス表の形式で結果が出力される。
WHERE句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる。
CHECK制約で複数の列の条件関係を定義する
CHECK制約でCASE式が使える。 論理積よりもゆるい条件法で記述できる。
条件を分岐させたUPDATE
複数のUPDATEを、CASE式を使って１つのSQLで実現できる。 退避用の値を用意しなくてすむ。
テーブル同士のマッチング
DECODE関数などと比べると、CASE式は式の中で式を評価できる（BETWEEN, Like, >, < etc..といった述語群を使える）。
CASE式の中で集約関数を使う
集約結果に対する条件をHAVING句ではなく、CASE式を使うことでSELECT句で書くことができる。
HAVING句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる。
まとめ
CASE式は、列名や定数を書ける場所には常に書くことができる。 便利だからCASE式を使おう。
Latest post:
- OpenWhiskのScala sbtプロジェクトのgiter8テンプレートを作った
- OpenWhisk+Scalaで作るServerless Architectureとっかかり
- BluemixにPlayframeworkアプリケーションをデプロイする
- sbt、Giter8を統合するってよ
- Scala 2.12.0でSAM型
Recent Books:
