久しぶりの技術系記事になります。
最近はコロナウィルスの影響もあり外出自粛や休業している会社もあります。
他にも働き方をテレワークなどのリモート業務に切り替えて、なるべく自宅から出ない形で日々を過ごすことが多くなっていると思います。
私も現在はリモートワークになりますので、普段の通勤時間が空いた分をこのような勉強時間にあてたりしています。
それでは以下より本題に入っていきたいと思います。
記事の目次
MySQLで基準となる郵便番号から近い順にデータ取得するための考え方
例えば1〜10の数値があった場合、昇順は1〜10、降順は10〜1の並びになります。
次に基準値がある場合を考えてみましょう。
基準値が5の場合、基準値から近い数値順でソートしようとすると次の並びになります。
５、４、６、３、７、、、みたいな感じです。
これは基準値と比べた差分が小さい順に並べた結果という事になります。
この考え方を郵便番号にも当てはめてしまえば解決できます。
郵便番号もハイフンをなくしてしまえばただの数値になるので、まとめると下記になります。
- 郵便番号からハイフンを削除し数値に変換（基準郵便番号も含め）。
- 基準郵便番号と比べた差分を昇順（小さい順）で並べる。
以上が、基準となる郵便番号から近い順にソートしたデータ取得方法になります。
あとは、上記をsqlで実行してあげればいいです。
MySQLで基準となる郵便番号から近い順でデータ取得する方法
まず郵便番号からハイフンを削除するにはREPLACE関数を使います。
REPLACE(str,from_str,to_str)
次に、基準郵便番号と比べた差分を昇順（小さい順）で並べる方法はABS関数を使って下記のようにします。
ABS(基準値 - 郵便番号)
ABS関数は数値の絶対値を求める関数になりますので、基準値から各郵便番号を引いた値の絶対値、つまり差分の数値を求められます。
あとは、この差分を昇順にソートすれば、基準郵便番号から近い順にソートされたデータが取得できるというわけです。
試してみましょう。
まずテーブルのスキーマはこんな感じです。
mysql> show columns from areas; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | zip_code1 | int(3) | NO | | NULL | | | zip_code2 | int(4) | NO | | NULL | | | zip_code | varchar(10) | NO | | NULL | | +-----------+-------------+------+-----+---------+----------------+
データを普通に取得するとこんな感じです。
mysql> select * from areas; +----+-----------+-----------+----------+ | id | zip_code1 | zip_code2 | zip_code | +----+-----------+-----------+----------+ | 1 | 111 | 1113 | 111-1113 | | 2 | 111 | 1112 | 111-1112 | | 3 | 111 | 1115 | 111-1115 | | 4 | 111 | 1111 | 111-1111 | | 5 | 111 | 1114 | 111-1114 | +----+-----------+-----------+----------+
では基準郵便番号を 111-1113 とした場合に、近い順で取得した結果が次になります。
mysql> SELECT *, ABS(1111113 - REPLACE(zip_code, '-', '')) AS zipCodeDiff FROM areas ORDER BY zipCodeDiff ASC; +----+-----------+-----------+----------+-------------+ | id | zip_code1 | zip_code2 | zip_code | zipCodeDiff | +----+-----------+-----------+----------+-------------+ | 1 | 111 | 1113 | 111-1113 | 0 | | 2 | 111 | 1112 | 111-1112 | 1 | | 5 | 111 | 1114 | 111-1114 | 1 | | 3 | 111 | 1115 | 111-1115 | 2 | | 4 | 111 | 1111 | 111-1111 | 2 | +----+-----------+-----------+----------+-------------+
基準値からの差分に zipCodeDiff というエイリアス名をつけ、それで昇順ソートしています。
因みに郵便番号を前半、後半で保存している場合も基本的には同じで、ハイフン削除している箇所を、CONCAT関数を使って文字列連結してあげればいいだけになります。
mysql> SELECT *, ABS(1111113 - CONCAT(zip_code1, zip_code2)) AS zipCodeDiff FROM areas ORDER BY zipCodeDiff ASC; +----+-----------+-----------+----------+-------------+ | id | zip_code1 | zip_code2 | zip_code | zipCodeDiff | +----+-----------+-----------+----------+-------------+ | 1 | 111 | 1113 | 111-1113 | 0 | | 2 | 111 | 1112 | 111-1112 | 1 | | 5 | 111 | 1114 | 111-1114 | 1 | | 3 | 111 | 1115 | 111-1115 | 2 | | 4 | 111 | 1111 | 111-1111 | 2 | +----+-----------+-----------+----------+-------------+
データベースから（MySQL）郵便番号が近い順にソートして取得する方法まとめ
店舗を扱う系のサービスを展開していると、検索や絞り込み、コンテンツとして近くの店舗を表示したい場合が出てきます。
そんな時は今回の考え方を利用してデータを取得してみてください。
因みに、これは郵便番号でしか使えない考え方ではないので、緯度経度などの位置情報や、口コミ数やポイントなどでも応用できると思います。
それでは久しぶりの技術系記事になりますが、少しでも参考にしてもらえればと思います。
