【SQL】GROUP BYによるデータの抽出および集計方法を解説

GROUP BYとは、データをグループ化するために使用する命令文のことです。本記事ではGROUP BYを用いたデータのグループ化および集計方法について解説します。WHEREやHAVINGとの連携による条件の絞り方やデータを表示する際の注意点なども紹介するので、参考にしてください。

GROUP BYとは

GROUP BYは、テーブル上にある任意のカラムの種類ごとにレコードをグループ化してまとめる機能を持ちます。大量のデータがあった場合でも、データをグループごとに分けたうえで、SUMやAVGなどの集計関数を用いて各グループの情報を集計するという使い方ができます。グループごとの特性の違いを見つけ出すのに役立つでしょう。

ほかの句や文と組み合わせると、さらに応用的なデータの抽出が可能です。グループ化する前後で条件を指定して表示データを絞ったり、グループ化したレコードを任意の順番に並び替えたりもできます。特に大量データを扱う際にはGROUP BYによるグループ化は使用頻度が多くなるため、覚えておきたい命令文の一つです。

グループ化した情報の集計

GROUP BYを用いてグループ化した情報を集計する方法について説明します。大抵の場合は集計関数を利用しますが、具体例も合わせて紹介します。

集計関数の活用

行数をカウントするCOUNTや合計値を返すSUM、最大値を返すMAX、平均値を返すAVGなど、複数のデータの集計に用いるのが集計関数であり、GROUP BYとともに利用できます。集計関数のカッコ内に引数としてカラム名を指定すると、指定したカラムに関する情報をグループごとに集計します。

グループ化して何を比較するのか、という目的に応じて適切な集計関数を選びましょう。COUNTやSUMなどはよく使用される関数ですが、ほかにも標準分散や標準偏差など、統計処理に用いる情報を返す関数もあります。なおGROUP BYを含まない文で集計関数を使用した場合は、すべての行に関して集計します。

集計の具体例

本記事では、商品データ（productテーブル）を例に解説します。テーブル内の各データは以下のような内容です。

商品データ（productテーブル）

次のコードは、productテーブルのcategory_cd（カテゴリコード）についてグループ化し、各グループについてCOUNTやSUM、MAX、MIN、AVGで集計した結果をまとめて表示します。FROMでテーブル名を指定し、GROUP BYでグループ化するカラム名であるcategory_cdを指定、SELECTには表示するカラム名と各集計関数を記載しています。

※サンプルコード

SELECT
 pr.category_cd,
 COUNT(unit_cost) AS count,
 SUM(unit_cost) AS sum,
 MAX(unit_cost) AS max,
 MIN(unit_cost) AS min,
 AVG(unit_cost) AS average
FROM  product AS pr
GROUP BY category_cd

各カテゴリコードはそれぞれ2つのレコードのみ含まれるため、合計値や最大値、最小値、平均値がいずれも適切な値になっていると確認できます。データが少ないとあまり利便性を感じられないでしょうが、大量のデータについて手っ取り早く各グループの特徴を把握するのに役立ちます。なお集計関数はSELECT文のなかで用いることが多く、SELECT文ではGROUP BYでグループ化したカラムか集約関数しか書けないことに注意してください。

GROUP BYの条件の絞り方

グループ化するデータを任意の条件で絞ることができます。グループ化する前と後のどちらで絞るかによって使用する句が異なるため、それぞれについて例を挙げながら解説します。

WHEREでグループ化する前に絞る

WHEREで指定した条件はGROUP BYより先に処理されるため、グループ化する前に絞りたいときはWHEREで条件を記述します。データに含まれた外れ値など、特定のデータをあらかじめ省いてからグループ化できることがメリットです。

以下のコードでは、productテーブルのcategory_cd（カテゴリコード）でグループ化し、COUNT、SUM、MAXの3つ集計関数を指定しました。今回はWHEREで「category_cd <> 2001」と記述し、category_cdが2001のレコードを省いています。

※サンプルコード

SELECT
  pr.category_cd,
  COUNT(unit_cost) AS count,
  SUM(unit_cost) AS sum,
  MAX(unit_cost) AS max
FROM product AS pr
WHERE category_cd <> 2001
GROUP BY category_cd

WHEREで指定したとおり、category_cdは2002、2003の2つのみ出力されています。各カテゴリコードのCOUNTやSUM、MAXの値は本記事の始めに出力した値から変更はありません。

HAVINGでグループ化した後に絞る

HAVING文で指定した条件はGROUP BYより後に処理されるため、グループ化した後に条件を絞ることができます。WHEREと異なるのは、元テーブルの行を絞るのではなくグループ化した結果を絞る点です。グループ化したデータの結果に応じて出力結果を変えたい場合に利用するとよいでしょう。例えばグループ化したデータのうち、数が少ないデータを表示から省いてしまうという使い方ができます。

以下のコードでは、HAVINGで「SUM(unit_cost) < 1500」と指定して、unit_cost（単価）の合計値が1500未満となるグループのみ抽出しました。合計値はグループ化した結果からわかる情報であるため、WHEREではなくHAVINGを用いて条件を指定する必要があります。

※サンプルコード

SELECT
 pr.category_cd,
 COUNT(unit_cost) AS count,
 SUM(unit_cost) AS sum,
 MAX(unit_cost) AS max
FROM product AS pr
GROUP BY category_cd
HAVING SUM(unit_cost) < 1500

結果を見ると、category_cdが2001と2002である2つのグループが表示されました。2003のデータが省かれているのは、unit_costの合計値が1820であり、HAVINGで指定した1500未満という条件に合致していないためです。

