GROUPING_ID (Transact-SQL)

[アーティクル]
10/16/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

GROUPING_ID グループ化のレベルを計算する関数です。 GROUPING_IDは、GROUP BYが指定されている場合は、SELECT <select>リスト、HAVING、またはORDER BY句でのみ使用できます。

Transact-SQL 構文表記規則

構文

GROUPING_ID ( <column_expression> [ , ...n ] )

引数

<column_expression>

SELECT - GROUP BY 句内のcolumn_expression。

戻り値の型

int

解説

GROUPING_ID <column_expression>は、GROUP BY リスト内の式と完全に一致する必要があります。たとえば、 DATEPART (yyyy, <column name>)でグループ化する場合は、 GROUPING_ID (DATEPART (yyyy, <column name>))を使用するか、 <column name>でグループ化する場合は、 GROUPING_ID (<column name>)を使用します。

GROUPING_ID() と GROUPING() の比較

GROUPING_ID (<column_expression> [ , ...n ]) は、各出力行の列リスト内の各列に対して返される GROUPING (<column_expression>) に相当するものを、1 と 0 の文字列として入力します。 GROUPING_ID は、その文字列を 2 進数として解釈し、等価の整数を返します。

たとえば、次のステートメントを考えてみましょう。

SELECT a, b, c, SUM(d),
GROUPING_ID(a, b, c)
FROM T
GROUP BY <group_by_list>

次の表に、 GROUPING_ID() の入力値と出力値を示します。

集計列	GROUPING_ID (a, b, c) の入力 = GROUPING(a) + GROUPING(b) + GROUPING(c)	GROUPING_ID() 出力
`a`	`100`	`4`
`b`	`010`	`2`
`c`	`001`	`1`
`ab`	`110`	`6`
`ac`	`101`	`5`
`bc`	`011`	`3`
`abc`	`111`	`7`

GROUPING_ID() の技術的な定義

各 GROUPING_ID 引数は、 GROUP BY リストの要素である必要があります。 GROUPING_ID() は、最小 nビット ビットが litである整数ビットマップを返します。点灯ビットは、対応する引数が指定された出力行のグループ化列ではないことを示します。最下位ビットは引数nに対応し、n-1最下位ビットは引数 1 に対応します。

GROUPING_ID() に相当するもの

1 つのグループ化クエリの場合、 GROUPING (<column_expression>) は GROUPING_ID (<column_expression>)と同等であり、両方とも 0を返します。

たとえば、次のステートメントは互いに対応しています。

ステートメント A

SELECT GROUPING_ID(A, B)
FROM T
GROUP BY CUBE(A, B)

ステートメント B

SELECT 3 FROM T GROUP BY ()
UNION ALL
SELECT 1 FROM T GROUP BY A
UNION ALL
SELECT 2 FROM T GROUP BY B
UNION ALL
SELECT 0 FROM T GROUP BY A, B

例

この記事の Transact-SQL コードサンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

A. GROUPING_IDを使用してグループ化レベルを識別する

次の例では、 Name および Title別の従業員数と、 AdventureWorks2022 データベース内の会社の合計を返します。 GROUPING_ID() は、集計レベルを示す Title 列の各行に対する値を作成するために使われます。

SELECT D.Name,
    CASE 
        WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle
        WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name
        WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Job Title',
    COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
    ON E.BusinessEntityID = DH.BusinessEntityID
INNER JOIN HumanResources.Department D
    ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12, 14)
GROUP BY ROLLUP(D.Name, E.JobTitle);

B. GROUPING_IDを使用して結果セットをフィルター処理する

基本的な例

次のコードでは、タイトルごとに従業員数の行のみを返すには、コメント文字を HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;から削除します。部門ごとの従業員数が含まれた行のみを返すには、HAVING GROUPING_ID(D.Name, E.JobTitle) = 1; からコメント文字を削除します。

