こんにちは、ECF Techブログ
担当のMichiharu.Tです。
本記事はSQL文について、実際に動作させながら学習する入門記事の第6回となります。
今回は複数の行をまとめて扱うグループ化についてご紹介いたします。表結合も併用する内容となり、難易度が少し上がるかと思いますが、どうぞよろしくお願いいたします。
本記事の題材となっているデータベース内の各表、および学習の始め方については、下記の第1回の記事からご覧ください。
集合関数
グループ化を学ぶ上で欠かせないのが集合関数です。まずは集合関数の1つであるcount関数を使ってみましょう。
実行例1
SELECT count(*) FROM items;
実行結果
count(*) -------- 11
countはデータの個数を返してくれる関数です。count(*)と書くと、その表の行数を返してくれます。
関数について
ここで関数について簡単に説明します。一言で説明すると、 ある特定の処理を行なってくれる機能 のことです。関数を利用することを「関数を呼び出す」と言います。
ここでは例として、「足し算関数」というものがあるものとして説明したいと思います。足し算関数は、「2つの値を与えると、その2つの値を加算し、答えを教えてくれる」機能をもった装置のようなものと考えてください。
では、この足し算関数を利用することを考えましょう。まず、関数を呼び出す際に2つの値を与える必要があります。この 関数を利用するときに与える値 を 「引数」 と呼びます。
足し算関数は2つの値を受け取ると、足し算の処理をして結果を呼び出した側に返してくれます。これを「戻り値」と呼びます。下の図は関数呼び出しのイメージです。
SQL文で関数を呼び出す際の基本形式は次のようになります。
関数名(引数)
集合関数も関数の一種です。実行例1では、countの引数として * を指定しています。count関数は引数に*を受け取ると、表の行数を返してくれます。また、引数に列名を指定するとその列のデータの個数を返してくれます。実行例で見てみましょう。
実行例2
SELECT count(cid) FROM items;
実行結果
count(cid) ---------- 10
countの引数に列名を指定すると、該当列がNULL以外のものの件数を返してくれます。items表にはcidがNULLの行が1つあるため、実行結果はcount(*)を使って行数表示をした時よりも1つ少ない「10」となっています。
代表的な集合関数
SQLで一般的に使用できる集合関数をご紹介します。
| 関数名 | 使い方 | 処理内容 |
|---|---|---|
| count | count(*) | 表の行数を取得 |
| count(列名) | 指定列のデータ数を取得(NULLはカウントされない) | |
| max | max(列名) | 列値の中で最大の値を取得 |
| min | min(れつめい) | 列値の中で最小の値を取得 |
| sum | sum(列名) | 列値の合計を取得 |
| avg | avg(列名) | 列値の平均を取得 |
それぞれ実行例を見ていきましょう。
実行例3
商品表(items)の中から最も高額な商品の金額(price)を表示する。
SELECT max(price) FROM items ;
実行結果
max(price) ---------- 100000
実行例4
ユーザー表(users)の中から、最も年齢の若いユーザーの年齢(age)を表示する。
SELECT min(age) FROM users ;
実行結果
min(age) -------- 25
実行例5
ユーザー表(users)の中から、年齢(age)の平均値を表示する。
SELECT avg(age) FROM users ;
実行結果
avg(age) ---------------- 31.8333333333333
実行例6
注文明細表(order_details)から、数量(amount)の合計を表示する。
SELECT sum(amount) FROM order_details ;
実行結果
sum(amount) ----------- 68
集合関数の実行結果は、列別名を用いて表示を変更することもできます。
実行例7
実行例6の実行結果における、「sum(amount)」の部分に列別名を指定して表示する。
SELECT sum(amount) as gokei FROM order_details ;
実行結果
gokei ----------- 68
集合関数の注意点
集合関数は表の複数行をまとめて取りあつかう関数です。そのため、SELECT句に集合関数と通常の列を単純に並べて記述することはできません。例えば次のような例です。
実行例8
SELECT name, count(*) FROM items ;
このSQL文は実行エラーとなります。
GROUP BY句
次は集合関数と共に使用されるGROUP BY句について見ていきましょう。GROUP BY句を使うことで、 「～ごとに」 という表現が可能です。たとえば、
- 商品表(items)から、 分類ID(cid)ごと に最も高額な商品を表示する
- 注文明細表(order_details)から、 ユーザーID(uid)ごと の注文件数を表示する
といったことが可能となります。また、このように「～ごとに」といったグループにまとめることを グループ化 と言います。GROUP BY句の文法は次のようになります。
GROUP BY 列名1, 列名2...
いくつかの実行例を見ながら学んでいきましょう。
実行例9
商品表(items)から、分類ID(cid)ごとに価格(price)の最高値を求め、分類IDと共に表示する。
SELECT cid, max(price) FROM items WHERE cid IS NOT NULL GROUP BY cid ;
実行結果
cid max(price) --- ---------- 1 100000 2 1200 3 25000
この実行例では下のように、分類IDごとに最大値を求めて取得しています。また、分類IDがNULLの行はWHERE句を用いてあらかじめ対象から除外しています。動作イメージは下のようになります。
GROUP BY句を使用するにあたり、下の2点をおさえておきましょう。
- ここまで学習した句（SELECT,FROM,WHERE）より下に記述します。
- GROUP BY句に記述した列は、SELECT句に記述することができます。
GROUP BY句に指定した列はSELECT句に使えるので利用性が高まります。次の実行例をご覧ください。
実行例10
注文明細表(order_details)と商品表(items)を結合し、商品番号(iid)ごとにまとめ、商品名(name)と注文数量の合計を表示します。
SELECT
i.name,
sum(od.amount)
FROM
order_details od
JOIN items i
ON od.iid = i.iid
GROUP BY
i.iid,
i.name
;
実行結果
name sum(od.amount) ------------ -------------- Desktop PC 2 Note PC 12 Mouse 22 Keyboard 11 USB Memory 3 LAN Cable 10 USB Cable 5 Mother Board 2 LAN Card 1
SQL文で注目すべき点は、GROUP BY句に列を2つ指定していることです。商品ID(iid)ごとにグループにできれば問題ないので、GROUP BY句に指定するのは iid列だけで良さそうです。ですが、集合関数を使用する場合、SELECT句に記述できる列は GROUP BY句に指定したもの に限られます。したがって、name列もGROUP BY句に指定しています。
HAVING句
次はHAVING句です。HAVING句はグループ化された行に対する条件を指定し、該当行を絞り込むことができます。文法は次のようになります。
HAVING 条件式
実行例10を元に使用してみましょう。
実行例11
注文明細表(order_details)と商品表(items)を結合し、商品番号(iid)ごとにまとめ、 注文数量が10以上の商品 について、商品名(name)と注文数量を表示します。
SELECT
i.name,
sum(od.amount)
FROM
order_details od
JOIN items i
ON od.iid = i.iid
GROUP BY
i.iid,
i.name
HAVING
sum(od.amount) >= 10
;
実行結果
name sum(od.amount) --------- -------------- Note PC 12 Mouse 22 Keyboard 11 LAN Cable 10
GROUP BY句までの内容は実行例10と同じです。今回はHAVING句で「注文数量の合計が10以上」という条件を追加していますので、上記のような実行結果となっています。数量の合計に対して条件を指定したいので、 HAVING句の条件式で集合関数を用いています。
下に実行例10・11を元にしたGROUP BY句、HAVING句のイメージをまとめています。
練習問題
それではここから練習問題です。
問1
ユーザー表(users)から、最も高い年齢を表示してください。
実行結果
max(age) -------- 44
問2
注文表(orders)から、ユーザーID(uid)ごとのレコード件数（行数）を実行結果のように表示してください。
uid kensu --- ----- 1 2 2 3 3 1 4 2 6 2
問3
商品表(items)と分類表(categories)を結合し、分類ID(cid)ごとにまとめ、それぞれの商品数を実行結果のように表示してください。
実行結果
name kosu -------- ---- PC 3 Supply 10 PC Parts 6
問4
注文表(orders)と注文明細表(order_details)を結合し、注文日(odate)ごとにまとめ、注文数量の合計が20個以上の注文日を下記のように表示してください。
実行結果
odate sum(amount) ---------- ----------- 2021-04-30 20 2021-05-20 20
問5（応用問題）
注文表(orders)と商品表(order_details)を結合し、商品ごとの注文金額の合計を求め、実行結果のように表示してください。注文金額の合計は商品の価格(price)×注文数量(amout)の合計値とします。
実行結果
name total ------------ ------- Desktop PC 200000 Note PC 1020000 Mouse 22000 Keyboard 13200 USB Memory 2400 LAN Cable 5000 USB Cable 1500 Mother Board 50000 LAN Card 3000
練習問題(解答例)
それでは、練習問題の解答例です。
問1
SELECT max(age) FROM users ;
問2
SELECT uid, count(*) AS kensu FROM orders GROUP BY uid ;
問3
SELECT
c.name,
sum(c.cid) as kosu
FROM
items i
JOIN categories c
ON i.cid = c.cid
GROUP BY
c.cid, c.name
;
問4
SELECT
odate,
sum(amount)
FROM
orders o
JOIN order_details od
ON o.oid = od.oid
GROUP BY
odate
HAVING
sum(amount) >= 20
;
問5
SELECT
i.name, sum(i.price * od.amount) AS total
FROM
order_details od
JOIN items i
ON od.iid = i.iid
GROUP BY
i.iid, i.name
;
おわりに
本日は以上とさせていただきます。最後までご覧くださりありがとうございます。GROUP BY句のポイントは「～～ごとにまとめる」です。集合関数と合わせて活用していきましょう。次回はORDER BY句についてご紹介する予定です。引き続き、よろしくお願いいたします。
合同会社イー・シー・エフでは、子ども向けプログラミングなどの教育講座を実施しています。プログラミング教室の案内や教育教材の情報、また関連するご相談・問い合わせにつきましては下記よりご確認ください。
