
目次
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で集約したときに、グループごとの合計、平均値、最大値、最小値などを求めることができる関数のことです。
よく使う関数を1つずつ見ていきましょう。
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より大きい2件が抽出されていますね。
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 テーブル選択
考え方としては、まずはテーブルを選択しましょう。
/**ステップ1:テーブルを選択**/
SELECT
*
FROM
hironimoTable
ステップ2 WHERE句で条件を絞り込む
次に条件でデータを絞り込む、今回の場合は、身長が150以上のデータに絞り込む
/**ステップ2:WHERE句で条件を絞り込む**/
SELECT
*
FROM
hironimoTable
WHERE
height >= 150
ステップ3 GROUP BY で集約
次に、集約をしていきましょう。今回は、nickNameで集約し、平均値を取得
/**ステップ3:GROUP BY で集約**/
SELECT
nickName
,AVG(weight) AS AVG_WEIGHT
FROM
hironimoTable
WHERE
height >= 150
GROUP BY
nickName
ステップ4 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つのステップを、順番に実行してデータを見ながら、考えることをおすすめします。
- 1:取得したい項目のテーブルを選択
- 2:条件を指定
- 3:集約する
- 4:集約項目で条件を抽出
また、WHEREとHAVINGの理解はとても大切です。
WHEREは、集約する前のデータに対して条件抽出を行い、
HAVINGは、集約した後のデータに対して条件抽出を行います。
この違いをしっかり、理解しておきましょう。