MySQLで2つの列の組み合わせごとの出現回数を集計する方法

MySQLでは、GROUP BY句とGREATEST()・LEAST()関数を組み合わせることで、2つの列にまたがる値の組み合わせごとの出現回数を簡単に集計できます。この記事では、具体的なサンプルを使って手順を解説します。

1. サンプルテーブルを作成する

まず、2つの名前列を持つテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> Name1 varchar(20),
    -> Name2 varchar(20)
    -> );
Query OK, 0 rows affected (0.61 sec)

2. テストデータを挿入する

次に、INSERT文を使ってレコードを登録します。

mysql> insert into DemoTable values('John','Adam');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Chris','David');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Robert','Mike');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('David','Chris');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Mike','Robert');
Query OK, 1 row affected (0.18 sec)

3. 登録したデータを確認する

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

mysql> select *from DemoTable;

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

+--------+--------+
| Name1  | Name2  |
+--------+--------+
| John   | Adam   |
| Chris  | David  |
| Robert | Mike   |
| David  | Chris  |
| Mike   | Robert |
+--------+--------+
5 rows in set (0.00 sec)

4. 2つの列の出現回数を集計するクエリ

ポイントは、GREATEST()とLEAST()を使って2列の値を「順序を無視したペア」として正規化することです。これにより、「Chris & David」と「David & Chris」のような順序が違うだけの組み合わせも同じグループとして扱えます。

mysql> select greatest(Name1,Name2),least(Name1,Name2),count(*) as Occurrences from DemoTable
    -> group by greatest(Name1,Name2),least(Name1,Name2);

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

+-----------------------+--------------------+-------------+
| greatest(Name1,Name2) | least(Name1,Name2) | Occurrences |
+-----------------------+--------------------+-------------+
| John                  | Adam               |           1 |
| David                 | Chris              |           2 |
| Robert                | Mike               |           2 |
+-----------------------+--------------------+-------------+
3 rows in set (0.02 sec)

解説

上記の結果から、以下のことが読み取れます。

• (John, Adam): 1回だけ出現
• (Chris, David): 「Chris→David」と「David→Chris」の2パターンで計2回出現
• (Robert, Mike): 「Robert→Mike」と「Mike→Robert」の2パターンで計2回出現

GREATEST()は引数の中で最も大きい（辞書順で後ろの）値を、LEAST()は最も小さい（辞書順で前の）値を返します。この2つをGROUP BYのキーにすることで、列の順序に関係なく同じペアとしてカウントできるのがこのテクニックのポイントです。友達関係やマッチングデータなど、ペアの出現頻度を分析したい場合に非常に便利な方法です。