関数の種類
これまでは、主にSQLの文法や構文といった、「守るべきルール」を中心に学習してきた。本ページでは、これまでと観点を変えて、SQLが持っている便利な道具を紹介する。その中心となるのが「関数」である。
関数とは、「ある値を“入力”すると、それに対応した値を“出力”する」機能である。このときの入力を「引数」と呼び、出力を「戻り値」と呼ぶ。
関数は大きく分けて、
- 算術関数
- 文字列関数
- 日付関数
- 変換関数
- 集約関数
がある。
集約関数は基本的に、COUNT、SUM、AVG、MAX、MINの5つだったが、そのほかの種類の関数の総数は200を超える。数が多いと言っても、頻繁に使う関数はせいぜい30～50くらいであるし、わからなければ関数リファレンスを引けば良い。
算術関数
いちばん基本的な算術関数については、もう既に学習済みである。
- +（足し算）
- -（引き算）
- *（掛け算）
- /（割り算）
まずは算術関数の学習のため、以下のようなサンプルのテーブル（SampleMath）を用意する。
データ型「NUMERIC」は、多くのDBMSが持っているデータ型で、「NUMERIC」という形式で数値の大きさを指定する。
CREATE TABLE SampleMath (m NUMERIC (10,3), n INTEGER, p INTEGER);
START TRANSACTION; INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL); INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL); INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3); INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL); INSERT INTO SampleMath(m, n, p) VALUES (5.555, 2, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL); INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL); COMMIT;
SELECT * FROM SampleMath;
結果
+----------+------+------+ | m | n | p | +----------+------+------+ | 500.000 | 0 | NULL | | -180.000 | 0 | NULL | | NULL | NULL | NULL | | NULL | 7 | 3 | | NULL | 5 | 2 | | NULL | 4 | NULL | | 8.000 | NULL | 3 | | 2.270 | 1 | NULL | | 5.555 | 2 | NULL | | NULL | 1 | NULL | | 8.760 | NULL | NULL | +----------+------+------+
ABS－絶対値
ABSは絶対値を求める関数である。絶対値とは、数値の符号を考えない、ゼロからの距離の置きさを表す関数である。
SELECT m, ABS(m) AS abs_col FROM SampleMath;
結果
+----------+---------+ | m | abs_col | +----------+---------+ | 500.000 | 500.000 | | -180.000 | 180.000 | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | | 8.000 | 8.000 | | 2.270 | 2.270 | | 5.555 | 5.555 | | NULL | NULL | | 8.760 | 8.760 | +----------+---------+
右側のabs_col列は、ABS関数で求めたmの絶対値である。-180の絶対値が、符号がとれて180になっている点に注目していただきたい。
この結果を見てすぐに気づくと思われるが、ABS関数の引数がNULLの場合、結果もNULLである。これはABS関数だけではなく、ほぼすべての関数がNULLに対してはNULLを返す決まりになっている。
MOD関数
MODは割り算の余り（剰余）を求める関数で、moduloの略である。たとえば「7 / 3」の余りは1なので、「MOD(7, 3) = 1」になる。小数の計算が入ると「余り」という概念がなくなってしまうので、MOD関数が使えるのは、必然的に整数の列だけになる。
割り算（n÷p）の余りを求める
SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;
結果
+------+------+---------+ | n | p | mod_col | +------+------+---------+ | 0 | NULL | NULL | | 0 | NULL | NULL | | NULL | NULL | NULL | | 7 | 3 | 1 | | 5 | 2 | 1 | | 4 | NULL | NULL | | NULL | 3 | NULL | | 1 | NULL | NULL | | 2 | NULL | NULL | | 1 | NULL | NULL | | NULL | NULL | NULL | +------+------+---------+
ROUND－四捨五入
四捨五入はROUNDという関数で行う。四捨五入のことを「丸める」ともいうが、英語でもround（丸い）という単語を使用する。丸めの桁数に1を指定すると、小数点第二位で、2を指定すると第三位で四捨五入する。
m列の数値をn列の丸め桁数で四捨五入する
SELECT m, n, ROUND(m, n) AS ROUND_COL FROM SampleMath;
結果
+----------+------+-----------+ | m | n | ROUND_COL | +----------+------+-----------+ | 500.000 | 0 | 500.000 | | -180.000 | 0 | -180.000 | | NULL | NULL | NULL | | NULL | 7 | NULL | | NULL | 5 | NULL | | NULL | 4 | NULL | | 8.000 | NULL | NULL | | 2.270 | 1 | 2.300 | | 5.555 | 2 | 5.560 | | NULL | 1 | NULL | | 8.760 | NULL | NULL | +----------+------+-----------+
文字列関数
これまで、関数ということで主に数値に対して使う算術演算子を中心に見てきた。しかし、SQLが持っている関数のうち、実は算術関数はごく一部でしかない。もちろん、算術関数は頻繁に使われる関数ではあるが、それと同じくらいよく使うのが文字列関数である。
SQLにも、置換、切り出し、短縮などよく使う文字列操作の機能がたくさん用意されている。
文字列関数を学習するために、もう1つサンプルのテーブル（SampleStr）を作ってみる。
CREATE TABLE SampleStr (str1 VARCHAR(40), str2 VARCHAR(40), str3 VARCHAR(40));
START TRANSACTION;
INSERT INTO SampleStr (str1, str2, str3) VALUES ('あいう', 'えお', NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田', '太郎', 'です');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL, 'あああ', NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎', 'abc', 'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ミックマック', 'ッ', 'っ');
COMMIT;
SELECT * FROM SampleStr;
結果
+--------------------+-----------+--------+ | str1 | str2 | str3 | +--------------------+-----------+--------+ | あいう | えお | NULL | | abc | def | NULL | | 山田 | 太郎 | です | | aaa | NULL | NULL | | NULL | あああ | NULL | | @!#$% | NULL | NULL | | ABC | NULL | NULL | | aBC | NULL | NULL | | abc太郎 | abc | ABC | | abcdefabc | abc | ABC | | ミックマック | ッ | っ | +--------------------+-----------+--------+
CONCAT－連結
実務では、「あいう + えお = あいうえお」のように、文字列を連結したいと思うことが頻繁にある。SQLでこれを実現するには、「CONCAT」という関数を使用する。
str1 + str2 と2つの文字をつなげる
SELECT str1, str2, CONCAT(str1, str2) AS str_concat FROM SampleStr;
結果
+--------------------+-----------+-----------------------+ | str1 | str2 | str_concat | +--------------------+-----------+-----------------------+ | あいう | えお | あいうえお | | abc | def | abcdef | | 山田 | 太郎 | 山田太郎 | | aaa | NULL | NULL | | NULL | あああ | NULL | | @!#$% | NULL | NULL | | ABC | NULL | NULL | | aBC | NULL | NULL | | abc太郎 | abc | abc太郎abc | | abcdefabc | abc | abcdefabcabc | | ミックマック | ッ | ミックマックッ | +--------------------+-----------+-----------------------+
LENGTH－文字長
文字列が何文字なのかを調べるための関数は、LENGTH（長さ）である。
str1の文字列の長さを調べてみる。
SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;
結果
+--------------------+---------+ | str1 | len_str | +--------------------+---------+ | あいう | 9 | | abc | 3 | | 山田 | 6 | | aaa | 3 | | NULL | NULL | | @!#$% | 5 | | ABC | 3 | | aBC | 3 | | abc太郎 | 9 | | abcdefabc | 9 | | ミックマック | 18 | +--------------------+---------+
LOWER－小文字化
LOWERはアルファベットの場合だけに関係する関数で、引数の文字列をすねて小文字に変換する。したがって、アルファベット意外に適用しても変化しない。また、最初から小文字の文字にも影響しない。
SELECT str1, LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
結果
+--------+---------+ | str1 | low_str | +--------+---------+ | abc | abc | | 山田 | 山田 | | ABC | abc | | aBC | abc | +--------+---------+
小文字があれば大文字もある。大文字化の関数はUPPERである。
RELPACE－文字列の置換
REPLACEは、文字列中にある一部分の文字列を、別の文字に置き換えるときに使う。
SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr;
str1：対象文字列
str2：置換前の文字列
str3：置換後の文字列
rep_str：置換結果
結果
+--------------------+-----------+--------+--------------------+ | str1 | str2 | str3 | rep_str | +--------------------+-----------+--------+--------------------+ | あいう | えお | NULL | NULL | | abc | def | NULL | NULL | | 山田 | 太郎 | です | 山田 | | aaa | NULL | NULL | NULL | | NULL | あああ | NULL | NULL | | @!#$% | NULL | NULL | NULL | | ABC | NULL | NULL | NULL | | aBC | NULL | NULL | NULL | | abc太郎 | abc | ABC | ABC太郎 | | abcdefabc | abc | ABC | ABCdefABC | | ミックマック | ッ | っ | ミっクマっク | +--------------------+-----------+--------+--------------------+
SUBSTRING－文字列の切り出し
SUBSTRINGは、文字列中にある一部分の文字列を切り出す場合に使用する。切り出し位置は、「左から何文字目」という数え方をする。
文字列の左から3番目と4番目の文字を抜き出す
SELECT str1, SUBSTRING(str1 FROM 3 FOR 4) AS sub_str FROM SampleStr;
結果
+--------------------+--------------+ | str1 | sub_str | +--------------------+--------------+ | あいう | う | | abc | c | | 山田 | | | aaa | a | | NULL | NULL | | @!#$% | #$% | | ABC | C | | aBC | C | | abc太郎 | c太郎 | | abcdefabc | cdef | | ミックマック | クマック | +--------------------+--------------+
UPPER－大文字化
UPPERは、アルファベットだけに関係する関数で、引数の文字列をすべて大文字に変換する。したがって、アルファベット意外に適用しても変化しない。また、最初から大文字の文字にも影響しない。
SELECT str1, UPPER(str1) AS up_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
結果
+--------+--------+ | str1 | up_str | +--------+--------+ | abc | ABC | | 山田 | 山田 | | ABC | ABC | | aBC | ABC | +--------+--------+
これとは反対に、小文字化する関数はLOWERである。
日付関数
CURRENT_DATE－現在の日付
CURRENT_DATEは、SQLを実行した日、つまりこの関数が実行された日を戻り値として返す。引数がないため、カッコ（）は不要。
CURRENT_DATEは、実行する日時によって戻り値が変化する。2020年2月11日に実行すれば「2020-02-11」が得られるし、2020年2月12日に実行すれば「2020-02-12」が得られる。
SELECT CURRENT_DATE;
結果
+--------------+ | CURRENT_DATE | +--------------+ | 2020-02-11 | +--------------+
CURRENT_TIME－現在の時間
CURRENT_TIMEは、SQLを実行した時間、つまりこの関数が実行された時間を取得する。これも引数がないためカッコ（）が不要である。
SELECT CURRENT_TIME;
結果
+--------------+ | CURRENT_TIME | +--------------+ | 19:47:04 | +--------------+
CURRENT_TIMESTAMP－現在の日時
CURRENT_TIMESTAMPは、CURRENT_DATE + CURRENT_TIMEの機能を持つ関数である。この関数を使うと現在の日付も日時も一緒に取得できるし、この結果から日時や時間だけを切り出すことも可能。
SELECT CURRENT_TIMESTAMP;
結果
+---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2020-02-11 19:49:21 | +---------------------+
EXTRACT－日付要素の切り出し
EXTRACTは、日付のデータからその一部分、たとえば「年」や「月」、または「時間」や「秒」だけを切り出す場合に使用する。戻り値は日付型ではなく、数値型になる。
日付要素を切り出してみる。
SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
結果
+---------------------+------+-------+------+------+--------+--------+ | CURRENT_TIMESTAMP | year | month | day | hour | minute | second | +---------------------+------+-------+------+------+--------+--------+ | 2020-02-11 19:54:01 | 2020 | 2 | 11 | 19 | 54 | 1 | +---------------------+------+-------+------+------+--------+--------+
変換関数
最後に紹介するカテゴリは、変換関数というちょっと特殊な働きをする関数の一群である。特殊と言っても、構文はこれまで見てきた関数と似ているし、数も少ないのですぐに覚えることが可能。
「変換」という言葉は意味の広いものであるが、SQLにおいては大きく2つ意味がある。1つが、データの型の変換、略して「型変換」や、英語で「キャスト」と呼ぶもの。そしてもう1つが、値の変換である。
CAST－型変換
型変換はCASTという関数で行う。
型変換はなぜ必要かというと、データ型に合わないデータをテーブルに登録したり、あるいは演算したりするときには、型の不一致であるがゆえのエラーが生じたり、暗黙の型変換を生じさせて処理速度を低下させるといった不都合が起きるためである。そういう場合には、事前に適切な型へ変換してあげる必要がある。
文字型から数値型への変換は以下のようになる。
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
結果
+---------+ | int_col | +---------+ | 1 | +---------+
文字型から日付型への変換は以下のようになる。
SELECT CAST('2020-02-11' AS DATE) AS date_col;
結果
+------------+ | date_col | +------------+ | 2020-02-11 | +------------+
この結果を見ると分かるように、文字型から整数型へ変更すると、「000」のような前ゼロが表示上消えるので、型変換されているという実感がわく。しかし、文字型から日付型へ変更するような場合、ユーザから見てデータの見た目に何か変化があるわけではないので、型変換されているイメージを持ちにくい。このことからもわかるように、型変換は、ユーザが使いやすいように用意された機能というより、DBMSにとって内部処理をやりやすくするために作られた機能である。
COALESCE－NULLを値へ変換
COALESCE（コアレス）は、SQL独特の関数である。可変個の引数をとり、左から順に引数を見て、最初にNULLでない値を返す。可変個なので、必要ならいくつでも引数を増やすことができる。
しかし、変わった関数でありながら、実は非常に頻繁に使われる。どのような場合に使うかというと、SQL文の中で、NULLを何か別の値に変えて扱いたい場合である。NULLが演算や関数の中にまぎれこむと、結果が全部NULLになってしまう。これを避けるときに重宝するのがCOALESCEである。
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2020-02-11') AS col_3;
結果
+-------+-------+------------+ | col_1 | col_2 | col_3 | +-------+-------+------------+ | 1 | test | 2020-02-11 | +-------+-------+------------+
SampleStrテーブルの列を使ったサンプル。
SELECT COALESCE(str2, 'NULLLです') FROM SampleStr;
結果
+-------------------------------+ | COALESCE(str2, 'NULLLです') | +-------------------------------+ | えお | | def | | 太郎 | | NULLLです | | あああ | | NULLLです | | NULLLです | | NULLLです | | abc | | abc | | ッ | +-------------------------------+
このように、NULLを含む列であっても、COALESCEで別の値に変換してからほかの関数や演算の入力とすることで、結果がNULLでなくなるのである。
なお、多くのDBMSがこのCOALESCEの簡略版の独自関数を用意している。しかし、これらは実装依存であるため、どんなDBMSでも使えるCOALESCEを使用することを推奨する。
