引数を与えて、戻り値を取得するもの。
| DUMP | dump() | 値の詳細 | 2008-06-23 |
| 文字列→日付 | to_date() | 日付変換 | |
| 文字化 | to_char() | 日付や数値を文字列に変換 | 2010-12-29 |
| 大文字化 | upper() | ||
| 数値化 | to_number() | 数値変換 | |
| 数値判断関数 | IS_NUMBER() | 文字列が数字かどうかの判断 | 2010-08-31 |
複数レコード（行）の集計を行う関数。
ある項目を合計したり、件数を数えたり。
| 件数を数える（カウントする） | count() | 2010-01-27 |
| 項目の値を集計する | sum() | |
| 項目の最小値・最大値を取得する | min()・max() | 2010-07-19 |
| 先頭・末尾（Oracleには無い） | first()・last() |
COUNT以外の集計関数は、対象となった値が全てNULLだった場合はNULLを返す。
それ以外の場合は何らかの値を返す。
COUNTは、対象が1件も無かった場合は0を返す。
SQL> select count(*), sum(1) from dual;
COUNT(*) SUM(1)
---------- ----------
1 1
SQL> set null null
SQL> select count(*), sum(1) from dual where 1=0; --1件も取れない
COUNT(*) SUM(1)
---------- ----------
0 null
集計関数と似ている。
| OVER | 分析関数() over | 2008-01-19 |
| 最小値の取得 | min() over | |
| 最大値の取得 | max() over | |
| 自分の前のレコードの値を取得 | lag() over | |
| 自分の後（次）のレコードの値を取得 | lead() over | |
| 先頭n件のselect | rownum, row_number() over | 2008-01-27 |
ある項目の値が最大（とか最小とか最新とか）であるキー（レコード）を取得したい場合、分析関数+OVERが使える。[2007-10-15]
普通の方法だと、副問い合わせと結合を使って同じテーブルを2回読み込む。
select b.* from
(
select
KEY,
max(TARGET) as MAX_TARGET
from
TABLE
group by KEY
) a,
TABLE b
where b.KEY = a.KEY AND b.TARGET = a.MAX_TARGET
;
集計関数と同名であるMAXやMINといった分析関数ではOVERというオプション(?)を使って特定範囲の値を取得できる。
select * from
(
select
KEY,
DATA1, DATA2, …,
TARGET,
max(TARGET) over(partition by KEY) as MAX_TARGET
from TABLE
)
where TARGET = MAX_TARGET
;
これも副問い合わせを使っているように見えるが、テーブルが1回しかスキャンされないので効率いいらしい。
副問い合わせ内で、テーブルの項目と、そのキー内で特有（最大値）の項目（MAX_TARGET）を追加したレコードをSELECTしている。
外側の問い合わせのWHEREで、1レコード内のTARGETとMAX_TARGETが等しいデータだけを抽出している。
したがって、同じ最大値のレコードが2つあったら、1レコードだけを特定することは出来ない。
参考： ORACLE HURRICANE MIXERさんのSQL調査報告一覧 > 集計関数と分析関数の MAX( ) を検証
→row_number関数+overを使う方法 [2008-01-19]