2.14.1　集合関数の形式と規則

(1)　機能

集合関数をSQL中に指定すれば，平均値，合計値，最大値，最小値，及び行数の算出，並びにXML型の値の結合ができます。

XMLAGG集合関数の詳細については，「XMLAGG」を参照してください。

集合関数の機能を次の表に示します。

表2‒24　集合関数の機能

項 目	集合関数
	AVG	SUM	MAX	MIN	COUNT	COUNT_FLOAT	XMLAGG
機能	平均値の算出	合計値の算出	最大値の算出	最小値の算出	行数の算出	行数の算出	XML型の値の結合
ナル値の扱い	無視	無視	無視	無視	無視※１	無視※１	無視
DISTINCT指定の意味	指定した値式の中で，値が重複する行を除いた後の平均値	指定した値式の中で，値が重複する行を除いた後の合計値	指定しても意味がない	指定しても意味がない	指定した値式の中で，値が重複する行を除いた行数	指定した値式の中で，値が重複する行を除いた行数	指定できません
適用対象が0件※３，又はナル値だけの集合の場合の関数値	ナル値	ナル値	ナル値	ナル値	0	0	ナル値
引数中での繰返し列の指定可否※２	指定できません	指定できません	指定できます	指定できます	指定できません	指定できません	指定できません

注※1

集合関数では，基本的にナル値を無視しますが，COUNT（＊）及びCOUNT_FLOAT（＊）の場合はナル値に関係なく，条件を満足するすべての行数を算出します。

注※2

集合関数MAX及びMINについては，FLAT指定を記述することで，引数に繰返し列を指定できます。繰返し列を指定した場合，集合関数MAX及びMINは適用対象である行中すべての要素から，最大値又は最小値を算出します。なお，列全体の値がナル値（要素数が0の繰返し列）の場合は，算出の対象外となります。

注※3

GROUP BY句又はHAVING句の指定がある場合，行数が0のグループの算出結果は出力しません。

列のデータ型と集合関数の値のデータ型の関係を次の表に示します。

表2‒25　列のデータ型と集合関数の値のデータ型の関係

集合関数の引数の データ型	AVG	SUM	MAX及びMIN	COUNT	COUNT_FLOAT	XMLAGG
INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	FLOAT	×
SMALLINT	INTEGER	INTEGER	SMALLINT	INTEGER	FLOAT	×
DECIMAL（p，s）	DECIMAL（max_prec※3，max_prec※3−p＋s）	DECIMAL（max_prec※3，s）	DECIMAL（p，s）	INTEGER	FLOAT	×
FLOAT	FLOAT	FLOAT	FLOAT	INTEGER	FLOAT	×
SMALLFLT	SMALLFLT	SMALLFLT	SMALLFLT	INTEGER	FLOAT	×
INTERVAL YEAR TO DAY	×	×	INTERVAL YEAR TO DAY	INTEGER	FLOAT	×
INTERVAL HOUR TO SECOND	×	×	INTERVAL HOUR TO SECOND	INTEGER	FLOAT	×
CHAR(n)	×	×	CHAR(n) ※１	INTEGER	FLOAT	×
VARCHAR(n)	×	×	VARCHAR(n) ※１	INTEGER	FLOAT	×
NCHAR(n)	×	×	NCHAR(n)	INTEGER	FLOAT	×
NVARCHAR(n)	×	×	NVARCHAR(n)	INTEGER	FLOAT	×
MCHAR(n)	×	×	MCHAR(n)	INTEGER	FLOAT	×
MVARCHAR(n)	×	×	MVARCHAR(n)	INTEGER	FLOAT	×
DATE	×	×	DATE	INTEGER	FLOAT	×
TIME	×	×	TIME	INTEGER	FLOAT	×
TIMESTAMP	×	×	TIMESTAMP	INTEGER	FLOAT	×
BINARY(n)※2	×	×	BINARY(n)	INTEGER	FLOAT	×
BLOB(n)	×	×	×	×	×	×
BOOLEAN	×	×	×	×	×	×
XML	×	×	×	×	×	XML
抽象データ型（XML型を除く）	×	×	×	×	×	×

（凡例）

×：使用できません。

注※1