GROUP BYしたデータの表示

グループ化したデータを表示する際の注意点を紹介します。グループ化したデータをわかりやすく表示するのに役立つ方法なので、参考にしてください。

ORDER BYで並び替え

ORDER BYは、指定した列の情報をもとに特定の順番になるようソートができます。GROUP BYでグループ化してからデータを並び替えるので、GROUP BYの次にORDER BYを記述します。順番を逆に書くとエラーとなるためご注意ください。

下記のコードでは、GROUP BYの後ろにORDER BYを置き、unit_cost（単価）の合計値をもとに並び替えを指示しています。「DESC」は降順の意味であり、合計値の大きい順に並び替えます。何も記載しなければ昇順に並べ替えますが、明示したい場合は「ASC」と記述してください。

※サンプルコード

SELECT
  pr.category_cd,
  COUNT(unit_cost) AS count,
  SUM(unit_cost) AS sum,
  MAX(unit_cost) AS max
FROM product AS pr
GROUP BY category_cd
ORDER BY SUM(unit_cost) DESC

出力結果を見ると、sumの列が降順になるよう並んでいます。注目している項目について並び替えた状態で表示させれば、重要なレコードを見つけやすくなるでしょう。

ROLLUPで全グループを集計

GROUP BYの後ろにROLL UPを付けると、全グループを集計した結果を出力します。データ全体と各グループで集計結果を見比べたい場合に便利な機能です。なおグループ名のような集計データ以外の項目はNULLと表示します。

以下のコードでは、GROUP BYのあとに「WITH ROLLUP」と記載しています。category_cd（カテゴリコード）ごとの集計結果とともに全データに関する集計結果も出力されます。

※RDBMSによっては、「ROLLUP(category_cd)」と記載する場合もあります。

※サンプルコード

SELECT
  pr.category_cd,
  COUNT(unit_cost) AS count,
  SUM(unit_cost) AS sum,
  MAX(unit_cost) AS max
FROM product AS pr
GROUP BY category_cd WITH ROLLUP

出力結果の1行目に全データに関する集計結果が表示されました。category_cdについては集計の対象とはならないため、「null」と表示されています。

GROUP BYの使い方のヒント

最後に、GROUP BYを使う際に役立つ方法や注意点を解説します。

複数カラムの指定

GROUP BYで複数のカラムを指定すると、各カラムの値の組み合わせごとにグループ化できます。ここでは結果がわかりやすくなるよう、別の商品データ（product_2テーブル）を用いて解説します。これまでお見せしていたデータとは異なりますのでご注意ください。

商品データ（product_2テーブル）

新たな商品データではproduct_cdとcategory_cdのデータを変更しており、1行目と2行目、5行目と6行目のレコードが重複しています。次のコードでproduct_cdとcategory_cdの2つのカラムについてグループ化すると、重複したレコードについては集計した結果を出力します。

※サンプルコード

SELECT
 pr.product_cd,
 pr.category_cd,
 COUNT(unit_cost) AS count,
 SUM(unit_cost) AS sum,
 MAX(unit_cost) AS max
FROM  product_2 AS pr
GROUP BY product_cd, category_cd

元データの1行目と2行目は出力結果の1行目に、元データの5行目と6行目は出力結果の4行目に集計されていることがわかります。GROUP BYで指定した列はSELECT文にも入れておきましょう。そうしなければ出力結果でどのカラムを基準にグループ化したか、わかりづらくなります。

NULLの扱い

NULLを含むカラムでグループ化すると、NULLも1つのグループとして集約されます。NULLを含んだ商品データ（product_3テーブル）を用いて解説します。この商品データは、4行目のunit_cost（単価）と5、6行目のcategory_cd（カテゴリコード）がNULLとなっています。

商品データ（product_3テーブル）

product_3テーブルを用いて、以下のコードで集計を行ないました。今回はCOUNT関数でunit_costを指定したときと、ワイルドカード（*）を指定したときの違いも比較します。

※サンプルコード

SELECT
 pr.category_cd,
 COUNT(unit_cost) AS count,
 COUNT(*) AS count2,
 SUM(unit_cost) AS sum,
 MAX(unit_cost) AS max
FROM product_3 AS pr
GROUP BY category_cd

元データの5、6行目はcategory_cdがNULLでしたが、出力結果の3行目にて1つのグループとして集計されています。また元データの4行目はcategory_cdが2002でしたが、unit_costがNULLであるため出力結果のcount、sum、maxのカラムでは無視されています。ここで集計結果のcountとcount2のカラムの違いに注目してください。COUNT(*)とワイルドカードを用いて指定したcount2のカラムでは、元データの4行目も集計されています。NULL値を含めてレコード数を数えたいときはCOUNT(*)と指定しましょう。

GROUP BYで任意のグループの情報を分析

GROUP BYは特定のカラムの種類ごとにレコードをグループ化する機能です。集計関数を用いてグループごとの合計値や平均値、最大値、最小値などが得られます。WHEREやHAVINGを用いれば、条件を絞ったうえで欲しいデータを抽出できますし、ORDER BYと合わせて使用すればグループ化したデータを並び替えて表示できます。

特に大量のデータを扱うときは、GROUP BYを用いると各グループの特徴をわかりやすく表現できるでしょう。ただしデータにNULLが含まれている場合の振る舞いには注意してください。グループ化するカラムにNULLがあればNULLのみで1つのグループとして扱われますし、NULLを含んだカラムを集計関数で集計しようとしても無視されてしまいます。データにNULLが含まれているかどうかを確認しておくとよいでしょう。