【MySQL】一度に異なる条件で集計する

MySQLでは集計関数を使用することで平均値や最大値、最小値など様々な値を求めることができますが、一工夫入れるだけでもっと便利に集計できるのでその方法をご紹介します。

目次

1. はじめに
2. 前提
3. 基本的な使い方
4. 応用的な使い方
5. おわりに

はじめに

こんにちは。
クラウドソリューショングループのakahane.tです。

集計関数は使用頻度が高い機能のひとつでグループごとに平均値や最大値、最小値など様々な値を求めることができます。
しかし、一度のグループ化で対象の異なる集計を実施する場合や複数のカラムにまたがって集計する場合はサブクエリで一纏めにするなどデータを調整する必要があります。
これに対して集計関数の基本的な使い方に一工夫入れるだけで簡単に集計できるのでその方法をご紹介します。

前提

今回の動作環境はMySQL8.0.39で、下記のような3人の生徒の5科目の試験結果をサンプルデータとして使用します。

以降ではsubjectが国語・英語・社会のものを文系科目、数学・理科のものを理系科目と記載します。
今回は生徒ごとの文系科目と理系科目の平均点を求める場合を例にご紹介します。

基本的な使い方

まず基本的な集計関数の構文で作成したSQLと実行結果です。

SELECT
  er1.name AS '名前',
  AVG(er2.score) AS '文系科目平均点',
  AVG(er3.score) AS '理系科目平均点'
FROM exam_result er1
LEFT JOIN exam_result er2
  ON er1.name = er2.name
  AND er1.subject = er2.subject
  AND er2.subject IN ('国語', '英語', '社会')
LEFT JOIN exam_result er3
  ON er1.name = er3.name
  AND er1.subject = er3.subject
  AND er3.subject IN ('数学', '理科')
GROUP BY
  er1.name
;

基本的な集計関数の使い方では、生徒ごとの文系科目と理系科目の平均点のように異なる集計を実施する場合は、テーブル結合を利用して集計したい値のみが存在するカラムを用意する必要があります。

応用的な使い方

続いて応用的な集計関数の使い方で同じ集計をしてみます。
応用的な集計関数の使い方とは、「集計関数の中で集計したい値のみ集計関数に渡す」です。
今回はCASE文を使用した例でご紹介します。

SELECT
  er1.name AS '名前',
  AVG(
    CASE
      WHEN er1.subject IN ('国語', '英語', '社会')
      THEN er1.score
    END
  ) AS '文系科目平均点',
  AVG(
    CASE
      WHEN er1.subject IN ('数学', '理科')
      THEN er1.score
    END
  ) AS '理系科目平均点'
FROM exam_result er1
GROUP BY
  er1.name
;

ポイントはAVG関数の中にCASE文があることで、これにより集計に使用する値を調整することができます。
今回の例でCASE文は集計したい科目に該当する場合はその科目の点数を返し、該当しない場合はNULLを返しています。
AVGはNULLを無視して集計するため、テーブル結合なしでも一度のグループ化で文系科目・理系科目それぞれの集計ができます。

おわりに

私自身この方法に出会うまでは集計する対象はテーブル結合とWHERE句で制御することが多かったので、集計関数の中で制御できることは盲点でした。
この方法を応用して集計関数に渡す値を調整してあげれば複数のカラムにまたがった集計や、レコードに存在しない固定値や計算結果を集計に含めることもできます。
集計関数の中で制御することで一度に異なる条件の集計を柔軟に実施できるので便利な方法ですが、これだけに頼ると処理するレコードが増えて性能劣化してしまうので注意が必要です。
また、MySQL8系からはWITH句でサブクエリを再利用できるようになったので、こちらも適切に組み合わせて集計するようにしましょう。