SELECT D.Name,
    E.JobTitle,
    GROUPING_ID(D.Name, E.JobTitle) AS [Grouping Level],
    COUNT(E.BusinessEntityID) AS [Employee Count]
FROM HumanResources.Employee AS E
INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH
    ON E.BusinessEntityID = DH.BusinessEntityID
INNER JOIN HumanResources.Department AS D
    ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12, 14)
GROUP BY ROLLUP(D.Name, E.JobTitle)
-- HAVING GROUPING_ID(D.Name, E.JobTitle) = 0; -- All titles
-- HAVING GROUPING_ID(D.Name, E.JobTitle) = 1; -- Group by Name;

フィルター処理されていない結果セットを次に示します。

Name	タイトル	グループ化のレベル	従業員数	名前
文書コントロール	コントロールスペシャリスト	0	2	文書コントロール
文書コントロール	文書コントロールアシスタント	0	2	文書コントロール
文書コントロール	文書コントロールマネージャー	0	1	文書コントロール
文書コントロール	`NULL`	1	5	文書コントロール
設備およびメンテナンス	設備管理アシスタント	0	1	設備およびメンテナンス
設備およびメンテナンス	設備マネージャー	0	1	設備およびメンテナンス
設備およびメンテナンス	管理人	0	4	設備およびメンテナンス
設備およびメンテナンス	メンテナンススーパーバイザー	0	1	設備およびメンテナンス
設備およびメンテナンス	`NULL`	1	7	設備およびメンテナンス
`NULL`	`NULL`	3	12	`NULL`

複雑な例

次の例では、GROUPING_ID() を使用して、複数のグループ化レベルが含まれた結果セットをグループ化レベルでフィルター処理します。同様のコードを使用して、複数のグループ化レベルを持つビューと、ビューをグループ化レベルでフィルター処理するパラメーターを渡してビューを呼び出すストアドプロシージャを作成できます。

DECLARE @Grouping NVARCHAR(50);
DECLARE @GroupingLevel SMALLINT;

SET @Grouping = N'CountryRegionCode Total';

SELECT @GroupingLevel = (
    CASE @Grouping
        WHEN N'Grand Total' THEN 15
        WHEN N'SalesPerson Total' THEN 14
        WHEN N'Store Total' THEN 13
        WHEN N'Store SalesPerson Total' THEN 12
        WHEN N'CountryRegionCode Total' THEN 11
        WHEN N'Group Total' THEN 7
        ELSE N'Unknown'
    END
);

SELECT T.[Group],
    T.CountryRegionCode,
    S.Name AS N'Store',
    (
        SELECT P.FirstName + ' ' + P.LastName
        FROM Person.Person AS P
        WHERE P.BusinessEntityID = H.SalesPersonID
    ) AS N'Sales Person',
    SUM(TotalDue) AS N'TotalSold',
    CAST(GROUPING(T.[Group]) AS CHAR(1)) + CAST(GROUPING(T.CountryRegionCode) AS CHAR(1)) + CAST(GROUPING(S.Name) AS CHAR(1)) + CAST(GROUPING(H.SalesPersonID) AS CHAR(1)) AS N'GROUPING base-2',
    GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) AS N'GROUPING_ID',
    CASE 
        WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 15
            THEN N'Grand Total'
        WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 14
            THEN N'SalesPerson Total'
        WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 13
            THEN N'Store Total'
        WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 12
            THEN N'Store SalesPerson Total'
        WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 11
            THEN N'CountryRegionCode Total'
        WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 7
            THEN N'Group Total'
        ELSE N'Error'
    END AS N'Level'
FROM Sales.Customer AS C
INNER JOIN Sales.Store AS S
    ON C.StoreID = S.BusinessEntityID
INNER JOIN Sales.SalesTerritory AS T
    ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader AS H
    ON C.CustomerID = H.CustomerID
