MySQLでユーザー定義変数の値に最も近いレコードをテーブルから取得する方法

MySQLでは、ABS()関数とORDER BY句を組み合わせることで、ユーザー定義変数に格納した値に最も近いレコードを簡単に取得できます。この記事では、実際にテーブルを作成し、指定した値に最も近いレコードを取り出す手順を詳しく解説します。

1. サンプルテーブルの作成

まず、CREATE TABLE文を使ってテーブルを作成します。

mysql> create table DemoTable
(
    CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ProductAmount int
);
Query OK, 0 rows affected (0.61 sec)

ここでは、顧客ID（CustomerId）を主キーとし、商品金額（ProductAmount）を格納するシンプルなテーブルを用意しました。

2. レコードの挿入

次に、INSERTコマンドを使ってテーブルにデータを挿入します。

mysql> insert into DemoTable(ProductAmount) values(5000);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(ProductAmount) values(6000);
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable(ProductAmount) values(7000);
Query OK, 1 row affected (0.26 sec)

3. 挿入したレコードの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行すると、次のような結果が出力されます。

+------------+---------------+
| CustomerId | ProductAmount |
+------------+---------------+
|          1 |          5000 |
|          2 |          6000 |
|          3 |          7000 |
+------------+---------------+
3 rows in set (0.00 sec)

4. ユーザー定義変数を使って最も近い値を取得する

それが本題となる、ユーザー定義変数の値に最も近いレコードを取得するクエリです。まずSET文で変数に値を代入します。ここでは「6990」を設定します。

mysql> set @value=6990;
Query OK, 0 rows affected (0.00 sec)

続いて、ABS(ProductAmount-@value)で各レコードとの差の絶対値を計算し、その差が最小となるレコードをLIMIT 1で取得します。

mysql> select ProductAmount from DemoTable order by ABS(ProductAmount-@value) LIMIT 1;

実行結果は以下のとおりです。

+---------------+
| ProductAmount |
+---------------+
|          7000 |
+---------------+
1 row in set (0.00 sec)

解説

変数には6990を設定しましたが、テーブル内には6990という値は存在しないため、最も近い値が返されることになります。この例では「7000」と「6990」の差は10、「6000」と「6990」の差は990なので、差が最小の7000が結果として返されました。

このように、ABS()関数で差の絶対値を求めて昇順にソートし、LIMIT 1で先頭の1件だけを取得すれば、任意の値に最も近いレコードを効率的に検索できます。なお、同点の差を持つ複数のレコードが存在する可能性がある場合は、LIMIT 1の代わりに条件を工夫してすべて該当レコードを取得する方法もあります。