１５章−１

(1) 実行結果を参考に次のようなSQLを作成しなさい。
・郵便番号を管理するテーブルzipcodeを作成し、zipcode.datからデータを取り込む。
（重複した郵便番号があるので、主キー制約は不要）

odexer=> \i 15_1_1.sql
CREATE TABLE

odexer=> select * from zipcode;
 num1 | num2 |                                              address
------+------+----------------------------------------------------------------------------------------------------
   60 |    0 | 北海道札幌市中央区以下に掲載がない場合
   64 |  941 | 北海道札幌市中央区旭ケ丘
   60 |   41 | 北海道札幌市中央区大通東
   60 |   42 | 北海道札幌市中央区大通西（１〜１９丁目）
   64 |  820 | 北海道札幌市中央区大通西（２０〜２８丁目）
   60 |   31 | 北海道札幌市中央区北一条東
   60 |    1 | 北海道札幌市中央区北一条西（１〜１９丁目）
   64 |  821 | 北海道札幌市中央区北一条西（２０〜２８丁目）
   		...
   		...
   		...

odexer=> select count(*) from zipcode;
 count
--------
 122671
(1 row)


(2-1) 実行結果を参考に次のようなSQLを作成しなさい。
・zipcodeテーブルから郵便番号の２つの数字で住所を検索する関数zipsrc1を作成する。
（PL/PgSQLではなくSQL関数で作成）

odexer=> \i 15_1_2.sql
CREATE FUNCTION

odexer=> select zipsrc1(64, 941);
         zipsrc1
--------------------------
 北海道札幌市中央区旭ケ丘
(1 row)

odexer=> select zipsrc1(501, 6121);
          zipsrc1
----------------------------
 岐阜県岐阜市柳津町上佐波
 岐阜県岐阜市柳津町上佐波西
 岐阜県岐阜市柳津町上佐波東
 岐阜県岐阜市柳津町佐波
 岐阜県岐阜市柳津町下佐波
 岐阜県岐阜市柳津町下佐波西
(6 rows)

odexer=> select zipsrc1(1, 1);
 zipsrc1
---------
(0 rows)


(2-2) 上記検索の性能をEXPLAIN ANALYZEで測定し、低速であることを確認しなさい。
注）関数の実行では検索の方法などの詳細情報が出力されないので、今回は処理時間のみに注目する。


(3-1) 実行結果を参考に次のようなSQLを作成しなさい。
・上記検索が高速に動作するよう、インデックスを作成する。

odexer=> \i 15_1_3.sql
CREATE INDEX

(3-2)　再度性能を測定し、高速になったことを確認しなさい。
注）
・実際にインデックスを使用したかどうかは出力されないので、やはり処理時間のみに注目する。
・インデックスの使用はpg_stat_user_indexesシステムカタログで確認できる。ただし今回は不要。


(4-1) 実行結果を参考に次のようなSQLを作成しなさい。
・zipcodeテーブルからハイフン(-)形式の郵便番号で住所を検索する関数zipsrc2を次の方針で作成する。
	1. PL/PgSQLではなくSQL関数で作成する。
	2. num1列とnum2列を桁を揃えてハイフン(-)で連結した文字列を、渡された引数と比較する。

odexer=> \i 15_1_4.sql
CREATE FUNCTION

odexer=> select zipsrc2('064-0941');
         zipsrc2
--------------------------
 北海道札幌市中央区旭ケ丘
(1 row)

odexer=> select zipsrc2('501-6121');
          zipsrc2
----------------------------
 岐阜県岐阜市柳津町上佐波
 岐阜県岐阜市柳津町上佐波西
 岐阜県岐阜市柳津町上佐波東
 岐阜県岐阜市柳津町佐波
 岐阜県岐阜市柳津町下佐波
 岐阜県岐阜市柳津町下佐波西
(6 rows)


(4-2) 上記検索では(3-1)で作成したインデックスを使用できないため、低速であることを確認しなさい。


(5-1) 実行結果を参考に次のようなSQLを作成しなさい。
・zipsrc2と同じ仕様で、インデックスを使用できる関数zipsrc3を次の方針で作成する。
	1. PL/PgSQLではなくSQL関数で作成する。
	2. substring関数で渡された引数から２つの数字を取り出し、それぞれをnum1列とnum2列を比較する。
ヒント）
	substring('064-0941' for 3) … '064-0941'の先頭から3文字 → '064'
	substring('064-0941' from 5 for 4) … '064-0941'の5文字目からから4文字 → '0941'

odexer=> \i 15_1_5.sql
CREATE FUNCTION

odexer=> select zipsrc3('064-0941');
         zipsrc3
--------------------------
 北海道札幌市中央区旭ケ丘
(1 row)

odexer=> select zipsrc3('501-6121');
          zipsrc3
----------------------------
 岐阜県岐阜市柳津町上佐波
 岐阜県岐阜市柳津町上佐波西
 岐阜県岐阜市柳津町上佐波東
 岐阜県岐阜市柳津町佐波
 岐阜県岐阜市柳津町下佐波
 岐阜県岐阜市柳津町下佐波西
(6 rows)

(5-2) zipsrc3の性能を測定し、高速であることを確認しなさい。


(6) 実行結果を参考に次のようなSQLを作成しなさい。
・郵便番号にはかなりの重複があり、それが分かるように表示する。ただし次の条件を満たすこと。
	1. 郵便番号と重複件数を重複件数の多い順に表示する。
	2. 10件数以上重複しているものを表示する。

odexer=> \i 15_1_6.sql
 郵便番号 | 重複件数
----------+----------
 441-0302 |       46
 779-3405 |       44
 029-4205 |       42
 441-0323 |       41
 989-2351 |       36
 989-6712 |       31
 969-3101 |       28
...
...
...
 969-3121 |       10
 036-0233 |       10
 981-2146 |       10
(88 rows)