今回は初心者訓練所#03として、データ分析には欠かせない集計関数について説明していきます。よく使われる関数なので、是非、使い方をマスターしてください。
集計関数とは？
集計関数とは、ある項目に対して合計したり、平均値を求めたり、あるいはデータ件数を取得したり等、その名の通り集計を行う関数になります。
都度計算をする必要がないので、データ分析等に利用すると便利な関数です。
また、集計関数を利用する際に、項目をグループ単位で集計できる集計関数には無くてはならない、GROUP BY句の紹介も合わせて行っていきます。
５つの集計関数（COUNT, SUM, AVG, MAX, MIN）
今回は、基本的な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 ) |
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 ); |
select文で登録内容を確認してみましょう。
| select * from tbl_employees; |
insert文で作成した6件のデータを確認できます。
それでは、COUNT関数を使用してデータ件数を取得してみましょう。
| select COUNT( * ) from tbl_employees; |
登録したデータ件数の「6」が返されます。
次に、項目Xに「kana」を指定して実行してみましょう。
| select COUNT( kana ) from tbl_employees; |
結果は「5」となります。
登録したデータを確認してみると、emp_code = 4の「kana」の値が「NULL」となっています（selectの結果は何も表示されていませんが）。「NULL」はCOUNT関数の集計対象とならないのため、件数は「5」が返されます。
COUNT関数の説明は以上になります。
SUMの使用例
先ほど作成したテーブル「tbl_employees」を使用して確認しましょう。
項目Xに「age」を指定して実行してみます。
| select SUM( age ) from tbl_employees; |
結果は「age」の合計「174」が返されます。
SUM関数もCOUNT関数と同様にemp_code = 4のNULL値は対象外となります。
SUM関数の説明は以上になります。
AVGの使用例
SUM関数と同様にテーブル「tbl_employees」を使用して確認しましょう。
select文で再度、データを確認してみます。
| select * from tbl_employees; |
それでは、項目Xに「age」を指定してAVG関数を実行してみましょう。
| select AVG( age ) from tbl_employees; |
結果として「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; |
それぞれ、ageの最大値「45」、最小値「23」が取得されます。
MAX関数、MIN関数の説明は以上になります。
集計値のグルーピング（GROUP BY句）、絞り込み（HAVING句）
集計関数で集計した値をグルーピングするには、GROUP BY句を使用します。
また、グルーピング結果に条件を付けてデータを絞り込む場合は、HAVING句を使用します。
GROUP BY句
GROUP BY句に指定した項目が、同じ値をグループ化します。複数項目を指定した場合は、同じ値の組合せでグループ化します。
HAVING句
GROUP 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; |
emp_code = 4のデータが更新されていることが確認できます。
それでは、GROUP BY句を使用して性別「gender」毎の平均年齢「age」を取得してみましょう。
| select gender , AVG ( age ) from tbl_employees GROUP BY gender; |
それぞれ性別「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; |
iHAVING句を追加した二つ目のselect文では、追加した条件（人数が2名以上）のデータのみが表示されていることが確認できます。
GROUP BY句、HAVING句の説明は以上になります。
おすすめの学習コンテンツ
本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください！
SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
SQLiteとは？他のデータベースとの違いも解説
SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
「SQLの教科書」ビックデータ時代の必須科目
SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
TOPSIC-SQL無料トライアルテスト
SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
過去に開催されたコンテストの問題にもチャレンジできます。
TOTOPSIC SQL CONTEST
SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
SQL-BOOT CAMP #1
ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう！」
まとめ
本記事では、初心者訓練所の第3弾として集計関数の一部を紹介しました。集計関数はGROUP BY句と合わせて利用すると色々な分析に使える便利な関数です。是非、ご自身でもデータを作成して自主練してみてください。
それでは、次の初心者訓練所で更なるスキルアップを目指しましょう！
(株)システムインテグレータ 製品企画室 元鬼軍曹 久保 司
