トップページ -> データの扱い -> 種々のオープンデータ -> 郵便番号 CSV データ(非辞書 CSV データ)から郵便番号データテーブル zipall を作成(リレーショナルデータベース管理システム SQLite3 を使用)
[サイトマップへ], [サイト内検索へ]

郵便番号 CSV データ(非辞書 CSV データ)から郵便番号データテーブル zipall を作成(リレーショナルデータベース管理システム SQLite3 を使用)

この Web ページでは,日本郵政公社「ゆうびんホームページ」で公開されている 2つの郵便番号データのCSV(カンマ区切り値)形式ファイルを使う.

  1. 住所の郵便番号(CSV形式)(ken_all.csv)
  2. 事業所の個別郵便番号(CSV形式)(JIGYOSYO.CSV)

これらのファイルを,リレーショナルデータベース管理システム SQLite のテーブル(テーブル名は zipall)に格納します.次の手順を踏みます.

作成するSQLite3 データベース: mydb01

※ この Web ページでは SQLite 3 での手順を説明します.SQL を使うので,他のデータベース管理システムでもほぼ同じ手順で動くはずです. 個別のデータベース管理システムの使用法は,この Web ページでは説明しませんので,下記の Web ページを見てください.


前準備

使用するソフトウエア

あらかじめ決めておく事項

この Web ページでは,データベースの作成を行うので, 作成するデータベースのデータベース論理名を決めておくこと. この Web ページでは,次のように書きます.

郵便番号 CSV データ(非辞書 CSV データ)

郵便番号 CSV データ(非辞書 CSV データ)を作成し,分かりやすいディレクトリにおいておく.

[image]

