概要
GROUP BY は SELECT コマンドの節です。オプションの GROUP BY 節は、FROM 節とオプションの WHERE 節の後、オプションの HAVING 節と ORDER BY 節の前に記述されます。
GROUP BY 節はクエリの結果行を受け取り、単独または複数のデータベース列によって結果行を個別のグループに分割します。SELECT を GROUP BY と併用するとき、GROUP BY フィールドの個別の各値に対して、1 行が取り出されます。GROUP BY は、NULL (値が指定されていない) を含むフィールドを別個の個別値グループとして扱います。
GROUP BY 節は概念的には InterSystems IRIS の集約関数の拡張キーワードである %FOREACH と似ていますが、%FOREACH はクエリ全体の母集団を制約することなく、サブ母集団で集約の選択ができるのに対し、GROUP BY はクエリ全体で実行します。
GROUP BY は、INSERT コマンドの SELECT 節で使用できます。GROUP BY は、UPDATE コマンドまたは DELETE コマンドでは使用できません。
フィールドの指定
GROUP BY 節の最も簡潔な形式では、GROUP BY City など、1 つのフィールドを指定します。これにより、一意の City 値ごとに任意の 1 つの行を選択します。フィールドのコンマ区切りリストを指定することもできます。このリストの結合された値は、単一のグループ化項として扱われます。これにより、City および Age 値の一意の組み合わせごとに任意の 1 つの行を選択します。したがって、GROUP BY City,Age は、GROUP BY Age,City と同じ結果を返します。
フィールドは、列名で指定する必要があります。有効な field 値としては、列名 (GROUP BY City) や、%ID (すべての行を返す) や、列名を指定するスカラ関数 (GROUP BY ROUND(Age,-1)) や、列名を指定する照合関数 (GROUP BY %EXACT(City)) があります。
列エイリアスでフィールドを指定できません。これを試行すると、SQLCODE -29 エラーが生成されます。列番号でフィールドを指定することはできません。これはリテラルとして解釈され、1 つの行を返します。集約フィールドを指定できません。これを試行すると、SQLCODE -19 エラーが生成されます。サブクエリを指定することはできません。これはリテラルとして解釈され、1 つの行を返します。
GROUP BY StreamField は実際のデータに対してではなく、ストリーム・フィールドの OID に対して機能します。すべてのストリーム・フィールド OID は一意の値であるため、GROUP BY は実際のストリーム・フィールドの重複データ値に影響を与えません。GROUP BY StreamField は、ストリーム・フィールドが NULL のレコード数を、1 レコードに削減します。詳細は、"ストリーム・データ (BLOB と CLOB) の格納と使用" を参照してください。
GROUP BY 節では、矢印構文 (–>) 演算子を使用して、ベース・テーブルではないテーブル内のフィールドを指定できます。例えば、GROUP BY Company->Name のように使用します。詳細は、"InterSystems SQL の使用法" の "暗黙結合 (矢印構文)" を参照してください。
GROUP BY 節内の field 値としてリテラルを指定すると、1 行が返されます。どの行が返されるかは不確定となります。したがって、7、'Chicago'、''、0、または NULL のどれを指定しても、1 行が返されます。ただし、コンマ区切りリスト内の field 値としてリテラルを指定すると、そのリテラルは無視され、GROUP BY が指定されたフィールド名の一意の組み合わせごとに任意の 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 が返されます。
クエリが集約関数のみで構成されていて、テーブルからのデータを返さない場合、集約関数に対して %ROWCOUNT=1 と空文字列 (または 0) の値が返されます。以下に例を示します。
SELECT AVG(Age) FROM Sample.Person WHERE Name %STARTSWITH 'ZZZZ'
ただし、このタイプのクエリにGROUP BY 節が含まれている場合は、%ROWCOUNT=0 が返され、集約関数の値は未定義のままになります。
照合、大文字/小文字の区別、および最適化
このセクションでは、大文字/小文字の区別のみが異なるデータ値を GROUP BY が処理する方法について説明します。
-
大文字/小文字の区別が異なる値を同一グループに分類する (大文字を返す)：
既定では、GROUP BY は、作成時に field に対して指定された照合に基づいて、文字列値をグループ化します。InterSystems IRIS には既定の文字列照合があり、ネームスペースごとにこれを設定できます。すべてのネームスペースにおける最初の文字列照合の既定値は SQLUPPER です。したがって、通常、指定がない限り、GROUP BY 照合では大文字と小文字は区別されません。
GROUP BY は、大文字の照合に基づいて SQLUPPER 照合でフィールドの値をグループ化します。大文字/小文字の区別のみが異なるフィールド値は、同一グループに分類されます。グループ化されたフィールド値は、すべて大文字で返されます。これには、GROUP BY が、実際のフィールド値にアクセスするのではなく、フィールドのインデックスを使用できるというパフォーマンス上の利点があります。そのため、インデックスが 1 つまたは複数の選択フィールドに対して存在する場合にのみ意味があります。この場合、結果的に、実際のデータ値がすべて大文字でなくても、GROUP BY フィールドの値はすべて大文字で返されます。
-
大文字/小文字の区別が異なる値を同一グループに分類する (実際の大文字/小文字を返す)：
GROUP BY は、大文字/小文字の区別が異なる値を同一グループに分類し、グループ化されたフィールドの値を実際のフィールドの大文字/小文字の値で返すことができます (ランダムに選択されます)。これには、返される値が実際の値であり、データ内の少なくとも 1 つの値の大文字/小文字の区別が表示されるという利点があります。フィールドのインデックスを使用できないというパフォーマンス上の欠点があります。select-item フィールドに %EXACT 照合関数を適用することで、個々のクエリに対してこれを指定することができます。
-
大文字/小文字の区別が異なる値を同一グループに分類しない (実際の大文字/小文字を返す)：
GROUP BY は、%EXACT 照合関数を GROUP BY フィールドに適用することによって、大文字/小文字を区別して値をグループ化することができます。これには、大文字/小文字の区別が異なる値をすべて別々のグループとして返すという利点があります。フィールドのインデックスを使用できない、というパフォーマンス上の欠点があります。
この動作は、管理ポータルを使用して、GROUP BY 節を含むすべてのクエリを対象としてシステム全体について構成できます。 [システム管理]、[構成]、[SQL およびオブジェクトの設定]、[SQL] の順に選択します。[GROUP BY および DISTINCT クエリで元の値を生成する] チェック・ボックスを表示して編集します。既定では、このチェック・ボックスにはチェックが付いていません。この既定の設定では、アルファベット値が大文字の照合によってグループ化されます。 (この最適化は DISTINCT 節にも有効です)。詳細は、"システム管理ガイド" にリストされている SQL およびオブジェクトの設定ページを参照してください。
また、$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドの FastDistinct オプションを使用して、このオプションをシステム全体で設定することもできます。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[DISTINCT の最適化を有効にする] 設定が表示されます。既定値は 1 です。
この最適化は選択したフィールドのインデックスを利用します。そのため、インデックスが 1 つまたは複数の選択したフィールドに対して存在する場合にのみ意味があります。インデックス内に格納されるときにフィールド値が照合され、アルファベット文字列はすべて大文字で返されます。このシステム全体用オプションを設定した後に、特定のクエリのその設定を、%EXACT 照合関数を使用することで大文字/小文字区別を維持するように上書きできます。
以下の例は、これらの動作を示しています。これらの例では、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. */
%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)
QUIT:(SQLCODE'=0)
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 文は、コミットされているかどうかに関係なく、実行されたすべての変更データを返します。
