zipall

市区町村名カタカナが空文字列のレコードの確認

テーブル KEN_ALL で，市区町村名カタカナ (a4) が「""」になっているレコードを確認する．

◆ bash プログラム

#!/bin/bash
cat >/tmp/a.$$.sql <<-SQL

SELECT *
FROM KEN_ALL
WHERE a4 = '""';

SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

*　町域名は，「小谷」を「コヤト」，「オオタニ」と読むように，同じ漢字で複数の読みがある．そのため，都道府県名や市区町村名のように，漢字から読みを一意に決めることはできない．

リレーショナルデータベースのテーブル ken_all の作成

郵便番号テーブル ken_all

郵便番号テーブルを定義する．テーブル名は ken_all

【テーブル定義の方針】

半角数字のうち，大小の比較や集計に使う項目は INTEGER
郵便番号のように先頭の 0 が意味を持つ項目は TEXT
半角カタカナと漢字は TEXT

【作業手順】

テーブル定義

◆ bash プログラム

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
drop table ken_all;
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

cat >/tmp/a.$$.sql <<-SQL

create table ken_all (
    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/zipdb

郵便番号テーブル ken_all の作成

KEN_ALL テーブルと JIGYOSYO テーブルを UNION ALL で連結し，ken_all テーブルに格納する．

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
drop table hoge2;
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

cat >/tmp/a.$$.sql <<-SQL

create table hoge2 AS
SELECT * FROM KEN_ALL
UNION ALL
SELECT * FROM JIGYOSYO;

insert into ken_all SELECT * FROM hoge2;

SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

郵便番号テーブル ken_all の中身の確認

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL

select * from ken_all limit 3;

SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

(オプション) ダンプ操作

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL

.output /tmp/ken_all.sql
.dump ken_all
.exit
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

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

jiscode    全国地方公共団体コード(JIS X0401、X0402)………　半角数字
zip_old    (旧)郵便番号(5桁)………………………………………　半角数字
zipcode    郵便番号(7桁)………………………………………　半角数字
ken_kana    都道府県名カタカナ　…………　半角カタカナ(コード順に掲載)
shichoson_kana    市区町村名カタカナ　…………　半角カタカナ(コード順に掲載)
choiki_kana    町域名カタカナ　………………　半角カタカナ(五十音順に掲載)
               ※「郵便番号CSVデータ作成プログラム」による自動編集後のもの
ken_kanji    都道府県名漢字　…………　漢字(コード順に掲載)
shichoson_kanji    市区町村名漢字　…………　漢字(コード順に掲載)
choiki_kanji    町域名漢字　………………　漢字(五十音順に掲載)
               ※「郵便番号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      会社名漢字

住所の郵便番号データだけを使う場合のテーブル定義

日本郵便が公開している郵便番号データのうち，住所の郵便番号（ＣＳＶ形式）(ken_all.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 );

* 日本郵便は，2023年6月更新分から住所の郵便番号（1レコード1行、UTF-8形式） (utf_ken_all.csv) を公開している．このデータは，1つの郵便番号が必ず1行で記載され，文字コードは UTF-8，読み仮名は全角カタカナである．従来データのように長い町域名が複数レコードに分割されないため，レコードの連結処理が不要になる．

1. 同一 jiscode に対して市区町村名漢字が複数あるという問題

◆ 次のSQL プログラムを実行し，この問題を解消する

begin transaction;
UPDATE JIGYOSYO SET a7='札幌市南区'       WHERE a0=1106;
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;

次を実行して確認する．

create table T2 AS select distinct a0, a6, a7 FROM JIGYOSYO;
SELECT * FROM T2 WHERE a0 IN ( SELECT a0 FROM T2 group by a0 HAVING COUNT(*) > 1 );

2. jiscode の一意性について

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

create table hoge AS
SELECT * FROM KEN_ALL
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 );

* 問題が見つかった場合には，次の形の SQL で，正しい市区町村名漢字に統一する（見本である．a0 と a7 の値は，確認結果に合わせて書き換える）

begin transaction;
UPDATE JIGYOSYO SET a7='＜正しい市区町村名漢字＞' WHERE a0=＜jiscode＞;
UPDATE KEN_ALL   SET a7='＜正しい市区町村名漢字＞' WHERE a0=＜jiscode＞;
commit;

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

