集約関数の概要
サポートされている集約関数
集約関数は、1 つの列内の 1 つ以上の値に対して処理を行い、1 つの値を返します。サポートされる関数は以下のとおりです。
-
SUM — 指定した列内の合計値を返します。
-
AVG — 指定した列の平均値を返します。
-
COUNT — テーブル内の行数、または指定した列内の NULL でない値の数を返します。
-
MAX — 指定した列内で使用されている最大値を返します。
-
MIN — 指定した列内で使用されている最小値を返します。
-
VARIANCE、VAR_SAMP、VAR_POP — 指定した列の値の統計的分散を返します。
-
STDDEV、STDDEV_SAMP、STDDEV_POP — 指定した列の値の統計的標準偏差を返します。
-
LIST — 指定した列で使用されているすべての値を、コンマで区切られたリストの形式で返します。
-
%DLIST — 指定した列で使用されているすべての値を、Caché リスト構造内の要素として返します。
-
XMLAGG — 指定した列で使用されているすべての値を、連結された文字列として返します。
-
JSON_ARRAYAGG — 指定した列で使用されているすべての値を、JSON フォーマットの配列として返します。
集約関数では、NULL のフィールドは無視されます。例えば、指定したフィールドが NULL だった場合は、LIST および %DLIST には、その行の要素は含まれません。COUNT では、指定したフィールドの非 NULL 値のみが数えられます。
すべての集約関数で、オプションの 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
集約関数 (COUNT を除く) は、ストリーム・フィールドに適用できません。これを実行しようとすると、SQLCODE -37 エラーが生成されます。COUNT は、ストリーム・フィールド値のカウントに使用できますが、多少の制約があります。
集約関数の使用
集約関数は、以下のような箇所で使用できます。
-
SELECT リスト。リストされた select-item としてか、サブクエリの select-item 内で使用できます。
集約関数は、エラーの発生なく DISTINCT BY 節内で指定できますが、この使用法には効果がなく、必ず単一の行が返されます。
集約関数は、以下のような箇所で直接使用することはできません。
-
ORDER BY 節。これを実行しようとすると、SQLCODE -73 エラーが生成されます。ただし、ORDER BY 節内で集約関数の列エイリアスを指定することは可能です。
-
WHERE 節。これを実行しようとすると、SQLCODE -19 エラーが生成されます。
-
GROUP BY 節。これを実行しようとすると、SQLCODE -19 エラーが生成されます。
-
TOP 節。これを実行しようとすると、SQLCODE -1 エラーが生成されます。
-
JOIN。ON 節で集約を指定しようとすると、SQLCODE -19 エラーが生成されます。USING 節で集約を指定しようとすると、SQLCODE -1 エラーが生成されます。
ただし、サブクエリの使用によって、集約関数値を WHERE 節または HAVING 節で指定することはできます。例えば、WHERE 節を使用して平均 Age 値より小さい Age 値を選択するために、サブクエリ内で AVG 集約関数を使用できます。
SELECT Name,Age,AvgAge
FROM (SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person)
WHERE Age < AvgAge
ORDER BY Age
集約関数とフィールドの組み合わせ
Caché SQL では、クエリ内の他の SELECT 項目と共に集約関数を指定できます。COUNT(*) などの集約関数は、別個のクエリ内に配置する必要はありません。
SELECT TOP 5 COUNT(*),Name,AVG(Age)
FROM Sample.Person
ORDER BY Name
選択リスト内で集約関数を指定して、フィールド選択項目を指定しない場合、Caché SQL は 1 行を返します。TOP 節は、TOP 0 (行を返さない) である場合を除いて無視されます。
SELECT TOP 7 AVG(Age),LIST(Age)
FROM Sample.Person
WHERE Age > 75
選択リスト内で集約関数と 1 つ以上のフィールド選択項目を指定した場合、Caché 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 および GROUP BY との組み合わせ
SELECT DISTINCT を select-item 集約関数および 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)
行カウント
1 つ以上の集約関数を含むクエリでは、%ROWCOUNT の値は以下のようにクエリに依存します。
-
選択リストに FROM 節のテーブル内のフィールド (集約関数に提供されているフィールド以外) への参照が含まれていなければ、クエリは行を選択しない場合に %ROWCOUNT 1 を返します。COUNT は 0 を返し、その他の集約関数は NULL を返します。
ZNSPACE "SAMPLES" SET q1="SELECT COUNT(*) AS NumRows,COUNT(FavoriteColors) AS NumColors,"_ "AVG(Age) AS AvgAge,MAX(Age) AS MaxAge FROM Sample.Person" SET q2="SELECT COUNT(*) AS NumRows,COUNT(FavoriteColors) AS NumColors,"_ "AVG(Age) AS AvgAge,MAX(Age) AS MaxAge FROM Sample.Person WHERE Name='ZZZ'" SET tStatement = ##class(%SQL.Statement).%New() QueryReturnsData SET qStatus = tStatement.%Prepare(q1) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"Rowcount with data:",rset.%ROWCOUNT,!! QueryReturnsNoData SET qStatus = tStatement.%Prepare(q2) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"Rowcount without data:",rset.%ROWCOUNT
FROM 節のテーブル内のフィールドを参照していない限り、select-item リストはその他の項目を含むことができます。
ZNSPACE "SAMPLES" SET q1="SELECT COUNT(*) AS NumRows,COUNT(Name) AS NumNames,"_ "(SELECT Name FROM Sample.Company) AS SubQ,$LENGTH('this string'),%CLASSNAME "_ "FROM Sample.Person WHERE Name='ZZZ'" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(q1) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"Rowcount:",rset.%ROWCOUNT
ただし、このタイプのクエリが、行を選択しないで GROUP BY 節を使用している場合は、%ROWCOUNT 0 を返します。
-
TOP 0 を含むクエリは常に %ROWCOUNT 0 を返します。集約関数は評価されません。
ZNSPACE "SAMPLES" SET q1="SELECT TOP 0 COUNT(*) AS NumRows,COUNT(Name) AS NumNames,"_ "AVG(Age) AS AvgAge FROM Sample.Person" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(q1) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"Rowcount:",rset.%ROWCOUNT
-
集約関数と集約フィールドを含むクエリは、返される行の %ROWCOUNT を返します。結果セットに行が含まれない場合、クエリは %ROWCOUNT 0 を返し、COUNT は NULL を返します。
ZNSPACE "SAMPLES" SET q1="SELECT TOP 4 COUNT(*) AS NumRows,COUNT(Name) AS NumNames,"_ "Name,AVG(Age) AS AvgAge FROM Sample.Person" SET q2="SELECT TOP 4 COUNT(* )AS NumRows,COUNT(Name) AS NumNames,"_ "Name,AVG(Age) AS AvgAge FROM Sample.Person WHERE Name='ZZZ'" SET tStatement = ##class(%SQL.Statement).%New() QueryReturnsData SET qStatus = tStatement.%Prepare(q1) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"Rowcount with data:",rset.%ROWCOUNT,!! QueryReturnsNoData SET qStatus = tStatement.%Prepare(q2) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"Rowcount without data:",rset.%ROWCOUNT
集約、トランザクション、およびロック
クエリに集約関数を含めると、クエリはすべての結果セット・フィールドにデータの現在の状態を返します。それには、コミットされていないデータ変更が含まれます。そのため、集約関数を含むクエリでは ISOLATION LEVEL READ COMMITTED 設定は無視されます。コミットされていないデータの現在の状態は以下のとおりです。
-
INSERT と UPDATE : 変更がまだコミットされておらず、ロールバックされる可能性があっても、変更された値は集約の計算に含まれます。
-
DELETE と TRUNCATE TABLE : 削除がまだコミットされておらず、ロールバックされる可能性があっても、削除された行は集約の計算に含まれません。
一般に、集約関数は多数の行のデータを使用するため、集計の計算に関係するすべての行にトランザクションのロックを発行することはできません。そのため集約の計算中に、別のユーザが、そのデータを変更するトランザクションを実行している可能性があります。