[アーティクル]
09/28/2010

GROUPING_ID (Transact-SQL)

グループ化のレベルを計算する関数を指定します。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>) に相当する値を、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 (<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 AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title);

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

簡単な例

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

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 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 AdventureWorks;
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 C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = 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 C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.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 AdventureWorks;
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'2003',N'2004')
    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)	等価の整数	グループ化レベル
2003	1	1	1762381	000	0	年月日
2003	1	2	21772.35	000	0	年月日
2003	2	1	3185233	000	0	年月日
2003	2	2	21684.41	000	0	年月日
2004	1	1	2239208	000	0	年月日
2004	1	2	46458.07	000	0	年月日
2004	2	1	3653194	000	0	年月日
2004	2	2	54598.55	000	0	年月日
2003	1	NULL	1784153	100	1	年月
2003	2	NULL	3206917	100	1	年月
2004	1	NULL	2285666	100	1	年月
2004	2	NULL	3707793	100	1	年月
2003	NULL	NULL	4991070	110	3	年
2004	NULL	NULL	5993459	110	3	年
NULL	NULL	NULL	10984529	111	7	総計

CUBE の例

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

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

USE AdventureWorks;
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'2003',N'2004')
    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);