集約関数

集約関数は、 SELECT ステートメントで使用し、グループ化操作の結果として取得されたタプルのすべてのグループに対して 1 つの値を返します。

これらの関数は、適用先のフィールドの名前を指定する式をパラメーターとして受け取ります。オプションで、このパラメーターの前に 2 つの修飾子 ALL または DISTINCT のいずれかを指定できます。これらの修飾子は、特定の集約関数のセマンティクスに影響し、集約関数をグループ内のすべてのタプルに適用するか、それとも対象のフィールドに異なる値が含まれるタプルにのみ適用するかを指定します。

Virtual DataPort でサポートされている集約関数

AVG

説明

AVG 関数は、テーブルのフィールドの NULL 以外の値の平均を返します。

フィールドのすべての値が NULL の場合、この関数は NULL を返します。

構文

AVG( <expression> ) : double
  • expression: 必須。式の型は intlongfloatdouble 、または decimal のいずれかである必要があります。

例 1

次のビュー ITEMS について考えてみます。

item

price

A

3.45

B

9.99

C

4.99

SELECT AVG(price) AS average_price
FROM ITEMS

average_price

6.1433333333333335

例 2

次のビュー ITEMS_2 について考えてみます。

item

price

A

3.45

B

9.99

C

4.99

D

NULL

SELECT AVG(price) AS average_price
FROM ITEMS_2

average_price

6.1433333333333335

COUNT

説明

COUNT 関数は、選択操作の結果のタプルの数を返します。

パラメーターが * の場合は、タプルの数を返します。

パラメーターがフィールドの場合は、NULL 以外の値の数を返します。

任意の型のフィールドに適用できます。

この関数は、 GROUP BY 句なしでクエリで使用できますが、その場合は * と一緒にのみ使用できます。

構文

COUNT( <count parameter> )

<count parameter> ::=
    <field name:identifier>
  | *
  • count parameter: 必須。フィールド名または * のいずれかを指定できます。

次のビュー ITEMS について考えてみます。

item

price

A

3.45

B

9.99

C

4.99

D

NULL

例 1

SELECT COUNT(*)
FROM items

count

4

例 2

SELECT COUNT(price)
FROM items

count

3

FIRST

説明

FIRST 関数は、各値グループのフィールドの最初の値を返します。

この関数は、修飾子 ALL または DISTINCT は無視します。

構文

FIRST ( <field name:identifier> )
  • field name: 必須。ビューのフィールド。

次のビュー V について考えてみます。

A

B

group1

one

group1

two

group1

NULL

group2

four

例 1

SELECT FIRST(b)
FROM v

first

one

例 2

SELECT a, FIRST(b)
FROM v
GROUP BY a

a

first

group1

one

group2

four

GROUP_CONCAT

説明

GROUP_CONCAT 関数は、グループごとに、各グループのすべてのフィールド値を連結した文字列を返します。

構文

GROUP_CONCAT( [ DISTINCT | ALL ] <field name:identifier>)
GROUP_CONCAT( [ <row separator:text> [, <field separator:text> ] ],
    <field name:identifier> [, <field name:identifier>]* )
GROUP_CONCAT( <ignore null:boolean> , <row separator:text>, <field separator:text>,
    <field name:identifier> [, <field name:identifier>]* )
  • ignore null: オプション。 true の場合に、行のいずれかのフィールドの field nameNULL であるときは、 GROUP_CONCAT はその行のすべてのフィールドを無視します。 false の場合、行は無視されず、 NULL 値は空の文字として処理されます。デフォルト値は true です。

  • row separator: オプション。各行の値を区切るために使用するリテラル。デフォルト値は , です。

  • field separator: オプション。同じ行のフィールドの値を区切るために使用するリテラル。デフォルト値はスペースです。

  • field name: 必須。連結する値を含むフィールド。

次のビュー V について考えてみます。

a

b

c

group1

1

one

group1

2

two

group1

NULL

three

group2

4

four

例 1

SELECT A, GROUP_CONCAT(':', c)
FROM v
GROUP BY a

a

group_concat

group1

one:two:three

group2

four

例 2

SELECT A, GROUP_CONCAT(':', ';', b, c)
FROM v
GROUP BY a

a

group_concat

group1

1;one:2;two

group2

4:four

3 番目の行のフィールド BNULL の場合、 GROUP_CONCAT は、その行のすべてのフィールドを無視します。つまり、値 three を無視します。

例 3

SELECT a, GROUP_CONCAT(false, ':', ';', b, c)
FROM v
GROUP BY a

