GROUPING_ID (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

Transact-SQL 構文表記規則

構文

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

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

<column_expression>
GROUP BY 句の column_expression です。

戻り値の型

int

解説

GROUPING_ID <column_expression> は、GROUP BY リストの式と正確に一致させる必要があります。 たとえば、DATEPART (yyyy, <列名>) でグループ化する場合は GROUPING_ID (DATEPART (yyyy, <列名>)) を使用し、<列名> でグループ化する場合は (<列名>) を使用します。

GROUPING_ID () と GROUPING () の比較

GROUPING_ID (<列式> [ ,...n ]) では、列リストの各列に関して返される GROUPING (<列式>) に相当する値を、0 と 1 の文字列として各出力行に入力します。 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 ビットが点灯する場合がある integer ビットマップを返します。 点灯した bit は、対応する引数が特定の出力行に対するグループ列でないことを示します。 最も順位が低い bit は引数 N に対応し、N-1番目に順位が低い bit は引数 1 に対応します。

GROUPING_ID () の対応

単一グループ クエリの場合、GROUPING (<列式>) は GROUPING_ID (<列式>) に対応し、いずれも 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

例

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

次の例では、AdventureWorks2022 データベースの Name と Title ごとの従業員数と、Name, と会社の合計が返されます。 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 を使用して結果セットをフィルター処理する

簡単な例

役職ごとの従業員数が含まれた行のみを返すには、次のコードの AdventureWorks2022 データベースの 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 N'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;

フィルター処理していない結果セットは、次のようになります。

名前 タイトル グループ化のレベル 従業員数 名前
文書コントロール コントロール スペシャリスト 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() を使用して、複数のグループ化レベルが含まれた結果セットをグループ化レベルでフィルター処理します。 これと同様のコードを使用すると、複数のグループ化レベルがあるビューとストアド プロシージャを作成できます。このストアド プロシージャは、ビューをグループ化レベルでフィルター処理するパラメーターを渡すことによって、ビューを呼び出します。 この例では、AdventureWorks2022 データベースを使います。

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. GROUPING_ID () を ROLLUP および CUBE と共に使用してグループ化レベルを識別する

以下は、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 列のレベル値も変更する必要があります。 この例では、AdventureWorks2022 データベースを使います。

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 列のレベル値も変更する必要があります。 この例では、AdventureWorks2022 データベースを使います

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 (Transact-SQL)
GROUP BY (Transact-SQL)