GROUP BY

1 つ以上の列に基づいて、クエリの結果行をグループ化する SELECT 節です。

Synopsis

SELECT ...
GROUP BY field

引数

field

1 つ以上のデータ取得元フィールド。1 つのフィールド名、またはコンマで区切られたフィールド名のリスト。

概要

GROUP BY は SELECT 文の節です。オプションの GROUP BY 節は、FROM 節とオプションの WHERE 節の後、オプションの HAVING 節と ORDER BY 節の前に記述されます。

GROUP BY 節はクエリの結果行を受け取り、単独または複数のデータベース列によって結果行を個別のグループに分割します。SELECT を GROUP BY と併用するとき、GROUP BY フィールドの個別の各値に対して、1 行が取り出されます。GROUP BY は、NULL (値が指定されていない) を含むフィールドを別個の個別値グループとして扱います。

GROUP BY 節は概念的には Caché の拡張である %FOREACH と似ていますが、%FOREACH はクエリ全体の母集団を制約することなく、サブ母集団で集約の選択ができるのに対し、GROUP BY はクエリ全体で実行します。

フィールドの指定

GROUP BY 節の最も簡潔な形式では、GROUP BY Home_State など、1 つのフィールドを指定します。フィールドは、その列名で指定する必要があります。列エイリアスや列番号によってフィールドを指定することはできません。集約フィールドを指定できません。これを試行すると、SQLCODE -19 エラーが生成されます。

有効なフィールド値としては、列名 (GROUP BY Home_State) や、%ID (すべての行を返す) や、列名を指定するスカラ関数 (GROUP BY ROUND(Age,-1)) や、列名を指定する照合関数 (GROUP BY %EXACT(Home_City)) があります。

GROUP BY 節では、矢印構文 (–>) 演算子を使用して、ベース・テーブルではないテーブル内のフィールドを指定できます。例えば、GROUP BY Company->Name のように使用します。詳細は、"Caché SQL の使用法" の"暗黙結合" を参照してください。

GROUP BY 節内で 1 つのリテラルを指定すると、1 行が返されます。どの行が返されるかは不確定となります。したがって、7、'Chicago'、''、0、または NULL のどれを指定しても、1 行が返されます。

GROUP BY および DISTINCT BY での集約関数

GROUP BY 節は、集約関数が計算される前に適用されます。以下の例では、COUNT 集約関数で、各 GROUP BY グループ内の行数をカウントします。

SELECT Home_State,COUNT(Home_State)
FROM Sample.Person
GROUP BY Home_State

DISTINCT BY 節は、集約関数が計算された後に適用されます。以下の例では、COUNT 集約関数で、テーブル全体の行数をカウントします。

SELECT DISTINCT BY(Home_State) Home_State,COUNT(Home_State)
FROM Sample.Person

テーブル全体に対する集約関数を計算するには、GROUP BY グループではなく、select-item サブクエリを指定します。

SELECT Home_State,(SELECT COUNT(Home_State) FROM Sample.Person)
FROM Sample.Person
GROUP BY Home_State

選択リストが集約フィールドで構成されている場合、DISTINCT 節で GROUP BY 節を使用しないでください。例えば、次のクエリは Home_State 値が同じ人の数を返すことを意図して作成したものです。

/* This query DOES NOT apply the DISTINCT keyword */
/* It is provided as a cautionary example  */
SELECT DISTINCT COUNT(*) AS mynum
FROM Sample.Person 
GROUP BY Home_State
ORDER BY mynum

このクエリの場合、DISTINCT キーワードを適用していないので、想定した結果が得られません。DISTINCT 集約と GROUP BY 節の両方を適用するには、次の例で示すようにサブクエリを使用します。

SELECT DISTINCT *
FROM (SELECT COUNT(*) AS mynum
      FROM Sample.Person 
      GROUP BY Home_State) AS Sub
ORDER BY Sub.mynum

この例では、Home_State 値が同じ人の数が正しく得られます。例えば、ある Home_State 値を持つ人が 8 人いる場合、このクエリからは 8 が返されます。

テーブルのデータに集約関数が適用されない場合は、集約について NULL (または 0) 値と共に %ROWCOUNT=1 が返されます。以下に例を示します。

SELECT AVG(Age) FOR Sample.Person WHERE Name %STARTSWITH 'ZZZZ'

ただし、このタイプのクエリに GROUP BY 節が含まれている場合は、%ROWCOUNT=0 が返されます。

SELECT AVG(Age) FOR Sample.Person WHERE Name %STARTSWITH 'ZZZZ' GROUP BY Home_State

照合、大文字/小文字の区別、および最適化

このセクションでは、大文字/小文字の区別のみが異なるデータ値を GROUP BY が処理する方法について説明します。