a

group_concat

group1

1;one:2;two:;three

group2

four

パラメーター ignoreNullsfalse であるため、 GROUP_CONCAT は、3 番目の行のフィールド B の値が NULL であっても、その行のフィールド C の値 (three) を無視しません。この場合、 NULL 値は空の文字と同様に処理されます。

LAST

説明

LAST 関数は、各値グループのフィールドの最後の値を返します。

この関数は、修飾子 ALL または DISTINCT は無視します。

構文

LAST ( <field name:identifier> )
  • field name: 必須。ビューのフィールド名。

次のビュー V について考えてみます。

a

b

group1

one

group1

two

group1

NULL

group2

four

例 1

SELECT LAST(b)
FROM v

last

four

例 2

SELECT a, LAST(b)
FROM v
GROUP BY a

a

last

group1

NULL

group2

four

LIST

説明

LIST 関数は、指定されたフィールドのすべての値を含む配列を返します。

引数として単一のフィールドを指定して呼び出した場合は、関数 NEST と同じ動作になります (「 NEST 」を参照)。

構文

LIST ( <field name:identifier> )
  • field name: 必須。ビューのフィールド。

次のビュー V について考えてみます。

a

b

group1

one

group1

two

group1

NULL

group2

four

例 1

SELECT LIST(b)
FROM v

list

Array { one, two, NULL, four }

例 2

SELECT a, LIST(b)
FROM v
GROUP BY a

a

list

group1

Array { one, two, NULL }

group2

Array { four }

MAX

説明

MAX 関数は、各値グループのフィールドの最大値を返します。

この関数は、修飾子 ALL または DISTINCT は無視します。

構文

MAX ( <expression> )
  • expression: 必須。式の型は、 date (非推奨の型)、 intervaldaysecondintervalyearmonthtexttimetimestamptimestamptz 、または任意の数値データ型になります。

戻り値の型は入力式の型です。

フィールドがテキスト型の場合、この関数は、値の各文字の Unicode 値を比較します。

次のビュー V について考えてみます。

a

b

c

group1

1

Virtual DataPort

group1

2

Data Catalog

group1

NULL

ITPilot

group2

4

denodo

例 1

SELECT MAX(b)
FROM v

max

4

例 2

SELECT a, MAX(b)
FROM v
GROUP BY a

a

max

group1

2

group2

4

例 3

SELECT MAX(c)
FROM v

max

denodo

この例では、結果は「denodo」になります。最初の文字の Unicode 値がすべての値の中で最大であるためです (d = 100、V = 86 など)。

MEDIAN

説明

MEDIAN 関数は、各値グループのフィールドの中央値を返します。グループの値の数が偶数個の場合は、2 つの中央値の平均を取ります。

この関数は、 NULL 値を考慮せずに結果を計算します。

修飾子 ALL および DISTINCT は無視します。

構文

MEDIAN ( <expression> )
  • expression: 必須。式の型は、 date (非推奨の型)、 timetimestamptimestamptz 、または任意の数値データ型になります。

次のビュー employee について考えてみます。

id

first_name

start_date

salary

01

Jason

1996/07/25

1234.56

02

Alison

1976/03/21

6661.78

03

James

1978/12/12

6544.78

04

Celia

1982/10/24

2344.78

05

Robert

1982/10/24

2334.78

05

Jason

1987/07/30

2224.50

例 1

SELECT MEDIAN(salary), MEDIAN(start_date)
FROM employee

median(salary)

median(start_date)

2339.78

1983/06/05

例 2

SELECT first_name, MEDIAN(salary), MEDIAN(start_date)
FROM employee
GROUP BY first_name

first_name

median(salary)

median(start_date)

Alison

6661.78

1976/03/21

Celia

2344.78

1982/10/24

James

6544.78

1978/12/12

Jason

1729.53

1992/01/26

Robert

2334.78

1984/01/15

MIN

説明

MIN 関数は、各値グループのフィールドの最小値を返します。

この関数は、修飾子 ALL または DISTINCT は無視します。

構文

MIN ( <expression> )
  • expression: 必須。式の型は、 date (非推奨の型)、 intervaldaysecondintervalyearmonthtexttimetimestamptimestamptz 、または任意の数値データ型になります。

フィールドがテキスト型の場合、この関数は、値の各文字の Unicode 値を比較します。

次のビュー V について考えてみます。

a

b

c

group1

1

Virtual DataPort

group1

2

Data Catalog

group1

NULL