GROUP BY GROUPING SETS(
    (S.Name,H.SalesPersonID),
    (H.SalesPersonID), 
    (S.Name), 
    (T.[Group]),
    (T.CountryRegionCode),
    ()
)
HAVING GROUPING_ID(
    (T.[Group]),
    (T.CountryRegionCode),
    (S.Name),
    (H.SalesPersonID)
) = @GroupingLevel
ORDER BY
    GROUPING_ID(S.Name, H.SalesPersonID),
    GROUPING_ID(
        (T.[Group]),
        (T.CountryRegionCode),
        (S.Name),
        (H.SalesPersonID)
    ) ASC;

C: ROLLUP および CUBE で GROUPING_ID() を使用してグループ化レベルを識別する

次の例のコードは、 GROUPING() を使用して Bit Vector(base-2) 列を計算する方法を示しています。 GROUPING_ID() は、対応する Integer Equivalent 列を計算するために使用されます。 GROUPING_ID() 関数の列順序は、GROUPING() 関数によって連結された列の列順序の逆になります。

これらの例では、グループ化レベルを示すための値を Grouping Level 列の各行に対して作成するために、GROUPING_ID() が使用されます。グループ化レベルは、常に 1 (0、1、2、... で始まる整数の連続したリストであるとは限りません。n)。

Note

GROUPING および GROUPING_ID を HAVING 句で使用して、結果セットをフィルター処理できます。

ROLLUP の例

この例では、次の CUBE 例のように、すべてのグループ化レベルは表示されません。 ROLLUP リスト内の列の順序を変更する場合は、Grouping Level列のレベル値も変更する必要があります。

SELECT DATEPART(yyyy, OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS N'Month',
    DATEPART(dd, OrderDate) AS N'Day',
    SUM(TotalDue) AS N'Total Due',
    CAST(GROUPING(DATEPART(dd, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(mm, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(yyyy, OrderDate)) AS CHAR(1)) AS N'Bit Vector(base-2)',
    GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) AS N'Integer Equivalent',
    CASE 
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 0
            THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 1
            THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 2
            THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 3
            THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 4
            THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 5
            THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 6
            THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 7
            THEN N'Grand Total'
        ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy, OrderDate) IN (N'2007', N'2008')
    AND DATEPART(mm, OrderDate) IN (1, 2)
    AND DATEPART(dd, OrderDate) IN (1, 2)
GROUP BY ROLLUP(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm, OrderDate), DATEPART(yyyy, OrderDate), DATEPART(dd, OrderDate)),
    DATEPART(yyyy, OrderDate),
    DATEPART(mm, OrderDate),
    DATEPART(dd, OrderDate);

次に結果セットの一部を示します。

年	Month	日	精算額	ビットベクター (基数 2)	等価の整数	グループ化のレベル
2007	1	1	1497452.6066	000	0	年月日
2007	1	2	21772.3494	000	0	年月日
2007	2	1	2705653.5913	000	0	年月日
2007	2	2	21684.4068	000	0	年月日
2008	1	1	1908122.0967	000	0	年月日
2008	1	2	46458.0691	000	0	年月日
2008	2	1	3108771.9729	000	0	年月日
2008	2	2	54598.5488	000	0	年月日
2007	1	`NULL`	1519224.956	100	1	年月
2007	2	`NULL`	2727337.9981	100	1	年月
2008	1	`NULL`	1954580.1658	100	1	年月
2008	2	`NULL`	3163370.5217	100	1	年月
2007	`NULL`	`NULL`	4246562.9541	110	3	年
2008	`NULL`	`NULL`	5117950.6875	110	3	年
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	総計

CUBE の例

この例では、GROUPING_ID() 関数を使用して、グループ化レベルを識別する値を Grouping Level 列の各行に対して作成します。

前の例の ROLLUP と異なり、CUBE ではすべてのグループ化レベルが出力されます。 CUBE リスト内の列の順序を変更する場合は、Grouping Level列のレベル値も変更する必要があります。

