SQL データを学ぶ

【初心者向けSQL】条件句を使いこなそう【WHERE / HAVING】

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

この記事の目標

  • 検索条件を指定したデータ抽出ができるようになる
  • WHERE句とHAVING句の違いを理解する

データ全部じゃなくて、一部のデータだけ抽出したいの。
なにかいい方法はないのかな。

現場の分析では特定の期間に絞ったり、
条件を満たしたユーザーだけ分析することが多いからね。
今日は集計に条件を指定する方法の解説をするよ!

ahilog

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

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

検索条件の指定方法

WHERE句:条件を満たした行のみを抽出して集計する

前回の記事で使った社員のプロフィールを再掲しますね。

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

前回の記事と同じように、性別ごとに平均年齢を集計すると
SQLはこんな感じになりますね。

SELECT 性別,avg(年齢) as 平均年齢
FROM table1
GROUP BY 性別

これだと性別ごとに「全員」の平均年齢となります。

今回は、もう少し発展させて
身長が160cm以上の社員だけを対象に平均年齢を集計することを考えましょう。

集計をする前に、160cm以上の人のデータに絞るのかな・・?
どうやってやればいいんだろう。

その通り!筋が良いね!
条件を満たしたデータの行だけに絞る操作を
WHERE句で指定できるんだ。

ahilog

WHERE句はFROMとGROUP BYの間に入る決まりなので
追記してみるとこんな感じになります。

SELECT 性別,avg(年齢) as 平均年齢
FROM table1
WHERE 身長 >= 160
GROUP BY 性別

WHERE句で身長が160cm以上のデータに絞ってから
SELECTに書いている集計が行われるので、

名前 出席番号 性別 身長 体重 年齢 趣味
伊藤さん 2 女性 160 50 33 釣り
上野くん 3 男性 172 82 35 サッカー
小山さん 5 男性 166 51 25 ゲーム

身長160cm以上の3人だけをWHERE句で抜き出して
集計が行われます。

結果は下記のようになります。

性別 平均年齢
男性 30
女性 33
ahilog

データアナリストはとても大きいデータを扱います。
特定のキャンペーン期間に絞る時
申し込み完了したユーザーに絞る時など

頻繁にWHERE句で対象を絞る作業を行います。

HAVING句:集計結果の中から条件を満たした行だけ抽出する

「集計結果から条件を満たした行」って
さっきやったWHEREとどう違うの・・?

集計前にデータを絞るか、集計後にデータを絞るかの違いなんだけど
ここでつまずく人が多いんだ。ゆっくり說明していくね。

ahilog

先程のWHERE句では、身長が160cm以上の社員だけを対象に
平均年齢を集計しました。

性別 平均年齢
男性 30
女性 33

この時、性別だけだと男性・女性の2行の出力になりますが
100行・1000行とあり、見たいデータが一部の時はどうでしょうか?

1000行もあって、欲しいデータが一部だったら
それを探さないといけないってこと??
めんどくさい・・・

分析するときは必要なデータだけ出力したほうが
時短になってお得です。
そんな時にHAVINGを使うんです。

ahilog

HAVING句は集計が終わった後に動作するので、
GROUP BYの後に書きます。

例えば、平均年齢が30歳以下の結果だけ出力したい場合
こんな感じになります。

SELECT 性別,avg(年齢) as 平均年齢
FROM table1
WHERE 身長 >= 160
GROUP BY 性別
HAVING 平均年齢 <= 30
性別 平均年齢
男性 30
ahilog

HAVINGで絞らずに全部出力しておいて、
後で分析する時にExcelなり、Pythonでフィルタしてもいいんですどね。
SQLで事前に処理しておくと効率的なこともあります。

まとめ:検索条件の指定方法の違い(WHERE句とHAVING句)

もう一度違いをまとめておきますね。

ここがポイント

  • WHERE句:条件を満たした行だけに絞って集計する時に使う(GROUP BYの前)
  • HAVING句:集計結果から条件を満たした行だけを出力する際に使う(GROUP BYの後)

僕がよく使うのはWHERE句です。

なぜなら、WHERE句がないと出せない集計結果があるからですね。

HAVING句はあとでExcelでフィルタをかけたりしても大丈夫なので
代用が聞きます。


ahilog

データ分析をするときには、
最初に思っていた仮説と違うことが多々あります。
HAVINGでデータを絞ってしまっていると
SQLを修正するところからやり直しになってしまうので
僕はあまり使っていません。

例えば、HAVINGで男性の結果だけに絞ってデータ出力をしてから
ツールで分析したとします。

やっぱり女性の分析がしたい!となると、またやり直しなんですよね。

それなら男女両方の結果を出しておいて、
手元の分析ツールで切り替えたほうが効率的
 ということです!
※もちろん場合にもよります。

BigQueryでやってみよう

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

使用するデータについて

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

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

ahilog

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

【課題1】チケットの種類別に男性の生存者数を計算しよう

チケットの種類はPclass・性別はsexなので
Pclassとsex別に生存者数を計算することを考えます。

生存者数ってどうやって数えましょう?

実はコレ、やり方が2通りありまして、

  • Survived = 1のPassengeridの数を集計する
  • Survivedのフラグ(生存なら1・死亡なら0)の合計を集計する

どっちでも正解なんですね。

Survived = 1のPassengeridを集計する場合

where句にSurvived = 1を入れることで集計前に生存者のデータに絞り
count(Passengerid)を使って行数を数えます。

select 
Pclass,count(Passengerid) as cnt
from 
`ahilog-analysis.titanic_sample.titanic_log`
where 
Survived = 1 and sex = "male"
group by Pclass

Survivedのフラグ(生存なら1・死亡なら0)の合計を集計する

生存なら1・死亡なら0になっているので
Survivedフラグの合計を計算すれば生存者数になるんですね。

select 
Pclass,sum(Survived) as cnt
from 
`ahilog-analysis.titanic_sample.titanic_log`
where 
sex = "male"
group by Pclass

ちなみに、Pclassの順番が3,1,2になっているの、気持ち悪いですよね。

SQLくん

順番きれいに並べてなんて、
言われてへんで!
やってほしいことは先に言うといてや!

ahilog

揃えたい場合はORDER BY句を書くといいですよ。

ORDER BY は一番最後に書きます。
並び替えるだけなので結果に影響しません。

select 
Pclass,sum(Survived) as cnt
from 
`ahilog-analysis.titanic_sample.titanic_log`
where 
sex = "male"
group by Pclass

ORDER BY 〇〇で、昇順に並び替えることができます。
ORDER BY 〇〇 DESCと書くと、降順になります。

【課題2】課題1の集計結果から40人以上の生存者数のものを抜き出そう

集計した後のデータから条件にあったものを選ぶので
こちらはHAVINGを使います。

select 
Pclass,sum(Survived) as cnt
from 
`ahilog-analysis.titanic_sample.titanic_log`
where 
sex = "male"
having
cnt > = 40
group by Pclass

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

WHERE句とHAVING句を使えれば、
色々な条件にあったデータを抽出することができるようになります。

ahilog

セールス職の方であれば、優良顧客にしぼってリストを出力したり
マーケティング職なら、CV者に絞ってリマケリスト作ったりできますね。

SQLは手順を間違えなければ、簡単に覚えることが出来ます!
この調子でデータ分析を自分でできるようになっていきましょう。

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

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

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

  • この記事を書いた人

yutasukun

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

-SQL, データを学ぶ

© 2021 ahilog Powered by AFFINGER5