ITPilot

group2

4

denodo

例 1

SELECT MIN(b)
FROM V

min

1

例 2

SELECT a, MIN(b)
FROM v
GROUP BY a

a

min

group1

1

group2

4

例 3

SELECT MIN(c)
FROM v

min

Data Catalog

この例では、結果は「Data Catalog」になります。最初の文字の Unicode 値がすべての値の中で最小であるためです (D = 68、d = 100、V = 86 など)。

NEST

説明

NEST 関数は、選択されたフィールドの値を含む配列を返します。結果は、FLATTEN ビューの結果の逆になります (FLATTEN ビューの詳細については、「 FLATTEN ビュー (データ構造のフラット化) 」を参照してください)。

構文

NEST( <field name:identifier> [, <field name:identifier> ]*):array
NEST(*)
  • field name: フィールド名。 (*) を使用すると、ビューのすべてのフィールドを関数に渡すのと同じ処理になります。

ビュー V について考えてみます。

int_sample

text_sample

register_sample

1

A

Register { hello , how're you }

1

B

Register { hello, good bye }

2

C

Register { another string, last string }

SELECT int_sample, NEST(text_sample, register_sample) AS nest_sample
FROM V
GROUP BY int_sample;

int_sample

nest_sample

1

Array [ A, Register { hello , how're you }

B, Register { hello, good bye }

]

2

Array [ C, Register { another string, last string } ]

STDEV

説明

STDEV 関数は、各値グループの標本標準偏差を返します。

この関数は、 NULL 値を考慮せずに結果を計算します。

構文

STDEV( <expression> ) : decimal
  • expression: 必須。任意の数値データ型。

ビュー sales_by_region について考えてみます。

region

state

revenue

South

Alabama

53168

South

Missisipi

5681

South

Tennessee

80166

Northeast

New York

12945

Northeast

Pennsylvania

69284

Northeast

New Hampshire

53168

SELECT region, STDEV(revenue)
FROM revenue
GROUP BY region

region

stdev

South

37709.24377832752

Northeast

29016.36924794922

STDEVP

説明

STDEVP 関数は、各値グループの母集団標準偏差を返します。

この関数は、 NULL 値を考慮せずに結果を計算します。

構文

STDEVP( <expression> ) : decimal
  • expression: 必須。任意の数値データ型。

ビュー sales_by_region について考えてみます。

region

state

revenue

South

Alabama

53168

South

Missisipi

5681

South

Tennessee

80166

Northeast

New York

12945

Northeast

Pennsylvania

69284

Northeast

New Hampshire

53168

SELECT region, STDEVP(revenue)
FROM revenue
GROUP BY region

region

stdevp

South

30789.46861437455

Northeast

23691.76628188695

SUM

説明

SUM 関数は、各値グループのフィールドの NULL 以外のすべての値の合計を返します。

構文

SUM ( <expression> )
  • expression: 必須。任意の数値データ型。

戻り値の型は入力式の型です。

次のビュー V について考えてみます。

a

b

group1

1

group1

2

group1

NULL

group2

4

例 1

SELECT SUM(b)
FROM v

sum

7

例 2

SELECT a, SUM(b)
FROM v
GROUP BY a

a

sum

group1

3

group2

4

VAR

説明

VAR 関数は、各値グループの標本分散を返します。

この関数は、 NULL 値を考慮せずに結果を計算します。

構文

VAR( <expression> ) : decimal
  • expression: 必須。任意の数値データ型。

ビュー sales_by_region について考えてみます。

region

state

revenue

South

Alabama

53168

South

Missisipi

5681

South

Tennessee

80166

Northeast

New York

12945

Northeast

Pennsylvania

69284

Northeast

New Hampshire

53168

SELECT region, VAR(revenue)
FROM revenue
GROUP BY region

region

var

South

1.4219870663333333E9

Northeast

8.419496843333333E8

VARP

説明

VARP 関数は、各値グループの母集団分散を返します。

この関数は、 NULL 値を考慮せずに結果を計算します。

構文

VARP( <expression> ) : double
  • expression: 必須。任意の数値データ型。

ビュー sales_by_region について考えてみます。

region

state

revenue

South

Alabama

53168

South

Missisipi

5681

South

Tennessee

80166

Northeast

New York

12945

Northeast

Pennsylvania

69284

Northeast

New Hampshire

53168

SELECT region, VARP(revenue)
FROM revenue
GROUP BY region

region

varp

South

9.479913775555555E8

Northeast

5.612997895555555E8