7.22.16　集合関数共通の規則と留意事項

(1)　用語の説明

1. DISTINCTを指定した一般集合関数をDISTINCT集合関数といいます。また，ALLを指定した一般集合関数をALL集合関数といいます。

2. 次の値式を集合関数の被集約引数といいます。

   • 逆分布関数PERCENTILE_CONTおよびPERCENTILE_DISCの場合は，WITHINグループ指定中のソートキーに指定した値式

     （例）

     SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY "C1") FROM "T1"

     下線部分が被集約引数になります。

   • ARRAY_AGG集合関数の場合は，引数に指定した値式，およびソート指定リスト中のソートキーに指定した値式

     （例）

     SELECT "C1",ARRAY_AGG("C2" ORDER BY "C3") AS "C2_LIST" FROM "T1" GROUP BY "C1"

     下線部分が被集約引数になります。

   • LISTAGG集合関数の場合は，引数に指定した値式，およびWITHINグループ指定中のソートキーに指定した値式

     （例）

     SELECT LISTAGG("C1",'|') WITHIN GROUP(ORDER BY "C2") AS "C1_LIST" FROM "T1"

     下線部分が被集約引数になります。

   • 上記以外の集合関数の場合は，集合関数の引数に指定した値式

     （例）

     SELECT "C1",SUM("C2") FROM "T1" GROUP BY "C1"

     下線部分が被集約引数になります。

3. 被集約引数中に含まれる列指定を被集約列指定といいます。

   （例）

   SELECT "C1",SUM("C2"+1) FROM "T1" GROUP BY "C1"

   下線部分が被集約列指定になります。

4. 被集約列指定が参照する表参照を含むFROM句を直接含む問合せ指定を，その被集約列指定の修飾問合せといいます。

   （例）

   SELECT "C1",SUM("T1"."C2") FROM "T1" GROUP BY "C1"

   下線部分（全体）が修飾問合せになります。

外への参照がある場合の修飾問合せの例を説明します。

（例）

SELECT "C1" FROM "T1" GROUP BY "C1" HAVING EXISTS   ...[1]
    (SELECT * FROM "T2" WHERE MAX("T1"."C2")>"T2"."C1")

［説明］

• 集合関数MAX("T1"."C2")の被集約列指定は"T1"."C2"になります。

• "T1"."C2"が参照する表参照は"T1"になります。

• "T1"をFROM句に直接含む問合せ指定は，[1]の部分になります。

• この問合せの修飾問合せは，[1]の部分の問合せ指定になります。

ページの先頭へ

(2)　共通の規則

1. 集合関数は，その集合関数の修飾問合せに直接含まれる選択式，HAVING句，またはORDER BY句に指定できます。ただし，ORDER BY句に集合関数を指定する場合は制限事項があります。制限事項については，「7.24.2　ORDER BY句にソート指定リストを指定した場合の規則」の「(2)　ソートキーに値式を指定した場合の規則」を参照してください。

2. 被集約引数に指定した値式が単独の列指定ではない場合，同一問合せ指定中に逆分布関数を複数指定できません。

   （例）エラーになるSQL文の例

   SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "C1"+"C2"),
          PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY "C1"+"C2")
       FROM "T1"

   被集約引数に指定した値式が単独の列指定ではないため，逆分布関数を複数指定できません。

3. 同一問合せ指定中に逆分布関数を複数指定する場合，被集約引数に指定する列指定は，同じ列を参照する必要があります。

   （例）エラーになるSQL文の例

   SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "C1"),
          PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "C2")
       FROM "T1"

   逆分布関数の列指定は，同じ列を参照する必要があります。

   メモ

   逆分布関数を複数指定できる例を次に示します。

   （例）

   SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "C1"),
          PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY "GC1")
       FROM "T1"
       GROUP BY "C1" AS "GC1"

   逆分布関数の列指定は，同じグループ化列を参照しているため，エラーにはなりません。

4. 同一問合せ指定中に逆分布関数を複数指定する場合，WITHINグループ指定のソート指定に指定する順序付け指定は，すべて同じにしてください。

   （例）エラーになるSQL文の例

   SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "C1" ASC),
          PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "C1" DESC)
       FROM "T1" 

5. 集合関数の被集約引数に指定する値式には，？パラメタを指定できません。

   （例）エラーになるSQL文の例

   SELECT MAX(CASE WHEN "C1">? THEN "C1" ELSE "C1"*100 END) FROM "T1"

   下線部分に？パラメタを指定できません。

6. 集合関数中に，集合関数，副問合せ，ウィンドウ関数，およびスカラ関数RANDOMROWは指定できません。

   （例）エラーになるSQL文の例

   SELECT SUM(CASE WHEN MAX("C1")>10000 THEN "C1" END) FROM "T1"

   下線部分に集合関数は指定できません。

7. 被集約引数が異なるDISTINCT集合関数（ウィンドウ関数中に指定したDISTINCT集合関数は含みません）は，同一問合せ指定中に64個まで指定できます。

8. 集合関数の被集約引数に指定する値式に単独の列指定以外の指定をした場合，その値式中に含まれる列指定には外への参照列を指定できません。

   （例）エラーになるSQL文の例

   SELECT SUM("C1") FROM "T1"
      HAVING EXISTS(SELECT * FROM "T2" WHERE AVG("T1"."C2"*1.05)>"C2")

   下線部分に外への参照列は指定できません。

9. FROM句，WHERE句，またはGROUP BY句のうち，最後に指定した句の結果が集合関数の入力となります。GROUP BY句の指定がある場合は，グループごとの結果が集合関数の入力となります。

10. 集合関数をウィンドウ関数として使用した場合，現在行のウィンドウ枠に含まれる行集合を集合関数の入力とします。

11. 次の被集約引数には，外への参照列を指定できません。

    • 逆分布関数の被集約引数

    • ARRAY_AGG集合関数の被集約引数

    • LISTAGG集合関数の被集約引数

12. 次のソート指定リストには，ソート指定を2つ以上指定できません。

    • 逆分布関数に指定するソート指定リスト

    • ARRAY_AGG集合関数に指定するソート指定リスト

    • LISTAGG集合関数に指定するソート指定リスト

ページの先頭へ

(3)　共通の留意事項

1. DISTINCT集合関数，逆分布関数，ARRAY_AGG集合関数，またはLISTAGG集合関数を指定した場合，作業表が作成されることがあります。作業表が作成される作業表用DBエリアの容量が正しく見積もられていない場合，性能低下の原因となることがあります。作業表用DBエリアの容量見積もりについては，マニュアルHADB　システム構築・運用ガイドを参照してください。作業表の詳細については，マニュアルHADB　AP開発ガイドの作業表が作成されるSQLを実行する際の考慮点を参照してください。

2. DISTINCT集合関数，ARRAY_AGG集合関数，またはLISTAGG集合関数を指定した場合，導出表が作成されることがあります。導出表の相関名は，HADBが次の名称規則に従って決定します。

   ##DRVTBL_xxxxxxxxxx

   xxxxxxxxxxは，10桁の整数です。

3. GROUP BY句またはHAVING句の指定がある場合，入力行数が0のグループの実行結果は出力されません。

ページの先頭へ