SQLの関数についてまとめています。
SQL関数の使い方
SQL関数には、組み込み関数とユーザ定義関数があります。直値やカラムを引数として、さまざまな計算が可能です。
SQL関数の定義方法は、CREATE FUNCTION
SQL関数（ストアドファンクション）は、あらかじめ用意されたものだけでなくユーザが定義することが可能です。
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
SONAME shared_library_name
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.3.1 ユーザー定義関数のための CREATE FUNCTION 構文
ストアドファンクションとストアドプロシジャの違いは、返り値の受け渡し方法にあります。ストアドファンクションは、関数自体が返り値を持つのに対し、ストアドプロシジャでは、引数にIN用、OUT用、IN/OUT用を設定して返り値の受け渡しを行います。
例えば、Selectクエリ内で以下のように使うには、my_funcをcreate FUNCTOINで定義しておく必要があります。
select emp_no, my_func(first_name) as first_name from employees;
SQL関数のサンプルコード
SQL関数のサンプルコードと実行例を紹介します。
レコード件数を取得するcount関数
countは対象テーブルのレコード件数を取得します。引数に*を指定しcount(*)とするとテーブル全件の件数を取得します。
mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 300025 | +----------+ 1 row in set (1.43 sec)
【関連記事】
▶SQLのカウントはcount関数使用 引数に条件指定も可能
文字列に変換するTO_CHAR関数
日付や数値を文字列に変換する関数です。
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL ---------------- 2020/08/20 12:00:00
TO_CHARは、Oracleなどで使用できる関数。MySQLやSQL Serverでは使用できません。
【関連記事】
▶TO_CHAR関数の使い方を解説！SQLで数値や日付を書式を指定して文字列に変換
文字列長を取得するLENGTH関数
取得したカラムや文字列の長さを取得します。以下の例では、employees（社員）テーブルから、社員番号（emp_no）、姓名の名（first_name）、姓名の姓（last_name）、first_nameの文字列長を取得しています。
select emp_no, first_name, last_name, length(first_name) from employees limit 10; +--------+------------+-----------+--------------------+ | emp_no | first_name | last_name | length(first_name) | +--------+------------+-----------+--------------------+ | 10001 | xGeorgi | Facello | 7 | | 10002 | Bezalel | Simmel | 7 | | 10003 | Parto | Bamford | 5 | | 10004 | Chirstian | Koblick | 9 | | 10005 | Kyoichi | Maliniak | 7 | | 10006 | Anneke | Preusig | 6 | | 10007 | Tzvetan | Zielinski | 7 | | 10008 | Saniya | Kalloufi | 6 | | 10009 | Sumant | Peac | 6 | | 10010 | Duangkaew | Piveteau | 9 | +--------+------------+-----------+--------------------+ 10 rows in set (0.00 sec)
【関連記事】
▶【SQL】文字数を調べる！SQLでのLENGTH関数の使い方を徹底解説！
文字列操作を行うSUBSTRING関数やRIGHT、LEFT関数
文字列から一部を切り出すなどの操作を行う関数です。
以下は、employees（社員）テーブルから、姓名の名（first_name）と姓名の姓（last_name）をleft関数で左から1文字を抜き出し、conat関数で文字列を連結し、イニシャルを取得しています。
mysql> select emp_no, first_name, last_name, concat(left(first_name,1),".",left(last_name,1)) as initial from employees limit 10; +--------+------------+-----------+---------+ | emp_no | first_name | last_name | initial | +--------+------------+-----------+---------+ | 10001 | Georgi | Facello | G.F | | 10002 | Bezalel | Simmel | B.S | | 10003 | Parto | Bamford | P.B | | 10004 | Chirstian | Koblick | C.K | | 10005 | Kyoichi | Maliniak | K.M | | 10006 | Anneke | Preusig | A.P | | 10007 | Tzvetan | Zielinski | T.Z | | 10008 | Saniya | Kalloufi | S.K | | 10009 | Sumant | Peac | S.P | | 10010 | Duangkaew | Piveteau | D.P | +--------+------------+-----------+---------+ 10 rows in set (0.00 sec)
【関連記事】
▶【SQL】文字列を扱う。SUBSTRING関数とRIGHT関数/LEFT関数について解説
グループ内での演算を行うMAXやMIN関数
対象グループの最大値や最小値を取得する関数です。group byと組み合わせて使うことが多いですが、単体でも使えます。
以下は、社員テーブル（employees）から、一番古い誕生日（min）を一番新しい誕生日（max）を取得する例です。
mysql> select min(birth_date),max(birth_date) from employees; +-----------------+-----------------+ | min(birth_date) | max(birth_date) | +-----------------+-----------------+ | 1952-02-01 | 1970-01-04 | +-----------------+-----------------+ 1 row in set (0.07 sec)
【関連記事】
▶【超便利！】SQLのMAX関数・MIN関数の使い方をわかりやすく解説
まとめ
- SQLの関数には組み込み関数と、ユーザ定義関数がある
- ユーザ定義関数は、CREATE FUNCIONで定義する
- SQL関数の引数には直値やカラム名を指定でき、selectの取得結果を加工できる
