データ分析に欠かせない「集計関数」について
(3)AVG関数
AVG関数は指定された項目Xの平均値を返します。ただし、項目の値がNULLの場合は除外して集計します。
下図の使用例の左側では「年齢」にNULL値がない場合の平均値を、右側では「年齢」にNULL値が存在した場合の平均値を取得しています。
SELECT文で再度、社員テーブルのデータを確認してみます。
SELECT * FROM tbl_employees;
項目Xに「age」を指定して年齢の平均値を取得してみましょう。
SELECT AVG( age ) FROM tbl_employees;
結果として、使用例の右側のNULLが存在した場合と同じ「34.8」→ [(40+33+45+33+23)÷5]が返されます。AVG関数も「age」がNULL値のemp_code = 4のデータは対象外となり、平均を求める際のデータ件数が5件となることに注意してください。
(4)MAX関数、MIN関数
MAX関数とMIN関数は、どちらも指定した項目の最大値と最小値を求める関数なので、合わせて説明します。
下図の使用例では、左と中央のSELECT文で「年齢」の最大値、最小値を取得しています。そして、右側の例では、条件式に「性別」を追加して最小値を取得しています。
それでは、それぞれの項目Xに「age」を指定した場合と、条件式を追加した場合でMAX関数、MIN関数を実行してみましょう。
SELECT MAX(age) , MIN(age) FROM tbl_employees; SELECT MAX(age) , MIN(age) FROM tbl_employees WHERE gender = 'F';
最大値と最小値を取得するので、もちろん「age」の値がNULLのデータは対象外となります。条件式を追加した場合は、条件に一致するデータ内での最大値、最小値が表示されていることも確認できます。
戦闘力アップ(GROUP BY句)
最後にGROUP BY句について説明します。集計関数で集計した値をグルーピングするには、下表にあるGROUP BY句を使用します。また、グルーピングした結果に条件を付けてデータを絞り込む場合はHAVING句を使用します。
ここでまた、社員テーブル「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のデータが更新されていることが確認できます。
(1)性別毎の平均年齢を求めてみよう!
下図の使用例では、GROUP BY句を使用して性別毎の平均年齢を求めています。
それでは、実際に確認してみましょう。
SELECT gender , AVG(age) FROM tbl_employees GROUP BY gender;
それぞれ性別「F」「M」の平均年齢が取得されています。
GROUP BY句を使用した場合、SELECT文の後に記述できる項目はGROUP BY句で使用した項目、または集計関数を使用した項目となるので注意してください。上記の場合はGROUP BY句で指定した項目「gender」と集計関数を使用した項目「age」になります。
(2)部署毎、性別毎の人数を集計してみよう!
次の使用例では、部署毎、性別毎に人数を集計しており、矢印の先の例ではHAVING句を使用して、集計した結果に条件を追加して表示する内容を絞り込んでいます。
使用例のように、始めに部署毎、性別毎の人数を集計した場合、次にHAVING句で条件を追加した場合をそれぞれ確認してみましょう。
SELECT dept_code , gender , COUNT(*) AS 人数 FROM tbl_employees GROUP BY dept_code , gender; SELECT dept_code , gender , COUNT(*) AS 人数 FROM tbl_employees GROUP BY dept_code , gender HAVING 人数 >= 2;
HAVING句を追加した2つ目のSELECT文では、追加した条件(人数が2名以上)のデータのみが表示されていることが確認できます。また、補足ですがCOUNT(*)をAS句で「人数」に置き換えて、HAVING句では置き換えた「人数」を使用しています。
おわりに
今回は、データ分析に欠かせない基本的な5つの集計関数と、集計関数にはなくてはならないGROUP BY句について説明しました。今後も、世の中ではより一層様々な情報が飛び交い、データ量も増大していくと思われます。ぜひ集計関数をマスターして、押し寄せる膨大なデータ群に立ち向かってください。
それでは、また次回のSQL-BOOTCAMPでお会いしましょう!