結果の文字集合は集合関数の引数に指定した値式の文字集合になります。

注※2

nは32,000以下にしてください。

注※3

max_precはDECIMAL型の精度の最大値です。次の表に，max_precの値について示します。なお，pd_sql_dec_op_maxprecオペランドについては，マニュアル「HiRDB システム定義」を参照してください。

表2‒26　DECIMAL型の精度の最大値

システム共通定義pd_sql_dec_op_maxprecオペランドの値	集合関数の引数の精度p	max_precの値
29	p≦29	29
	p＞29	38
38	任意	38

ページの先頭へ

(2)　形式

　集合関数：：＝｛COUNT（＊）｜COUNT_FLOAT（＊）｜ALL集合関数｜DISTINCT集合関数｜XMLAGG集合関数｝
　ALL集合関数：：＝｛AVG｜SUM｜MAX｜MIN｜COUNT｜COUNT_FLOAT｝（〔ALL〕｛値式｜FLAT指定｝）
　DISTINCT集合関数：：＝｛AVG｜SUM｜MAX｜MIN｜COUNT｜COUNT_FLOAT｝（DISTINCT ｛値式｜FLAT指定｝）
　FLAT指定：：＝FLAT（列指定）

XMLAGG集合関数の形式については，「XMLAGG」を参照してください。

ページの先頭へ

(3)　副問合せを使用しない場合の規則

1. GROUP BY句，WHERE句，又はFROM句のうち，最後に指定された句の結果として得られる各グループが，集合関数の入力になります。ただし，GROUP BY句を指定しない場合，WHERE句，又はFROM句の結果がグループ化列（GROUP BY句で指定した値式）を持たない一つのグループになります。また，集合関数の演算結果はグループごとに得られます。

2. 集合関数は，SELECT句，及びHAVING句に指定できます。

3. GROUP BY句，HAVING句，又は集合関数を指定した場合，SELECT句，及びHAVING句中の列指定は，次のどちらかにしてください。

   • グループ化列（GROUP BY句で指定した値式）

   • 集合関数の引数中に指定

4. SELECT DISTINCTを指定した場合，DISTINCT集合関数は指定できません。

ページの先頭へ

(4)　副問合せを使用する場合の規則

1. 集合関数を副問合せ中に指定した場合，集合関数の入力対象になる集合を次に示します。

   • 集合関数は，問合せ指定（副問合せの括弧内の指定も含む）ごとに指定できます。その問合せ指定ごとに集合関数の入力対象の集合が決まります。

   • COUNT（＊）又はCOUNT_FLOAT（＊）の入力対象になる集合は，COUNT（＊）又はCOUNT_FLOAT（＊）を直接含む問合せ指定で決まります。そのほかの集合関数の入力対象になる集合は，その引数中で参照する表をFROM句で指定した問合せ指定で決まります。

   • 問合せ指定中のGROUP BY句，WHERE句，又はFROM句のうち，最後に指定された句の結果として得られる各グループが集合関数の入力になります。

   ただし，GROUP BY句を指定しないと，WHERE句，又はFROM句の結果が，グループ化列（GROUP BY句で指定した値式）を持たない一つのグループになります。また，集合関数の演算結果は，グループごとに得られます。

2. ある問合せ指定Qに対する集合関数は，その問合せ指定QのSELECT句，又はHAVING句中にだけ指定できます。そのHAVING句の副問合せ中のFROM句中のON探索条件，WHERE句，及びHAVING句にも，問合せ指定Qの表の列を引数として参照（外への参照）すると問合せ指定Qに対する集合関数を指定できます。

3. 問合せ指定中でGROUP BY句，若しくはHAVING句を指定する場合，又はSELECT句に集合関数を指定する場合，その問合せ指定のSELECT句，及びHAVING句中の列指定は次に示す条件が必要です。

   SELECT句中の列指定の場合

   • その問合せ指定中のFROM句の表を参照している。

   • グループ化列である，又は集合関数の引数中に指定している。

   HAVING句中の列指定の場合

   • その問合せ指定中のFROM句の表を参照しているか，又は外側の問合せ指定中のFROM句の表を参照（外側への参照）している。

   • その問合せ指定中のFROM句の表を参照している場合で，グループ化列，又は集合関数の引数中に指定している。

