郵便番号テーブル ken_all を用いた演習

テーブルの分解

テーブルの分解の意味について補足します．例えば，テーブル R(A,B,C) があったとき，

select A, B from R; の結果をテーブル S とする
select A, C from R; の結果をテーブル T とする

S と T をあわせると、元のテーブル R システムを全て含んでいることになります．こうした場合，「S と T を R の分解だ」と言ったりします．この「分解」は SQL のプログラムで簡単に書けます．

やみくもに分解すれば良い、というわけではありません．上記の例で，S と T を結合 (join) すると R に戻ること（つまり無損失分解）が必ず保証されるといった性質が重要です．例えば，属性 A が，テーブル R のキーならば，この S と T を結合すると R に戻ることが保証されます．

異なり数

異なり数は，

select count(属性名) FROM テーブル名;

という形での SQL で数えます．

SQL 演習

「郵便番号CSVデータ作成プログラム」による自動編集前は「町域名漢字」に「一円」を含んでいたもの

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select count(*)
  FROM ken_all
  WHERE org_choiki_kanji LIKE '%一円%';
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

「郵便番号CSVデータ作成プログラム」による自動編集前は「町域名漢字」に「以下に掲載がない場合」を含んでいたもの

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select count(*)
  FROM ken_all
  WHERE org_choiki_kanji LIKE '%以下に掲載がない場合%';
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

「全国地方公共団体コード」が「30207」であるもの
これは，要するに，「全国地方公共団体コード」30207 の中で，郵便番号が 647000 になるのは，「以下に掲載がない場合」ということの確認．
```
#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  SELECT *
  FROM zips
  WHERE jiscode = 30207;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
```

「以下に掲載がない場合」は，同一の「全国地方公共団体コード」に対しては１回しか現れないことの確認

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select count(*)
  FROM zips
  WHERE org_choiki_kanji LIKE '%以下に掲載がない場合%'
  group by jiscode
  ORDER BY COUNT(*) ASC;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

「郵便番号CSVデータ作成プログラム」による自動編集前は「町域名漢字」に「の次に番地が来る場合」を含んでいたもの

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  SELECT *
  FROM zips
  WHERE org_choiki_kanji LIKE '%の次に番地がくる場合%';
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

「全国地方公共団体コード」が「30401」であるもの
これは，「白浜町」の次に番地がくる場合は郵便番号6492211で，番地がこない場合は6492200だなあという確認（但し、下の結果で分かるように、町域が掲載されている場合には、対応する郵便番号になる）．
```
#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  SELECT *
  FROM zips
  WHERE jiscode = 30401;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
```

郵便番号CSVデータ作成プログラム」による「町域名漢字」の変更に関する集計

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select count(*), comment_kanji
  FROM zips
  group by comment_kanji
  ORDER BY COUNT(*) DESC;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

「町域名漢字」が「""」

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  SELECT org_choiki_kanji, COUNT(*)
  FROM zips
  WHERE choiki_kanji = '""'
  group by org_choiki_kanji
  ORDER BY COUNT(*) DESC, org_choiki_kanji ASC;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

「以下に掲載がない場合」についての確認

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  SELECT * FROM zips WHERE choiki_kanji = '""' AND NOT org_choiki_kanji = '以下に掲載がない場合';
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

候補キーに関する分析

テーブル ken_all では, jiscode, choiki_kanji のペアは、候補キーになりません．下記を実行したら分かります．
* jiscode, choiki_kanji が同一の値であるようなレコード（行）を探す SQL
```
#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select jiscode, choiki_kanji, count(*)
    from ken_all
    group by jiscode, choiki_kanji
    having count(*) > 1;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
```

テーブル ken_all では, zipcode も、候補キーになりません．

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select zipcode, count(*)
    from ken_all
    group by zipcode
    having count(*) > 1;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

複数属性の関係の分析

属性間の関係を見るために

select count(DISTINCT B), A, MAX(B), MIN(B)
FROM T
group by A
ORDER BY COUNT(DISTINCT B) DESC, A ASC;

というパターンの SQL を多用します．属性 A で集約 (group by) していますので，「同じ A の値を持つものについて，『COUNT(DISTINCT B), A, MAX(B), MIN(B)』を求める．つまり，同じ A の値を持つような B の異なり数がどれだけあるか，また，B の最大値，最小値はいくらか」を集約することになります．また，「ORDER BY COUNT(DISTINCT A) DESC」が入っていますので，同じ値を持つような B の異なり数が最大のものが一番上に表示されます．

「一つの郵便番号で二以上の町域を表す場合の表示」の値が false になっている行について，同じ「郵便番号」の値を持つ「全国地方公共団体コード」がどれだけあるか
「一つの郵便番号で二以上の町域を表す場合の表示」が false であるので，この問い合わせの結果は「１」になっていないといけないのですが，念のため確認します．
```
#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select count(DISTINCT jiscode), zipcode, MAX(jiscode), MIN(jiscode)
  FROM ken_all
  WHERE flag13 = 0
  group by zipcode
  ORDER BY COUNT(DISTINCT jiscode) ASC, zipcode ASC;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
```
「一つの郵便番号で二以上の町域を表す場合の表示」の値が false になっている行について，同じ「全国地方公共団体コード」の値を持った異なった「郵便番号」がどれだけあるか
これは，「１」以上の値が出てきても当然なのですが，念のため実行してみる．
```
#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
  select count(DISTINCT zipcode), jiscode, MAX(zipcode), MIN(zipcode)
  FROM ken_all 
  WHERE flag13 = 0
  group by jiscode
  ORDER BY COUNT(DISTINCT zipcode) DESC, jiscode ASC;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
```