大文字/小文字の区別が異なる値を同一グループに分類する (大文字を返す)：
既定では、GROUP BY は、作成時に field に対して指定された照合に基づいて、文字列値をグループ化します。Caché には既定の文字列照合があり、ネームスペースごとにこれを設定できます。すべてのネームスペースにおける最初の文字列照合の既定値は SQLUPPER です。したがって、通常、指定がない限り、GROUP BY 照合では大文字と小文字は区別されません。
GROUP BY は、大文字の照合に基づいて SQLUPPER 照合でフィールドの値をグループ化します。大文字/小文字の区別のみが異なるフィールド値は、同一グループに分類されます。グループ化されたフィールド値は、すべて大文字で返されます。これには、GROUP BY が、実際のフィールド値にアクセスするのではなく、フィールドのインデックスを使用できるというパフォーマンス上の利点があります。この場合、結果的に、実際のデータ値がすべて大文字でなくても、GROUP BY フィールドの値はすべて大文字で返されます。
大文字/小文字の区別が異なる値を同一グループに分類する (実際の大文字/小文字を返す)：
GROUP BY は、大文字/小文字の区別が異なる値を同一グループに分類し、グループ化されたフィールドの値を実際のフィールドの大文字/小文字の値で返すことができます (ランダムに選択されます)。これには、返される値が実際の値であり、データ内の少なくとも 1 つの値の大文字/小文字の区別が表示されるという利点があります。フィールドのインデックスを使用できないというパフォーマンス上の欠点があります。select-item フィールドに %EXACT 照合関数を適用することで、個々のクエリに対してこれを指定することができます。管理ポータルを使用して、GROUP BY 節を含むすべてのクエリに対してこの動作を構成できます。[システム管理]、[構成]、[SQL およびオブジェクトの設定]、[一般SQL設定] を選択します。[最適化] タブで、[DISTINCT の最適化を有効にする] チェック・ボックスのチェックを外します。既定では、このチェック・ボックスにチェックが付いており、アルファベットの値が大文字の照合によってグループ化されます。
大文字/小文字の区別が異なる値を同一グループに分類しない (実際の大文字/小文字を返す)：
GROUP BY は、%EXACT 照合関数を GROUP BY フィールドに適用することによって、大文字/小文字を区別して値をグループ化することができます。これには、大文字/小文字の区別が異なる値をすべて別々のグループとして返すという利点があります。フィールドのインデックスを使用できない、というパフォーマンス上の欠点があります。

以下の例は、これらの動作を示しています。これらの例では、Sample.Person に、SQLUPPER 照合、ならびに ‘New York’ および ‘new york’ という Home_City フィールド値があるレコードが含まれているとします。

SELECT Home_City FROM Sample.Person GROUP BY Home_City
/* groups together Home_City values by their uppercase letter values
   returns the name of each grouped city in uppercase letters. 
   Thus, 'NEW YORK' is returned.                          */

SELECT %EXACT(Home_City) FROM Sample.Person GROUP BY Home_City
/* groups together Home_City values by their uppercase letter values
   returns the name of a grouped city in original letter case. 
   Thus, 'New York' or 'new york' may be returned, but not both. */

SELECT Home_City FROM Sample.Person GROUP BY %EXACT(Home_City)
/* groups together Home_City values by their original letter case
   returns the name of each grouped city in original letter case. 
   Thus, both 'New York' and 'new york' are returned as separate groups. */

管理ポータルを使用して、GROUP BY 節を含むクエリの最適化を構成できます。[システム管理]、[構成]、[SQL およびオブジェクトの設定]、[一般SQL設定] を選択します。[最適化] タブで、[DISTINCT の最適化を有効にする] チェック・ボックス・オプションを表示および設定します。(この最適化は DISTINCT 節にも有効です)。既定はチェックを付けます (“はい” に設定)。この最適化が適用されると、アルファベット値が大文字で照合されてグループ化されます。詳細は、"Caché 詳細構成設定リファレンス" で説明されている SQL 構成設定を参照してください。

$SYSTEM.SQL.SetFastDistinct()Opens in a new tab メソッドを使用して、このシステム全体のオプションを 1 または 0 に設定することもできます。

  WRITE $SYSTEM.SQL.SetFastDistinct(1)

この最適化は選択したフィールドのインデックスを利用します。そのため、インデックスが 1 つまたは複数の選択したフィールドに対して存在する場合にのみ意味があります。インデックス内に格納されるときにフィールド値が照合され、アルファベット文字列はすべて大文字で返されます。このシステム全体用オプションを設定した後に、特定のクエリのその設定を、%EXACT 照合関数を使用することで大文字/小文字区別を維持するように上書きできます。

%ROWID

GROUP BY 節を指定すると、カーソル・ベースの埋め込み SQL クエリで %ROWID 変数が設定されなくなります。GROUP BY によって返される行が制限されない場合であっても、%ROWID は設定されません。以下に例を示します。

   SET %ROWID=999
   &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT Name, Home_State
        INTO :name,:state FROM Sample.Person
        WHERE Home_State %STARTSWITH 'M' 
        GROUP BY Home_State)
   &sql(OPEN EmpCursor)
   FOR { &sql(FETCH EmpCursor)
        QUIT:SQLCODE  
        WRITE !,"RowID: ",%ROWID," row count: ",%ROWCOUNT
        WRITE " Name=",name," State=",state
  }
   &sql(CLOSE EmpCursor)

クエリ動作のこの変更は、カーソル・ベースの埋め込み SQL SELECT クエリにのみ適用されます。ダイナミック SQL SELECT クエリと非カーソル埋め込み SQL SELECT クエリでは、%ROWID が設定されることはありません。

トランザクションでのコミットされた変更

GROUP BY 節を含むクエリでは、READ COMMITTED 分離レベルはサポートされていません。READ COMMITTED として定義されたトランザクションでは、GROUP BY 節がない SELECT 文は、コミットされている変更データのみを返します。つまり、このような文は現在のトランザクションの前のデータの状態を返します。GROUP BY 節のある SELECT 文は、コミットされているかどうかに関係なく、実行されたすべての変更データを返します。

例

以下の例では、最初の文字で名前をグループ化しています。最初の文字、その最初の文字を共有する名前の数、および名前の値の 1 つの例を返します。名前は、実際の値の大文字/小文字の区別に関係なく、SQLUPPER 照合を使用してグループ化されます。Name select-item に含まれている最初の文字が大文字であることに注意してください。％EXACT 照合は、実際の名前の値を表示するために使用されます。

SELECT Name AS Initial,COUNT(Name) AS SameInitial,%EXACT(Name) AS Example
FROM Sample.Person GROUP BY %SQLUPPER(Name,2)