SQLもプログラミング言語…っぽい４
このシリーズも４回目になる。お題は以下の通り
テーブルの正規化
正規化とはなんぞや？
ここが分かりやすかった → データベースの正規化(正規形)とはなんぞや
以下、上のリンクに従って概要をまとめてみる
非正規形
わかりやすく言うとExcelで左のセルを縦に結合したとき、右に複数行のデータがあればそれは非正規形であると言える。データベースに配列型をぶっこもうとしている状態、アカンのである。
| 食品名 | 栄養成分 | 値 | 単位 | |
| 精白米 | ||||
| 炭水化物 | 168 | kcal | ||
| エネルギー | 37.1 | g | ||
| ビタミン | C | 1 | μg | |
| D | 1 | μg | ||
| E | 1 | μg | ||
上の例だと、「精白米」という１レコードに対して「炭水化物、エネルギー、ビタミン」という３レコードがくっついている。
この考えをそのままデータベースに適用しようとすると、配列型が用意されてないDBだとできない*1。それで、VARCHAR型に無理やりカンマ区切りでデータを入れたりするやらかし先生がいるかもしれない。「炭水化物,エネルギー,ビタミン」みたいな感じで…
実は、これはSQLの有名なアンチパターンで、「Jaywalking」でググるといろいろ出てくる。
www.slideshare.net
上のスライドには種本がある、自分はまだ読めていない
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (45件) を見る
第1正規形
先ほどのExcelの結合部分を右の行数に合わせて分割する。繰り返し項目が増えるが、これでレコードがそれぞれ１行で表せる。
| 食品名 | 栄養成分 | 値 | 単位 |
| 精白米 | 炭水化物 | 168 | kcal |
| 精白米 | エネルギー | 37.1 | g |
| 精白米 | ビタミンC | 1 | μg |
| 精白米 | ビタミンD | 1 | μg |
| 精白米 | ビタミンE | 1 | μg |
第2正規形・第3正規形
この２つはそんなに変わらない気がする。表を独立する部分で切り出して行けば自ずとこれになる*2。自分が作った例では表が簡単すぎて分割できない…他のサイトを見てみてください。
外部結合の設定
SQLは上の要領で分割して粗結合にしたテーブルを外部結合して見たい情報を揃えるのが一般的な使い方だ。
以前取り上げた例をもとにやってみよう
SQLもプログラミング言語…っぽい - なんとな～くしあわせ？の日記
LEFT JOIN
Parentテーブル
| 主キー | 整理番号 | 姓 | 名 |
| 1 | 12345 | 山田 | 太郎 |
| 2 | 12346 | 山田 | 花子 |
| 3 | 12346 | 山田 | 花子 |
| 4 | 12347 | 山田 | 嘘子 |
| 5 | 12348 | 山田 | 良子 |
Childテーブル
| 整理番号 | 住所 | 郵便番号 |
| 12345 | どこかの台 １丁目 | 131-0202 |
| 12346 | すすきが原 ３丁目 | 251-0003 |
| 12346 | ニセコ | 201-0003 |
Parentテーブルの整理番号とChildテーブルの整理番号が共通してるので、Parentテーブルを基本として左外部結合してみる
CREATE TABLE parent(`主キー` text, `整理番号` int, `姓` text, `名` text); INSERT INTO parent(`主キー`, `整理番号`, `姓`, `名`) VALUES (00001, 12345, "山田", "太郎") , (00002, 12346, "山田", "花子") , (00003, 12346, "山田", "花子") , (00004, 12347, "山田", "嘘子") , (00005, 12348, "山田", "良子"); CREATE TABLE child(`整理番号` int, `住所` text, `郵便番号` text); INSERT INTO child(`整理番号`, `住所`, `郵便番号`) VALUES (12345, "どこかの台 １丁目", "131-0202") , (12346, "すすきが原 ３丁目", "251-0003") , (12346, "ニセコ", "201-0003");
このように、左に指定したテーブルが中心となる。そのため右に結合したテーブルにNULLが入ることもある。
| 主キー | 整理番号 | 姓 | 名 | 整理番号 | 住所 | 郵便番号 |
| 1 | 12345 | 山田 | 太郎 | 12345 | どこかの台 １丁目 | 131-0202 |
| 2 | 12346 | 山田 | 花子 | 12346 | すすきが原 ３丁目 | 251-0003 |
| 3 | 12346 | 山田 | 花子 | 12346 | すすきが原 ３丁目 | 251-0003 |
| 2 | 12346 | 山田 | 花子 | 12346 | ニセコ | 201-0003 |
| 3 | 12346 | 山田 | 花子 | 12346 | ニセコ | 201-0003 |
| 4 | 12347 | 山田 | 嘘子 | (null) | (null) | (null) |
| 5 | 12348 | 山田 | 良子 | (null) | (null) | (null) |
右に配置されるレコードが複数行ありえる場合、レコードが増えてしまう。それを防ぐためには、以下のようにレコードを一意に絞りこめるようにする。
LEFT JOIN child ON parent.`整理番号` = child.`整理番号` AND child.`郵便番号` = '251-0003'
書籍の紹介
とまあここまでは実は応用情報技術者試験の範囲のようだ。
以下の本のSQLのところを見るともっと網羅的な解説が載っている。
ニュースペックテキスト 応用情報技術者 平成29・30年 (情報処理技術者試験)
- 作者: TAC情報処理講座
- 出版社/メーカー: TAC出版
- 発売日: 2017/07/15
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
履歴管理
みんなやってることなので、Qiitaに記事がある
*1:Postgresql先生にはあるようだ ぱせらんメモ - PostgreSQLで配列型のカラムを使ってみる
*2:コッド博士はこれをちゃんと定義して説明したから偉い、想像するだけなら凡人でもできる
AOJ - ALDS1_7_A, B, C (木構造) を解いてみた
勝手に解いてろとか言わないで…
ALDS1_7_A, B, Cは、木構造（根付き木、二分木）です
根付き木 | アルゴリズムとデータ構造 | Aizu Online Judge
二分木 | アルゴリズムとデータ構造 | Aizu Online Judge
木の巡回 | アルゴリズムとデータ構造 | Aizu Online Judge
木の復元 | アルゴリズムとデータ構造 | Aizu Online Judge
木構造を使うときの基本
まあ、木構造単体でプログラミングコンテストの問題が出されることはほとんど無さそうなのですが…*1
- とりあえず、構造体 or クラスで以下のような型を作る
- 木構造の１要素を１つのNodeというクラスで表現します。中には親要素のid、左と右の要素のidを持ちます。
- Rubyの場合はStructを使うと楽にそういうのが作れる、Generics無しにそれを配列型にできるし
Node = Struct.new(:parent, :left, :right) nodes = Array.new(N) { Node.new }
ALDS1_7_A - 根付き木
１つの節（ノード）に複数の子がくっつく場合の構造。
用意した関数を紹介しておく
get_depth ( id, nodes )
その接点の深さを求める
get_children ( id, nodes )
その接点の直下の子ノードを求める
ALDS1_7_B - 二分木
１つの節（ノード）に２つの子しかつかない場合の構造。
用意した関数を紹介しておく
get_depth ( id, nodes )
その接点の深さを求める
get_height ( id, nodes )
その接点の高さを求める、深さと逆のため再帰的にノードを求める必要がある(Finding height in Binary Search Tree - Stack Overflow)
get_sibling ( id, nodes )
その接点と同じ階層にあるノードを求める、二分木の場合隣接するものはあるかないか２択になる
ALDS1_7_C - 木の巡回
木の巡回には３種類あるという話。
- 根節点、左部分木、右部分木の順で節点の番号を出力する。これを木の先行順巡回 (preorder tree walk) と呼びます。
- 左部分木、根節点、右部分木の順で節点の番号を出力する。これを木の中間順巡回 (inorder tree walk) と呼びます。
- 左部分木、右部分木、根節点の順で節点の番号を出力する。これを木の後行順巡回 (postorder tree walk) と呼びます。
用意した関数を紹介しておく
pre_parse ( root, nodes )
根節点、左部分木、右部分木の順で節点の番号を出力する
in_parse ( root, nodes )
左部分木、根節点、右部分木の順で節点の番号を出力する
post_parse ( root, nodes )
左部分木、右部分木、根節点の順で節点の番号を出力する
MyBatisエラー別対応とか
Wikiの方に書いた記事によくアクセスがあるようなので、はてなに移しておく。
MyBatisのエラー別対応
エラーメッセージ："Mapped Statements collection does not contain value for クラス名"
原因
- ディレクトリ構成が変
- Mapperファイルに対応するソースファイルを用意していない、またはその逆
ここのサイトに詳しいトラブルシュートが書いてある
引用
たぶん設定で変えられると思うのですが、Mavenではリソースの配置場所が最初からあらかじめ決められています。javaのソースファイルはsrc/main/java配下に、それ以外のリソースはsrc/main/resources配下に。他にもいろいろ決まっているので、気になる方はここ(Introduction to the Standard Directory Layout)を参照してください。つまり、classファイル出力先でjavaとxmlを一緒にしたい場合、src/main/java配下にHogeMapper.javaを作成したら、src/main/resources配下にHogeMapper.xmlを置く必要があったわけでした。
- 上記の場合は、Mavenで決められてるソースファイルやリソースファイルのディレクトリ仕様に従っていないから出るエラーでした。
- あとはおそらく、Mapperファイルに対応するソースファイルを用意していない、またはその逆をやらかした時に出るエラー
このディレクトリ仕様に関する話はどうやらMyBatisのドキュメントの方には書いてないようです。まあMyBatisのデベロッパーたちもまさかそんな間違いをされるとは思ってなかったのかも（そうしないでも動くことは動く）
エラーメッセージ："Mapped Statements collection already contains value for クラス名"
原因
- Mapperファイルの二重読み込み
上とは逆の話
MyBatisは実行時にクラスパス上にあるすべてのXMLファイル(Mapper)を見ようとする。eclipseなどのIDEを使っている場合、生成された余計なクラスファイルを見に行った結果、２重にXMLを読み込んでいることがある。
XMLファイルの指定は様々だが、MyBatis+Springならば例えばこう
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation" value="/WEB-INF/config/mybatis-config.xml" /> <property name="mapperLocations" value="/WEB-INF/classes/your/package/name/*.xml" /> </bean>
Mapper用のXMLファイルがMavenのsrc/main/resources以下に配置されていたものとする。その場合、Mavenビルドのシーケンス上 compileフェイズで target/classes 以下にクラスファイルとXMLファイルが吐かれる。同様にして、{生成されたアプリ名}/WEB-INF/classes/your/package/name/*.xml上にもマッパーファイルが吐きだされる。eclipseのJettyプラグインなどでこれを実行すると、本来は後者のXMLファイルのみを読み込むことを想定しているのだが、前者のXMLも後から読み込まれるのでこのエラーが出る。どちらかというとeclipseが余計なおせっかいをしている印象だ。とりあえずclassesディレクトリを削除してアプリを動かすと問題なく動くようになる。
