集約関数の概要

集約関数とフィールドの組み合わせ

InterSystems SQL では、クエリ内の他の SELECT 項目と共に集約関数を指定できます。COUNT(*) などの集約関数は、別個のクエリ内に配置する必要はありません。

SELECT TOP 5 COUNT(*),Name,AVG(Age)
FROM Sample.Person
ORDER BY Name

選択リスト内で集約関数を指定して、フィールド選択項目を指定しない場合、InterSystems SQL は 1 行を返します。TOP 節は、TOP 0 (行を返さない) である場合を除いて無視されます。

SELECT TOP 7 AVG(Age),LIST(Age)
FROM Sample.Person
WHERE Age > 75

選択リスト内で集約関数と 1 つ以上のフィールド選択項目を指定した場合、InterSystems SQL は、フィールド項目に必要な数の行を返します。

SELECT DISTINCT Age,AVG(Age),LIST(Age)
FROM Sample.Person
WHERE Age > 75

列の名前とエイリアス

既定では、集約関数の結果に割り当てられる列名は Aggregate_n となります。ここで、n という数字接尾辞は、SELECT リストで指定されている列順序番号です。したがって、以下の例では、列名 Aggregate_2 および Aggregate_5 が作成されます。

SELECT TOP 5 Home_State,COUNT(*),Name,Age,AVG(Age)
FROM Sample.Person
ORDER BY Name

別の列名 (列のエイリアス) を指定するには、AS キーワードを使用します。

SELECT COUNT(*) AS PersonCount
FROM Sample.Person,Sample.Employee

列エイリアスを使用して、ORDER BY 節で集約フィールドを指定できます。.以下の例では、年齢が平均年齢から離れる順番で人々をリストします。

SELECT Name,Age,
    AVG(Age) AS AvgAge,
    ABS(Age - AVG(Age)) AS RelAge
FROM Sample.Person
ORDER BY RelAge

列エイリアスの詳細は、SELECT 文を参照してください。

ORDER BY の使用

LIST、%DLIST、XMLAGG、および JSON_ARRAYAGG の各関数は、複数の行のテーブル列の値を単一の集約値に結合します。すべての集約フィールドが評価された後に ORDER BY 節がクエリ結果セットに適用されるため、ORDER BY がこれらの集約内の値の順序に直接影響することはありません。特定の状況下では、これらの集約の結果が順番に表示されることがありますが、この順番を信頼すべきではありません。特定の集約結果値内にリストされる値を、明示的に順序付けすることはできません。

DISTINCT キーワード節

すべての集約関数で、オプションの DISTINCT キーワード節がサポートされています。このキーワードは、集約操作を、非重複 (一意) フィールド値のみに限定します。既定のフィールドの照合 (%SQLUPPER) を使用する場合、大文字と小文字のみが異なるフィールド値は個別値と見なされません。DISTINCT が指定されていない場合、既定で、重複値を含むすべての非 NULL 値に対して集約操作が実行されます。MIN および MAX 集約関数では、DISTINCTキーワードは、サポートされていますが操作は実行されません。

集約関数は、オプションの BY(item-list) 従属節を含めて、完全な DISTINCT キーワード節の構文をサポートします。詳細は、"DISTINCT 節" を参照してください。

集約関数の DISTINCT field1 節では、NULL の field1 値は無視されます。これは SELECT 文の DISTINCT 節とは異なります。SELECT DISTINCT 節では、個別のフィールド値ごとに 1 行が返されるのと同じように、個別の NULL ごとに 1 行が返されます。ただし、集約関数の DISTINCT BY(field2) field1 では、field2 の個別の NULL は無視されません。例えば、FavoriteColors に 50 個の個別値と複数の NULL が含まれている場合、返される DISTINCT 行数は 51、COUNT(DISTINCT FavoriteColors) は 50、COUNT(DISTINCT BY(FavoriteColors) %ID) は 51 になります。

SELECT DISTINCT FavoriteColors,
       COUNT(DISTINCT FavoriteColors),
       COUNT(DISTINCT BY(FavoriteColors) %ID)
 FROM Sample.Person

DISTINCT および GROUP BY との組み合わせ

SELECT DISTINCT を selectItem 集約関数および GROUP BY 節と共に使用した場合は、DISTINCT キーワードが指定されていない場合と同じ結果が返されます。希望の結果を得るには、その集約関数をサブクエリに配置します。

例えば、人数別の状態 (4 人の状態、6 人の状態などがあります) の数を返すとします。この結果が得られることを期待して、次のようにします。

SELECT DISTINCT COUNT(*) AS PersonCounts
FROM Sample.Person
GROUP BY Home_State

しかし実際には、次のように DISTINCT キーワードを指定しない場合と同じ、各状態の人数が得られます。

SELECT COUNT(*) AS PersonCounts
FROM Sample.Person
GROUP BY Home_State

目的の結果を得るには、次のようにサブクエリを使用する必要があります。

SELECT DISTINCT * 
FROM (SELECT COUNT(*) AS PersonCounts FROM Sample.Person
      GROUP BY Home_State)

行カウント

クエリが集約値を返す場合、%ROWCOUNT の値は以下のようにクエリに依存します。

• 集約関数のみ：集約値を計算し、%ROWCOUNT 1 を返します。集約関数のみのクエリで行が選択されない場合でも、%ROWCOUNT 1: COUNT=0 が返されます。その他の集約関数では、NULL が返されます。

• GROUP BY で集約関数のみ：GROUP BY 節で選択された各グループの集約値を返します。%ROWCOUNT は選択されたグループの数です。クエリで行が選択されない場合、GROUP BY はグループを選択せず、クエリは %ROWCOUNT 0 を返します。

• DISTINCT で集約関数のみ：集約値を計算し、%ROWCOUNT 1 を返します。クエリで行が選択されない場合、DISTINCT は個別の値を選択せず、クエリは %ROWCOUNT 0 を返します。

• TOP 節で集約関数のみ：ゼロでない TOP 値の場合、集約値を計算し、%ROWCOUNT 1 を返します。TOP=0 の場合は、%ROWCOUNT 0 を返します。集約は計算されません。

• フィールドでの集約：クエリがフィールド値と集約関数を返す場合、返される行数は選択された行の数です。クエリで行が選択されない場合、%ROWCOUNT 0 が返され、集約は計算されません。

これらの結果は、サブクエリまたは式の selectItem が存在しても影響を受けません。