SQL

SQLServer　グループ化　集計関数　Group By　HAVING

12分28秒
SQLServer　グループ化　集計関数　Group By　HAVING

SQLServerでの、Group By について、紹介していきたいと思います。

初めて、Group By を学んだときはとても苦戦しました。初心者の人は、少し鬼門になるかもしれませんが、

Group Byは必ず必要なスキルなので、必ず覚えておきましょう。

グループ化　Group By の使い方

Group By とは、データを集計する時に使います。

特に、データの合計、平均、最大値、最小値、グループごとのカウントなどを求める時によく使用します。

それでは、基本的な書き方を紹介します。

SELECT
  集約項目
FROM
  テーブル
WHERE
  条件
GROUP BY
  集約項目

 

これだけだとわかりにくいので、例を見てみましょう。こんなデータを作成してみました。

このデータを【nickName】で集約してみましょう。

SELECT
  nickName
FROM
  hironimoTable
GROUP BY
  nickName

 

結果

全部で15件あったデータが、【nickName】で集約されて、4件になりましたね。

Group By 集約項目で、データを集約することができます。これがGroup Byです。

 

Group Byは複数項目指定することもできます。【nickName】と【animal】で集約してみましょう。

SELECT
  nickName
 ,animal
FROM
 hironimoTable
GROUP BY
  nickName
 ,animal

 

結果

nickNameとanimalで集約を行ったので、2項目セットでの集約になっています。

nickNameのみの時は、1行ずつでしたが、今回は さとう、すずき が2行ずつになっていますね。

基本的なGroup By の使い方は以上です。慣れるまでは少し難しいと思いますが、色々ためしてみてください。

 

集約関数の使い方

それでは、集約ができるようになりましたので、次は集約関数を使用してみましょう。

集約関数とは、Group Byで集約したときに、グループごとの合計、平均値、最大値、最小値などを求めることができる関数のことです。

よく使う関数を１つずつ見ていきましょう。

COUNT関数　件数取得

1つ目は、COUNT関数です。文字通り、集約したデータの件数を取得することができます。

先ほどの【nickName】を集約した例をCOUNTしてみましょう。

SELECT
  nickName
 ,COUNT(nickName) AS KENSU
FROM
 hironimoTable
GROUP BY
  nickName

 

結果

【nickName】で集約した時、各データが何件あるかを取得することができます。

今回であれば、さとう：4件、すずき：4件、たなか：2件、ひろにも：5件、の計15件です。

上の方にある元データを見直してみると、正しい件数が取得できていることが確認できます。

 

SUM関数　データの合計

SUM関数は、イメージが湧きやすいと思います。集約した項目の合計を取得することができます。EXCELとかでもSUMはよく使いますよね。

例を見ていきましょう。

SELECT
  nickName
 ,sum(weight) AS SUM_WEIGHT
FROM
 hironimoTable
GROUP BY
  nickName

 

結果

【nickName】で集約したときの、weightの合計が表示されています。

元データを見てみると、さとう行のweightは、50、60、70、80ですね。

これらの合計は、260なので、SUM_WEIGHTの値と一致しています。

 

AVG関数　データの平均値

AVG関数を紹介します。AVGとはaverageの略で平均値を求めるための集約関数となってます。

例を見ていきましょう。

SELECT
  nickName
 ,AVG(weight) AS AVG_WEIGHT
FROM
  hironimoTable
GROUP BY
  nickName

 

結果

【nickName】で集約したデータを集計して、各行のweightの平均値が表示されています。

さとう行を注目して見てみましょう。

COUNT関数で取得した時は【KENSU】：4
SUM関数で取得した【SUM_WEIGHT】：260

260 / 4 = 65 なので、AVG関数で取得した、【AVG_WEIGHT】も65なので正しく取得できていることが確認できましたね。

 

MAX関数　データの最大値

MAX関数を紹介します。MAXとは、文字通り集約したデータの各項目の最大の値を取得することができます。

例をみてみましょう。

SELECT
  nickName
 ,MAX(weight) AS MAX_WEIGHT
FROM
  hironimoTable
GROUP BY
  nickName

 

結果

【nickName】で集約したデータを集計して、各行のweightの最大値が表示されています。

さとう行を注目して見てみましょう。

元データのさとう行は4行あり、その時のweightの値は、50、60、70、80なので
MAX関数を使用すると、一番大きい値の80が表示されていることが確認できましたね。

 

MIN関数　データの最小値

最後にMIN関数を紹介します。MINとは、文字通り集約したデータの各項目の最小の値を取得することができます。

