GROUP BY を使用してデータを集計する

3 分

集計関数は分析に便利ですが、集計する前にデータをサブセットに配置したい場合があります。このセクションでは、GROUP BY 句を使用してこれを行う方法について説明します。

GROUP BY 句を使用する

ここまで説明したとおり、SELECT ステートメントが処理されると、FROM 句と WHERE 句が評価された後に、仮想テーブルが作成されます。これで、仮想テーブルの内容をさらなる処理に使用できるようになります。 GROUP BY 句を使用して、この仮想テーブルの内容を行のグループに分割できます。

行をグループ化するには、GROUP BY 句で 1 つ以上の要素を指定します。

GROUP BY <value1> [, <value2>, …]

GROUP BY により、グループを作成し、この句で指定した要素によって決定される行を各グループに挿入します。

たとえば、次のクエリにより、Sales.SalesOrderHeader テーブルの CustomerID ごとに 1 行のグループ化された行のセットが生成されます。 GROUP BY 処理の別のとらえ方は、CustomerID の値が同じであるすべての行がグループ化され、1 つの結果行に返されるということです。

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

上記のクエリは、次のクエリと同じです。

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

GROUP BY 句が処理され、各行がグループに関連付けられた後、クエリの以後のフェーズで、SELECT リストには含まれているが、GROUP BY リストには出現しないソース行のすべての要素を集計する必要があります。この要件は、SELECT および HAVING 句の記述方法に影響します。

では、GROUP BY または DISTINCT を使用してクエリを記述するときの違いは何でしょうか。 CustomerID の個別の値を知りたいだけの場合、違いはありません。しかし、GROUP BY を使用すると、他の要素を SELECT リストに追加して、それらをグループごとに集計できます。

最も単純な集計関数は COUNT(*) です。次のクエリは、CustomerID から元の 830 個のソース行を取得し、それらを CustomerID の値に基づいて 89 個のグループにグループ化しています。個別の CustomerID 値ごとに、GROUP BY クエリで 1 行の出力が生成されます。

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

CustomerID の各値ごとにクエリで行を集計してカウントするため、結果には、SalesOrderHeader テーブル内の各顧客に属する行の数が示されます。

CustomerID

OrderCount

1234

1005

GROUP BY では、結果の順序が保証されないことに注意してください。多くの場合、クエリプロセッサによってグループ化の操作が実行された結果として、結果がグループ値の順序で返されます。ただし、この動作に依存することは避けてください。結果を並べ替える必要がある場合は、ORDER 句を明示的に含める必要があります。

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;

今度は指定した順序で結果が返されます。

CustomerID

OrderCount

1005

1234

SELECT ステートメント内の句は、次の順序で適用されます。

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

列の別名は SELECT 句で割り当てます。これは、GROUP BY 句の "後"、ただし ORDER BY 句の "前" に記述します。 ORDER BY 句では列の別名を参照できますが、GROUP BY 句では参照できません。次のクエリの結果は、"無効な列名" エラーになります。

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;

ただし、次のクエリは成功し、結果は顧客 ID でグループ化されて並べ替えられます。

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;

GROUP BY エラーのトラブルシューティング

SELECT ステートメントで GROUP BY を使うことに慣れるための一般的な障害は、次の種類のエラーメッセージが発生する理由を理解することです。

メッセージ 8120、レベル 16、状態 1、行 2 列 <column_name> は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

たとえば次のクエリは、SELECT リスト内の各列が、GROUP BY 句または各グループに対して操作を行う集計関数のいずれかの列であるため、許可されます。

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

次のクエリは、、PurchaseOrderNumber が GROUP BY の一部ではなく、かつ集計関数で使用されていないため、エラーが返されます。

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

このクエリで返されるエラーは次のとおりです。

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

もう 1 つの考え方を次に示します。このクエリからは、CustomerID の値ごとに 1 行が返されます。しかし、同じ CustomerID の行が異なる PurchaseOrderNumber 値を持つ可能性があります。では、返す必要があるのはどちらの値でしょうか。

顧客 ID ごと、かつ注文書ごとに注文を表示する場合は、次のように PurchaseOrderNumber 列を GROUP BY 句に追加できます。

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;

このクエリからは、顧客と発注書の組み合わせごとに 1 行と、その組み合わせの注文数が返されます。

GROUP BY を使用してデータを集計する

GROUP BY 句を使用する

GROUP BY エラーのトラブルシューティング

フィードバック