GROUPING_ID (Transact-SQL)

[アーティクル]
07/02/2013

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

構文

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

引数

<column_expression>
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 ビットが点灯する場合がある integer ビットマップを返します。点灯した bit は、対応する引数が特定の出力行に対するグループ列でないことを示します。最も順位が低い bit は引数 N に対応し、N-1 番目に順位が低い bit は引数 1 に対応します。

GROUPING_ID () の対応

単一グループクエリの場合、GROUPING (<column_expression>) は GROUPING_ID (<column_expression>) に対応し、いずれも 0 を返します。

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

SELECT GROUPING_ID(A,B)
FROM T 
GROUP BY CUBE(A,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 を使用してグループ化レベルを識別する

次の例では、Name と Title ごとの従業員数、Name ごとの従業員数、および会社の合計従業員数が返されます。 GROUPING_ID() は、集計レベルを識別する各行の値を Title 列に作成します。

USE AdventureWorks2012;
GO
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);

以下に結果セットを示します。

Name                       Job Title                           Employee Count
---------------------      ----------------------------        --------------
Document Control           Control Specialist                    2
Document Control           Document Control Assistant            2
Document Control           Document Control Manager              1
Document Control           Total: Document Control               5
Facilities and Maintenance Facilities Administrative Assistant   1
Facilities and Maintenance     Facilities Manager                1
Facilities and Maintenance     Janitor                           4
Facilities and Maintenance     Maintenance Supervisor            1
Facilities and Maintenance     Total: Facilities and Maintenance 7
NULL                           Company Total:                    12

(10 row(s) affected)

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

簡単な例

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

USE AdventureWorks2012;
GO
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() を使用して、複数のグループ化レベルが含まれた結果セットをグループ化レベルでフィルター処理します。これと同様のコードを使用すると、複数のグループ化レベルがあるビューとストアドプロシージャを作成できます。このストアドプロシージャは、ビューをグループ化レベルでフィルター処理するパラメーターを渡すことによって、ビューを呼び出します。

USE AdventureWorks2012;
GO
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) になるとは限りません。

注
GROUPING と GROUPING_ID は、結果セットをフィルター処理するために HAVING 句で使用できます。

ROLLUP の例

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

USE AdventureWorks2012;
GO
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);

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

年	月	日	精算額	ビットベクター (基数 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 列のレベル値も変更する必要があります。

USE AdventureWorks2012;
GO
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);