Previous PageTable Of ContentsNext Page

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

ab

POWER(9.0, 3.0)

729.0000000000000000

POWER(a NUMBER, b NUMBER)

NUMBER

ab

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データ型を使用した場合、自動的にパディングされるという副作用がありますので注意してください。一般的に、ここで説明する関数は文字列データ型でないデータであっても、最初に文字列表現に変換することで使用できます。

3-20 SQL文字列関数と演算子

関数

戻り値型

説明

結果

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(デフォルトはスペース)を文字列の前に追加して、stringlengthの長さにします。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(デフォルトはスペース)を文字列に追加して、stringlengthの長さにします。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番目の引数は入力書式または出力書式を定義するテンプレートです。

3-22書式設定関数

関数

戻り値型

説明

結果

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

文字列stringformatに対応した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

文字列stringformatに対応した数値に変換

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_CHARTO_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桁より1大きい値です。

もし、与えられた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_TIMESTAMPTO_DATEは入力文字列の複数の空白スペースを無視します。FXはテンプレートの第1項目として指定される必要があります。例えば、TO_TIMESTAMPにはたった1つのスペースがあることになっているので、TO_TIMESTAMP('2000    JUN', 'YYYY MON')が正しく、

    n TO_CHARテンプレートでは、通常のテキストが許され、そのまま出力されます。

    n 文字列からtimestampもしくはdateへの変換において、YYYYYYY、もしくはY, YYYフィールドが存在するとCCフィールドは無視されます。CCYYもしくはYと共に使用されると、年は(CC-1)*100+YYのように計算されます。

以下の表に、数値型の値の書式設定に使用可能なテンプレートパターンを示します。

3-25数値書式用のテンプレートパターン

パターン

説明

9

指定された桁数での値

0

前にゼロが付いた値

. (句読点)

小数点

, (コンマ)

千単位で区切る符号

$

ドルマーク

PR

負の値の角括弧表示

S

(ロケール使用の)記号付き値

L

(ロケール使用の)通貨記号

D

(ロケール使用の)小数点

G

(ロケール使用の)グループ区切り文字

MI

(数値 < 0であれば)指定位置にマイナス記号

RN もしくは rn

(1~3999の入力値による)ローマ数字

V

n 桁シフト(注意事項を参照)

数値型書式の使用上の注意事項は次のとおりです。

    n 99が並んでいる数と同じ桁数の値を出力します。桁が使用可能でない場合、スペースを出力します。

    n THはゼロ未満の値と小数は変換しません。

    n Vは事実上、入力値に10nを掛けます。ここでnVに続く桁数です。 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章を参照して、日付/時刻データ型についての背景となっている情報に精通していなければなりません。

3-27日付/時刻演算子

演算子名

結果

+

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 の日付/時刻関数において、DATETIMESTAMPデータ型は入れ替え可能です。

3-28日付/時刻関数

関数名

戻り値型

説明

結果

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桁より1多い数となります、(西暦の年)

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

sequenceCREATE 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標準関数はNVLIFNULLと類似の機能を提供し、他のいくつかのデータベースシステムで使用されています。

3.5.9.3 NULLIF

NULLIF関数は、value1value2が等しい場合、null値を返します。その他の場合はvalue1を返します。

    NULLIF(value1, value2)

これを使って、上記のCOALESCEの例の逆演算を実行できます。

    SELECT NULLIF(value1, '(none)') ...

もしvalue1が(none)であれば、null値を返します。その他の場合はvalue1を返します。

3.5.9.4 GREATEST と LEAST

GREATESTLEAST関数は数値がいくらあっても、その中から最大値もしくは最小値を選択します。

    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)

SOMEANYの同義語です。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と同様、副問い合わせが完全に評価されると前提してはなりません。

Previous PageTable Of ContentsNext Page