3.5 関数と演算子
Postgres Plusは組み込みデータ型に対して数多くの関数と演算子を用意しています。
3.5.1 論理演算子
通常の論理演算子が使用できます。: AND, OR, NOT
SQLは3値のブール演算論理を使用し、ここでNULL値は"不明"を意味します。以下の真理値表を参照してください。
表3-14 AND/OR真理値表
a |
b |
a AND b |
a OR b |
True |
True |
True |
True |
True |
False |
False |
True |
True |
Null |
Null |
True |
False |
False |
False |
False |
False |
Null |
False |
Null |
Null |
Null |
Null |
Null |
表3-15 NOT真理値表
a |
NOT a |
True |
False |
False |
True |
Null |
Null |
AND演算子とOR演算子はオペランドの交換が可能です。つまり、結果に影響を与えることなく左右のオペランドを入れ替えることができます。
3.5.2 比較演算子
以下の表に示す、通常の比較演算子が使用可能です。
表 3-16比較演算子
演算子 |
説明 |
< |
小なり |
> |
大なり |
<= |
等しいかそれ以下 |
>= |
等しいかそれ以上 |
= |
等しい |
<> |
等しくない |
!= |
等しくない |
比較演算子はその意味が通るならば全てのデータ型で使用できます。全ての比較演算子は二項演算子で、booleanデータ型を返します。1 < 2 < 3のような式は（ブール値と3を比較する<演算子がないので）無効です。
比較演算子に加えて特殊なBETWEEN構文が使えます。
a BETWEEN x AND y
は
a >= x AND a <= y
と同一です。同様に、
a NOT BETWEEN x AND y
は
a < x OR a > y
と同一です。
内部的に最初の形式を2番目の形式に書き換えるのに必要となるCPUサイクル以外それぞれの形式には違いはありません。
値がNULLかNULLでないかを検証するには次の構文を使います。
expression IS NULL expression IS NOT NULL
NULLとNULLとは"等しい"関係にはありませんので、expression = NULLと記述してはいけません （NULL値は不明の値を表しているため、不明な値同士が同じかどうかは識別できません）。これは標準SQLに従った動作です。
アプリケーションによっては、expression = NULLが、expressionがNULL値と評価されるのであれば真を返すことを期待することがあります。こうしたアプリケーションは標準SQLに従うように改修することを強く推奨します。
3.5.3 算術関数と演算子
Postgres Plusの数多くの型に対する算術演算子が用意されています。全ての可能な演算に対して一般的な算術規約のない型（例えば、日付/時刻データ型）については、後続する節で実際の動作を説明します。
以下の表に、利用可能な算術演算子を示します。
表 3-17 算術演算子
演算子名 |
説明 |
例 |
結果 |
+ |
和 |
2 + 3 |
5 |
- |
差 |
2 – 3 |
-1 |
* |
積 |
2 * 3 |
6 |
/ |
商（整数の割り算では余りを切り捨て） |
4 / 2 |
2 |
以下の表に使用可能な算術関数を示します。これら関数の多くは、異なる引数型を持つ複数の形で提供されています。特別な場合を除き、ある任意形式の関数はその引数と同じデータ型を返します。DOUBLE PRECISIONデータに対する関数のほとんどはホストシステムのCライブラリの上層に実装されています。このため、境界近くの場合の精度と振舞いはホストシステムに依存して変わります。
表 3-18 算術関数
関数 |
戻り値型 |
説明 |
例 |
結果 |
ABS(x) |
xと同じ |
絶対値 |
ABS(-17.4) |
17.4 |
CEIL(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
引数より小さくない最小の整数 |
CEIL(-42.8) |
-42 |
EXP(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
指数 |
EXP(1.0) |
2.7182818284590452 |
FLOOR(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
引数より大きくない最大の整数 |
FLOOR(-42.8) |
43 |
LN(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
自然対数 |
LN(2.0) |
0.6931471805599453 |
LOG(b NUMBER, x NUMBER) |
NUMBER |
bを底とした対数 |
LOG(2.0, 64.0) |
6.0000000000000000 |
MOD(y, x) |
引数の型と同一 |
y/xの剰余 |
MOD(9, 4) |
1 |
NVL(x, y) |
引数の型と同一; 両引数は同一の型 |
もしx がnullの場合、NVLはyを返す |
NVL(9, 0) |
9 |
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION) |
DOUBLE PRECISION |
aのb乗 |
POWER(9.0, 3.0) |
729.0000000000000000 |
POWER(a NUMBER, b NUMBER) |
NUMBER |
aのb乗 |
POWER(9.0, 3.0) |
729.0000000000000000 |
ROUND(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
四捨五入 |
ROUND(42.4) |
42 |
ROUND(v NUMBER, s INTEGER) |
NUMBER |
sの桁で四捨五入 |
ROUND(42.4382, 2) |
42.44 |
SIGN(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
引数の符号（-1、0、+1） |
SIGN(-8.4) |
-1 |
SQRT(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
平方根 |
SQRT(2.0) |
1.414213562373095 |
TRUNC(DOUBLE PRECISION or NUMBER) |
入力型と同一 |
切り捨て |
TRUNC(42.8) |
42 |
TRUNC(v NUMBER, s INTEGER) |
NUMBER |
sの桁で切り捨て |
TRUNC(42.4382, 2) |
42.43 |
WIDTH_BUCKET(op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER) |
INTEGER |
オペランドが割り当てられる、b1からb2までのバケット数countの等深度ヒストグラムのバケットを返します。 |
WIDTH_BUCKET(5.35, 0.024, 10.06, 5) |
3 |
以下の表に使用可能な三角関数を示します。全ての三角関数はDOUBLE PRECISIONデータ型の引数と戻り値を取ります。
表3-19 三角関数
関数 |
説明 |
ACOS(x) |
逆余弦関数 |
ASIN(x) |
逆正弦関数 |
ATAN(x) |
逆正接関数 |
ATAN2(x, y) |
x/yの逆正接関数 |
COS(x) |
余弦関数 |
SIN(x) |
正弦関数 |
TAN(x) |
正接関数 |
3.5.4 文字列関数と演算子
本節では文字列の値の調査や操作のための関数と演算子について説明します。ここでの文字列とはCHARデータ型, VARCHAR2データ型, および CLOBデータ型、を含みます。補足説明のない限り、下記に挙げている全ての関数はこれら全てのデータ型に対して使用できますが、CHARデータ型を使用した場合、自動的にパディングされるという副作用がありますので注意してください。一般的に、ここで説明する関数は文字列データ型でないデータであっても、最初に文字列表現に変換することで使用できます。
関数 |
戻り値型 |
説明 |
例 |
結果 |
string || string |
CLOB |
文字列結合 |
'Enterprise' || 'DB' |
Postgres Plus |
CONCAT(string, string) |
CLOB |
文字列結合 |
'a' || 'b' |
ab |
INSTR(string, set, [ start [, occurrence ] ]) |
INTEGER |
string中の文字列集合setの位置を検索し、string文字列のstart位置から始まり、最初、2番目、三番目と順次集合を検索する |
INSTR('PETER PIPER PICKED UP A PACK OF PICKED PEPPERS','PI',1,3) |
33 |
LOWER(string) |
CLOB |
文字列を小文字に変換 |
LOWER('TOM') |
tom |
SUBSTR(string, start [, count ]) |
CLOB |
startから始まりcountで指定された長さ分の文字列をstringから切り出す。もしcountが指定されていなければ文字列の最後までを切り出す。 |
SUBSTR('This is a test',6,2) |
is |
TRIM([ LEADING | TRAILING | BOTH ] [ characters ] FROM string) |
CLOB |
characters（デフォルトでは空白）で指定された文字のみを含む最も長い文字列を、stringの先頭、末尾、そしてその両方から削除します。 |
TRIM(BOTH 'x' FROM 'xTomxx') |
Tom |
LTRIM(string [, set]) |
CLOB |
string 文字列のset で指定された文字列を先頭から削除します。set が指定されてない場合はスペースがデフォルトで使用されます。 |
LTRIM('abcdefghi', 'abc') |
defghi |
RTRIM(string [, set]) |
CLOB |
string 文字列のset で指定された文字列を末尾から削除します。set が指定されてない場合はスペースがデフォルトで使用されます。 |
RTRIM('abcdefghi', 'ghi') |
abcdef |
UPPER(string) |
CLOB |
文字列を大文字に変換 |
UPPER('tom') |
TOM |
この他、以下の表に列挙する文字列操作関数が使えます。そのいくつかは、表3-20で説明した標準SQLの文字列関数を実装するため、内部的に使用されます。
表 3-21 その他の文字列関数
関数 |
戻り値型 |
説明 |
例 |
結果 |
ASCII(string) |
INTEGER |
引数の最初のバイトのASCIIコード |
ASCII('x') |
120 |
CHR(INTEGER) |
CLOB |
与えられたASCIIコードの文字 |
CHR(65) |
A |
DECODE(expr, expr1a, expr1b [, expr2a, expr2b ]... [, default ]) |
expr1b, expr2b,..., defaultの引数型と同じ |
expr1a,expr2a 等から最初にExprと一致するものを探します。一致するものが見つかると、expr1b, expr2bなどの対応した引数のペアを返します。もし見つからなければ、defaultを返します。もし見つからなく、defaultが指定されていなければ、nullを返します。 |
DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found') |
Three |
INITCAP(string) |
CLOB |
それぞれの単語の第一文字を大文字にし、残りは小文字のまま残します。ここで単語とは、英数字以外の文字で区切られた、英数字からなる文字の並びのことです。 |
INITCAP('hi THOMAS') |
Hi Thomas |
LPAD(string, length INTEGER [, fill ]) |
CLOB |
文字fill（デフォルトはスペース）を文字列の前に追加して、stringをlengthの長さにします。stringが既にlengthの長さを超えている場合は（右側が）切り捨てられます。 |
LPAD('hi', 5, 'xy') |
xyxhi |
NVL(expr1, expr2) |
引数の型と同一; 両引数は同一の型 |
もしexpr1がnullでなければexpr1を、それ以外はexpr2を返します。 |
NVL(null, 'abc') |
abc |
REPLACE(string, search_string [, replace_string ] |
CLOB |
stringに出現する全てのsearch_string文字列をreplace_string文字列に置換します。もし、replace_string を指定しないとsearch_string 文字列の部分は削除されます。 |
REPLACE( 'GEORGE', 'GE', 'EG') |
EGOREG |
RPAD(string, length INTEGER [, fill ]) |
CLOB |
文字fill（デフォルトはスペース）を文字列に追加して、stringをlengthの長さにします。stringが既にlengthの長さを超えている場合は切り捨てられます。 |
RPAD('hi', 5, 'xy') |
hixyx |
TRANSLATE(string, from, to) |
CLOB |
from集合で指定された文字と一致するstringにある全ての文字は、それに対応するtoで指定された文字に置き換えられます。 |
TRANSLATE('12345', '14', 'ax') |
a23x5 |
3.5.5 LIKE式によるパターンマッチング
Postgres Plusは伝統的なSQLのLIKE式によるパターンマッチングを提供しています。LIKE式の書式は以下のとおりです。
string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ]
それぞれのpatternは、文字列の集合を定義します。LIKE式はpattern.によって示される文字列の集合にstringが含まれていれば真を返します（想像される通り、NOT LIKE式はLIKE式が真を返す場合には偽を返し、その逆もまた同じです。同等の式としてNOT (string LIKE pattern)とも表現できます）。
patternがパーセント記号もしくはアンダースコアを含んでいない場合patternは自身の文字列そのものです。この場合LIKE式は等号演算子のように振舞います。patternの中にあるアンダースコア（_）は任意の一文字とのマッチを意味し、パーセント記号（%）は0文字以上の文字列とのマッチを意味します。
以下にいくつか例を示します。
'abc' LIKE 'abc' true 'abc' LIKE 'a%' true 'abc' LIKE '_b_' true 'abc' LIKE 'c' false
LIKEによるパターンマッチは常に文字列全体に対して行われます。ですから、文字列内の任意位置における並びとマッチさせるにはパーセント記号を先頭と末尾に付ける必要があります。
アンダースコアやパーセント記号というリテラルを他の文字のマッチに使用するのではなく、そのものにマッチさせたい場合には、patternの中のそれぞれのアンダースコアとパーセント記号の前にエスケープ文字を付けなければなりません。デフォルトのエスケープ文字はバックスラッシュですが、ESCAPE句で他の文字を指定することができます。エスケープ文字そのものをマッチさせるにはエスケープ文字を2つ書きます。
リテラル文字列においてバックスラッシュには始めから特別な意味合いがあるので、バックスラッシュを含んだパターン定数を記述する時は問い合わせの中で2つのバックスラッシュを記述する必要があることに注意してください（エスケープ文字列構文の使用を前提）。したがって、実際にバックスラッシュそのものにマッチするパターンを記述するには、文の中でバックスラッシュを4つ記述する必要があります。ESCAPE句で他のエスケープ文字を選択すればこのような状況を回避でき、バックスラッシュはLIKE式にとって特殊な文字ではなくなります（とは言っても、リテラル文字列パーサにとっては依然として特殊文字なので、やはり2つは必要です）。
同時にESCAPE ''と記述することでエスケープ文字を選択しないことも可能です。これにより、事実上エスケープ機構が働かなくなります。つまり、パターン内のアンダースコアおよびパーセント記号の特別な意味を解除することはできなくなります。
3.5.6 データ型書式設定関数
The Postgres Plusの書式設定関数は多彩なデータ型（日付/時刻データ型、整数データ型、浮動小数点数データ型、数値データ型）を整形された文字列に変換したり、整形された文字列を特定のデータ型に変換する強力なツールの一式を提供しています。表 3-22にこれらを列挙しています。これら関数は共通の呼び出し規約を踏襲しています。最初の引数は整形される値で2番目の引数は入力書式または出力書式を定義するテンプレートです。
関数 |
戻り値型 |
説明 |
例 |
結果 |
TO_CHAR(DATE [, format ]) |
VARCHAR2 |
date/timeをformatに対応した文字列に変換します。もしformatが指定されていなければデフォルトはDD-MON-YYとなります。 |
TO_CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM') |
07/25/2007 09:43:02 AM |
TO_CHAR(INTEGER [, format ]) |
CLOB |
整数をformatに対応した文字列に変換 |
TO_CHAR(2412, '999,999S') |
2,412+ |
TO_CHAR(NUMBER [, format ]) |
CLOB |
数値をformatに対応した文字列に変換 |
TO_CHAR(10125.35, '999,999.99') |
10,125.35 |
TO_CHAR(DOUBLE PRECISION, format) |
CLOB |
実数、倍精度数DOUBLE PRECISIONをformatに対応した文字列に変換 |
TO_CHAR(CAST(123.5282 AS REAL), '999.99') |
123.53 |
TO_DATE(string [, format ]) |
DATE |
文字列stringをformatに対応したDATE日付型に変換 |
TO_DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS') |
04-JUL-07 13:39:10 |
TO_NUMBER(string [, format ]) |
NUMBER |
文字列stringをformatに対応した数値に変換 |
TO_NUMBER('2,412-', '999,999S') |
-2412 |
TO_TIMESTAMP(string, format) |
TIMESTAMP |
タイムスタンプ形式文字列をformatに対応したTIMESTAMP データ型に変換 |
TO_TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm') |
05-DEC-00 20:30:25 |
（TO_CHAR用）出力テンプレート文字列には、それが認識され、整形される値から適切に整形されたデータで置き換えられるパターンがあります。テンプレートパターンではない全てのテキストは単にそのままコピーされます。同様に、（TO_CHAR以外用）入力テンプレート文字列では、テンプレートパターンは入力されたデータ文字列の探し出される部分と、そこで見つけ出される値を特定します。
以下の表に、TO_CHARとTO_DATEで利用できる日付/時刻型の書式テンプレートパターンを示します。
表 3-23日付/時刻型の書式テンプレートパターン
パターン |
説明 |
HH |
時 (01-12) |
HH12 |
時 (01-12) |
HH24 |
時(00-23) |
MI |
分(00-59) |
SS |
秒(00-59) |
SSSSS |
深夜0時からの秒数(0-86399) |
AM または A.M. または PM または P.M. |
午前/午後の指定（大文字） |
am または a.m. または pm または p.m. |
午前/午後の指定（小文字） |
Y,YYY |
コンマ付き年（4桁以上） |
YEAR |
年 (詳細表示) |
SYEAR |
年 (詳細表示) (紀元前は先頭にマイナス表示) |
YYYY |
年（4桁以上） |
SYYYY |
年 (4桁以上) (紀元前は先頭にマイナス表示) |
YYY |
年の下3桁 |
YY |
年の下2桁 |
Y |
年の下1桁 |
IYYY |
ISO年（4以上の桁） |
IYY |
ISO年の下3桁 |
IY |
ISO年の下2桁 |
I |
ISO年の下1桁 |
BC または B.C. または AD または A.D. |
紀元前後の指定（大文字） |
bc または b.c. または ad または a.d. |
紀元前後の指定（小文字） |
MONTH |
完全な大文字での月名 |
Month |
完全な大文字小文字混在した月名 |
month |
完全な小文字での月名 |
MON |
短縮形の大文字での月名（英語では3文字。翻訳された場合は長さは可変です。） |
Mon |
短縮形の大文字小文字混在した月名（英語では3文字。翻訳された場合は長さは可変です。） |
mon |
短縮形の小文字での月名（英語では3文字。翻訳された場合は長さは可変です。） |
MM |
月番号（01～12） |
DAY |
完全な大文字での曜日 |
Day |
完全な大文字小文字混在した曜日 |
day |
完全な小文字での曜日 |
DY |
短縮形の大文字での曜日（英語では3文字。翻訳された場合は長さは可変です。） |
Dy |
短縮形の大文字小文字混在した曜日（英語では3文字。翻訳された場合は長さは可変です。） |
dy |
短縮形の小文字での曜日（英語では3文字。翻訳された場合は長さは可変です。） |
DDD |
1年通算の日にち番号（001～366） |
DD |
1月通算の日にち番号（01～31） |
D |
1週通算の日にち番号（1～7：日曜日=1） |
W |
月中の週番号（1～5）（その月の初日がある週が第1週） |
WW |
年間を通じた週番号（1～53）（元旦のある週が第1週） |
IW |
ISO 週番号（新年の最初の木曜日がある週が第1週） |
CC |
世紀（2桁。21世紀は2001-01-01から始まります。） |
SCC |
CC と同じ ただし紀元前はマイナス表示 |
J |
ユリウス日（紀元前4713年1月1日からの通算経過日） |
Q |
四半期 |
RM |
ローマ数字による月（I～XII：I=1月）（大文字） |
rm |
ローマ数字による月（i～xii：i=1月）（小文字） |
RR |
年の最後2桁が与えられたときの、最初の2桁。結果は現在の年と、2桁の年によるアルゴリズムに従います。与えられた2桁の年の最初の2桁は、以下の例外を除いて、現在の年の最初の2桁と同じになります。 与えられた2桁の年が<50で、本年の下2桁が>=50であるなら、与えられた年の最初の2桁は、現在の年の最初の2桁より１大きい値です。 もし、与えられた2桁の年が>= 50で、現在の年の最後の2桁が< 50ならば、与えられた年の最初の2桁は、現在の年の最初の2桁より1小さい値です。 |
RRRR |
TO_DATE関数だけに有効です。2桁と4桁の年に有効です。もし、2桁の年が与えられると、RRフォーマットと同様に最初の2桁を返します。もし、4桁の年が与えられると、4桁の年を返します。 |
ある種の修飾子はどのようなテンプレートパターンに対しても、その振舞いを変更するために適用することができます。例えば、FMMonthはFM修飾子の付いたMonthパターンです。以下の表に、日付/時刻書式の修飾子パターンを示します。
表 3-24日付/時刻書式用のテンプレートパターン修飾子
修飾子 |
説明 |
例 |
FM接頭辞 |
字詰めモード（空白およびゼロのパディングを無効） |
FMMonth |
TH接尾辞 |
大文字の序数を追加 |
DDTH |
Th接尾辞 |
小文字の序数を追加 |
DDth |
FX接頭辞 |
固定書式のグローバルオプション（使用上の注意事項を参照） |
FX Month DD Day |
SP 接尾辞 |
スペルモード |
DDSP |
日付/時刻型書式の使用上の注意事項は次のとおりです。
n FMはパターンの出力を固定長にするため、先頭にはゼロ、末尾には空白を追加してしまう機能を無効にします。
n FXオプションがテンプレートで使用されていない場合には、TO_TIMESTAMP とTO_DATEは入力文字列の複数の空白スペースを無視します。FXはテンプレートの第1項目として指定される必要があります。例えば、TO_TIMESTAMPにはたった1つのスペースがあることになっているので、TO_TIMESTAMP('2000 JUN', 'YYYY MON')が正しく、
n TO_CHARテンプレートでは、通常のテキストが許され、そのまま出力されます。
n 文字列からtimestampもしくはdateへの変換において、YYY、YYYY、もしくはY, YYYフィールドが存在するとCCフィールドは無視されます。CCがYYもしくはYと共に使用されると、年は(CC-1)*100+YYのように計算されます。
以下の表に、数値型の値の書式設定に使用可能なテンプレートパターンを示します。
表 3-25数値書式用のテンプレートパターン
パターン |
説明 |
9 |
指定された桁数での値 |
0 |
前にゼロが付いた値 |
. (句読点) |
小数点 |
, (コンマ) |
千単位で区切る符号 |
$ |
ドルマーク |
PR |
負の値の角括弧表示 |
S |
（ロケール使用の）記号付き値 |
L |
（ロケール使用の）通貨記号 |
D |
（ロケール使用の）小数点 |
G |
（ロケール使用の）グループ区切り文字 |
MI |
（数値 < 0であれば）指定位置にマイナス記号 |
RN もしくは rn |
（1～3999の入力値による）ローマ数字 |
V |
n 桁シフト（注意事項を参照） |
数値型書式の使用上の注意事項は次のとおりです。
n 9は9が並んでいる数と同じ桁数の値を出力します。桁が使用可能でない場合、スペースを出力します。
n THはゼロ未満の値と小数は変換しません。
n Vは事実上、入力値に10nを掛けます。ここでnはVに続く桁数です。 TO_CHAR 関数は小数点を含む数値とVとの混在をサポートしません（例えば、99.9V99 は許可されません）。
以下の表にTO_CHAR関数を使用した例をいくつか示します。
表3-26 TO_CHAR の例
式 |
出力 |
TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD HH12:MI:SS') |
'Tuesday , 06 05:39:18' |
TO_CHAR(CURRENT_TIMESTAMP, 'FMDay, FMDD HH12:MI:SS') |
'Tuesday, 6 05:39:18' |
TO_CHAR(-0.1, '99.99') |
' -.10' |
TO_CHAR(-0.1, 'FM9.99') |
'-.1' |
TO_CHAR(0.1, '0.9') |
' 0.1' |
TO_CHAR(12, '9990999.9') |
' 0012.0' |
TO_CHAR(12, 'FM9990999.9') |
'0012.' |
TO_CHAR(485, '999') |
' 485' |
TO_CHAR(-485, '999') |
'-485' |
TO_CHAR(1485, '9,999') |
' 1,485' |
TO_CHAR(1485, '9G999') |
' 1,485' |
TO_CHAR(148.5, '999.999') |
' 148.500' |
TO_CHAR(148.5, 'FM999.999') |
'148.5' |
TO_CHAR(148.5, 'FM999.990') |
'148.500' |
TO_CHAR(148.5, '999D999') |
' 148.500' |
TO_CHAR(3148.5, '9G999D999') |
' 3,148.500' |
TO_CHAR(-485, '999S') |
'485-' |
TO_CHAR(-485, '999MI') |
'485-' |
TO_CHAR(485, '999MI') |
'485 ' |
TO_CHAR(485, 'FM999MI') |
'485' |
TO_CHAR(-485, '999PR') |
'<485>' |
TO_CHAR(485, 'L999') |
'$ 485' |
TO_CHAR(485, 'RN') |
' CDLXXXV' |
TO_CHAR(485, 'FMRN') |
'CDLXXXV' |
TO_CHAR(5.2, 'FMRN') |
'V' |
TO_CHAR(12, '99V999') |
' 12000' |
TO_CHAR(12.4, '99V999') |
' 12400' |
TO_CHAR(12.45, '99V9') |
' 125' |
3.5.7 日付/時刻関数と演算子
表 3-28 は、日付/時刻型の値の処理で使用可能な関数を示しています。詳細は、以下の副節で説明します。表 3-27 では、、（+、*等の）基本的な算術演算子の振舞いを説明しています。書式設定関数については第 3.5.6章を参照してください。第3.2.4章を参照して、日付/時刻データ型についての背景となっている情報に精通していなければなりません。
演算子名 |
例 |
結果 |
+ |
DATE '2001-09-28' + 7 |
05-OCT-01 00:00:00 |
+ |
TIMESTAMP '2001-09-28 13:30:00' + 3 |
01-OCT-01 13:30:00 |
- |
DATE '2001-10-01' - 7 |
24-SEP-01 00:00:00 |
- |
TIMESTAMP '2001-09-28 13:30:00' - 3 |
25-SEP-01 13:30:00 |
- |
TIMESTAMP '2001-09-29 03:00:00' - TIMESTAMP '2001-09-27 12:00:00' |
@ 1 day 15 hours |
表 3-28 の日付/時刻関数において、DATEとTIMESTAMPデータ型は入れ替え可能です。
関数名 |
戻り値型 |
説明 |
例 |
結果 |
ADD_MONTHS(DATE, NUMBER) |
DATE |
日付への月数の加算; 項 3.5.7.1を参照 |
ADD_MONTHS('28-FEB-97', 3.8) |
31-MAY-97 00:00:00 |
CURRENT_DATE |
DATE |
現在の日付; 項 3.5.7.7を参照 |
CURRENT_DATE |
04-JUL-07 |
EXTRACT(field FROM TIMESTAMP) |
DOUBLE PRECISION |
部分フィールドの取得; 項 3.5.7.2を参照 |
EXTRACT(hour FROM TIMESTAMP '2001-02-16 20:38:40') |
20 |
LAST_DAY(DATE) |
DATE |
与えられた日付の月の、最後の日を返す。もし、与えられた日付が時間を含んでいれば、その値は変更なく引き継がれる。 |
LAST_DAY('14-APR-98') |
30-APR-98 00:00:00 |
LOCALTIMESTAMP [ (precision) ] |
TIMESTAMP |
現在の日時(現在のトランザクションの開始); 項 3.5.7.7を参照 |
LOCALTIMESTAMP |
04-JUL-07 15:33:23.484 |
MONTHS_BETWEEN(DATE, DATE) |
NUMBER |
2つの日付間の月数; 項 3.5.7.3を参照 |
MONTHS_BETWEEN('28-FEB-07', '30-NOV-06') |
3 |
NEXT_DAY(DATE, dayofweek) |
DATE |
指定した日付に続くdayofweekに一致する日付; 項 3.5.7.4を参照 |
NEXT_DAY('16-APR-07','FRI') |
20-APR-07 00:00:00 |
ROUND(DATE [, format ]) |
DATE |
formatに基づいた丸め; 項 3.5.7.5を参照 |
ROUND(TO_DATE('29-MAY-05'), 'MON') |
01-JUN-05 00:00:00 |
SYSDATE |
DATE |
現在の日時 |
SYSDATE |
06-AUG-07 10:06:27 |
TRUNC(DATE [, format ]) |
DATE |
formatに基づいたトランケート; 項 3.5.7.6を参照 |
TRUNC(TO_DATE('29-MAY-05'), 'MON') |
01-MAY-05 00:00:00 |
3.5.7.1 ADD_MONTHS関数
ADD_MONTHS関数は、指定された日付に指定月数を加算（第2引数が負の場合は減算）します。結果の月での日にちは、月の最終日を例外として、それ以外では指定された日付と同じです。最終日が指定された場合は、いつもその月の最終日が結果となります。
計算を実行する前に、月数引数の端数は切り捨てられます。
もし、指定された日付が時刻部分を含んでいたら、その部分はそのまま結果へ引き継がれます。
以下にADD_MONTHS関数の例を示します。
SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;
add_months
--------------------
13-OCT-07 00:00:00
(1 row)
SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL;
add_months
--------------------
28-FEB-07 00:00:00
(1 row)
SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL;
add_months
--------------------
29-FEB-04 00:00:00
(1 row)
3.5.7.2 EXTRACT関数
EXTRACT関数は、日付/時刻の値から年や時などの部分フィールドを抽出します。EXTRACT f関数はDOUBLE PRECISION型の値を返します。以下に有効なフィールド名を示します。
YEAR
年フィールド。
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
2001
(1 row)
MONTH
年内の月番号（1～12）。
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
2
(1 row)
DAY
（月内の）日付フィールド（1～31）
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
16
(1 row)
HOUR
時のフィールド（0～23）
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
20
(1 row)
MINUTE
分フィールド（0～59）
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
38
(1 row)
SECOND
端数を含んだ秒フィールド（0～59）
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
date_part
-----------
40
(1 row)
3.5.7.3 MONTHS_BETWEEN関数
MONTHS_BETWEEN関数は、2つの日付の間の月数を返します。結果は、最初の日付が2番目の日付より大きい場合は正、最初の日付が2番目より小さい場合は負の数値となります。
両方の日付引数の月の日が同じであるか、両方の日付引数がそれぞれその月の最終日ならば、結果は常に整数の月となります。
以下にMONTHS_BETWEEN関数の例をいくつか示します。
SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL;
months_between
----------------
2
(1 row)
SELECT MONTHS_BETWEEN('15-OCT-06','15-DEC-06') FROM DUAL;
months_between
----------------
-2
(1 row)
SELECT MONTHS_BETWEEN('31-JUL-00','01-JUL-00') FROM DUAL;
months_between
----------------
0.967741935
(1 row)
SELECT MONTHS_BETWEEN('01-JAN-07','01-JAN-06') FROM DUAL;
months_between
----------------
12
(1 row)
3.5.7.4 NEXT_DAY関数
NEXT_DAY関数は、指定された日付より大きく、最初に指定された曜日となる日付を返します。少なくても曜日の最初の3文字を指定しなくてはなりません。例:SAT もし、指定された日付が時間の部分も含んでいれば、その部分は、そのまま結果に反映されます。
以下にNEXT_DAY関数の例を示します。
SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY') FROM DUAL;
next_day
--------------------
19-AUG-07 00:00:00
(1 row)
SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON') FROM DUAL;
next_day
--------------------
20-AUG-07 00:00:00
(1 row)
3.5.7.5 ROUND関数
ROUND関数は、指定したテンプレートパターンで丸められた日付を返します。もし、テンプレートパターンが省略されたなら、日付は最新の日で丸められます。以下の表にROUND関数のテンプレートパターンを示します。
表 3-29 ROUND 関数のテンプレートパターン
パターン |
説明 |
CC, SCC |
January 1, cc 01を返します。ここでccは指定された年の最後2桁が<= 50ならば最初の2桁、もし最後の2桁が> 50ならば、最初の2桁より１多い数となります、（西暦の年） |
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y |
January 1, yyyyを返します。ここでyyyyは丸められた年です。6月30日は切り捨てられ、7月1日は切り上げられます。 |
IYYY, IYY, IY, I |
ISO年の初めへの丸めを行います。ISO年は6月30日以前を切り捨てるか、7月1日以降を切り上げるかによって決定されます。 |
Q |
四半期の初日を返します。ここで、四半期の2ヶ月目の15日以前は切り捨てるか、四半期2ヶ月目の16日以降、及び四半期の後半は切り上げることで決定されます。 |
MONTH, MON, MM, RM |
もし、日が15日以前であれば指定された月の初日を、16日もしくはそれ以降であれば次の月の初日を返します。 |
WW |
その年の元日の曜日と同じ曜日の、最も近い日に丸められます。 |
IW |
そのISO年の元日の曜日と同じ曜日の、最も近い日に丸められます。 |
W |
その月の初日の曜日と同じ曜日の、最も近い日に丸められます。 |
DDD, DD, J |
その日の開始時に丸められます。午前11:59:59より早い場合は同一日に、午後12:00:00以降は次の日の開始時に丸められます。 |
DAY, DY, D |
直近に日曜日に丸めます |
HH, HH12, HH24 |
直近の時に丸めます |
MI |
直近の分に丸めます |
以下にROUND関数の利用例を示します。
以下の例は、直近の100年（世紀）への丸めです。
SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
Century
-------------
01-JAN-1901
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
Century
-------------
01-JAN-2001
(1 row)
次は、直近の年への丸めの例です。
SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;
Year
-------------
01-JAN-1999
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;
Year
-------------
01-JAN-2000
(1 row)
次の例は、直近のISO年に丸めます。最初の例は、2004年への丸めです。ISO年の2004年は2003年の12月29日に始まります。2番目の例は2005年への丸めです。ISO年の2005年は、同じ年の1月3日に始まります。
（ISO年は7日単位、月曜から日曜の月曜日から始まります。新年は少なくともその中の4日を含んでいなければなりません。したがってISO年は前年の12月から始まることがあります。）
SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;
ISO Year
-------------
29-DEC-2003
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;
ISO Year
-------------
03-JAN-2005
(1 row)
次の例は、直近の四半期に丸めるものです。
SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;
Quarter
--------------------
01-JAN-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;
Quarter
--------------------
01-APR-07 00:00:00
(1 row)
次の例は、直近の月に丸めるものです。
SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;
Month
--------------------
01-DEC-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;
Month
--------------------
01-JAN-08 00:00:00
(1 row)
次の例は、直近の週に丸めるものです。最初の例では、2007年の初日は月曜日です。1月18日に近い月曜日は1月15日となります。2番目の例では、1月19日に近い月曜日は1月22日となります。
SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;
Week
--------------------
15-JAN-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;
Week
--------------------
22-JAN-07 00:00:00
(1 row)
次の例は、直近のISO週への丸めです。ISO週は月曜日から始まります。最初の例では、2004年1月1日に最も近い月曜日は2003年12月29日になります。2番目の例では、2004年1月2日は2004年1月4日が最も近い月曜日となります、
SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;
ISO Week
--------------------
29-DEC-03 00:00:00
(1 row)
SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;
ISO Week
--------------------
05-JAN-04 00:00:00
(1 row)
次の例は、その月の初日と同じ曜日で始まる週に丸めるます。
SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;
Week
--------------------
08-MAR-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;
Week
--------------------
01-MAR-07 00:00:00
(1 row)
次の例は、直近の日に丸めるものです。
SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;
Day
--------------------
04-AUG-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;
Day
--------------------
05-AUG-07 00:00:00
(1 row)
次の例は、最初の曜日（日曜日）に丸めるものです。
SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;
Day of Week
--------------------
05-AUG-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;
Day of Week
--------------------
12-AUG-07 00:00:00
(1 row)
次の例は、直近の時間に丸めるものです。
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;
Hour
--------------------
09-AUG-07 08:00:00
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;
Hour
--------------------
09-AUG-07 09:00:00
(1 row)
次の例は、直近の分に丸めるものです。
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;
Minute
--------------------
09-AUG-07 08:30:00
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;
Minute
--------------------
09-AUG-07 08:31:00
(1 row)
3.5.7.6 TRUNC関数
TRUNC関数は、指定されたテンプレートパターンに従って切り捨てられた日付を返します。もし、テンプレートパターンが省略された場合、日付は直近の日で切り捨てられます。以下の表にTRUNC関数のテンプレートパターンを示します。
表 3-30 TRUNC 関数の日付テンプレートパターン
パターン |
説明 |
CC, SCC |
January 1, cc01を返します。ここでccは指定された年の最初の2桁です。 |
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y |
January 1, yyyyを返します。ここでyyyyは指定された年です。 |
IYYY, IYY, IY, I |
指定された年を含むISO年の最初の日付を返します。 |
Q |
指定された日付を含む四半期の最初の日を返します。 |
MONTH, MON, MM, RM |
指定された月の最初の日を返します。 |
WW |
その年の元日と同じ曜日で、指定日と同じか以前で、最も遅い日付を返します。 |
IW |
指定した日付を含むISO週の最初の日付を返します。 |
W |
その月の初日と同じ曜日で、指定日と同じか以前で、最も遅い日付を返します。 |
DDD, DD, J |
指定された日付の開始時を返します。 |
DAY, DY, D |
指定された日付を含む、週の初日（日曜日）を返します。 |
HH, HH12, HH24 |
時間の先頭を返します。 |
MI |
分の先頭を返します。 |
以下はTRUNC関数の利用例です。
以下の例は、100年単位への切捨です。
SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
Century
-------------
01-JAN-1901
(1 row)
以下の例は、年単位への切捨です。
SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;
Year
-------------
01-JAN-1999
(1 row)
以下の例は、ISO年の始まりへの切捨です。
SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;
ISO Year
-------------
29-DEC-2003
(1 row)
以下の例は、四半期の開始日への切捨です。
SELECT TRUNC(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;
Quarter
--------------------
01-JAN-07 00:00:00
(1 row)
以下の例は、月の開始日への切捨です。
SELECT TRUNC(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;
Month
--------------------
01-DEC-07 00:00:00
(1 row)
次の例は、年の元日によって決まる週の初めに切捨てます。2007年の元日は月曜日であり、1月19日より前で直近の月曜日は1月15日となります。
SELECT TRUNC(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;
Week
--------------------
15-JAN-07 00:00:00
(1 row)
次の例は、ISO週の始まりに切捨てます。ISO週は月曜日に始まります。2004年1月2日は、ISO週は月曜日から始まるので、2003年12月29日となります。
SELECT TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;
ISO Week
--------------------
29-DEC-03 00:00:00
(1 row)
次の例は、月の初日と同じ曜日で始まる週に切捨てます。
SELECT TRUNC(TO_DATE('21-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;
Week
--------------------
15-MAR-07 00:00:00
(1 row)
次の例は、日の開始時間に切捨てます。
SELECT TRUNC(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;
Day
--------------------
04-AUG-07 00:00:00
(1 row)
次の例は、週の開始日（日曜日）に切捨てます。
SELECT TRUNC(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;
Day of Week
--------------------
05-AUG-07 00:00:00
(1 row)
次の例は、時間の開始に切捨てます。
SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;
Hour
--------------------
09-AUG-07 08:00:00
(1 row)
次の例は、分の開始に切捨てます。
SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;
Minute
--------------------
09-AUG-07 08:30:00
(1 row)
3.5.7.7 現在の日付/時刻
Postgres Plusは、現在の日付時刻に関した値を返す多くの関数を提供します。これらの標準SQL関数はすべて、現在のトランザクションの開始時刻に基づいた値を返します。
n CURRENT_DATE
n LOCALTIMESTAMP
n LOCALTIMESTAMP(precision)
n SYSDATE
LOCALTIMESTAMP関数では、精度の引数をオプションで与えることができ、それに合わせて秒フィールドの端数桁を丸める結果をもたらします。精度の引数がない場合、結果は使用可能な最大精度で出力されます。
SELECT CURRENT_DATE FROM DUAL;
date
-----------
06-AUG-07
(1 row)
SELECT LOCALTIMESTAMP FROM DUAL;
timestamp
------------------------
06-AUG-07 16:11:35.973
(1 row)
SELECT LOCALTIMESTAMP(2) FROM DUAL;
timestamp
-----------------------
06-AUG-07 16:11:44.58
(1 row)
SELECT SYSDATE FROM DUAL;
timestamp
--------------------
06-AUG-07 16:11:48
(1 row)
これらの関数は、現在のトランザクションの開始時刻を返します。この値は、トランザクションが実行されている間は変化しません。これは、次の機能を検討した結果です。単一トランザクションで、" current "時間を一貫性を持った表現を行うことができるようにすることを目的とし、このため、同一トランザクションで何回変更を行っても同一のタイムスタンプを生成します。他の多くのデータベースシステムでは、これらの値をより頻繁に増加させます。
3.5.8 シーケンス操作関数
本節ではPostgres Plusのシーケンスオブジェクトに対し演算を行う関数について説明します。シーケンスオブジェクト（シーケンスジェネレータとも単にシーケンスとも呼ばれます）とはCREATE SEQUENCEコマンドで作成される1行の特別なテーブルです。シーケンスオブジェクトは通常テーブルの行に一意の識別子を生成するために使用されます。以下に列挙されているシーケンス関数は、シーケンスオブジェクトから連続したシーケンス値を取得するための、簡易でマルチユーザに対応した関数です。
sequence.NEXTVAL sequence.CURRVAL
sequenceはCREATE SEQUENCEコマンドで生成されたシーケンスに対するハンドラIDです。
以下に各関数の使用方法を説明します。
NEXTVAL
シーケンスオブジェクトをその次の値に進め、その値を返します。これは自動的に処理されます。複数のセッションが同時にNEXTVALを実行したとしても、それぞれのセッションは個別のシーケンス値を間違いなく受け取ります。
CURRVAL
現在のセッションにおいて、そのシーケンスからNEXTVALによって取得された直近の値を返します（セッション内でそのシーケンスに対しNEXTVALが呼ばれていない場合には、エラーが報告されます）。これはローカルのセッション値を返すことから、現在のセッションが実行した後に、別のセッションがNEXTVALを実行しようがしまいが、以前の値を返すことに注意してください。
デフォルトの引数によってシーケンスオブジェクトが作成されているなら、NEXTVALの呼び出しで1から始まる連続的な値を返します。その他の振る舞いをCREATE SEQUENCEコマンドの特別な引数を使用してさせることが可能です。
重要項目: 同一のシーケンスから数値を取得する同時実行トランザクション同士のブロックを防止するため、NEXTVAL演算は決してロールバックされません。と言うことは、たとえNEXTVALを実行したトランザクションが後にアボートしたとしても、値が一度取り出されたらそれは使用されたものと考えます。つまり、アボートされたトランザクションは、割り当てられた値のシーケンス内に未使用の"欠損"を残す可能性があります。
3.5.9 条件式
本節ではPostgres Plusで使用可能なSQL準拠の条件式について説明します。
3.5.9.1 CASE
SQLのCASE式は他の言語のif/else構文に類似した汎用条件式です。
CASE WHEN condition THEN result [ WHEN ... ] [ ELSE result ] END
CASE句は式が有効な位置であればどこでも使用可能です。conditionとはboolean型の結果を返す式です。もし結果が真であればCASE式の値はresultとなります。もし結果が偽であれば同じようにして後に続くWHEN句が調べられます。WHENのconditionの1つも真でない場合、CASE式の値はELSE句のresultになります。ELSE句がなく、どの条件とも一致しない場合、結果はNULLです。
以下に例を示します。
SELECT * FROM test;
a
---
1
2
3
(3 rows)
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other
(3 rows)
全てのresult式のデータ型は単一の出力型に互換性がなければなりません。
以下の"簡略形" CASE式は上に記述した一般形式からの特別な変形です。
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
expressionは計算され、等しいものが見つかるまでWHEN句で指定された全てのvalueと比較されます。等しいものが見つからない場合、ELSE句のresult（もしくはNULL値）が返されます。これはC言語のswitch文に似ています。
上の例は簡略形CASE構文を使って次のように書くことができます。
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other
(3 rows)
CASE式は、結果を決定するために必要ではない副式をまったく評価しません。例えば、以下は0除算エラーを防ぐための方法です。
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
3.5.9.2 COALESCE
COALESCE関数は、nullでない自身の最初の引数を返します。全ての引数がNULLの場合にのみnullが返されます。
COALESCE(value [, value2 ] ... )
データを表示の目的で取り出す際、null値の代わりにデフォルト値が使用されます。以下に例を示します。
SELECT COALESCE(description, short_description, '(none)') ...
CASE式同様、COALESCEは結果を決定するために必要でない引数を評価しません。つまり、非null引数が見つかれば、その右側にある引数は評価されません。このSQL標準関数はNVLとIFNULLと類似の機能を提供し、他のいくつかのデータベースシステムで使用されています。
3.5.9.3 NULLIF
NULLIF関数は、value1とvalue2が等しい場合、null値を返します。その他の場合はvalue1を返します。
NULLIF(value1, value2)
これを使って、上記のCOALESCEの例の逆演算を実行できます。
SELECT NULLIF(value1, '(none)') ...
もしvalue1が(none)であれば、null値を返します。その他の場合はvalue1を返します。
3.5.9.4 GREATEST と LEAST
GREATESTとLEAST関数は数値がいくらあっても、その中から最大値もしくは最小値を選択します。
GREATEST(value [, value2 ] ... ) LEAST(value [, value2 ] ... )
評価される全ての数値は、結果として得られるデータの型と共通の型に変換できなくてはなりません。リストの中のnull値は無視されます。全ての値がnullと評価された場合に限って結果はnullになります。
GREATESTおよびLEASTはSQL標準に載っていませんが、共通した拡張です。
3.5.10 集約関数
集約関数は複数の入力値から単一の結果を計算します。以下の表に組み込み集約関数を示します。
表 3-31汎用目的の集約関数
関数 |
引数のデータ型 |
戻り値型 |
説明 |
AVG(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
整数型の引数であれば全てNUMBER、浮動小数点の引数であればDOUBLE PRECISION、それ以外は引数のデータ型と同じ |
全ての入力値の平均値（算術平均） |
COUNT(*) |
BIGINT |
入力行の数 | |
COUNT(expression) |
全て |
BIGINT |
expressionが非null値を持つ入力行の個数 |
MAX(expression) |
全ての数値、string、date/time型のいずれか |
引数型と同一 |
全ての入力値間でのexpressionの最大値 |
MIN(expression) |
全ての数値、string、date/time型のいずれか |
引数型と同一 |
全ての入力値間でのexpressionの最小値 |
SUM(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
SMALLINTまたはINTEGER型の引数であればBIGINT、BIGINT型の引数であればNUMBER、浮動小数点の引数であればDOUBLE PRECISION、それ以外は引数のデータ型と同じ |
全ての入力値にわたってexpressionの和 |
上記の関数は、COUNT関数を除き、1行も選択されなかった場合NULL値を返すことに注意してください。特に、行の選択がないSUM関数は、予想されるであろうゼロではなくnullを返します。必要であれば、nullをゼロと交換する目的でCOALESCE関数を使うことできます。
統計解析処理によく使用される集約関数を以下の表に示します。（これらは、より一般的に使用される集約関数との混乱を防ぐために別出ししました。）説明の部分におけるNは、すべての入力式が非nullの入力行の個数を表します。すべての場合にて、例えばNが0の時など計算が無意味である場合にはnullが返されます。
表 3-32統計処理用の集約関数
関数 |
引数の型 |
戻り値の型 |
説明 |
CORR(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
相関係数 |
COVAR_POP(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
母共分散 |
COVAR_SAMP(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
標本共分散 |
REGR_AVGX(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
独立変数の平均値(sum(X) / N) |
REGR_AVGY(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
依存変数の平均値(sum(Y) / N) |
REGR_COUNT(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
両式が非nullとなる入力行の個数 |
REGR_INTERCEPT(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
(X, Y)の組み合わせで決まる、線型方程式に対する最小二乗法のy切片 |
REGR_R2(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
相関係数2乗値 |
REGR_SLOPE(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
(X, Y)の組み合わせで決まる、最小二乗法に合う線型方程式の傾き |
REGR_SXX(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
sum (X2) – sum (X)2 / N （依存変数の"二乗和"） |
REGR_SXY(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
sum (X*Y) – sum (X) * sum (Y) / N （依存変数×独立変数の"和"） |
REGR_SYY(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
sum (Y2) – sum (Y)2 / N （独立変数の"二乗和"） |
STDDEV(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
浮動小数点型の引数ではDOUBLE PRECISION、それ以外ではNUMBER |
STDDEV_SAMPの歴史的な別名 |
STDDEV_POP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
浮動小数点型の引数ではDOUBLE PRECISION、それ以外ではNUMBER |
入力値に対する母標準偏差 |
STDDEV_SAMP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
浮動小数点型の引数ではDOUBLE PRECISION、それ以外ではNUMBER |
入力値に対するサンプル標準偏差 |
VARIANCE(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
浮動小数点型の引数ではDOUBLE PRECISION、それ以外ではNUMBER |
VAR_SAMPの歴史的な別名Historical alias for VAR_SAMP |
VAR_POP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
浮動小数点型の引数ではDOUBLE PRECISION、それ以外ではNUMBER |
入力値に対する母分散（母標準偏差の二乗） |
VAR_SAMP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
浮動小数点型の引数ではDOUBLE PRECISION、それ以外ではNUMBER |
入力値に対するサンプル分散（サンプル標準偏差の二乗） |
3.5.11 副問い合わせ表現
本節ではPostgres Plusで使用できるSQL準拠の副問い合わせについて説明します。本節で記載した全ての式は結果として論理値（真/偽）を返します。
3.5.11.1 EXISTS
EXISTSの引数は、任意のSELECT文または副問い合わせ文です。副問い合わせはそれが何らかの行を返すか否かの決定のために評価されます。もし1つでも行を返すのであれば、EXISTSの結果は"真"となり、副問い合わせが行を返さない場合、EXISTSの結果は"偽"となります。
EXISTS(subquery)
副問い合わせは、その回りの問い合わせ内の変数を参照することができます。その値は副問い合わせの評価時には定数として扱われます。
この副問い合わせは通常、最後まで実行されず、少なくとも1つの行が返されたかどうかを判定し得るに足りる時点まで実行されます。実際に副作用が生じるか否かを予想するのは難しいので、（シーケンス関数を呼び出すような）何らかの副作用を持つ副問い合わせを記述することはお勧めしません。
結果は何らかの行が返されるのかのみに依存し、それらの行の内容には依存しないことから、副問い合わせの出力リストは通常興味のあるものではありません。よく使われるコーディング規約は、全てのEXISTSテストを(SELECT 1 WHERE ...)といった形式で記述することです。とは言っても、INTERSECTを使う副問い合わせのようにこの規則には例外があります。
以下の簡単な例はdeptno上の内部結合のようですが、たとえempの行と複数一致したとしてもdeptのそれぞれの行に対して多くても1つの出力行を生成します。
SELECT dname FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno); dname ------------ ACCOUNTING RESEARCH SALES (3 rows)
3.5.11.2 IN
右辺は括弧で括られた副問い合わせで、正確に1列を返すものでなければなりません。左辺式は評価され、副問い合わせの結果行と比較されます。副問い合わせの行のどれかと等しい場合、INの結果は"真"です。（副問い合わせが行を返さない特別の場合を含め）等しい行が見つからない場合、結果は"偽"です。
expression IN (subquery)
左辺の式がnullを生じる場合、または右辺の値に等しいものがなくて少なくとも1つの右辺の行がnullを持つ場合、IN構文の結果は偽ではなくnullとなります。これは、null値の論理的な組み合わせに対するSQLの通常の規則に従うものです。
EXISTSと同様、副問い合わせが完全に評価されると前提してはなりません。
3.5.11.3 NOT IN
右辺は括弧で括られた副問い合わせで、正確に1つの列を返さなければなりません。左辺の式は副問い合わせ結果の行それぞれに対して評価、比較されます。（副問い合わせが行を返さない特別な場合を含む）等しくない副問い合わせの行だけがあると、NOT INの結果は"真"です。等しい行が1つでもあれば、結果は"偽"です。
expression NOT IN (subquery)
左辺の式でnullが生じる場合、または右辺の値に等しいものがなく、少なくとも1つの右辺の式がnullを生み出す場合、NOT IN構文の結果は真ではなくnullとなることに注意してください。これは、null値の論理的な組み合わせに対するSQLの通常の規則に従うものです。
EXISTSと同様、副問い合わせが完全に評価されると前提してはなりません。
3.5.11.4 ANY/SOME
右辺は括弧で括られた副問い合わせで、正確に1つの列を返さなければなりません。左辺の式は副問い合わせの結果行それぞれに対して、指定されたoperatorを使用して評価、比較されます。なお、operatorは結果として論理値を生成する必要があります。真の結果が1つでもあると、ANYの結果は"真"です。（副問い合わせが行を返さない特別な場合を含む）真の結果がないと、結果は"偽"です。
expression operator ANY (subquery) expression operator SOME (subquery)
SOMEはANYの同義語です。INは= ANYと等価です。
成功したものがなく、右辺の行が演算子の結果として1つでもnullを生成した場合、ANY構文の結果は偽ではなくnullになります。これは、null値の論理的な組み合わせに対するSQLの通常の規則に従うものです。
EXISTSと同様、副問い合わせが完全に評価されると前提してはなりません。
3.5.11.5 ALL
右辺は括弧で括られた副問い合わせで、正確に1つの列を返さなければなりません。左辺の式は副問い合わせの結果行それぞれに対して、指定されたoperatorを使用して評価、比較されます。なお、operatorは結果として論理値を生成する必要があります。（副問い合わせが行を返さない特別な場合を含む）全ての行が真になる場合、ALLの結果は"真"です。1つでも偽の結果があると、結果は"偽"です。比較がすべての行で偽を返さず、かつ、少なくとも1つの行でnullを返した場合、結果はnullとなります。
expression operator ALL (subquery)
NOT INは<> ALLと等価です。
EXISTSと同様、副問い合わせが完全に評価されると前提してはなりません。
Copyright © 2013 EnterpriseDB Corporation. All rights reserved.