SQL関数

関数とは？

SQL99 では、特殊なコマンドとして関数と呼ばれる機能を提供します
実際に関数はひとつのコマンドであり、何らかの値を返します

関数の役割は、特殊な値の取得や計算のパッケージ化です
データベースの性質上、列の合計や平均を取得するのは重要な機能です
しかし、SQL文にプログラミング言語のような機能を持たせるのはやや冗長です
そこで、基本的な集計機能や文字列操作機能を関数として提供するのです

関数は、関数名、入力値、そして結果の3つから構成されます
関数の中には、入力値を必要としないものが存在しますが
SQL の性質上、結果の無い関数というものは存在しません
また、一般的に関数に渡す値を引数と呼びます

関数名( 引数 )

 これが、一般的な関数の呼び出し方法です
関数コマンドが実行されると、入力された値に基づいて何らかの結果を返します
そのため、関数は式のオペランドとして指定することができるコマンドなのです

同じ値を与えると、常に同じ結果を返す関数を決定性関数と呼び
呼び出されるたびに異なる結果を返す関数を非決定性関数と呼びます
決定性関数は、引数に何らかの計算を与えて結果を返す関数などが、
非決定性関数は、システムの値を参照する関数などが考えられます

また、入力するひとつの値に対し、ひとつの結果を返す関数をスカラ関数、
入力する集合に対し、ひとつの結果を返す関数を集計関数とも分類されます
スカラ関数とは、数値や文字列などを解析、変換する関数などを指し
集計関数は、列の検索、合計、平均などを求める関数を表します

集計関数

集計関数を用いれば、列の合計や平均などを求めることができるようになります
これは、表計算ソフトウェアなどでも最も基本とされる重要な機能です

集計関数に渡されるのは単一の値ではなく値の集合です
これに対し、常に 1 つの結果を返します
集計関数は、AVG や SUM など、次のような関数が定義されています

関数解説
AVG(expression)expression で指定した列の平均値を返します
COUNT(expression)expression で指定した列が保有する NULL 以外の行数を返します
COUNT(*)対象に存在する行数を返します
MIN(expression)expression で指定した列の最小値を返します
MAX(expression)expression で指定した列の最大値を返します
SUM(expression)expression で指定した列の合計を返します

expression には対象テーブルに存在する列を指定します
これを SELECT 文で用いれば、関数が返した値を表示することができます

集計関数の効果を調べるために、例の社員表を使いましょう

表 Staff
CodeNameBloodHeightWeight
0001アルクェイド ブリュンスタッドNULL16752
0002シエルO16552
0003遠野 秋葉A16045
0004翡翠B15643
0005琥珀B15643

このテーブルを作って、次のように関数の効果をテストしてください
SELECT AVG(Height) FROM Staff
+-------------+
| AVG(Height) |
+-------------+
|    160.8000 |
+-------------+
この文は、表 Staff に登録されている社員の平均身長を算出しています
同様に、体重の合計値を取得してみましょう
SELECT SUM(Weight) FROM Staff
+-------------+
| SUM(Weight) |
+-------------+
|         235 |
+-------------+
MIN 関数や MAX 関数を使えば、すべての行の中から
もっとも小さな、またはもっとも大きな行だけを抽出することができます

COUNT 関数は、列、またはテーブルの行数を取得する方法として使えます
COUNT 関数の引数に列を指定した場合は、その列の行数が返ります
列の行数とテーブルの行数は同じのように思われますが
この場合、COUNT 関数は NULL 値の行を無視します
これに対し、COUNT(*) という表記は、テーブルの行数をそのまま返します
SELECT COUNT(Blood), COUNT(*) FROM Staff
+--------------+----------+
| COUNT(Blood) | COUNT(*) |
+--------------+----------+
|            4 |        5 |
+--------------+----------+
この SQL 文は COUNT 関数を使って Blood 列の行数とテーブルの行数を表示しています
Blood 列はアルクェイドが NULL となっています
列名を指定した COUNT 関数は NULL の行を無視するため、結果は4を返します
これに対し、COUNT(*) は純粋な行数である 5 を返していることがわかります

時間関数

行の追加を行うとき、現在時刻を入力しなければならないとしたらどうしましょう
時計を持ってきて、それを移して SQL 文を作成するのでしょうか？
それでも可能ではありますが、あまり確実な方法とは言えません

そこで、時間関数を用いることによって現在時刻を取得できます
時間関数は現在時刻を取得するだけなので、引数は存在しません

関数解説
CURRENT_DATE現在の日付を返します
CURRENT_TIME現在の時刻を返します
CURRENT_TIMESTAMP現在の日付と時刻を返します

これらの関数には引数が存在しないため、関数の識別子だけを指定します
>CREATE TABLE Test (
	INPUT_DATE DATE,
	INPUT_TIME TIME,
	INPUT_TS TIMESTAMP
)

