SQLの絵本 - 4章 主な関数

SQLの絵本―データベースがみるみるわかる9つの扉のメモ。

概要

  • 関数名 (引数)の形式で利用する
  • RDBMSにより固有の関数が存在するので注意する

関数とは

  • 引数の必要な関数も不必要な関数もある
  • 引数が必要な関数
    • 単一行関数: 1行ごとに処理をし行の数だけ結果を返す関数
    • グループ関数: 複数の行を処理し1つの結果を返す関数

数値関数

CEILING CEIL

いわゆる天井関数。
SQL ServerMySQLではCEILING、OracleやPostageSQLではCEILを使う。

FLOOR

いわゆる床関数。
SQL ServerOracleMySQL、PostageSQLで使える。

RAND RND

0以上1未満の乱数を返す。
SQL ServerMySQLではRAND、AccessではRNDが使える。
引数としてSEEDを指定できるが省略可能。

その他の数値関数

ABS ROUND POWER SQRT MOD SIN COS TAN EXP LOG SIGNなどが利用可能

文字列関数

LEN、LENGTH

文字列の長さを返す。

SUBSTRING SBSTR

部分文字列を返す。第2引数に開始箇所、第3引数に文字数を指定する。

SUBSTR('hoge', 2, 2);
LTRIM RTRIM

左(LTRIM)、右(RTRIM)に存在する空白を削除する。

UPPER LOWER

文字列に含まれるアルファベットをを大文字(UPPER)、小文字(LOWER)に変換する。

日付関数

日時の取得

引数なしで実行すると現在日時が得られる。

年、月、日、時、分、秒

YEAR MONTH DAY HOUR MINUTE SECONDといった関数に日時を渡すと各種の情報を取得できる。

DATEADD

時間を加算する。SQL ServerAccessで利用可能。

DATEDIFF

日付型同士の差を得る。SQL ServerAccessで利用可能。

集約関数

集約関数は列を対象として演算をする。引数には列名を指定する。

  • AVG: 列の平均値(NULL除く)
  • SUM: 列の合計(NULL除く)
  • COUNT: 列の行数。列名の場合はNULL以外の行、*を指定するとNULLを含めた全ての行。
SELECT AVG(score) AS average, SUM(score) AS total_score, COUNT(name) AS game FROM tbl_game;
  • MAX: 列中の最大値
  • MIN: 列中の最小値

集約関数をSELECT文のオプションと組み合わせる

GROUP BYと組み合わせる

GROUP BY句によりグループ化したテーブルの列に集約関数を適用すると、グループ化された単位ごとに集約関数が適用される。

SELECT name, MAX(score) AS high, MIN(score) AS low FROM tbl_game GROUP BY name;
+--------+------+------+
| name   | high | low  |
+--------+------+------+
| koba   |  125 |   64 |
| takane |  160 |   90 |
| tone   |  140 |   98 |
+--------+------+------+
GROUP BY ... HAVINGと組み合わせる

WHERE句の中では集約関数が使えない。集約関数と条件式を組み合わせるにはHAVING句を用いる。

SELECT name, SUM(score) AS over300 FROM tbl_game GROUP BY name HAVING (SUM(score) >= 300);
+--------+---------+
| name   | over300 |
+--------+---------+
| takane |     360 |
| tone   |     343 |
+--------+---------+

変換関数

CAST関数を使うとデータ型を変換することができる。
CAST(0.245 AS CHAR)のように使用するが、RDBMSにより動作が割と違う。
例えばMySQLMySQL :: MySQL 5.6 リファレンスマニュアル :: 12.10 キャスト関数と演算子を参照