WebLog

SQLで「距離」が近い文字列を求める

2023/10/22 21:00

やりたいこと

ある文字列が与えられたとき、その文字列から一番近い「距離」の文字列を SQL で取得したい。

例えば、日本の郵便番号は「000-0000」のようなハイフン付きの 7 桁の数字で管理されており、2 つの異なる郵便番号は下位の桁まで一致するほど近い地域の郵便番号を表す。

以下のようなテーブルがあったとき、郵便番号 「4130033」に対して、「最寄り」の郵便番号を SQL で取得したい。

テーブル定義
1create table postal_code (
2 pcode char(7),
3 district_name varchar(256),
4 constraint pk_code primary key(pcode)
5);
6
7insert into postal_code values
8('4130001', 'A'),
9('4130002', 'B'),
10('4130103', 'C'),
11('4130041', 'D'),
12('4103213', 'E'),
13('4380824', 'F');
14
15-- 求める結果
16-- pcode
17-- -------
18-- 4130001
19-- 4130002
20-- 4130041

回答

回答
1select
2 pcode,
3 district_name
4from postal_code
5where case when pcode = '4130033' then 0
6 when pcode like '413003%' then 1
7 when pcode like '41300%' then 2
8 when pcode like '4130%' then 3
9 when pcode like '413%' then 4
10 when pcode like '41%' then 5
11 when pcode like '4%' then 6
12 else null end = (
13 select min(
14 case when pcode = '4130033' then 0
15 when pcode like '413003%' then 1
16 when pcode like '41300%' then 2
17 when pcode like '4130%' then 3
18 when pcode like '413%' then 4
19 when pcode like '41%' then 5
20 when pcode like '4%' then 6
21 else null end
22 )
23 from postal_code
24 );
別解
1select
2 pcode,
3 district_name
4from (
5 select
6 pcode,
7 district_name,
8 case when pcode = '4130033' then 0
9 when pcode like '413003%' then 1
10 when pcode like '41300%' then 2
11 when pcode like '4130%' then 3
12 when pcode like '413%' then 4
13 when pcode like '41%' then 5
14 when pcode like '4%' then 6
15 else null end as distance,
16 min(
17 case when pcode = '4130033' then 0
18 when pcode like '413003%' then 1
19 when pcode like '41300%' then 2
20 when pcode like '4130%' then 3
21 when pcode like '413%' then 4
22 when pcode like '41%' then 5
23 when pcode like '4%' then 6
24 else null
25 ) over (
26 order by case when pcode = '4130033' then 0
27 when pcode like '413003%' then 1
28 when pcode like '41300%' then 2
29 when pcode like '4130%' then 3
30 when pcode like '413%' then 4
31 when pcode like '41%' then 5
32 when pcode like '4%' then 6
33 else null end
34 ) as min_distance
35 from postal_code
36) tmp
37where distance = min_distance;

参考

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus) | ミック |本 | 通販 | Amazon

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus) | ミック |本 | 通販 | Amazon

AmazonでミックのSQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)。アマゾンならポイント還元本が多数。ミック作品ほか、お急ぎ便対象商品は当日お届けも可能。またSQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)もアマゾン配送商品なら通常配送無料。

最新の投稿