SQLのdistinctについてまとめてます。
SQLのdistinctの構文
distinctは対象データが重複していた場合、重複を１つにまとめることができます。
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.17 DISTINCT の最適化
以下は、部署（departments）ごとに所属する社員の役職を抽出するSQLです。titles（役職）テーブルと、dept_emp（部署・社員紐付けテーブル）にjoinをおこなって、重複データを除去しています。
SELECT distinct dept_name, title FROM `titles` left join dept_emp on titles.emp_no = dept_emp.emp_no left join departments on dept_emp.dept_no = departments.dept_no order by dept_name,title; +--------------------+--------------------+ | dept_name | title | +--------------------+--------------------+ | Customer Service | Assistant Engineer | | Customer Service | Engineer | | Customer Service | Manager | | Customer Service | Senior Engineer | | Customer Service | Senior Staff | | Customer Service | Staff | | Customer Service | Technique Leader | | Development | Assistant Engineer | | Development | Engineer | | Development | Manager | | Development | Senior Engineer | | Development | Senior Staff | | Development | Staff | | Development | Technique Leader | | Finance | Manager | | Finance | Senior Staff | | Finance | Staff | | Human Resources | Manager | : :
【関連記事】
▶SQL distinctのサンプルコード集 group byよりも700倍速い？
SQLのdistinctは、group byと比較すると大きな速度差が出るケースあり
以下は、同じ結果を出すのにdistinctとgroup byを使用した例です。distinctを使った場合は、抽出に0.2秒ですが、group byを使った場合は、27秒かかっています。
mysql> SELECT distinct first_name, gender FROM `employees` limit 100;
+-------------+--------+
| first_name | gender |
+-------------+--------+
| Georgi | M |
| Bezalel | F |
| Parto | M |
| Chirstian | M |
| Kyoichi | M |
| Anneke | F |
| Tzvetan | F |
| Saniya | M |
| Sumant | F |
:
:
100 rows in set (0.20 sec)
mysql> SELECT first_name, gender FROM `employees`
-> group by first_name, gender limit 100;
+-------------+--------+
| first_name | gender |
+-------------+--------+
| Georgi | M |
| Bezalel | F |
| Parto | M |
| Chirstian | M |
| Kyoichi | M |
| Anneke | F |
| Tzvetan | F |
| Saniya | M |
| Sumant | F |
:
:
100 rows in set (27.55 sec)
【関連記事】
▶SQL distinctとgroup byの実行計画が全く同じなのに大きな速度差が出る
実は、対象テーブルのemployeesは20万件データがあります。group byの対象になっているfirst_name（姓名の名）と、gender（性別）には、インデックスが設定されていません。そのため、20万件のテーブルフルスキャンが実行され、これほどの時間がかかってしまうんですね。
グルーピングごとの集計や最大・最小値などをおこなう場合は有効ですが、今回は重複を除いてただ表示だけすれば良いので、distinctを使う方が効率的です。
distinctを指定すると、単純にselectでデータを取得後、重複を捨てるという動きになり、高速に結果が出るんですね。
同じ結果になるのなら、積極的にdistinctを使用するのが良いでしょう。
countとdistinctを組み合わせて、「データ種類の数」をカウント
データ数を数えるcount関数と組み合わせると、データ種類の数を数えることができます。
【関連記事】
▶SQLのcountの構文 重複除外や集計区間限定でのカウント方法
以下の例は、部署名（dept_name）ごとに、所属している社員の役職の数をカウントする例です。
SELECT dept_name, count(distinct title) FROM `titles` left join dept_emp on titles.emp_no = dept_emp.emp_no left join departments on dept_emp.dept_no = departments.dept_no group by dept_name order by dept_name +--------------------+-----------------------+ | dept_name | count(distinct title) | +--------------------+-----------------------+ | Customer Service | 7 | | Development | 7 | | Finance | 3 | | Human Resources | 3 | | Marketing | 3 | | Production | 7 | | Quality Management | 5 | | Research | 7 | | Sales | 3 | +--------------------+-----------------------+ 9 rows in set (3.58 sec)
【関連記事】
▶SQL distinctとcountを組み合わせてデータ種類をカウントする
まとめ
- distinctは、データの重複を除去するSQL
- group byと比較すると、圧倒的に高速に動作する
- count関数とdistinctを組み合わせてデータ種別のカウントが可能
