トップページインストール,使い方日本郵政の「郵便番号データ」を SQLite 3 で使う郵便番号テーブル ken_all を用いた演習

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

テーブルの分解

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

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

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

異なり数

異なり数は,

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

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

SQL 演習

候補キーに関する分析

複数属性の関係の分析

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

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 の異なり数が最大のものが一番上に表示されます.

  1. 「一つの郵便番号で二以上の町域を表す場合の表示」の値が false になっている行について,同じ「郵便番号」の値を持つ「全国地方公共団体コード」がどれだけあるか

    「一つの郵便番号で二以上の町域を表す場合の表示」が false であるので,この問い合わせの結果は「1」になっていないといけないのですが, 念のため確認します.

    #!/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
    

    [image]
  2. 「一つの郵便番号で二以上の町域を表す場合の表示」の値が false になっている行について,同じ「全国地方公共団体コード」の値を持った異なった「郵便番号」がどれだけあるか

    これは,「1」以上の値が出てきても当然なのですが,念のため実行してみる.

    #!/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