SQL データを学ぶ

【初心者向けSQL】データの集計をしてみよう【集計関数 / GROUP BY】

この記事では、下記の部分まで理解することを目指しましょう。

この記事の目標

  • 集計関数を使えるようになる(count,sum,avg)
  • GROUP BY の使い方を覚える

前回の講義で列の抽出はできるようになったけど、
これだけだとエクセルで列だけ切り出したほうがマシだわ!

ちょっとまって!笑
SQLは数行書くだけでエクセルだと手間のかかるような作業もできちゃうんだ。
今日は取り出した列を使って集計をしてみるよ!

ahilog

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが
本記事ではBigQuery環境を想定して執筆します。

このシリーズの記事はこちらです。

必要なデータを集計してみよう

集計とはなにか

データはそのままでは意味をなさないので
特徴を見やすくまとめる必要があります。

ahilog

犯罪が起きた記録だけあっても、
その地域の治安ってわからないですよね。

例えば地域の治安の良し悪しを分析するのであれば
犯罪記録から

  • 発生件数の合計
  • 1日あたりの発生数の平均

などを調べると思います。

データを加工して、その特徴をわかりやすくまとめる作業を「集計」といいます。

おなじみ「合計」や「平均」などが集計にあたりますね。

次は、SQLでどうやって集計をするかを解説していきます。

SQLでデータを集計してみよう

ahilog

前回やったSELECT FROMの使い方は覚えてる?

SELECTの後にほしい列を書いて、
FROMの後にデータの居場所を書くんだよね!

ahilog

その通り!よく勉強しているね。
実はSELECTの後に列だけじゃなくて、
「合計」や「平均」を指定することもできるんだ。

前回の記事では、table.1のようなデータを使って、
必要な情報(名前と身長と体重)を抽出しました。

名前社員番号身長体重年齢趣味
相葉くん11504525音楽
伊藤さん21605033釣り
上野くん31728235サッカー
江頭さん41544540野球
小山さん51665125ゲーム
table.1:社員のプロフィール
SELECT 名前,身長,体重
FROM table1

これを少し加工してあげることで
名簿の件数を計算する場合は、

SELECT count(名前)
FROM table1

と書くことができちゃうんです。

答えは「5」になります。

count()は集計関数と呼ばれるものです。

中身に欲しい物を入れてあげることで、その件数を数えてくれます。

年齢の平均を知りたいのであれば、avg()関数を使ってこう書きます。

SELECT avg(年齢)
FROM table1

この場合は答えは「31.6」ですね。

他にも数字の合計を計算するsum()など
色々便利な集計関数があるので、気になった人は是非調べてください!

SQLのコマンドは、このサイトが色々まとまっていてとても便利です。
逆引きSQL構文集

「〜ごとに」をあらわす「GROUP BY」の紹介

何か分析して傾向がみたい という場合
セグメントごとに集計することがあります。

ahilog

例えばマーケティングや営業の仕事をしていると
「性年代別」や「都道府県別」などに
成果を確認したいとき
がありますよね。

そんな時に使うのが「GROUP BY」構文です。
順を追って説明しますね。

先程の社員のプロフィールに性別を追加してみました。

名前出席番号性別身長体重年齢趣味
相葉くん1女性1504525音楽
伊藤さん2女性1605033釣り
上野くん3男性1728235サッカー
江頭さん4女性1544540野球
小山さん5男性1665125ゲーム
table1.2 社員のプロフィール(性別追加)

GROUP BYは最後にFROMの次に書きます。

性別ごとに平均身長を集計するのであれば
こんな感じになります。

SELECT avg(身長)
FROM table1
GROUP BY 性別

集計結果はこのようになります。

col_0
169
154.6

あれれ、集計結果はちゃんと出力されているみたいだけど
どちらの性別の結果かわからない・・!
しかもcol_0って何なの・・・?

SQLくん

とりあえず集計だけはしたったで!
集計結果しかSELECTには書いてへんかったからな!
あと集計結果の列名も適当につけといたで!

SQLは指定した通りに動くから、
丁寧に書いてあげないと書いたことしかやってくれないんだよ。。

ahilog
SELECT avg(身長)
FROM table1
GROUP BY 性別

このSQLには2つの欠点があります。

みなさんおわかりでしょうか・・?

  • SELECT文の中に「性別」の列を指定していない
  • avg(身長)の列名が定義されていない

人間だったなら、気を利かせて適当にラベルをつけてくれるのですが
SQLの場合はそうはいきません。

そのため、このように書きなおしましょう。

SELECT 性別, avg(身長) as 平均身長
FROM table1
GROUP BY 性別

as 〇〇 と書くことでその列に名前をつけることが出来ます。
性別も集計結果に出してほしいので追加しましょう。

その結果がこちらです。

性別 平均身長
男性 169
女性 154.6

無事に性別ごとに平均身長が出力されました。

GROUP BY は複数の列を指定することも可能です。
例えば性別・年齢別に集計をしたい場合は GROUP BY 性別,年齢 と書くことができます。

BigQueryでやってみよう

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが
本記事ではBigQuery環境を想定して執筆します。

使用するデータについて

BigQueryにtitanicのデータを格納しており、
こちらをもとに分析します。

データはtitanic_sample というフォルダの中に
titanic_logという名前で格納してあります。

ahilog

この場合、FROM titanic_sample.titanic_log と「.」をつけて
フォルダ名を書く必要があるので注意しましょう。
大抵の場合、データは何かしらのフォルダにまとめて置かれています。

乗客の性別ごとに人数を数えよう

今回はsex(性別)の列を使って、性別ごとに乗客の数を数えてみましょう。
乗客の数を数えるので、count()の集計関数を使います。

問題は何を数えるか ですよね。

結論から言うと、
今回のデータは行数 = 人数になっているので何を数えても問題ありません!

ahilog

情報にダブりがない状態を「ユニーク」といいます。
1行1人の情報にユニーク化されているので、
何を数えても人数になるんですね。

今回は乗船者一人ずつに振り分けられているPassengeridを使いましょう。

実際に書くとこんな感じになります。

select
sex,count(Passengerid) as cnt 
from 
`ahilog-analysis.titanic_sample.titanic_log`
group by sex

いかがでしたでしょうか。

今回は集計関数の使い方と、
さらに細かく集計するのに便利なGROUP BYについて取り扱いました。

皆さんのSQLライフが充実することを願ってます!

より体系的にガッツリと勉強したい方にはUdemyがおすすめです!
リッチな動画コンテンツで豊富な知識を手に入れましょう。

キャリアアップに必要な知識を身につけよう | Udemyオンライン動画コース

  • この記事を書いた人

yutasukun

京都橘高校からの史上初東大受験・現役合格を成し遂げ 「リアルドラゴン桜」として横断幕が出たことアリ。 そんな過去の栄光も虚しく10年が経過。 今はデータアナリストとして汐留の広告代理店で バリバリはたらく毎日。

-SQL, データを学ぶ

© 2021 ahilog Powered by AFFINGER5