SQLの集計関数とは?集計関数を活用してデータ分析

 2022.06.27  株式会社システムインテグレータ

今回は初心者訓練所#03として、データ分析には欠かせない集計関数について説明していきます。よく使われる関数なので、是非、使い方をマスターしてください。

集計関数とは? 

集計関数とは、ある項目に対して合計したり、平均値を求めたり、あるいはデータ件数を取得したり等、その名の通り集計を行う関数になります。
都度計算をする必要がないので、データ分析等に利用すると便利な関数です。
また、集計関数を利用する際に、項目をグループ単位で集計できる集計関数には無くてはならない、GROUP BY句の紹介も合わせて行っていきます。

便利な集計関数の紹介

今回は、基本的な5つの集計関数を紹介していきます。

COUNT関数

COUNT関数は指定された項目Xの件数を返します。
ただし、項目Xの値がNULLの場合は件数から除外されます。
また、項目Xに「*」(アスタリスク)を指定した場合は、テーブルの行数を返します。

COUNT ( 項目X )

SUM関数

SUM関数は指定された項目Xの合計を返します。
ただし、項目Xの値がNULLの場合は除外して集計します。

SUM ( 項目X  )

AVG関数

AVG関数は指定された項目Xの平均値を返します。
ただし、項目Xの値がNULLの場合は除外して集計します。

AVG ( 項目X )

MAX関数

MAX関数は、指定された項目Xの最大値を返します。

MAX ( 項目X )

MIN関数

MIN関数は、指定された項目Xの最小値を返します。

MIN ( 項目X )

集計関数の使用例

それでは、実際にそれぞれの関数について確認していきましょう。
インストールが簡単なデータベース、SQLiteを使用して説明していきます。

COUNT関数

準備として、確認用のテーブルを作成してデータを挿入してみましょう。

create table tbl_employees (emp_code integer , emp_name text , kana text , gender text , age integer , dept_code integer);
insert into tbl_employees values ( 1 , 'HoriiTaro' , 'ホリイタロウ' , 'M' ,40 , 1 );
insert into tbl_employees values ( 2 , 'IshimuraHana' , 'イシムラハナ' , 'F' , 33 , 2 );
insert into tbl_employees values ( 3 , 'KitajimaRuna' , 'キタジマルナ' , 'F' , 45 , 3 );
insert into tbl_employees values ( 4 , 'MuroiYuri' , NULL ,'F' , NULL , 1 );
insert into tbl_employees values ( 5 , 'KitazawaAika' , 'キタザワアイカ' , 'F' ,33 , 1 );
insert into tbl_employees values ( 6 , 'MitaniKoukiti' , 'ミタニコウキチ' , 'M' , 23 , 2 );

sql-03-01

select文で登録内容を確認してみましょう。

select * from tbl_employees;

sql-03-02

insert文で作成した6件のデータを確認できます。

それでは、COUNT関数を使用してデータ件数を取得してみましょう。

select COUNT( * ) from tbl_employees;

sql-03-03

登録したデータ件数の「6」が返されます。

次に、項目Xに「kana」を指定して実行してみましょう。

select COUNT( kana ) from tbl_employees;

sql-03-04

結果は「5」となります。
登録したデータを確認してみると、emp_code = 4の「kana」の値が「NULL」となっています(selectの結果は何も表示されていませんが)。「NULL」はCOUNT関数の集計対象とならないのため、件数は「5」が返されます。

COUNT関数の説明は以上になります。 

SUM関数

先ほど作成したテーブル「tbl_employees」を使用して確認しましょう。
項目Xに「age」を指定して実行してみます。

select SUM( age ) from tbl_employees;
 sql-03-05

結果は「age」の合計「174」が返されます。
SUM関数もCOUNT関数と同様にemp_code = 4のNULL値は対象外となります。

SUM関数の説明は以上になります。 

AVG関数

SUM関数と同様にテーブル「tbl_employees」を使用して確認しましょう。
select文で再度、データを確認してみます。

select * from tbl_employees;

sql-03-06

それでは、項目Xに「age」を指定してAVG関数を実行してみましょう。

select AVG( age ) from tbl_employees;

sql-03-07

結果として「34.8」→〔(40+33+45+33+23)÷5〕が返されます。
AVG関数も「age」がNULL値のemp_code = 4のデータは対象外となり、平均を求める際のデータ件数が5件となることに注意してください。

