補足
本記事で書いたイケてないSQLに対し、問題の解説をしてくださっている記事がありますので先頭でリンクします。@YSRKENさん、どうもありがとうございます。
【SQLite】地名調査の記事から分かるSQLチューニングの基礎
本記事は、SQL書いて遊べるよ、というレベルまでで参照いただけると目的に対してちょうど良いです。
概要
このメモの内容
SQLite3を使って日本の地名に関して調べ物をするときの始め方をメモします。
記事を書くきっかけ
Web上の記事で「災害の記憶をいまに伝える 日本全国「あぶない地名」この漢字が入っていたら要注意！（一覧表付き）」1というのを見かけ、このような文字が入っている地名がどのくらいあるのか気になったためです。
前提
とにかくざっとデータを眺めて様子をつかむことを目的としています。SQLも簡単に書けるレベルで使用しています。
方法
データ入手
地名が入ったデータといえば、郵便番号辞書をどこかから入手すると良さそうです。
googleで調べると、「住所.jp」2 で、自由に使えるデータを公開しています。
CSVだけだなく、SQLite3でそのまま読み込めるデータも用意してくれているようなので、今回はこれを使います。
サイトから、全国のデータが入ったzipファイルをダウンロードします。ファイル名は「zenkoku.sqlite3.zip」でした。このzipを伸張して「zenkoku.sqlite3」にします。
sqlite3起動
筆者はOSXで作業しているので、標準でSQLite3が導入されています。SQLite3が導入されていない環境では、適宜導入してください。
ダウンロードしたzenkoku.sqlite3があるディレクトリで、sqlite3コマンドを実行し、SQLite3のコンソールがつかえるようにします。
$ sqlite3
データ読み込み
.openコマンドでダウンロード済みのファイルを開きます。
sqlite> .open zenkoku.sqlite3
読み込んだ内容を調べてみる
.tablesコマンドを使ってテーブル一覧を見ます。対象にするカラムにあたりをつけます。
sqlite> .tables
ad_address t_version
.schemaコマンドを使ってテーブルのスキーマを見ます。
sqlite> .schema ad_address
CREATE TABLE(id
INTEGER NOT NULL DEFAULT 0,ken_id
INTEGER DEFAULT NULL,new_id
...(中略)...
INTEGER DEFAULT NULL,id
PRIMARY KEY ()
);
内容を表示
とりあえず、「蛇」が含まれる町名を表示してみます。
select zip,ken_name,city_name,town_name from ad_address where town_name like "%蛇%";
個数でいうと61個。
sqlite> select count(town_name) from ad_address where town_name like "%蛇%";
61
今回の目的の町名を調べる
調べたい語句が含まれるテーブルを1つ作って、簡単に調べることにします。
まず、対象語句格納用のテーブルを作る
下記のようなものすごく単純なテーブルを作ります。
CREATE TABLE words (word)
次に、調べたい対象の語句を入れる
記事を書くきっかけになったサイト1を参考に作ったテーブルに語句を入れていきます。
insert into words values ('鮎');
insert into words values ('梅');
insert into words values ('荻');
insert into words values ('女');
insert into words values ('柿');
insert into words values ('蟹');
insert into words values ('釜');
insert into words values ('亀');
insert into words values ('草');
insert into words values ('倉');
insert into words values ('駒');
insert into words values ('鷺');
insert into words values ('桜');
insert into words values ('猿');
insert into words values ('蛇崩');
insert into words values ('蛇抜');
insert into words values ('滝');
insert into words values ('椿');
insert into words values ('津留');
insert into words values ('灘');
insert into words values ('野毛');
insert into words values ('萩');
insert into words values ('放');
insert into words values ('袋');
insert into words values ('衾');
insert into words values ('龍');
実行（対象とする語句を1つ以上含むケース）
こんな感じで実行すると、調べたい対象の地名が出てきます。
sqlite> select zip,ken_name,city_name,town_name from ad_address as ad,words as w1 where ad.town_name like '%'||w1.word||'%'||'%';
5871個ありました。
sqlite> select count(town_name) from ad_address as ad,words as w1 where ad.town_name like '%'||w1.word||'%'||'%';
5871
SQLite3で実行時間を出す。
.timerコマンドで、実行時間を表示するようになります。実行時間がかかるときに参考になります。
sqlite> .timer on
実行（対象とする語句を2つ以上含むケース）
1つのときと同じ要領で、where以下に2回含むパターンを指定します。
select zip,ken_name,city_name,town_name from ad_address as ad,words as w1,words as w2 where ad.town_name like '%'||w1.word||'%'||w2.word||'%';
この実行はそこそこ時間がかかるので、「結構あるもんだな」などの感想を持ちながら目で追える感じです。実行時間はこのくらいでした。
Run Time: real 33.766 user 33.564572 sys 0.133176
実行(さらに)
1, 2, と来たら3を試してみたくなるのが人情だと思いますが、（前述のステップを実行した人であればうすうすわかると思いますが）結論から言って、今回対象としたカラムでは、3つ入っているものはありませんでした。
sqlite> select zip,ken_name,city_name,town_name from ad_address as ad,words as w1,words as w2,words as w3 where ad.town_name like '%'||w1.word||'%'||w2.word||'%'||w3.word||'%';
Run Time: real 1047.457 user 1041.483093 sys 2.566790
まとめ
地名データをSQLite3に取り込んで、調べ物を始めるときの方法についてメモをまとめました。