SQL入門の3回目はgroup byによるグループ化です。大量のデータを整理するにはグループ化が欠かせません。グループ化を行うgroup by句と、それと組み合わせて利用される集計関数について説明します。
目次
group by
group by句は、列の値によってグループ化を行う命令です。以下のように記述します。
group by 列名
例えば、以下は性別（gender）の列の値でグループ化する例です。
SELECT gender FROM `sample.people` groupby gender
出力は以下の通りです。
male
female
gender列の値を2つのグループに分け、その値を表示しています。
このように、グループ分けに使用した列の値を取得するだけであれば、distinct句を使って列の一意な値を取得することと変わりません。group byを使うと、その列の値でテーブル全体をグループに分け、それぞれのグループに対して集計関数を使って、そのグループの特徴を抽出できることが大きな特徴です。
性別と年齢を含むテーブルにおいて、性別でグループ化するときのイメージを以下に示します。
group by句で、性別の値に応じて、グループに分けます。グループに分割した後は、それらの各グループを1行になるように集計します。列によってはいろいろな値が入っています。年齢であれば性別と関係なくバラバラな数値が格納されています。年齢はそのままでは1行にまとめられません。1行にまとめるための処理を行うのが集約関数です。
集約関数
以下は主な集約関数です。
- count = 個数を返す
- avg = 平均値を返す
- sum = 合計を返す
- max = 最大値を返す
- min = 最小値を返す
これらの集約関数を使えば、複数の値から1つの値を求める、すなわち複数の行の内容をまとめて１行にすることが可能になります。例えば、それぞれの性別の平均を求めるには以下のように平均を求めるavg関数を使用します。
SELECT gender, avg(dob_age) FROM `sample.people` groupby gender
出力は以下のようになります。
行 gender f0_
1 male 51.698529411764689
2 female 47.9912280701754
集計関数の列名にはデフォルトのf0_という値が割り当てられます。明示的に列名を指定する場合はasで名前を指定してください。
ここで、SELECTの後に指定する、列の名前としては、
- group by でグループ分けに使った列
- 集約関数
のどちらかでないとエラーになることに注意してください。
例えば、以下の例はエラーになります。
SELECT gender, dob_age FROM `sample.people` groupby gender
最後の”group by”に着目してください。gender列でグループ分けしています。dob_ageは行ごとに異なる値が格納されています。
- genderはgroup byで指定されているので、maleかfemaleの2行
- dob_ageはいろいろな年齢が格納されているので、複数の行
となってしまい、行の数がそろわないためです。
グループ分けした結果をテーブル形式として出力する場合、グループ分けされた値と同じ行数になるように、他の列は集計関数を使う必要があることに注意してください。
集計関数
集計でよく利用される関数を以下に説明します。
- 関数：avg(列や式)
- 概要：集計されたグループ内で NULL 以外の値の平均を返します
- 関数：max(列や式)
- 概要：集計されたグループ内で NULL 以外の最大値を返します。
- 関数：min(列や式)
- 概要：集計されたグループ内で NULL 以外の最小値を返します。
- 関数：sum(列や式)
- 概要：集計されたグループ内で NULL 以外の値の合計を返します。
- 関数：count(列や式)
- 概要：レコードの個数を返します
sum, min, max, avgなどは数値による計算を行うため、列の値は数値である必要があります。一方、countは単にレコード（行）の数を数えるだけなので、どのようなデータ型でも大丈夫です。単にレコード数を数えるために、「count(*)」と記述することも可能です。
複数列の組み合わせによる集計
group byは単一の列だけではなく、複数の列の組み合わせでグループ分けを行うことも可能です。単にgroup by の後ろにカンマで列を列挙します。
groupby 列1, 列2, ...
以下はpeopleテーブルで、性別と国番号の組み合わせでグループ化をした例です。それぞれのレコード数をcount関数で集計しています。
SELECT gender, nationality, count(*) as count
FROM `sample.people` groupby gender, nationality
orderby nationality
演習
ex-group1.py
peopleテーブルで、国別の平均年齢を求めてください。
ex-group2.py
park-dataテーブルで、Area_Name毎の平均滞在時間（Total_Time__in_minutes__if_available_）を求めてください。
ex-group3.py
peopleテーブルで、男女別の最大の年齢、最小の年齢を求めてください。結果は以下のようになります。
行 gender max_age min_age
1 male 78 21
2 female 78 22
ex-group4.py
park-dataでArea_IDごとの平均数（Number_of_Squirrels）を求めてください。出力はArea_IDの降順としてください。
ex-group5.py
park-dataでArea＿Nameごとに最大のEnd_Timeを求めてください。End_Timeの昇順にならべてください。
行 Area_Name end_time
1 BROOKLYN 3:50:00 PM
2 CENTRAL MANHATTAN 4:00:00 PM
3 UPPER MANHATTAN 4:15:00 PM
4 LOWER MANHATTAN 4:38:00 PM
ex-group6.py
squirrel-dataにおいて、エリアごとにどんな色(Primary_Fur_Color)のリスが何匹いるか集計してください。数値の大きい順にならべてください。nullのレコードは削除してください。10レコードになります。
1 CENTRAL MANHATTAN Gray 158
2 UPPER MANHATTAN Gray 123
3 LOWER MANHATTAN Gray 66
...
9 LOWER MANHATTAN Cinnamon 5
10 UPPER MANHATTAN Cinnamon 1
解答例
ex-group1.py
SELECT nationality, avg(dob_age) as ave_age FROM `sample.people`
groupby nationality orderby nationality
ex-group2.py
SELECT Area_Name, avg(Total_Time__in_minutes__if_available_) astimeFROM `sample.park-data` groupby Area_Name
ex-group3.py
SELECT gender, max(dob_age) as max_age, min(dob_age) as min_age
FROM `sample.people` groupby gender
ex-group4.py
SELECT Area_Name, avg(Number_of_Squirrels)
FROM `sample.park-data`
groupby Area_Name orderby Area_Name descex-group5.py
SELECT Area_Name, max(End_Time) as end_time
FROM `sample.park-data`
groupby Area_Name orderby end_time
ex-group6.py
SELECT Area_Name, Primary_Fur_Color, count(*) as count
FROM `sample.squirrel-data`
where Primary_Fur_Color isnotnullgroupby Area_Name, Primary_Fur_Color orderby count deschaving
グループした結果を絞り込みたいときにはhaving句を使用します。
- where = 元のテーブルの各行に対して条件を適用する
- having = グループ化を行ったあとの集計結果に対して条件を適用する
条件を指定するという点ではwhere句と同じなので混乱しやすいので注意が必要です。以下の例はwhereとhavingを適用した例です。
SQLのイメージは以下の通りです。もともとの住所録テーブルから、東京の住人だけを抽出し、その結果に対してgroup byで性別毎の平均年齢を計算し、その結果が50より大きいグループを抽出するイメージです。
select 性別, avg(年齢) as 平均年齢 from 住所録
where 住所="東京"
groupby 性別
having 平均年齢 >50
where句はもとのテーブルに適用され、それぞれの行ごとに条件が適用されます。一方、having句はグループ化が終わった後の結果に条件が適用されます。混乱しやすい点でもあるので注意してください。
演習
ex-having1.py
peopleテーブルで40才以上の人を選択し、Nationalittyt毎のグループに分けます。平均年齢が60以上のグループを抽出してください。以下のような結果としてください。
行 nationality age
1 0 62.409090909090914
2 1 60.777777777777764
3 4 61.7419354838
ex-having2.py
squirrel-dataでinteractions_with_Humansがnullの行は除外します。Park_Name毎に集計し、行の数が20より大きいPark_Nameを、レコード数が多い順に抽出してください。集計結果は以下の通りです。
行 count Park_Name
1 52 Tompkins Square Park
2 51 Washington Square Park
3 39 McCarren Park
4 25 Marcus Garvey Park
5 24 Riverside Park (Section Near Grant Memorial)
6 23 J. Hood Wright Park
ex-having3.py
squirrel-dataでLocation毎に集計して、レコード数をカウントします。そのカウントが40以上のLocationのみを列挙してください。その際カウントは降順で表示します。
行 count Location
1 220 Ground Plane
2 84 Above Ground
3 54 Above Ground, Specific Location
ex-having4.py
squirrel-dataにおいて、LocationとActivitiesの組み合わせでレコード数を集計してください。集計数は降順で並び替え、20以上の項目のみ表示します。
行 count Location Activities
1 89 Ground Plane Foraging
2 31 Above Ground Climbing
3 30 Ground Plane Running
4 24 Ground Plane Eating
解答例
ex-having1.py
SELECT nationality, avg(dob_age) as age FROM `sample.people`
where dob_age >=40groupby nationality
having age >=60orderby nationality
ex-having2.py
SELECTcount(*) as count, Park_Name FROM `sample.squirrel-data`
where interactions_with_Humans isnotnullgroupby Park_Name
having count >20orderby count descex-having3.py
SELECTcount(*) as count, Location FROM `sample.squirrel-data`
groupby Location
having count >=40orderby count descex-having4.py
SELECTcount(*) as count, Location, Activities FROM `sample.squirrel-data`
groupby Location, Activities
having count >=20orderby count desc