1. 集計関数とは?
集計関数は、1つの列に対して操作することができる演算機能をいう。
主な集計関数は以下の通り
・AVG関数
→ グループごとの値の平均を返す関数
・SUM関数
→ グループごとの合計の値を返す関数
・MAX関数
→ グループごとの最大の値を返す関数
・MIN関数
→ グループごとの最小の値を返す関数
・COUNT関数
→ グループごとの項目数を返す関数
よく使う関数としたは、上記の関数である。
2. SQLを使って操作をしてみよう
では早速SQLを操作して集計関数を使って色々試してみる
まずは新しいデータベースを作成する
今回は新しく shukei_testデータベースを作成する。
以下のコマンドを実行
create database shukei_test;
このように表示されたらデータベースが正常に作成されている。
作成したデータベース(shuke_test)があるか確認する。
show databases;
shukei_testデータベースがあることを確認できた。
では新しく作成した shuke_testデータベースを使う
下記コマンドを実行
use shukei_test;
これで shukei_test データベースを使うことができる
ではここから新しいテーブルを作成する
今回は productテーブルを作成する
下記コマンドを実行する
create table product (id int(10), name varchar(20), count int(10), type varchar(20));
今回は、idカラム、nameカラム、countカラム、typeカラムを作成
これでテーブル自体の作成をしたので、次に実際にデータを追加する。
insert into product (id, name, count, type) values (1, '作業机', 10, '家具'), (2, '作業用椅子', 13, '家具'), (3, '炊飯器', 15, '家電'), (4, '電子レンジ', 17, '家電'), (5, '布団', 15, '家具');
これで productテーブルが完成した。
ではここから集計関数の操作をしていく
集計関数を使うときは、GROUP BY句と一緒に使う
2-1. 1 AVG関数を使い、 typeごとのcountカラムの平均値を取得する
select type, avg(count) from product group by type;
このように グループ化して集計関数を使うことで、家具と家電に分類して countカラムの平均値を取得することができた。
2-2. 2 SUM関数を使い、 typeごとの総数を取得する
select type, sum(count) from product group by type;
それぞれ typeごとにグループ化して総数を取得することができた。
2-3. 3 MAX関数を使い、 typeごとの一番多い count を取得する
select type, max(count) from product group by type;
それぞれをグループ化して、typeごとの最大のcount数を取得することができた。
2-4. 4 MIN関数を使い、typeごとの最小数を取得する
select type, min(count) from product group by type;
それぞれグループ化して、typeごとの最小のcount数を取得することができた
2-5. 5 SUM関数を使い、 typeごとの合計を取得する
select type, sum(count) from product group by type;
それぞれグループ化して、typeごとの合計を取得することができた。
2-6. 6 HAVING句を使い、条件を絞って集計関数を使用する
HAVING句を使うことで、グループ化が実行された後に条件を絞り込むことができる。
実際に操作をしてみる
select type, count(count) as count_stock from product group by type having count > 10;
上記のSQL文を実行した時にエラーが生じてしまった。
エラーの内容
ERROR 1054 (42S22): Unknown column 'count' in 'having clause'
countカラムが重複していることが原因
countカラムを別の名前のカラムに変更する
カラム名を変更するには、ALTER TABLE 文を使う
カラム名の変更の構文
ALTER TABLE テーブル名
RENAME COLUMN 古いカラム名 TO 新しいカラム名
それでは、 countカラムを stockカラムに変更する
alter table product rename column count to stock;
productテーブルを確認すると、 countカラムがstockカラムに変更されていることを確認できた。
再度having句で条件を指定して、集計関数を利用する
select type, count(stock) as count_stock from product group by type having count_stock > 1;
stockカラムを AS句で count_stockカラムに変更、
typeカラムをグループ化した後に、 HAVING句で count_stockカラムが1以上を取得している。
2-7. 7 グループ化した後に ORDER BY句を使い、それぞれ昇順、降順で並べてみる
昇順(デフォルト) ASC
降順 DESC
select type, count(stock) as count_stock from product group by type order by count_stock asc;
それぞれ typeごとにグループ化して、カラム名をAS句で count_stock に変更して、 count_stockを昇順にして取得することができた。
降順の場合