例をみてみましょう。

SELECT
  nickName
 ,MIN(weight) AS MIN_WEIGHT
FROM
  hironimoTable
GROUP BY
  nickName

 

結果

【nickName】で集約したデータを集計して、各行のweightの最小値が表示されています。

さとう行を注目して見てみましょう。

元データのさとう行は4行あり、その時のweightの値は、50、60、70、80なので
MIN関数を使用すると、一番小さい値の50が表示されていることが確認できましたね。

 

HAVING 集約した後の条件抽出

HAVING 使い方

GROUP BY を使用する時に欠かせない、HAVINGについて紹介していきます。

HAVINGとは、WHERE句ととても似ていて、データの条件抽出をすることができます。
ただし、WHERE句と違う点はGROUP BY で集計した項目に対して条件抽出をするときに使うという点です。
つまり、GROUP BYで集約し、集約関数で取得した集計データに対して条件抽出ができるということです。

書き方は、

SELECT
  集約項目
FROM
  テーブル
WHERE
  条件
GROUP BY
  集約項目
HAVING
  集約項目条件

 

このように、GROUP BY のあとにHAVINGを記載していきます。

例えば、先ほどAVG関数を使用して、weightの平均値を求めましたが、
その中でも50より大きいデータのみ取得したい場合は、先ほどのHAVING句を使用して取得する必要があります。

SELECT
  nickName
 ,AVG(WEIGHT)
FROM
  hironimoTable
GROUP BY
  nickName
HAVING
  AVG(weight) > 50

結果

HAVING句を指定していないときは、
さとう：65
すずき：45
たなか：15
ひろにも：71.1
の4件でしたね。

HAVING AVG(weight) > 50を指定すると、
さとう：65
ひろにも：71.1
weightの平均値が50より大きい２件が抽出されていますね。

HAVINGの使用方法が理解できたかと思います。

WHERE句とHAVING句

最後にWHERE句とHAVING句の併用した時を紹介します。

【height】が150以上のデータのweightの平均値が60を超えるデータを知りたいといった場合について考えてみましょう。
なんだかややこしくなってきましたね。例を見ながら順にみていきます。

このようなSQLになります。

SELECT
  nickName
 ,AVG(weight) AS AVG_WEIGHT
FROM
  hironimoTable
WHERE
  height >= 150
GROUP BY
  nickName
HAVING
  AVG(weight) > 60

 

ステップ１　テーブル選択

考え方としては、まずはテーブルを選択しましょう。

/**ステップ1:テーブルを選択**/
SELECT
  *
FROM
  hironimoTable

ステップ２　WHERE句で条件を絞り込む

次に条件でデータを絞り込む、今回の場合は、身長が150以上のデータに絞り込む

/**ステップ2:WHERE句で条件を絞り込む**/
SELECT
  *
FROM
  hironimoTable
WHERE
  height >= 150

ステップ３　GROUP BY で集約

次に、集約をしていきましょう。今回は、nickNameで集約し、平均値を取得

/**ステップ3：GROUP BY で集約**/
SELECT
  nickName
 ,AVG(weight) AS AVG_WEIGHT
FROM
  hironimoTable
WHERE
  height >= 150
GROUP BY
  nickName

ステップ４　HAVING 集約項目を抽出

最後に集約項目の条件抽出をして完了です。今回は、平均体重が60より大きいデータを取得

/**ステップ4：HAVINGで集約項目を抽出する**/
SELECT
  nickName
 ,AVG(weight) AS AVG_WEIGHT
FROM
  hironimoTable
WHERE
  height >= 150
GROUP BY
  nickName
HAVING
  AVG(weight) > 60

 

GROUP BY はややこしいですが、私はいつもこの4ステップで考えています。

是非、参考にしてみてください。

まとめ

今回は、GROUP BY での集約について紹介しましたが、SQLを使用する上で必須の知識です。
今回の例は単純なものでしたが、GROUP BY は複雑になることが多いので、
最後に照会した4つのステップを、順番に実行してデータを見ながら、考えることをおすすめします。

4つのステップ
  • 1：取得したい項目のテーブルを選択
  • 2：条件を指定
  • 3：集約する
  • 4：集約項目で条件を抽出

 

また、WHEREとHAVINGの理解はとても大切です。
WHEREは、集約する前のデータに対して条件抽出を行い、
HAVINGは、集約した後のデータに対して条件抽出を行います。
この違いをしっかり、理解しておきましょう。

おすすめの記事