4. 問合せ指定中にSELECT DISTINCTを指定すると，その問合せ指定に対するDISTINCT指定の集合関数は指定できません。DISTINCT指定の集合関数をDISTINCT集合関数といいます。

5. 副問合せ中で集合関数の引数に，外への参照する列を含む演算は指定できません。

ページの先頭へ

(5)　共通規則

1. 集合関数は，SET句，IF文，WHILE文，SET文，RETURN文，WRITE LINE文，ADD句，及びGROUP BY句に指定できません。

2. 集合関数の引数中に，埋込み変数，及び?パラメタは指定できません。

   ただし，XMLAGG集合関数の値式にXMLQUERY関数を指定した場合，そのXMLQUERY関数のXML問合せ変数中の値式には埋込み変数又は？パラメタを指定できます。

3. 集合関数の引数中には，列指定（予備列を除く）を含む値式を指定してください。

4. 集合関数の引数中に，集合関数及びウィンドウ関数は指定できません。

5. 文字列データの大小関係は，集合関数の引数に指定した値式の文字集合で使用する文字コードに従います。

6. 各国文字列データの大小関係は，使用する各国文字コード（シフトJISコード，EUC日本語漢字コード，又はEUC中国語漢字コード）に従います。

7. 混在文字列データの大小関係は，ASCIIコードと使用する各国文字コード（シフトJISコード，EUC日本語漢字コード，EUC中国語漢字コード，中国語漢字コード（GB18030），又はUnicode（UTF-8））に従います。

8. 平均値（AVG）は，有効数字以下が切り捨てられます。

9. 計算途中でオーバフローが発生した場合はエラーになります。ただし，オーバフローエラー抑止が設定されている場合は，エラーにはなりません。オーバフローエラー抑止が設定されている場合，対象になる集合関数を次に示します。

   • AVG

   • SUM

   • COUNT

   • COUNT_FLOAT

   なお，オーバフローエラー抑止が設定されている場合の演算結果については，「オーバフローエラー抑止が設定されている場合の演算結果」を参照してください。

10. COUNT_FLOATの値が2の53乗（16けた）以上になると，値は丸められます。

11. 集合関数の引数中にコンポネント指定は指定できません。

12. 集合関数の引数中に繰返し列を指定する場合，次の規則に従ってください。

    • 繰返し列は，FLAT指定を記述した集合関数MAX又はMINだけ指定できます。

    • FLAT指定に記述する列指定は，添字のない繰返し列だけ指定できます。

    • GROUP BY句に列指定以外の値式を指定した問合せ指定には，FLAT指定を記述した集合関数は指定できません。

13. 問合せ指定ごとにDISTINCT集合関数は二つ以上指定できません。

    ただし，次の場合には，DISTINCT集合関数を二つ以上指定できます。

    • 集合関数MAX及びMINには，異なる値式を指定できます。

    • 集合関数AVG，SUM，COUNT及びCOUNT_FLOATには，同じ形式で記述した値式を指定できます。

14. 集合関数の引数に指定する値式中に，副問合せは指定できません。

ページの先頭へ

(6)　使用例

1. 在庫表（ZAIKO）のすべての商品の在庫量（ZSURYO）の平均を求めます。

     SELECT AVG(ZSURYO) FROM ZAIKO

2. 在庫表（ZAIKO）の商品名（SNAME）がスカートの行の在庫量（ZSURYO）の合計を求めます。

     SELECT N'スカート',SUM(ZSURYO)
         FROM ZAIKO
         WHERE SNAME = N'スカート'

3. 在庫表（ZAIKO）から単価（TANKA）と在庫量（ZSURYO）の積（金額）の合計を求めます。

     SELECT SUM(TANKA * ZSURYO)
         FROM ZAIKO

4. 在庫表（ZAIKO）から在庫量（ZSURYO）の最大値，最小値，及び件数の合計を求めます。

     SELECT COUNT(*),MAX(ZSURYO),MIN(ZSURYO)
         FROM ZAIKO

5. 次の競技結果表から，種目コードが'0001'の種目の最高点（最大値）及び最低点（最小値）を求めます。なお，得点列は要素数3の繰返し列です。

   

ページの先頭へ