sql_集計関数

06集計関数

はじめにで前述したように、DBに保存したデータを適切に取り出したり、分析したりすることがDBを使用するうえでのカギとなります。

この章では、データを分析するための便利なSQLを学んでいきます。

group by句

さっそく集計関数から学び始めたいところですが、その前に集計関数を使用する際に頻出するgroup by句の動作を確認します。

group by句を使用すると、任意のカラムをグループ単位にまとめることができます。

同じクラスの学生がそれぞれ何人いるかを集計したい場合、以下のSQLを実行します。

select class, count(*) from students group by class;

実行結果から、クラス1が2人、他のクラスは1人ずつ生徒がいることが分かりました。

※count関数については後述します。

このように、group byを使用することにより、データを任意のグループにわけることが可能です。また、group byに複数カラムを指定し、組み合わせでグループ化することもできます。

＜補足：distinct句＞

distinct句を使用すれば、group byと同じような結果を得ることができます。group byとの違いは、group byが「グループ単位にまとめる」に対し、distinctは「重複行を削除する」です。

なので、使い分けとしては「グループ単位にまとめた結果を集計したい場合はgroup by」、「単にデータの重複を削除したい場合はdistinct」と考えればよいです。

having句

集計関数を利用する際に頻出するhaving句も紹介します。

having句は、抽出条件を指定できる命令です。一見、「where句と何が違うの？」と思うかもしれませんが、決定的な違いは「having句は、条件に集計関数を含めることができる」という点です。

では、実際に動作を確認していきましょう。

まず、動作確認に必要なデータを準備します。以下のSQLを順に実行してください。

①テストの点数管理（scores）テーブルの作成

create table scores(
   id INT(11) AUTO_INCREMENT NOT NULL,
   student_id INT(11) NOT NULL,
   subject VARCHAR(30) NOT NULL,
   score INT(3) NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (student_id) REFERENCES students(id)
);

②scoresテーブルにデータを登録

insert into school.scores
(student_id, subject, score)
values
(1, '国語', 80),
(2, '数学', 45),
(3, '国語', 40),
(4, '社会', 55);

③データが登録されているか確認

select * from scores;

では、実際にhaving句の動作を見てみましょう。

平均点が50点以上の教科を絞り込んで取得します。

select subject, avg(score) from scores group by subject having avg(score) >= 50;

where句の中では集計関数を記述することができないので、上の検索結果を得るためにwhere句に条件を指定することはできません。

この点が、having句とwhere句の最大の相違点となっています。

ここまで集計関数とともに使用するSQLの説明をしてきました。ここからは集計関数の紹介をしようと思います。

count関数

指定された項目の件数を取得することができます。

group byの説明の中で使用した以下のSQLでは、それぞれのclassに何人の生徒がいるかをカウントすることができます。

select class, count(*) from students group by class;

sum関数

指定された項目の合計を取得することができます。

項目の指定方法は、以下です。

select class, sum(sore) from students group by class;

avg関数

指定された項目の平均を取得することができます。

項目の指定方法は、以下です。

select class, avg(score) from students group by class;

max関数

指定された項目の最大値を取得することができます。

項目の指定方法は、以下です。

select class, max(score) from students group by class;

min関数

指定された項目の最小値を取得することができます。

項目の指定方法は、以下です。

select class, min(score) from students group by class;