【MySQL】SUBSTRING_INDEXでIPアドレスのサブパートごとの重複数をカウントする方法

IPアドレスのような文字列データを部分ごとに集計したい場合、MySQLのSUBSTRING_INDEX()関数を活用するのが効果的です。この関数を使えば、区切り文字を基準に文字列を切り出し、IPアドレスのネットワーク部（上位3オクテット）だけを取り出してグループ化し、重複数を簡単にカウントできます。

テーブルの作成

まず、サンプル用のテーブルを作成しましょう。

mysql> create table DemoTable
(
    SystemIpAddress text
);
Query OK, 0 rows affected (0.58 sec)

レコードの挿入

insertコマンドを使って、テーブルにいくつかのIPアドレスを登録します。

mysql> insert into DemoTable values('192.168.130.67');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('192.168.130.87');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('192.168.131.47');
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable values('192.168.134.50');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('192.168.131.12');
Query OK, 1 row affected (0.21 sec)

登録データの確認

selectステートメントで、テーブル内の全レコードを表示してみます。

mysql> select *from DemoTable ;

実行すると、次のような結果が得られます。

+-----------------+
| SystemIpAddress |
+-----------------+
| 192.168.130.67  |
| 192.168.130.87  |
| 192.168.131.47  |
| 192.168.134.50  |
| 192.168.131.12  |
+-----------------+
5 rows in set (0.00 sec)

サブパートごとに重複をカウントするクエリ

IPアドレスの上位3オクテット（サブパート）ごとにレコード数を集計するクエリは以下の通りです。SUBSTRING_INDEX()の第3引数に「3」を指定することで、3つ目のドットより前の部分（例：192.168.130）を取り出し、それをGROUP BYでグループ化して件数をカウントしています。

mysql> select substring_index(tbl.SystemIpAddress, '.', 3) , count(*) as Total from DemoTable tbl
    group by substring_index(tbl.SystemIpAddress, '.', 3)
    order by Total desc limit 5;

実行結果は次のようになります。

+----------------------------------------------+-------+
| substring_index(tbl.SystemIpAddress, '.', 3) | Total |
+----------------------------------------------+-------+
| 192.168.130                                  |     2 |
| 192.168.131                                  |     2 |
| 192.168.134                                  |     1 |
+----------------------------------------------+-------+
3 rows in set (0.00 sec)

この結果から、「192.168.130」と「192.168.131」のネットワークにはそれぞれ2件、「192.168.134」には1件のIPアドレスが登録されていることが一目でわかります。ログ解析やアクセス集計など、IPアドレスをネットワーク単位で統計処理したい場合に非常に便利なテクニックなので、ぜひ活用してみてください。