【SQL入門】単一関数とグループ関数まとめ～文字関数・日付関数・数値関数・変換関数・汎用関数・グループ関数～

【今回の紹介】

仕事の都合で「oraclesql基礎」を取得することになったのでその学習メモを載せます

ちなみにインプットはこの書籍
【オラクル認定資格試験対策書】ORACLE MASTER Bronze[12c SQL基礎](試験番号:1Z0-061)完全詳解+精選問題集(オラクルマスタースタディガイド)

今回は。これまでoracleのSQL関数として学習してきた内容を求めたいと思います。

【内容】

まず、SQL関数には多く分けて2つあります。

　①単一関数
対象のレコード分処理結果を返す

　②グループ関数
　　複数のレコードに対し一つの結果を返す。
　

もう少し詳細に乗せると以下のようになります

　①単一関数

　　(1)文字関数
　　(2)数値関数
　　(3)日付関数
　　(4)変換関数
　　(5)汎用関数

　②グループ関数
　　(1)グループ関数

これからこれらの関数について簡単にまとめます。

①単一関数(対象レコード分返す)

(1)文字関数:文字列を引数として文字列または数値を返す

　
　　　(1)UPPER:対象カラムを全て大文字にする
　　　　UPPER　(列名)
　
　　　(2)LOWER:対象カラムを全て小文字にする
　　　　LOWER　(列名)
　
　　　(3)INICAP:対象カラムの頭文字だけ大文字に変換し、それ以外は小文字で返す
　　　　INICAP　(列名)
　
　　　(4)CONCAT:文字列を結合する
　　　　CONCAT (列名1,列名2)
　
　　　(5)SUBSTR:指定した部分文字列をかえす(mからn文字分)
　　　　SUBSTR(列名,m,n)
　
　　　(6)LENGTH:指定したカラムの文字数をかえす
　　　　LENGTH(列名)
　
　　　(7)INSTAR:指定した文字パターンが現れる位置を返す(m文字目からn回目に一致した文字列の位置)
　　　　INSTAR (列名,検索文字,m,n)
　
　　　(8)LPAD:n文字になるように左側に指定の文字を埋め込む
　　　　LPAD(列名,n,埋め込み文字)
　
　　　(9)RPAD:n文字になるように右側に指定の文字を埋め込む
　　　　RPAD(列名,n,埋め込み文字)
　
　　　(10)TRIM:前後の削除文字または任意の文字をかえす
　　　　TRIM (削除パターン 削除文字 from 列名)
　　　　　削除パターン
　　　　　　①LEADING:文字列の先頭にある削除文字を削除
　
　　　　　　②TRAILIMG:文字列の末尾にある削除文字を削除

　　　　　　③BOTH:文字列の先頭及び末尾にある削除文字を削除

　　　(11)REPLACE:文字列の置き換えを行う
　

(2)数値関数:数値を引数として数値を戻す

　　　(1)ROUND:小数点の切り上げを行う
　　　　ROUND(列名,m)
　　
　　　(2)TRUNCK:小数点の切り下げを行う
　　　　TRUNCK(列名,m)
　　　
　　　　※切り上げまたは切り下げの位置について(引数mで調整)
　　
　　　　　ROUNDとTRANCKに関しては、mで指定する位置によって桁数の
　　　　　切り上げまたは切り下げを行う位置が変わる。
　　　　　　例)たとえばmを0またはmの指定なしだと整数値が戻るように四捨五入を行う
　　　　　　　mを1にすると小数点第1位が対象
　　　　　　　mを-1にするとにすると1桁目が対象になる
　　
　　　(3)MOD:指定した数字の余りを返す（m÷nの余りを返す）
　　　　MDD(m,n)

(3)日付関数

　　　
　　日付関数とは、日付値を引数とし、日付または数値を戻す
　
　　　(1)日付の加算・減算
　　　　select 列名（日付型)+30
　　　　select 列名（日付型)+30
　　　　select SYSDATE-列名（日付型)
　
　　　　※日付型同士の計算は経過した時間が返される
　　　　 「日」の部分に関して計算がおこなわれる
　　　
(2)時間数の加算・減算
　　　　select 列名（日付型)+3/24
　　　　select 列名（日付型)+3/24
　　　　※〇/24という表記を行うと「時間」の部分に関して
　　　　計算がおこなわれる
　　
　(3)MONTH_BETWEEN:指定した2つの日付間の月数を返す
　　　　MONTH_BETWEEN(列名,列名)
　　
　(4)ADD_MONTHS:指定した日付のnヶ月後を返す
　　　　ADD_MONTHS(列名,n)
　　　　※もし列名に入る値が月の最終日でだった場合、
　　　　　nヶ月後の月の最終日を返す
　　
　(5)NEXT_DAY:指定した日付の次のm曜日の日付を返す
　　　　NEXT_DAY(列名,'曜日')
　　
　(6)LAST_DAY:指定した日付の前のm曜日の日付を返す
　　　　LAST_DAY(列名,'曜日')
　　
　　
　　※日付関数に関するメモ
　
　　　a.英語環境では（日-月―年）表示⇒日本では（年-月-日）

　　　b.日付の乗算・割算はできない

　　　c.日付同士の加算はできない(日付にある数値を加算は可能)

　　　d.日付同士の減算は可能

(4)変換関数

　　　
　　　変換関数とは、引数として受け入れた値を別のデータ型に変換する

　　　■暗黙の型変換について