生成されたテーブル JIGYOSYO で，「都道府県名カタカナ」，「市区町村名カタカナ」が「""」になっているレコードを確認する．

下の実行例では，そのようなレコードが存在しないので，問題ない．

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

「都道府県名カタカナ」，「市区町村名カタカナ」が「""」になっているレコードが存在する場合の対処法を下に説明する．出来上がったテーブル JIGYOSYO に加工を行う．

「都道府県名カタカナ」，「市区町村名カタカナ」が「""」になっているレコードは，他のレコードを使って転記できる．下記の例の通り，「都道府県名漢字」，「市区町村名漢字」が同じ値になっている別のレコードに，「都道府県名カタカナ」，「市区町村名カタカナ」が書いてある．

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

create table S AS
select distinct a7, a4 FROM KEN_ALL
UNION ALL
select distinct a7, a4 FROM JIGYOSYO;
select distinct a7, a4
FROM S
ORDER BY a7;

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

create table 元の表 (
    漢字    text,
    カタカナ text,
    文字数 INTEGER
);

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;

上記のテーブルを，下記のように変更したい．つまり，「漢字」が「北」，「福」で，かつ「カタカナ」が空文字列のレコードに，「カタカナ」，「文字数」を転記したい．一方，「漢字」が「青」のレコードは，転記元がないので，手を付けずに残す．

漢字  カタカナ  文字数
======================
北    キタ      2
北    キタ      2
北    キタ      2
北    キタ      2
北    キタ      2
青          0
青          0
福    フク      2
福    フク      2
福    フク      2
福    フク      2
----------------------


転記後のテーブルの例

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

select distinct 漢字, カタカナ, 文字数
FROM 元の表;

「青」のように，レコードが 1 つしかないものと， 「福」，「北」のように，レコードが 2 個あるものがある． 「青」のように，レコードが 1 つしかないものを選ぶ SQL は，副問い合わせを使って書ける．

まず，「漢字」で集約して，「漢字」ごとに「カタカナ」の異なり数を求め，異なり数が 1 のレコードだけを選ぶ．異なり数が 1 とは「その『漢字』の『カタカナ』が全部空文字列である」という意味である．ある『漢字』に対して，空文字列でない『カタカナ』が混ざっている場合には，異なり数は 2 以上になる．そのため，「HAVING COUNT(DISTINCT(カタカナ)) = 1;」という条件を SQL に含める．
* 属性「文字数」は「カタカナ」に関数従属するので， HAVING に「文字数」を指定する必要はない．
副問い合わせの結果として {「青」} のような漢字の集合が得られるので，それを使って，元の表からレコードを選び出す．

SQL は次のようになる．

select distinct 漢字, カタカナ, 文字数
FROM 元の表
WHERE 漢字 IN
    ( SELECT 漢字
      FROM 元の表
      group by 漢字
      HAVING COUNT(DISTINCT(カタカナ)) = 1 );

同様に， 「福」，「北」のように，「カタカナ」の値が空文字列のレコードと，ある 1 つの値を持つレコードの 2 種類だけがあるものを選ぶ SQL も書ける（3 種類以上は除外する）．先ほどの SQL の「= 1」を「= 2」に変えただけである．

select distinct 漢字, カタカナ, 文字数
FROM 元の表
WHERE 漢字 IN
    ( SELECT 漢字
      FROM 元の表
      group by 漢字
      HAVING COUNT(DISTINCT(カタカナ)) = 2 );

この SQL に手を加えて，「カタカナ」が空文字列でないものだけを出力するようにする（WHERE に「カタカナ <> ''」を追加している）．

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

以上をまとめて，次のような 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 );

出力される表は，次のことをあらわしている．

「青」のレコードには，もともと「カタカナ」の情報が無いので，転記しない．
「福」のレコードには，「フク」，「2」を転記する
「北」のレコードには，「キタ」，「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 カタカナ = '';

* 2 つの UPDATE は，「文字数」を先に更新する．「カタカナ」を先に更新すると，条件「カタカナ = ''」に合致しなくなり，「文字数」が更新されない．