MySQLでドメインレコードと検索ボリュームから人気ドメインを抽出する方法

MySQLのテーブルに保存されたURLやドメイン名、検索回数などのデータから、検索ボリュームが最も高い人気ドメインを特定したいケースは多くあります。このような場合、GROUP BY句とORDER BY句を組み合わせて使用することで、簡単に集計・並べ替えが可能です。

この記事では、実際にテーブルを作成し、サンプルデータを挿入して、人気ドメインを抽出するクエリの手順を詳しく解説します。

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

まず、URL・ドメイン名・検索回数を格納するテーブルを作成します。

mysql> create table DemoTable
-> (
-> URL varchar(40),
-> DomainName varchar(20),
-> SearchTimes int
-> );
Query OK, 0 rows affected (0.62 sec)

2. レコードの挿入

次に、INSERTコマンドを使ってテーブルにいくつかのレコードを挿入します。同じドメインが複数回登場するデータを用意するのがポイントです。

mysql> insert into DemoTable values('www.gmail.com','gmail.com',4);
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable values('www.google.com','google.com',3);
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable values('www.gmail.com','gmail.com',9);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('www.facebook.com','facebook.com',8);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('www.facebook.com','facebook.com',2);
Query OK, 1 row affected (0.16 sec)

3. テーブル内容の確認

SELECT文で全レコードを表示して、データが正しく登録されているか確認しましょう。

mysql> select *from DemoTable;

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

+------------------+--------------+-------------+
| URL              | DomainName   | SearchTimes |
+------------------+--------------+-------------+
| www.gmail.com    | gmail.com    |           4 |
| www.google.com   | google.com   |           3 |
| www.gmail.com    | gmail.com    |           9 |
| www.facebook.com | facebook.com |           8 |
| www.facebook.com | facebook.com |           2 |
+------------------+--------------+-------------+
5 rows in set (0.00 sec)

4. 人気ドメインを抽出するクエリ

ここからが本題です。ドメインごとに検索回数を合計し、合計値の降順で並べ替えることで、検索ボリュームの高い人気ドメインを上位から順に表示できます。

mysql> select DomainName,sum(SearchTimes) as TotalSearch from DemoTable
-> group by DomainName
-> order by TotalSearch desc;

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

+--------------+-------------+
| DomainName   | TotalSearch |
+--------------+-------------+
| gmail.com    |          13 |
| facebook.com |          10 |
| google.com   |           3 |
+--------------+-------------+
3 rows in set (0.00 sec)

クエリのポイント解説

• SUM(SearchTimes): ドメインごとの検索回数を合計します。別名「TotalSearch」を付けることで、結果を見やすくしています。
• GROUP BY DomainName: 同じドメイン名のレコードを1つのグループにまとめます。これにより、gmail.comのように複数行に分かれているデータも正しく集計されます。
• ORDER BY TotalSearch DESC: 合計検索数の降順（大きい順）に並べ替え、最も人気のあるドメインを先頭に表示します。

このように、GROUP BYとORDER BYを組み合わせるだけで、アクセスログや検索履歴のデータから人気ドメインのランキングを簡単に作成できます。LIMIT句を追加すれば、上位N件のみを取得することも可能なので、用途に応じて活用してみてください。