>INSERT Test VALUES(
	CURRENT_DATE, CURRENT_TIME ,
	CURRENT_TIMESTAMP
)

>SELECT * FROM Test;
+------------+------------+----------------+
| INPUT_DATE | INPUT_TIME | INPUT_TS       |
+------------+------------+----------------+
| 2003-08-07 | 01:09:17   | 20030807010917 |
+------------+------------+----------------+
この SQL 文は、日付、時刻、そしてその両方の型の列を持つテーブルを宣言し
これに、コンピュータの時計が指す現在時刻を追加しています

数値関数

数値関数は、数学関数を含めて多くの数値処理を行うべきですが
SQL99 が定めているのはほんの一部の機能だけです
複雑な数学処理などは一切サポートされていないため、製品独自の拡張となります

関数解説
BIT_LEGNTH(expression)expression のビット数を返します
CHAR_LENGTH(expression)expression の文字数を返します
EXTRACT(datepart FROM exp)exp から datepart に指定した時間単位だけを抽出します
datepart には時間単位を指定します
時間単位は YEAR, DAY, HOUR, MINUTE, SECOND,
TIMEZONE_HOUR, TIMEZONE_MINUTE のいずれかです
OCTET_LENGTH(expression)expression のオクテット数を返します
POSITION(substr IN tgstr)文字列 tgstr 内から部分文字列 substr で始まる位置を返します。

BIT_LENGTH() 関数は指定した数値や文字のビット数を取得します
これに対して CHAR_LENGTH 関数は、文字数を返します
例えば、1 文字が 16 ビットの文字コードの場合、ビット数から文字数は得られません

OCTET_LENGTH() は、BIT_LENGTH() 同様に、指定した式のオクテット数を返します
オクテットとは、データサイズの単位で 8 ビットを 1 とします
8 ビットならば 1 オクテット、16 ビットなら 2 オクテットとなります

8 ビットを 1 とする単位には、他にバイトがありますが
厳密には、バイトは 8 ビットではなくて、コンピュータの最低処理単位を指します
もし、コンピュータの最低処理単位が 4 ビットならば、1 バイトは 4 ビットになります
このようなコンピュータアーキテクチャに依存する単位を
グローバルなネットワークやデータベースの分野で使うべきではありません
そこで、確実に 8 ビットを 1 とする単位にオクテットが用いられるのです

EXTRACT は時間から特定の要素だけを取得したい場合に使えます
datepart に YEAR を指定すれば exp から年だけを取得することができます

POSITION は、指定した文字列内から特定の文字列を検索できます
もし、tgstr 内に substr と同じ文字列があれば、その文字列の開始位置を返します
SELECT 
	Name,
	CHAR_LENGTH(Name) AS Length,
	OCTET_LENGTH(Name) AS Size
FROM Staff
+-------------------------------+--------+------+
| Name                          | Length | Size |
+-------------------------------+--------+------+
| アルクェイド ブリュンスタッド |     15 |   29 |
| シエル                        |      3 |    6 |
| 遠野 秋葉                     |      5 |    9 |
| 翡翠                          |      2 |    4 |
| 琥珀                          |      2 |    4 |
+-------------------------------+--------+------+
この文は、CHAR_LENGTH() と OCTET_LENGTH() 関数を使って
テーブルから Name 列の各データの文字数とメモリサイズを表示します

文字列関数

文字列の連結や抽出、変換などを行うには文字列関数を利用します
SQL99 で定められている文字列関数は次のようなものがあります

関数解説
CONCATENATE(string1 || string2)指定した 2 つの文字列を結合します
CONVERT(
char_value target_char_set
USING from_of_use source_char_name
)		文字列の表現を変換します
LOWER(string)文字列を小文字に変換します
SUBSTRING(
extractioni_string FROM starting_position
[FOR length] [COLLATE collation_name]
)		文字列の一部を抽出します
TRANSLATE(
char_value target_char_set USING translation_name
)		文字列を異なる型に変換します
TRIM(
[ [ { LEADING | TRAILING | BOTH } ] { removal_string } FROM ]
target_string [COLLATE collation_name]
)		文字列の前後から指定した文字を削除します
UPPER(string)文字列を大文字に変換します

これらの関数は、確実に実装されているものではありません
主に CONCATENATE、LOWER、UPPER などは多くの実装で共通しますが
その他の関数は、実装によって部分的に異なるものが多いでしょう
SELECT UPPER('Blue Blue Glass Moon')
+-------------------------------+
| UPPER('Blue Blue Glass Moon') |
+-------------------------------+
| BLUE BLUE GLASS MOON          |
+-------------------------------+
この文は、UPPER() 関数に文字列を指定し、アルファベットを大文字に変換しています


前のページへ戻る次のページへ