3. そのほかの関数について
今までは、集計関数を使用したSQL操作をしたが、SQLには他にも色々な関数があるので、ここでは代表的なSQL関数をいくつか操作していく。
4. 文字列関数
文字列関数は、文字列の項目に対して操作を行う関数になる
例として、文字列の文字数を取得したり、文字列を連結したりする
では代表的な文字列関数を操作していく
4-1. 1 LENGTH関数
LENGTH関数は、引数に指定した文字列の長さを取得する関数
select length("HelloWorld!!");
length関数を使うことで、HelloWorld!! の文字列を取得することができた。
先程作成した productテーブルを使用して、length関数を使ってみる。
productテーブルからnameカラムを取得して、nameカラムの文字数を取得してみる。
select length(name) from product;
それぞれのnameカラムの文字列を取得することができた。
これだと何の商品が何文字かがわからないため、nameカラムの文字数の隣に、 商品名を表示する。
select name, length(name) from product;
これで商品名と、各商品ごとの文字数を取得することができた。
次にWHERE句を使用して条件を抽出してから、文字列を取得するように操作してみる。
今回は、文字列が10文字以上の商品 (nameカラム)を取得する
select name, length(name) from product where length(name) > 10;
このように where句を使い条件を絞り、10文字以上の商品名を取得することができた。
4-2. 2 CONCAT関数
CONCAT関数は、引数に指定した文字列を連結した文字列として取得することができる。
文字列を結合するための関数
実際に操作してみよう
まずは新しくテーブルを作成する。
humanテーブルを作成する
create table human (id int(10), last_name varchar(20), first_name varchar(20), area varchar(10));
今回は、idカラム、 last_nameカラム、 first_nameカラム、 areaカラムを作成
実際にデータを追加する
insert into human (id, last_name, first_name, area) values (1, '鈴木', '一郎', '愛知県'), (2, '佐藤', '花子', '北海道'), (3, '平山', '吾郎', '香川県'), (4, '山田', '朋子', '静岡県'), (5, '田沢', '英二', '東京都');
humanテーブルを作成
では実際にCONCAT関数を使用して、last_name と first_nameを結合していく
select concat(last_name,'',first_name)from human;
このようにCONCAT関数を使用することで、 last_name と first_name の文字列を結合することができた
4-3. 3 REPLACE関数
REPLACE関数を使用すると、文字と文字を置き換えることができる
(引数に指定した文字列のなかに含まれる指定の文字列を新しい文字列に置き換えることができる)
構文
REPLACE (指定の文字列, 置き換える文字列, 置き換え後の文字列)
では実際に新しいテーブルを作成するところから始める。
create table staff (id int(10), name varchar(20), department varchar(20));
今回は、idカラム、 nameカラム、 departmentカラムを作成
データを追加する
insert into staff (id, name, department) values (1, '佐藤吾郎', '営業部'), (2, '鈴木一郎', '営業部'), (3, '佐藤花子', '総務部'), (4, '小泉京子', '経理部'), (5, '平山一雄', '開発部'), (6, '大石正明', '開発部'), (7, '田沢英二', '営業部');
staffテーブルを作成できた
この中の「開発部」を「システム開発部」に変更するとする。
select id, name, replace(department, '開発部', 'システム開発部') from staff;
このようにREPLACE関数を使用することで、「開発部」の項目を全て「システム開発部」に置き換えることができた。
4-4. 4 INSERT関数
INSERT関数を使用すると、引数に指定した文字列の中の指定した位置から指定した長さの部分を別の文字列に置き換えることができる
実際に新しいテーブルを作成する
create table user (name varchar(20), area varchar(20));
nameカラムと、 areaカラムを作成
データを追加する
insert into user (name, area) values ('鈴木次郎', '静岡県静岡市葵区'), ('佐藤花子', '東京都文京区御徒町'), ('平山悟', '埼玉県さいたま市 南区'), ('大石正明', '大阪府堺市');
userテーブルを作成
ではここで areaカラムの 東京都文京区御徒町を変更する
変更後
東京都台東区御徒町
今回変更するのは、4文字目から3文字分(文京区)を新しい文字列(台東区)に置き換えて取得する
select insert('東京都文京区御徒町', 4, 3, '台東区');
指定の文字列を変更することができた
7. まとめ
ここまで集計関数や日付関数など実際に操作してみた。
SQLには今回紹介した関数以外にもまだ色々な関数があるので、後日紹介していこうと思う。
集計関数は、実際の開発現場でよく利用するので、操作に慣れておこう。
SQLの操作は、何回も反復して操作をしながら手で覚えていくことが大事なので、今後も繰り返し色々なSQL文を書いて練習してSQLのスキルを上げていこう