AVG関数の説明は以上になります。

MAX関数、MIN関数

MAX関数とMIN関数は、最大値と最小値を求める関数なので同時に説明していきます。
それでは、それぞれの項目Xに「age」を指定してMAX関数、MIN関数を実行してみましょう。

select MAX ( age ) , MIN ( age )  from tbl_employees;

sql-03-08

それぞれ、ageの最大値「45」、最小値「23」が取得されます。

MAX関数、MIN関数の説明は以上になります。

集計値のグルーピングについて

集計関数で集計した値をグルーピングするには、GROUP BY句を使用します。
また、グルーピング結果に条件を付けてデータを絞り込む場合は、HAVING句を使用します。

GROUP BY句

GROUP BY句に指定した項目が、同じ値をグループ化します。複数項目を指定した場合は、同じ値の組合せでグループ化します。

HAVING句

GOROUP BY句でグループ化された結果に条件式が適用されます。
条件式に指定できるのは、グループ化に指定した項目や、関数で集計したグループ単位の結果のみになります。

GROUP BY句とHAVING句の書式は以下になります。 

SELECT 項目X , 項目Y , ・・・ FROM テーブル名
GROUP BY 項目X , 項目Y ・・・
HAVING 条件式;

GROUP BY句を使ってみましょう

今迄使用したテーブル「tbl_employees」を更新して確認の準備をしましょう。
update文を使用して、emp_code = 4のそれぞれNULLになっていた項目「kana」「age」に値を設定して、select文で更新内容を確認してみましょう。

update tbl_employees set kana =  'ムロイユリ' , age = 27 where emp_code = 4;
select * from tbl_employees;

sql-03-09

emp_code = 4のデータが更新されていることが確認できます。

それでは、GROUP BY句を使用して性別「gender」毎の平均年齢「age」を取得してみましょう。

select gender , AVG ( age ) from tbl_employees GROUP BY gender;

sql-03-10

それぞれ性別「F」「M」の平均年齢が取得されています。

※select文の後に記述できる項目は、GROUP BY句で使用した項目または、集計関数を使用した項目となりますので注意してください。上記の場合は。GROUP BY句で指定した項目「gender」、集計関数を使用した項目「AVG(age)」となります。

HAVING句を使ってみましょう

最後に、HAVING句を使ってGROUP BY句で集計したデータを条件式で絞り込んでみましょう。
「tbl_employees」から部署「dept_code」毎のデータ件数(人数)を集計して、集計した人数が2名以上の部署を表示してみましょう。

初めにGROUP BY句のみでselectした場合、次にHAVING句で条件を追加した場合で表示してみましょう。

sselect dept_code , COUNT ( * ) from tbl_employees
GROUP BY dept_code ;
select dept_code , COUNT ( * ) from tbl_employees
GROUP BY dept_code HAVING COUNT ( * ) > 1;

sql-03-11

iHAVING句を追加した二つ目のselect文では、追加した条件(人数が2名以上)のデータのみが表示されていることが確認できます。

GROUP BY句、HAVING句の説明は以上になります。

おすすめの学習コンテンツ

本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください!

SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
 SQLiteとは?他のデータベースとの違いも解説

SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
 「SQLの教科書」ビックデータ時代の必須科目

SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
 TOPSIC-SQL無料トライアルテスト

SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
 SQL-BOOT CAMP #1 
 ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう!」

まとめ

本記事では、初心者訓練所の第3弾として集計関数の一部を紹介しました。集計関数はGROUP BY句と合わせて利用すると色々な分析に使える便利な関数です。是非、ご自身でもデータを作成して自主練してみてください。
それでは、次の初心者訓練所で更なるスキルアップを目指しましょう!

(株)システムインテグレータ 製品企画室 元鬼軍曹 久保 司

5分でわかる!TOPSIC-SQL

RELATED POST関連記事


RECENT POST「SQL‐BOOTCAMP」の最新記事


SQL‐BOOTCAMP

SQLの算術関数とは?便利な関数とその使い方について解説

SQL‐BOOTCAMP

SQLの文字列関数とは?よく利用される関数とその使い方について解説

SQLの集計関数とは?集計関数を活用してデータ分析

TOPSIC TOPへ