テーブルの作成

  1. テーブル定義

    bash プログラム

    #!/bin/bash
    
    cat >/tmp/a.$$.sql <<-SQL
    drop table KENALL; 
    drop table JIGYOSYO; 
    SQL
    cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
    #
    cat >/tmp/a.$$.sql <<-SQL
    create table KENALL (
      a0 integer, 
      a1 text, 
      a2 text, 
      a3 text, 
      a4 text, 
      a5 text, 
      a6 text, 
      a7 text, 
      a8 text, 
      a9 integer, 
      a10 integer, 
      a11 integer, 
      a12 integer, 
      a13 integer, 
      a14 integer, 
      a15 integer, 
      a16 integer, 
      a17 integer, 
      a18 integer, 
      a19 integer, 
      a20 integer, 
      a21 text, 
      a22 text, 
      a23 text, 
      a24 text, 
      a25 text, 
      a26 text 
    ); 
    create table JIGYOSYO (
      a0 integer, 
      a1 text, 
      a2 text, 
      a3 text, 
      a4 text, 
      a5 text, 
      a6 text, 
      a7 text, 
      a8 text, 
      a9 integer, 
      a10 integer, 
      a11 integer, 
      a12 integer, 
      a13 integer, 
      a14 integer, 
      a15 text, 
      a16 integer, 
      a17 integer, 
      a18 integer, 
      a19 integer, 
      a20 integer, 
      a21 text, 
      a22 text, 
      a23 text, 
      a24 text,  
      a25 text, 
      a26 text 
    );  
    SQL
    cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
    

    [image]
  2. テーブルの作成
    • KENALL: /tmp/KEN_ALLDATA.csv からインポート
    • JIGYOSYO: /tmp/JIGYOSYO.csv からインポート

    このとき、nkf を用いて、 ファイルの文字コードを UTF8 に変換

    bash プログラム

    #!/bin/bash
    
    cat >/tmp/a.$$.sql <<-SQL
    .mode csv
    .import /tmp/1.$$.csv KENALL
    .import /tmp/2.$$.csv JIGYOSYO
    vacuum; 
    select * from KENALL limit 3; 
    select * from JIGYOSYO limit 3; 
    SQL
    nkf -w /tmp/KEN_ALLDATA.csv > /tmp/1.$$.csv
    nkf -w /tmp/JIGYOSYO.csv > /tmp/2.$$.csv
    cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
    

    [image]
  3. SQLiteiman を用いた確認表示

    Sqliteman のようなツールを使って,テーブルの中身を確認すると,次のようになります.要するに漢字や半角カナも大丈夫のようです(全ての行は確認してませんが)

    KENALL テーブル

    [image]

    JIGYOSYO テーブル

    [image]
  4. テーブルの調整

    この操作が必要な理由は下に書いています

    日本語が使えるツール(SQLite 3 の場合には SQLitemanpupSQLiteDatabase Browser など)で,次の SQL を実行します.

    bash プログラム

    #!/bin/bash
    cat >/tmp/a.$$.sql <<-SQL
    BEGIN TRANSACTION;
    UPDATE KENALL SET a7='札幌市南区'       WHERE a0=1106;
    UPDATE KENALL SET a7='鶴ヶ島市'         WHERE a0=11241;
    UPDATE KENALL SET a7='茅ヶ崎市'         WHERE a0=14207;
    UPDATE KENALL SET a7='羽咋郡宝逹志水町' WHERE a0=17386;
    UPDATE KENALL SET a7='名古屋市東区'     WHERE a0=23102;
    UPDATE KENALL SET a7='名古屋市中区'     WHERE a0=23106;
    UPDATE KENALL SET a7='名古屋市名東区'   WHERE a0=23115;
    UPDATE KENALL SET a7='蒲生郡竜王町'     WHERE a0=25384;
    UPDATE KENALL SET a7='京都市上京区'     WHERE a0=26102;
    UPDATE KENALL SET a7='福岡市博多区'     WHERE a0=40132;
    UPDATE KENALL SET a7='福岡市中央区'     WHERE a0=40133;
    COMMIT;
    
    BEGIN TRANSACTION;
    UPDATE JIGYOSYO SET a7='札幌市南区'       WHERE a0=1106;
    UPDATE JIGYOSYO SET a7='鶴ヶ島市'         WHERE a0=11241;
    UPDATE JIGYOSYO SET a7='茅ヶ崎市'         WHERE a0=14207;
    UPDATE JIGYOSYO SET a7='羽咋郡宝逹志水町' WHERE a0=17386;
    UPDATE JIGYOSYO SET a7='名古屋市東区'     WHERE a0=23102;
    UPDATE JIGYOSYO SET a7='名古屋市中区'     WHERE a0=23106;
    UPDATE JIGYOSYO SET a7='名古屋市名東区'   WHERE a0=23115;
    UPDATE JIGYOSYO SET a7='蒲生郡竜王町'     WHERE a0=25384;
    UPDATE JIGYOSYO SET a7='京都市上京区'     WHERE a0=26102;
    UPDATE JIGYOSYO SET a7='福岡市博多区'     WHERE a0=40132;
    UPDATE JIGYOSYO SET a7='福岡市中央区'     WHERE a0=40133;
    COMMIT;
    SQL
    #
    cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
    

    [image]
  5. テーブル JIGYOSYO の「都道府県名カタカタ」,「市区町村名カタカナ」の空文字列に関する作業

    作業手順

    1. 属性「都道府県名カタカナ」について,空文字列の部分に転記

      下記の SQL を実行.カットアンドペーストしてください.

      ※ a3 ・・・ 都道府県名カタカナ, a6 ・・・ 都道府県名漢字, a9 ・・・ 都道府県名カタカナ文字数

      bash プログラム

      #!/bin/bash
      cat >/tmp/a.$$.sql <<-SQL
      drop table alldata; 
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      
      cat >/tmp/a.$$.sql <<-SQL
      drop table WORKINGKENLIST; 
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      
      cat >/tmp/a.$$.sql <<-SQL
      
      CREATE TABLE alldata AS
      SELECT * FROM KENALL
      UNION ALL
      SELECT * FROM JIGYOSYO;
      
      CREATE TABLE WORKINGKENLIST AS
          SELECT DISTINCT a6, a3, a9
          FROM alldata
          WHERE a6
              IN ( SELECT a6
                   FROM alldata
                   GROUP BY a6
                   HAVING COUNT(DISTINCT(a3)) = 1 )        
          UNION
          SELECT DISTINCT a6, a3, a9
          FROM alldata
          WHERE a3 <> '""'
            AND a6
              IN ( SELECT a6
                   FROM alldata
                   GROUP BY a6
                   HAVING COUNT(DISTINCT(a3)) = 2 );
      UPDATE JIGYOSYO SET
          a9 = (SELECT WORKINGKENLIST.a9 FROM WORKINGKENLIST WHERE JIGYOSYO.a6 = WORKINGKENLIST.a6)
      WHERE a3 = '""';
      UPDATE JIGYOSYO SET
          a3 = (SELECT WORKINGKENLIST.a3 FROM WORKINGKENLIST WHERE JIGYOSYO.a6 = WORKINGKENLIST.a6)
      WHERE a3 = '""';
      UPDATE KENALL SET
          a9 = (SELECT WORKINGKENLIST.a9 FROM WORKINGKENLIST WHERE KENALL.a6 = WORKINGKENLIST.a6)
      WHERE a3 = '""';
      UPDATE KENALL SET
          a3 = (SELECT WORKINGKENLIST.a3 FROM WORKINGKENLIST WHERE KENALL.a6 = WORKINGKENLIST.a6)
      WHERE a3 = '""';
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]
    2. 念のため,「都道府県名カタカナ」が「""」になっているレコードが存在しないことを確認しておく.

      bash プログラム

      #!/bin/bash
      cat >/tmp/a.$$.sql <<-SQL
      
      SELECT *
      FROM JIGYOSYO
      WHERE a3 = '""';
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]

      bash プログラム

      #!/bin/bash
      cat >/tmp/a.$$.sql <<-SQL
      
      SELECT *
      FROM KENALL
      WHERE a3 = '""';
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]
    3. 属性「市区町村名カタカナ」について,空文字列の部分に転記

      下記の SQL を実行.

      ※ a4 ・・・ 市区町村名カタカナ, a7 ・・・ 市区町村名漢字, a10 ・・・ 市区町村名カタカナ文字数

      bash プログラム

      #!/bin/bash
      cat >/tmp/a.$$.sql <<-SQL
      drop table WORKINGSHICHOSONLIST; 
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      
      cat >/tmp/a.$$.sql <<-SQL
      
      CREATE TABLE WORKINGSHICHOSONLIST AS
          SELECT DISTINCT a7, a4, a10
          FROM alldata
          WHERE a7
              IN ( SELECT a7
                   FROM alldata
                   GROUP BY a7
                   HAVING COUNT(DISTINCT(a4)) = 1 )        
          UNION
          SELECT DISTINCT a7, a4, a10
          FROM alldata
          WHERE a4 <> '""'
            AND a7
              IN ( SELECT a7
                   FROM alldata
                   GROUP BY a7
                   HAVING COUNT(DISTINCT(a4)) = 2 );
      UPDATE JIGYOSYO SET
          a10 = (SELECT WORKINGSHICHOSONLIST.a10 FROM WORKINGSHICHOSONLIST WHERE JIGYOSYO.a7 = WORKINGSHICHOSONLIST.a7)
      WHERE a4 = '""';
      UPDATE JIGYOSYO SET
          a4 = (SELECT WORKINGSHICHOSONLIST.a4 FROM WORKINGSHICHOSONLIST WHERE JIGYOSYO.a7 = WORKINGSHICHOSONLIST.a7)
      WHERE a4 = '""';
      UPDATE KENALL SET
          a10 = (SELECT WORKINGSHICHOSONLIST.a10 FROM WORKINGSHICHOSONLIST WHERE KENALL.a7 = WORKINGSHICHOSONLIST.a7)
      WHERE a4 = '""';
      UPDATE KENALL SET
          a4 = (SELECT WORKINGSHICHOSONLIST.a4 FROM WORKINGSHICHOSONLIST WHERE KENALL.a7 = WORKINGSHICHOSONLIST.a7)
      WHERE a4 = '""';
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      ※「市区町村名カタカナ」が「""」になっているレコードは残る.勉強用に使うだけのつもりなので、ここであきらめる.

      bash プログラム

      #!/bin/bash
      cat >/tmp/a.$$.sql <<-SQL
      
      SELECT *
      FROM JIGYOSYO
      WHERE a4 = '""';
      
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]

      bash プログラム

      #!/bin/bash
      cat >/tmp/a.$$.sql <<-SQL
      
      SELECT *
      FROM KENALL
      WHERE a4 = '""';
      
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]

    ※ (注意)なお,町域については、「小谷」を「コヤト」,「オオタニ」などいろんな読み方をすることがある.都道府県や市区町村と同じというわけにはいかない.

  6. 郵便番号データテーブル zipall

    郵便番号データテーブルを定義します.テーブル名は zipall

    【テーブル定義の方針】

    1. 半角数字は INTEGER
    2. 半角カタカナと漢字は TEXT

    【作業手順】

    1. テーブル定義

      bash プログラム

      #!/bin/bash
      
      cat >/tmp/a.$$.sql <<-SQL
      drop table zipall; 
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      
      cat >/tmp/a.$$.sql <<-SQL
      
      CREATE TABLE zipall (
          jiscode             INTEGER NOT NULL CHECK ( jiscode >= 1000 AND jiscode <= 50000 ),
          zip_old             TEXT, 
          zipcode             TEXT, 
          ken_kana            TEXT NOT NULL,
          shichoson_kana      TEXT NOT NULL,
          choiki_kana         TEXT NOT NULL,
          ken_kanji           TEXT NOT NULL,
          shichoson_kanji     TEXT NOT NULL,
          choiki_kanji        TEXT,
      
          num_ken_kana        INTEGER NOT NULL,
          num_shichoson_kana  INTEGER NOT NULL,
          num_choiki_kana     INTEGER NOT NULL,
          num_ken_kanji       INTEGER NOT NULL,
          num_shichoson_kanji INTEGER NOT NULL,
          num_choiki_kanji    INTEGER NOT NULL,
      
          flag10              INTEGER NOT NULL,
          flag11              INTEGER NOT NULL CHECK ( flag11 >= 0 AND flag11 <= 1 ),
          flag12              INTEGER NOT NULL CHECK ( flag12 >= 0 AND flag12 <= 3 ),
          flag13              INTEGER NOT NULL CHECK ( flag13 >= 0 AND flag13 <= 1 ),
          info14              INTEGER, 
          info15              INTEGER, 
      
          org_choiki_kana     TEXT,
          org_choiki_kanji    TEXT,
          comment_kanji       TEXT,
          comment_kana        TEXT,
          kaisyamei_kana      TEXT,
          kaisyamei_kanji     TEXT );
      
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]
    2. 郵便番号データテーブル zipall の作成

      #!/bin/bash
      
      cat >/tmp/a.$$.sql <<-SQL
      drop table hoge2; 
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      
      cat >/tmp/a.$$.sql <<-SQL
      
      CREATE TABLE hoge2 AS
      SELECT * FROM KENALL
      UNION ALL
      SELECT * FROM JIGYOSYO;
      
      INSERT INTO zipall SELECT * FROM hoge2;
      
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]
    3. 郵便番号データテーブル zipall の中身の確認

      #!/bin/bash
      
      cat >/tmp/a.$$.sql <<-SQL
      
      select * from zipall limit 3;
      
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      [image]
    4. (オプション) ダンプ操作

      #!/bin/bash
      
      cat >/tmp/a.$$.sql <<-SQL
      
      .output /tmp/zipall.sql
      .dump zipall
      .exit
      SQL
      #
      cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
      

      (参考) 郵便番号データテーブル zipall の各列の説明

      jiscode    全国地方公共団体コード(JIS X0401、X0402)……… 半角数字
      zip_old    (旧)郵便番号(5桁)……………………………………… 半角数字
      zipcode    郵便番号(7桁)……………………………………… 半角数字
      ken_kana    都道府県名カタカナ ………… 半角カタカナ(コード順に掲載) (注1)
      shichoson_kana    市区町村名カタカナ ………… 半角カタカナ(コード順に掲載) (注1)
      choiki_kana    町域名カタカナ ……………… 半角カタカナ(五十音順に掲載) (注1)
                     ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
      ken_kanji    都道府県名漢字 ………… 漢字(コード順に掲載) (注1,2)
      shichoson_kanji    市区町村名漢字 ………… 漢字(コード順に掲載) (注1,2)
      choiki_kanji    町域名漢字 ……………… 漢字(五十音順に掲載) (注1,2)
                     ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
      num_ken_kana    都道府県名カタカナ文字数
                     ※「郵便番号CSVデータ作成プログラム」により追加された属性
      num_shichoson_kana    市区町村名カタカナ文字数
                     ※「郵便番号CSVデータ作成プログラム」により追加された属性
      num_choiki_kana    町域名カタカナ文字数
                     ※「郵便番号CSVデータ作成プログラム」により追加された属性
      num_ken_kanji    都道府県名漢字文字数
                     ※「郵便番号CSVデータ作成プログラム」により追加された属性
      num_shichoson_kanji    市区町村名漢字文字数
                     ※「郵便番号CSVデータ作成プログラム」により追加された属性
      num_choiki_kanji    町域名漢字文字数
                     ※「郵便番号CSVデータ作成プログラム」により追加された属性
      flag10    一町域が二以上の郵便番号で表される場合の表示 (「1」は該当、「0」は該当せず)
                     ※ 事業所の個別郵便番号では「北海道」のような文字列が入る
      flag11    小字毎に番地が起番されている町域の表示  (「1」は該当、「0」は該当せず)
      flag12    丁目を有する町域の場合の表示 (「1」は該当、「0」は該当せず)
      flag13    一つの郵便番号で二以上の町域を表す場合の表示 (「1」は該当、「0」は該当せず)
      info14    更新の表示 (「0」は変更なし、「1」は変更あり、「2」廃止(廃止データのみ使用))
                     ※ 事業所の個別郵便番号では空値(NULL)になることがある.
      info15    変更理由 (「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、「3」区画整理、「4」郵便区調整、集配局新設、「5」訂正、「6」廃止(廃止データのみ使用))  
                     ※ 事業所の個別郵便番号では空値(NULL)になることがある.
      org_choiki_kana      org町域名カタカナ
                     ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
      org_choiki_kanji      org町域名漢字
                     ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
      comment_kanji      コメント漢字
      comment_kana      コメントカタカナ
      kaisyamei_kana      会社名カタカナ
      kaisyamei_kanji      会社名漢字
      

      (参考)「郵便番号CSVデータ作成プログラム」を使わない場合

      日本郵政公社「ゆうびんホームページ」で公開されている郵便番号データについて, もし

      という場合には,下記のテーブル定義を使ってください.そのこと以外は,ほぼ、この Web ページの手順が使えます.

      CREATE TABLE zips (
          jiscode INTEGER NOT NULL,
          zip_old INTEGER NOT NULL,
          zipcode INTEGER NOT NULL,
          ken_kana TEXT NOT NULL,
          shichoson_kana TEXT NOT NULL,
          choiki_kana TEXT NOT NULL,
          ken_kanji TEXT NOT NULL,
          shichoson_kanji TEXT NOT NULL,
          choiki_kanji TEXT NOT NULL,
      
          flag10 TEXT NOT NULL,
          flag11 INTEGER NOT NULL,
          flag12 INTEGER NOT NULL,
          flag13 INTEGER NOT NULL,
          info14 INTEGER,
          info15 INTEGER );
      

    テーブル JIGYOSYO の調整が必要な理由

    a0 と a7 の関数従属性

    出来上がった JIGYOSYO テーブルについて,次の SQL を実行してみる.

    SELECT DISTINCT a0, a6, a7 FROM JIGYOSYO;
    

    [image]

    同じ a0 の値 (a0 = 1106) に対して,2つの違う a7 の値 「南区」と「札幌市南区」が現れている.これを問題視します.今まで行ってきた手順の間違いということではなさそうです.この種の問題がある行は,次の SQL で洗い出すことができる.

    CREATE TABLE T AS SELECT DISTINCT a0, a6, a7 FROM JIGYOSYO;
    SELECT * FROM T WHERE a0 IN ( SELECT a0 FROM T GROUP BY a0 HAVING COUNT(*) > 1 );
    

    [image]

    以上で JIGYOSYO テーブルは終わり.

    KENALL テーブルについても同様.

    CREATE TABLE U AS SELECT DISTINCT a0, a6, a7 FROM KENALL;
    SELECT * FROM U WHERE a0 IN ( SELECT a0 FROM U GROUP BY a0 HAVING COUNT(*) > 1 );
    

    [image]

    jiscode の一意性について

    「KENALL テーブルと JIGYOSO テーブルの UNION ALL をとって,新しいテーブルを作ると, また,同じ a0 の値に対して,2つの違う a7 の値が出ている」 という問題がないことを確認します

    CREATE TABLE hoge AS
    SELECT * FROM KENALL
    UNION ALL
    SELECT * FROM JIGYOSYO;
    
    CREATE TABLE V AS SELECT DISTINCT a0, a6, a7 FROM hoge;
    SELECT * FROM V WHERE a0 IN ( SELECT a0 FROM V GROUP BY a0 HAVING COUNT(*) > 1 );
    

    [image]

    ※もし問題が見つかった場合には、次のようなSQLで解決(このまま使ってはいけません。あくまでも見本)

    BEGIN TRANSACTION;
    UPDATE JIGYOSYO SET a7='鳩ヶ谷市' WHERE a0=11226;
    UPDATE JIGYOSYO SET a7='羽咋郡宝逹志水町' WHERE a0=17386;
    UPDATE KENALL   SET a7='鳩ヶ谷市' WHERE a0=11226;
    UPDATE KENALL   SET a7='羽咋郡宝逹志水町' WHERE a0=17386;
    COMMIT;
    

    テーブル JIGYOSYO の「都道府県名カタカタ」,「市区町村名カタカナ」の空文字列

    出来上がったテーブル JIGYOSYO に加工を行う必要があります

    説明

    下の図から分かる通り,生成されたテーブル JIGYOSYO では, 「都道府県名カタカタ」,「市区町村名カタカナ」が「""」になっているレコードがあります.

    SELECT * FROM JIGYOSYO WHERE a3 = '""';
    

    [image]

    JIGYOSYO テーブルで, 「都道府県名カタカタ」,「市区町村名カタカナ」が「""」になっているレコードは,他のレコードを使って転記を行うことができる.

    下記の例で分かる通り,「都道府県名漢字」,「市区町村名漢字」の値が同じ値になっているような別のレコードに, 「都道府県名カタカタ」,「市区町村名カタカナ」が書いてあります.

    ※ a3 ・・・ 都道府県名カタカナ, a4 ・・・ 市区町村名カタカナ, a6 ・・・ 都道府県名漢字, a7 ・・・ 市区町村名漢字,

    CREATE TABLE S AS
    SELECT DISTINCT a7, a4 FROM KENALL
    UNION ALL
    SELECT DISTINCT a7, a4 FROM JIGYOSYO;
    SELECT DISTINCT a7, a4
    FROM S
    ORDER BY a7;
    

    [image]

    これを転記するというのが方針です. ここで言っている「転記」を,SQL でどう行うか,以下,説明します. ここでは,説明のため,郵便番号データテーブルでなく,仮の簡略化したテーブルを使って説明します. 例えば,あるテーブルがあって,「漢字」,「カタカナ」,「文字数」が次のようになっているとしましょう. 説明用に,テーブル名,列名を日本語にしています.

    CREATE TABLE 元の表 (
        漢字    TEXT,
        カタカナ TEXT,
        文字数 INTEGER
    );
    

    [image]

    BEGIN TRANSACTION;
    INSERT INTO 元の表 VALUES('北', 'キタ', 2);
    INSERT INTO 元の表 VALUES('北', 'キタ', 2);
    INSERT INTO 元の表 VALUES('北', "", 0);
    INSERT INTO 元の表 VALUES('北', "", 0);
    INSERT INTO 元の表 VALUES('北', "", 0);
    INSERT INTO 元の表 VALUES('青', "", 0);
    INSERT INTO 元の表 VALUES('青', "", 0);
    INSERT INTO 元の表 VALUES('福', 'フク', 2);
    INSERT INTO 元の表 VALUES('福', 'フク', 2);
    INSERT INTO 元の表 VALUES('福', "", 0);
    INSERT INTO 元の表 VALUES('福', "", 0);
    COMMIT;
    

    [image]

    [image]
    説明用のテーブル (テーブル名は「元の表」としています)

    上記のテーブルを,下記のように変更したいという問題です.「漢字」が「北」,「福」になっていて,かつ, 「カタカナ」が "" になっているレコードには,「カタカナ」,「文字数」を転記したい! ということです. 一方で,「漢字」が「青」のレコードは,転記のしようがないので,手を付けずに残します.

    漢字  カタカナ  文字数
    ======================
    北    キタ      2
    北    キタ      2
    北    キタ      2キタ      2キタ      2
    青          0
    青          0
    福    フク      2
    福    フク      2
    福    フク      2フク      2
    ----------------------
    
    転記後のテーブルの例

    さて,元のテーブルから,変更後のテーブルを得る SQL を段階的に考えましょう. 「SELECT DISTINCT 漢字, カタカナ, 文字数 FROM 元の表;」を実行して,同一値を持つレコードの重複を取り除くと,次のようになります.

    SELECT DISTINCT 漢字, カタカナ, 文字数
    FROM 元の表;
    

    [image]
    図.「SELECT DISTINCT 漢字, カタカナ, 文字数 FROM 元の表;」の実行結果

    「青」のように,レコードが1つしかないもの「福」,「北」のように,レコードが2個あるものが見て取れます. では,「青」のように,レコードが1つしかないものを選ぶ SQL はどう書けるかというと, これは,副問い合わせを使うのが簡単です.

    1. まずは,「漢字」で集約して,個々の「漢字」ごとに,「カタカナ」の異なり数がいくつあるかを調べ,異なり数が1のレコードだけを選びます. これは,「『カタカナ』が全部 "" になっている」.言い換えると「 ある『漢字』に対して,『カタカナ』として,""そうでないレコードが混ざっている場合には,異なり数は2以上になる」という意味です. そのために, 「HAVING COUNT(DISTINCT(カタカナ)) = 1;」という条件を SQL 内に含めることになります.

      ※ なお,属性「文字数」は,「カタカナ」に依存する,つまり,関数従属なので, HAVING のところに「文字数」を指定する必要はありません.

    2. 副問い合わせの結果,{「青」} のような,漢字の集合が得られますので,それを使って,元の表からレコードを選び出す.

    SQL とその実行結果は,次のようになります.

    SELECT DISTINCT 漢字, カタカナ, 文字数
    FROM 元の表
    WHERE 漢字 IN
        ( SELECT 漢字
          FROM 元の表
          GROUP BY 漢字
          HAVING COUNT(DISTINCT(カタカナ)) = 1 );
    

    [image]

    同様に, 「福」,「北」のように,カタカナの値が "" であるレコード,ある1つの値を持つものという2種類だけあるものを選ぶ(3種類以上は除外します.) SQL も書けます.SQL とその実行結果は,次のようになります. 先ほどの SQL の「= 1」を「= 2」に変えただけです.

    SELECT DISTINCT 漢字, カタカナ, 文字数
    FROM 元の表
    WHERE 漢字 IN
        ( SELECT 漢字
          FROM 元の表
          GROUP BY 漢字
          HAVING COUNT(DISTINCT(カタカナ)) = 2 );
    

    [image]

    この SQL に少し手を加えて,「カタカタ」が空文字列でないものだけを出力するようにします.(WHERE に,「カタカナ IS NOT NULL」を追加しています).

    SELECT DISTINCT 漢字, カタカナ, 文字数
    FROM 元の表
    WHERE カタカナ <> ''
        AND 漢字 IN
        ( SELECT 漢字
          FROM 元の表
          GROUP BY 漢字
          HAVING COUNT(DISTINCT(カタカナ)) = 2 );
    

    [image]

    以上をまとめて,次のような UNION (和)を考えます.

    SELECT DISTINCT 漢字, カタカナ, 文字数
    FROM 元の表
    WHERE 漢字 IN
        ( SELECT 漢字
          FROM 元の表
          GROUP BY 漢字
          HAVING COUNT(DISTINCT(カタカナ)) = 1 )
    
    UNION
    
    SELECT DISTINCT 漢字, カタカナ, 文字数
    FROM 元の表
    WHERE カタカナ <> '' 
        AND 漢字 IN
        ( SELECT 漢字
          FROM 元の表
          GROUP BY 漢字
          HAVING COUNT(DISTINCT(カタカナ)) = 2 );
    

    [image]

    出力される表は,次のことをあらわしています.

    1. 「青」のレコードには,もともと「カタカナ」の情報が無いので,転記しない.
    2. 「福」のレコードには,「フク」,「2」を転記する
    3. 「北」のレコードには,「アオ」,「2」を転記する

    こうして出来た表を,元の表における転記用に使いたいので, 「CREATE TABLE ... AS ...」を使って,「作業用」という名前のテーブルに保存します.そして,「作業用」と「元の表」の2つのテーブルを使い,「元の表」における転記を行う.

    SQL とその実行結果は,次のようになります.期待通りの結果です.

    CREATE TABLE 作業用リスト AS
        SELECT DISTINCT 漢字, カタカナ, 文字数
        FROM 元の表
        WHERE 漢字 IN
            ( SELECT 漢字
              FROM 元の表
              GROUP BY 漢字
              HAVING COUNT(DISTINCT(カタカナ)) = 1 )
    
        UNION
    
        SELECT DISTINCT 漢字, カタカナ, 文字数
        FROM 元の表
        WHERE カタカナ <> '' 
            AND 漢字 IN
            ( SELECT 漢字
              FROM 元の表
              GROUP BY 漢字
              HAVING COUNT(DISTINCT(カタカナ)) = 2 );
    
    UPDATE 元の表 SET
      文字数 = (SELECT 作業用リスト.文字数 FROM 作業用リスト WHERE 元の表.漢字 = 作業用リスト.漢字 )
    WHERE カタカナ = '';
    
    UPDATE 元の表 SET
      カタカナ = (SELECT 作業用リスト.カタカナ FROM 作業用リスト WHERE 元の表.漢字 = 作業用リスト.漢字 )
    WHERE カタカナ = '';
    

    [image]

本サイトは金子邦彦研究室のWebページです.サイトマップは,サイトマップのページをご覧下さい. 本サイト内の検索は,サイト内検索のページをご利用下さい.

問い合わせ先: 金子邦彦(かねこ くにひこ) [image]