　　　暗黙の変換がおこなわれるのは以下のような時
　　　基本的に変換を行って意味があるもののみ変換する

　　　　・date型の比較
　　　　　列名（date型）>'14-04-01'

　　　　・数値の比較
　　　　　列名(数値型)='01'

　　　　・関数の引数
　　　　　SUBSTR(SYSDATE,1,3)

　　　などなど
　　　

　　　■明示的な変換について
　　　
　　　(1)TO_CHAR
　　　　(1)日付→文字値
　　　　　TO_CHAR(列名(日付),'YYYY/MM/DD')
　　　　　※対象の列名に加え、出力書式をパラメータとして渡すことができる
　　　　
　　　　(2)数値→文字値
　　　　　TO_CHAR(数値),'L99,999')
　　　　　※対象の列名に加え、出力書式をパラメータとして渡すことができる
　　　　　　ちなみにLは￥を示し、9の数が表示桁数となる
　　　　
　　　(2)TO_DATE
　　　　①文字値→日付
　　　　　TODATE(列名(文字値),'00-01-00')
　　　　　
　　　(3)TO_NUMBER
　　　　①文字→数値
　　　　　TODATE(列名(文字値),'L9,999,999')
　　　
　　　

　　　■メモ
　　
　　　「どの型変換も返還後の書式を指定できる」

　　　　日付から文字値や文字値から数値など、

　　　　(変換したい列名,変換した書式)

　　　　といった形で変換できる

　　　　その際、思ったより変換書式の種類が豊富。

　　　　桁区切りや￥マークなどなど　　

(5)汎用関数

　　
　　汎用関数とは、NULLに関する操作を行う関数のこと
　　

　　　(1)NVL関数:NULL判定を行い、NULL以外であれば式1、NULLならば式2をかえす

　　　NVL(式１,式2)

　　　※戻り値は式1と同じデータ型ににする必要あり
　　
　　
　　 (2)NVL2関数:NULL判定を行い、NULL以外であれば式2、NULLならば式3をかえす

　　　NVL(式１,式2,式3)
　　　※戻り値の可能性がある式2・式3は同じにする必要あり

　　 (3)NULLIF関数:式1と式2を判定し、等しい場合はNULLを返し、異なる場合は式1を返す

　　　NULLIF(式１,式2)

　　 (4)COALESCE関数:引数を左からNULLかどうか判定を行い、最初に見つかったNULLでない値を返す
　　　すべての引数がNULLであった場合はNULLを返す
　
　　　COALESCE(列名a,列名b,列名c)
　
　　※すべて引数は同じデータ型にする必要がある
　　　　同じ列に返すのでデータ型をそろえる。
　　
　　(5)case式

　　case 列名
　　 when 条件 then　戻り値
　　 when 条件 then　戻り値
　　 when 条件 then　戻り値
　　 else デフォルト値
　　 END
　　
　　 ※一致条件なしelseも省略されている場合は、NULLを返す
　　
　　(6)decode式

　　decode (列名条件,条件,戻り値,
　　 条件,戻り値,
　　 条件,戻り値)
　　 else デフォルト値
　　 END

　　
　　 ※一致条件なしelseも省略されている場合は、NULLを返す
　　 decode関数はoracle固有らしい　　

■メモ

　　「いつ型変換・NULL判定を行うべきか」
　
　　型の変換やNULL判定処理などSQL関数でも、SQL取得後別の言語でも
　同様の処理を行うことができるようにみえる。
　どのような違いがあって、どう使い分けるべきなのかという疑問があるが
　少し調べてもわからないため、要調査

②グル―プ関数（集計結果を1フィールド返す）

(1)グループ関数

　　　グループ関数とは指定のグループ単位に処理をかえす関数のこと。集計関数や複数行関数とも呼ばれる。
　　count(*)以外では、基本的にグループ関数ではNULL値を無視する

　　※AVG(列名)などを使用する際、NULLを除くと全体の数が変わるため
　　　もし対象列がNULLを許容している場合は、NULLを0に置き換える
　　　必要がある。

　例）SUM,AVG,MAXなど
　

　※使用可能な句はselect句,order句,having句
　　使用不可能な句はwhere句
　

　(1)count:取り出されたデータ件数を表示。引数によって処理が異なる
　　
　　count(*):NULLや重複値を含むすべてのデータ件数
count(列名):NULLを除くすべてのデータ件数（重複は含まれる）
　　count(distinct列名):NULLと重複を除くデータ件数

　　
　(2)MAXとMIN:指定列の最大を取得(MAX)。指定列の最小を取得(MIN)
　引数の列の型としては、数値・日付・文字が可能

　 MAX(列名)
MIN(列名)
　　
　　

　(3)AVG:平均値を取得

　　AVG(列名)　

　(4)SUM:合計を取得

　　SUM(列名)

■より細かなグループを指定して集計処理を行う(GROUP BY)

　(1)GROUP BYの注意

　　・列別名は使用できない

　　・where句order by句の間に記述

　　・グループ関数を最大に2レベルまでネストできる

　　・GROUP BY句で指定した列名をselect句に入れなくてもエラーにならない
　　・ORDER BYを行う際は、GROUP BY句またはグループ関数以外を選択すると
　　　エラーになる
　　　
　　■メモ

　　「MAX・MINが文字型・日付型をどう判断するのか」

　　　文字
　　　　MIN→辞書順で一番早い文字
　MAX→辞書順で一番遅い文字
　　　日付
　　　　MIN→一番古い日付
　MAX→一番新しい日付