SQLiteでデータベース(SQL)の基本を学ぶ(6)

データベース

こんにちは、ECF Techブログ
担当のMichiharu.Tです。

本記事はSQL文について、実際に動作させながら学習する入門記事の第6回となります。
今回は複数の行をまとめて扱うグループ化についてご紹介いたします。表結合も併用する内容となり、難易度が少し上がるかと思いますが、どうぞよろしくお願いいたします。

本記事の題材となっているデータベース内の各表、および学習の始め方については、下記の第1回の記事からご覧ください。

データベース
データベースのカテゴリです。トレーニングとして使えるSQLの入門記事などを連載しています。

集合関数

グループ化を学ぶ上で欠かせないのが集合関数です。まずは集合関数の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文は実行エラーとなります。

SQLiteの実行ではエラーになりませんが、意味のない表示結果となります。

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句についてご紹介する予定です。引き続き、よろしくお願いいたします。


合同会社イー・シー・エフでは、子ども向けプログラミングなどの教育講座を実施しています。プログラミング教室の案内や教育教材の情報、また関連するご相談・問い合わせにつきましては下記よりご確認ください。

ECFエデュケーション
キッズも大人も。沖縄からITのマナビを応援します
タイトルとURLをコピーしました