GROUP BY GROUPING SETS

GROUPING SETS 句は、SELECT 文の GROUP BY 句の拡張です。GROUPING SETS 句を使用すると、複数の SELECT 文を使用しなくても結果を複数の方法でグループ化できます。つまり、応答時間を減らし、パフォーマンスを向上させることができます。

たとえば、次の 2 つのクエリ文はセマンティック上同義です。ただし、2 番目のクエリでは GROUP BY GROUPING SETS 句を使用することで、グループ化基準をより効率的に定義します。

複数の SELECT 文を使用した複数のグループ化

SELECT NULL, NULL, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
   UNION ALL
SELECT City, State, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY City, State
   UNION ALL
SELECT NULL, NULL, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY CompanyName;

GROUPING SETS を使用した複数のグループ化

SELECT City, State, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) );

どちらの方法でも、次に示す同じ結果が生成されます。

	City	State	CompanyName	Cnt
1	(NULL)	(NULL)	(NULL)	8
2	(NULL)	(NULL)	Cooper Inc.	1
3	(NULL)	(NULL)	Westend Dealers	1
4	(NULL)	(NULL)	Toto's Active Wear	1
5	(NULL)	(NULL)	North Land Trading	1
6	(NULL)	(NULL)	The Ultimate	1
7	(NULL)	(NULL)	Molly's	1
8	(NULL)	(NULL)	Overland Army Navy	1
9	(NULL)	(NULL)	Out of Town Sports	1
10	'Pembroke'	'MB'	(NULL)	4
11	'Petersburg'	'KS'	(NULL)	1
12	'Drayton'	'KS'	(NULL)	3

ロー 2 ～ 9 は、CompanyName ごとにグループ化して生成されたローで、ロー 10 ～ 12 は、City と State の組み合わせでグループ化されたローです。ロー 1 は、一致したカッコ () のペアを使用して指定される空のグループ化セットで表される総合計です。空のグループ化セットは、GROUP BY に対する入力のすべてのローの分割 1 つを表します。

NULL 値は、グループ化セットで使用されない任意の式のプレースホルダとして使用されていることに注意してください。これは、結果セットが結合可能である必要があるためです。たとえば、ロー 2 ～ 9 は、クエリの 2 番目のグループ化セット (CompanyName) から得られます。グループ化セットには式として City または State が含まれないため、ロー 2 ～ 9 では City と State の値にプレースホルダの NULL が含まれますが、CompanyName の値には CompanyName に出現する重複しない値が含まれます。

NULL はプレースホルダとして使用されるため、プレースホルダの NULL とデータ内の実際の NULL を混乱しがちです。プレースホルダの NULL を NULL データと区別するには、GROUPING 関数を使用してください。GROUPING 関数を使用したプレースホルダの NULL の検出を参照してください。

例

次の例では、クエリから返される結果を調整するために GROUPING SETS を使用する方法と、結果をわかりやすく整理するために ORDER BY 句を使用する方法を示します。クエリは、各年 (Year) の四半期 (Quarter) ごとの注文の合計数と、年 (Year) ごとの合計数を返します。年 (Year)、四半期 (Quarter) の順で並べることで、結果を理解しやすくなります。

SELECT Year( OrderDate ) AS Year, 
        Quarter( OrderDate ) AS Quarter, 
        COUNT (*) AS Orders 
FROM SalesOrders
GROUP BY GROUPING SETS ( ( Year, Quarter ), ( Year ) )
ORDER BY Year, Quarter;

このクエリは、次の結果を返します。

	Year	Quarter	Orders
1	2000	(NULL)	380
2	2000	1	87
3	2000	2	77
4	2000	3	91
5	2000	4	125
6	2001	(NULL)	268
7	2001	1	139
8	2001	2	119
9	2001	3	10

ロー 1 と 6 は、それぞれ 2000 年と 2001 年の注文の小計です。ロー 2 ～ 5 とロー 7 ～ 9 は、小計ローのディテール・ローに当たります。つまり、これらのローは、四半期ごとと年ごとの注文の合計数を示します。

この結果セットには、すべての年のすべての四半期の総合計がありません。総合計が含まれるようにするには、クエリで GROUPING SETS 指定に空のグループ化指定「()」を含める必要があります。

空のグループ化指定の使用

GROUP BY 句で空の GROUPING SETS 指定「()」を使用すると、結果で合計されるすべての項目について総合計のローが生成されます。総合計の行では、すべてのグループ化の式に対するすべての値にプレースホルダの NULLが含まれます。GROUPING 関数を使用すると、ローの基本となるデータで値を評価するため、プレースホルダの NULL と実際の NULL を区別できます。GROUPING 関数を使用したプレースホルダの NULL の検出を参照してください。

重複したグループ化セットの指定

GROUPING SETS 句では、重複したグループ化指定を使用できます。この場合、SELECT 文の結果に同一のローが含まれます。

次のクエリには、重複したグループ化が含まれます。

SELECT City, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY GROUPING SETS( ( City ), ( City ) );

このクエリは、次の結果を返します。重複したグループ化の結果として、ロー 1 ～ 3 がロー 4 ～ 6 と同一であることに注意してください。

	City	Cnt
1	'Drayton'	3
2	'Petersburg'	1
3	'Pembroke'	4
4	'Drayton'	3
5	'Petersburg'	1
6	'Pembroke'	4

適切な形式の実践

GROUP BY GROUPING SETS 句でのグループ化構文の解釈は、単純な GROUP BY 句の場合とは異なります。たとえば、GROUP BY (X, Y) は X と Y の値の異なる組み合わせによってグループ化されます。しかし GROUP BY GROUPING SETS (X, Y) の場合は、2 つの個別のグループ化セットを指定し、その 2 つのグループ化の結果がユニオンされます。つまり、結果は (X) でグループ化されてから、(Y) でグループ化された同じ結果にユニオンされます。

適切な形式を使用し、複雑な式の場合のあいまいさを避けるために、エラーが発生する可能性がある場合は指定内の各グループ化セットをカッコで囲んでください。たとえば、次の両方の文は正しく、セマンティック上同一ですが、2 番目が推奨される形式を反映した文です。

SELECT * FROM t GROUP BY GROUPING SETS ( X, Y );
SELECT * FROM t GROUP BY GROUPING SETS( ( X ), ( Y ) );