SELECT DATEPART(yyyy, OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS N'Month',
    DATEPART(dd, OrderDate) AS N'Day',
    SUM(TotalDue) AS N'Total Due',
    CAST(GROUPING(DATEPART(dd, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(mm, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(yyyy, OrderDate)) AS CHAR(1)) AS N'Bit Vector(base-2)',
    GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) AS N'Integer Equivalent',
    CASE 
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 0
            THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 1
            THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 2
            THEN N'Year Day'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 3
            THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 4
            THEN N'Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 5
            THEN N'Month'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 6
            THEN N'Day'
        WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 7
            THEN N'Grand Total'
        ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy, OrderDate) IN (N'2007', N'2008')
    AND DATEPART(mm, OrderDate) IN (1, 2)
    AND DATEPART(dd, OrderDate) IN (1, 2)
GROUP BY CUBE(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)),
    DATEPART(yyyy, OrderDate),
    DATEPART(mm, OrderDate),
    DATEPART(dd, OrderDate);

次に結果セットの一部を示します。

年	Month	日	精算額	ビットベクター (基数 2)	等価の整数	グループ化のレベル
2007	1	1	1497452.6066	000	0	年月日
2007	1	2	21772.3494	000	0	年月日
2007	2	1	2705653.5913	000	0	年月日
2007	2	2	21684.4068	000	0	年月日
2008	1	1	1908122.0967	000	0	年月日
2008	1	2	46458.0691	000	0	年月日
2008	2	1	3108771.9729	000	0	年月日
2008	2	2	54598.5488	000	0	年月日
2007	1	`NULL`	1519224.956	100	1	年月
2007	2	`NULL`	2727337.9981	100	1	年月
2008	1	`NULL`	1954580.1658	100	1	年月
2008	2	`NULL`	3163370.5217	100	1	年月
2007	`NULL`	1	4203106.1979	010	2	年日
2007	`NULL`	2	43456.7562	010	2	年日
2008	`NULL`	1	5016894.0696	010	2	年日
2008	`NULL`	2	101056.6179	010	2	年日
2007	`NULL`	`NULL`	4246562.9541	110	3	年
2008	`NULL`	`NULL`	5117950.6875	110	3	年
`NULL`	1	1	3405574.7033	001	4	月日
`NULL`	1	2	68230.4185	001	4	月日
`NULL`	2	1	5814425.5642	001	4	月日
`NULL`	2	2	76282.9556	001	4	月日
`NULL`	1	`NULL`	3473805.1218	101	5	Month
`NULL`	2	`NULL`	5890708.5198	101	5	Month
`NULL`	`NULL`	1	9220000.2675	011	6	日
`NULL`	`NULL`	2	144513.3741	011	6	日
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	総計

次の方法で共有

GROUPING_ID (Transact-SQL)

構文

引数

<column_expression>

戻り値の型

解説

GROUPING_ID() と GROUPING() の比較

GROUPING_ID() の技術的な定義

GROUPING_ID() に相当するもの

ステートメント A

ステートメント B

例

A. GROUPING_IDを使用してグループ化レベルを識別する

B. GROUPING_IDを使用して結果セットをフィルター処理する

基本的な例

複雑な例

C: ROLLUP および CUBE で GROUPING_ID() を使用してグループ化レベルを識別する

ROLLUP の例

CUBE の例

フィードバック

その他のリソース

次の方法で共有

GROUPING_ID (Transact-SQL)

構文

引数

<column_expression>

戻り値の型

解説

GROUPING_ID() と GROUPING() の比較

GROUPING_ID() の技術的な定義

GROUPING_ID() に相当するもの

ステートメント A

ステートメント B

例

A. GROUPING_IDを使用してグループ化レベルを識別する

B. GROUPING_IDを使用して結果セットをフィルター処理する

基本的な例

複雑な例

C: ROLLUP および CUBE で GROUPING_ID() を使用してグループ化レベルを識別する

ROLLUP の例

CUBE の例

関連するコンテンツ

フィードバック

その他のリソース