SQL distinctとcountを組み合わせてデータ種類をカウントする

SQLのdistinctを使ったcountについてまとめています。

distinct countは、データの種類を数えるのに便利なSQL

distinctとcountを組み合わせると、データの種類を数えることができます。

以下のSQLは、部署（dept_name）ごとに役職（title）数をカウントするSQLです。distinct titleをカウントすることで、部署ごとの役職数を算出できるんですね。distinctは重複データを捨てるためのSQLです。

なお、データベースはMySQLのサンプルデータベースEmployeesを使用しています。

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のサンプルコード集 group byよりも700倍速い？

上記のSQLですが、実行に3.58秒かかっています。なお、titlesの件数は約44万件でした。

mysql> select count(*) from titles;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+

役職数のカウントなので、titles44万件をフルスキャンするのは仕方ないですね。どこかにボトルネックがないかどうか、explainで実行計画をチェックしてみます。

mysql> explain 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;
+----+-------------+-------------+------------+--------+-------------------+---------+---------+----------------------------+--------+--
--------+----------------------------------------------+
| id | select_type | table       | partitions | type   | possible_keys     | key     | key_len | ref                        | rows   | f
iltered | Extra                                        |
+----+-------------+-------------+------------+--------+-------------------+---------+---------+----------------------------+--------+--
--------+----------------------------------------------+
|  1 | SIMPLE      | titles      | NULL       | index  | NULL              | PRIMARY | 209     | NULL                       | 442189 |  
 100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | dept_emp    | NULL       | ref    | PRIMARY           | PRIMARY | 4       | employees.titles.emp_no    |      1 |  
 100.00 | Using index                                  |
|  1 | SIMPLE      | departments | NULL       | eq_ref | PRIMARY,dept_name | PRIMARY | 16      | employees.dept_emp.dept_no |      1 |  
 100.00 | NULL                                         |
+----+-------------+-------------+------------+--------+-------------------+---------+---------+----------------------------+--------+--
--------+----------------------------------------------+
3 rows in set, 1 warning (0.12 sec)

出力結果の「type」を見ると、それぞれインデックスを使っていたり単純なフルスキャンはしていないようなので妥当だと思われます。titlesはindex（フルインデックススキャン）が使用されていますが、これはしょうがないでしょう。dept_empはref、departmentsはeq_refなのはjoinを使っているため改善の余地はありません。

テスト環境など、データの少ない段階では遅いクエリも目立たないものですが念のためexplainでボトルネックがないかどうかチェックしておきましょう。

まとめ

ポテパンダの一言メモ

• distinctとcountを組み合わせるとデータの種類をカウントすることができる
• データ種類のカウントはテーブルフルスキャンとなることが多いため、クエリに時間がかかる
• explainによる実行計画で「type」項目を確認して、ボトルネックがないか確認する