SQL文における関数
SQL文にも関数が存在します。
他の言語の関数と用法は同様で、渡された引数に基づいて処理を行い、値（戻り値）を返します。（引数の有無や数は関数により異なります）
集計関数
集計関数とは、ある項目に対してデータの値を集計する関数です。
集計関数を使用すれば、最大値、最小値、件数、合計などを集計することが可能です。
データの件数（COUNT関数 ）
COUNT関数は指定された項目の件数を返します。ただし、項目の値がNULLの場合は件数から除外されます。
また、項目に「＊」（アスタリスク）を指定した場合は、テーブルの行数を返します。
例えば、次の指定によって、単価が200以上の商品の数を調べることができます。
SELECT COUNT (*) FROM fruits WHERE price >= 200 ;
データの合計（SUM関数 ）
SUM関数は指定された項目の合計を返します。ただし、項目の値がNULLの場合は除外して集計します。
SELECT SUM(合計を求める列名) FROM テーブル名;
データの平均値（AVG関数 ）
AVG関数は指定された項目の平均値を返します。ただし、項目の値がNULLの場合は除外して集計します。
SELECT AVG(平均値を求める列名) FROM テーブル名;
データの最大値、最小値（MAX関数、MIN関数 ）
MAX関数は、指定された項目の最大値を返します。
SELECT MAX(最大値を求める列名) FROM テーブル名;
MIN関数は、指定された項目の最小値を返します。
SELECT MIN(最小値を求める列名) FROM テーブル名;
グループ化して集計する
グループ分け（GROUP BY）
データをグループ化すると、グループごとの集計値を求めることができます。
グループ化を行うには、集計関数とGROUP BYという句を組み合わせます。
例えば、以下のように「商品」表があったとします。
「商品」表
| code （商品コード、INT） | name （商品名、VARCHAR(20 CHAR)） | price （単価、INT） | region （地方、VARCHARA(20 CHAR)） |
|---|---|---|---|
| 101 | メロン | 800 | 南部 |
| 102 | いちご | 150 | 中部 |
| 103 | りんご | 120 | 北部 |
| 104 | レモン | 200 | 南部 |
| 201 | くり | 100 | 北部 |
| 202 | かき | 160 | 中部 |
| 301 | もも | 130 | 南部 |
| 302 | キウイ | 200 | 南部 |
この「商品」表において、「地方」ごとの商品の平均単価を求めたい場合、
GROUP BY句に「地方」列、SELECT句に「地方」列、AVG関数を指定します。
SELECT region, AVG (price) FROM fruits GROUP BY region ;
グループ分けした際の絞り込み（HAVING）
グループ化して集計した値について、さらに条件を指定して絞り込みたい場合には、
WHERE句ではなく、HAVING句を条件に指定します。
SELECT region, AVG (price) FROM fruits GROUP BY region HAVING AVG (price) >= 200 ;
この場合、平均単価が200以上の地方だけが抽出されます。
※GROUP BY句の注意点
SELECT句に含めた集計関数以外の列（非集計カラム）は、すべてGROUP BY句に含める必要があります。
SELECT region, AVG (price), name FROM fruits GROUP BY region HAVING AVG (price) >= 200 ; （× エラー）
SELECT region, AVG (price), name FROM fruits GROUP BY region, name HAVING AVG (price) >= 200 ; （〇 OK）
これは、各グループ内で非集計カラムの値が一意に定まることが保証されないとDatabaseが困ってしまうためです。
エラーになる例ではGROUP BY句にnameが含まれておらず、DB側からするとregionとnameの組み合わせが一意かどうか判断できず、nameの値をどの行から持って来ていいか分からなくなってしまいます。
その他の関数
文字列処理
LENGTH関数
LENGTH関数は、文字の長さを取得できます。
SELECT name, LENGTH(name) FROM fruits;
CONCAT関数
COCAT関数は、文字列を連結できます。引数は2つのみで、カラムや文字列も指定できます。
SELECT name, CONCAT(price, '円') FROM fruits;
3つ以上の文字列を連結したい場合、入れ子にします。
SELECT CONCAT( name , CONCAT(price, '円') ) FROM fruits;
SUBSTR関数
SUBSTR関数は、文字列の切り出しができます。
文字列(第1引数)の、先頭より(第2引数)文字目から(第3引数)文字を取得します。
SELECT SUBSTR(name, 1, 2) FROM fruits;
(この場合、「いちご」というデータであれば「いち」を切り出して取得します。)
数値処理
ROUND関数
数値を四捨五入します。
第1引数の数値を、第2引数に応じた桁で四捨五入します。
・1 →小数点以下２桁目
・0 →小数点以下１桁目
・-1→一の位の桁
SELECT name, ROUND(price, -2) FROM fruits;
この場合、単価を十の位で四捨五入した値が得られます。
日付処理
SYSDATE関数
システム日付をDATE型(年月日形式)のデータとして取得します。
SELECT SYSDATE FROM DUAL;
FROM句のDUALはダミーテーブルです。OracleではSELECT文にはFROM句 が必須のため、便宜上ダミーテーブルを記述します。
なお、DATE型からTIMESTAMP型(年月日時分秒の形式)に変換することもできます。
SELECT TO_TIMESTAMP( TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') ) FROM DUAL;
(文字列型に変換するTO_CHAR関数でTIMESTAMP形式の文字列に変換し(第2引数のフォーマットに合わせた文字列に変換)、更に文字列をTIMESTAMP型に変換するTO_TIMESTAMP関数で変換しています。)
SYSTIMESTAMP関数
システム日付をTIMESTAMP WITH TIME ZONE型(年月日形式、タイムゾーンを含む)のデータとして取得します。
OracleではTIMESTAMP型にも種類があり、「TIMESTAMP WITH TIME ZONE」はタイムゾーンも保存してくれます。
タイムゾーンとは地域ごとの世界標準時とのずれを示すもので、日本の場合は世界の標準時UTC+0900（9時間）のずれとなります。
SELECT SYSTIMESTAMP FROM DUAL;
タイムゾーンの参考サイト
システム開発での日付・時刻の扱い方
EXTRACT関数
DATE型、TIMESTAMP型から、指定した日時フィールドを取得します。
EXTRACT関数内のFROMの後に抽出対象、FROMの前に抽出したい形式を指定します。
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
上記の場合、今日の年を取得します。YEARの部分をMONTH、DAYに置き換え可能です。
抽出対象がTIMESTAMP型であれば、HOUR、MINUTE、SECONDも抽出できます。
この他にもSQLの関数は多数存在しますので、用途に応じたものを選んで使用するといいでしょう。
参考サイト：Oracle SQL 関数
練習問題
次の「輸出先」表を使って、問１から問10に関するSQL文を作成してください。
「輸出先」表 ( 人口の単位は[ 万人 ] )
| 輸出先コード | 輸出先名 | 人口 | 地方 |
|---|---|---|---|
| 12 | ミナンミ王国 | 100 | 南洋 |
| 15 | パローヌ国 | 200 | 中部 |
| 22 | トカンタ国 | 150 | 北洋 |
| 23 | アルファ帝国 | 80 | 北洋 |
| 25 | リトール王国 | 150 | 南洋 |
| 31 | タハル王国 | 240 | 北洋 |
| 32 | サザンナ王国 | 80 | 南洋 |
| 33 | マリヨン国 | 300 | 中部 |
問1: 最小の人口は何万人でしょうか？
問2: 最大の人口は何万人でしょうか？
問3: 輸出先にある国の人口をすべて合計してください。
問4: 輸出先コードが20以上の国の人口をすべて合計してください。
問5: 人口が100万人以上の国は何か国ありますか？
問6: 北洋に属する国は何か国ありますか？
問7: 北洋に属する国で最大の人口は何万人でしょうか？
問8: リトール王国を除いた人口を合計すると何万人でしょうか？
問9: 平均人口が200万人以上となる地域を調べてください。
問10: ３か国以上が属する地域を調べてください。