GROUPING_ID(Transact-SQL)

아티클
10/16/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

GROUPING_ID 그룹화 수준을 계산하는 함수입니다. GROUPING_ID은 목록에만 SELECT <select> 사용할 수 있으며, HAVINGORDER BY 지정된 경우 절을 사용할 수 GROUP 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>)하려는 경우 , use GROUPING_ID (DATEPART (yyyy, <column name>)); 또는 그룹화 기준 <column name>인 경우 다음을 사용합니다 GROUPING_ID (<column name>).

GROUPING_ID()와 GROUPING() 비교

GROUPING_ID (<column_expression> [ , ...n ]) 는 각 출력 행의 GROUPING (<column_expression>) 열 목록에 있는 각 열의 반환에 해당하는 값을 0과 0의 문자열로 입력합니다. GROUPING_ID 는 해당 문자열을 base-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비트가 켜질 수 있는 정수 비트맵을 반환합니다. 조명 비트는 해당 인수가 지정된 출력 행에 대한 그룹화 열이 아님을 나타냅니다. 가장 낮은 순서 비트는 인수 n에 해당하고 n-1^번째 가장 낮은 순서 비트는 인수 1에 해당합니다.

GROUPING_ID() 해당 항목

단일 그룹화 쿼리 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 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

A. GROUPING_ID 사용하여 그룹화 수준 식별

다음 예제에서는 데이터베이스의 직원 수 및 Title회사 합계를 AdventureWorks2022 반환합니다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 사용하여 결과 집합 필터링

기본 예제

다음 코드에서 제목별로 직원 수가 있는 행만 반환하려면 주석 문자를 HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;제거합니다. department별 직원 수를 갖는 행만 반환하려면 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;

필터링되지 않은 결과 집합은 다음과 같습니다.

속성	제목	Grouping Level	Employee Count	Name
문서 제어	Control Specialist	0	2	문서 제어
문서 제어	Document Control Assistant	0	2	문서 제어
문서 제어	Document Control Manager	0	1	문서 제어
문서 제어	`NULL`	1	5	문서 제어
시설 및 유지 관리	Facilities Administrative Assistant	0	1	시설 및 유지 관리
시설 및 유지 관리	Facilities Manager	0	1	시설 및 유지 관리
시설 및 유지 관리	Janitor	0	4	시설 및 유지 관리
시설 및 유지 관리	Maintenance Supervisor	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()를 사용하여 그룹화 수준 식별

다음 예제의 코드는 열을 계산 Bit Vector(base-2) 하는 데 사용하는 GROUPING() 방법을 보여 줍니다. GROUPING_ID()는 해당 Integer Equivalent 열을 계산하는 데 사용됩니다. GROUPING_ID() 함수의 열 순서는 GROUPING() 함수로 연결되는 열의 순서와 반대입니다.

이 예제에서 GROUPING_ID()는 Grouping Level 열의 각 행에 대해 그룹화 수준을 식별하는 값을 생성합니다. 그룹화 수준이 항상 1(0, 1, 2, ...n).

참고 항목

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);

다음은 결과 집합의 일부입니다.

Year	Month	일	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452.6066	000	0	Year Month Day
2007	1	2	21772.3494	000	0	Year Month Day
2007	2	1	2705653.5913	000	0	Year Month Day
2007	2	2	21684.4068	000	0	Year Month Day
2008	1	1	1908122.0967	000	0	Year Month Day
2008	1	2	46458.0691	000	0	Year Month Day
2008	2	1	3108771.9729	000	0	Year Month Day
2008	2	2	54598.5488	000	0	Year Month Day
2007	1	`NULL`	1519224.956	100	1	Year Month
2007	2	`NULL`	2727337.9981	100	1	Year Month
2008	1	`NULL`	1954580.1658	100	1	Year Month
2008	2	`NULL`	3163370.5217	100	1	Year Month
2007	`NULL`	`NULL`	4246562.9541	110	3	Year
2008	`NULL`	`NULL`	5117950.6875	110	3	Year
`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);

다음은 결과 집합의 일부입니다.

Year	Month	일	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452.6066	000	0	Year Month Day
2007	1	2	21772.3494	000	0	Year Month Day
2007	2	1	2705653.5913	000	0	Year Month Day
2007	2	2	21684.4068	000	0	Year Month Day
2008	1	1	1908122.0967	000	0	Year Month Day
2008	1	2	46458.0691	000	0	Year Month Day
2008	2	1	3108771.9729	000	0	Year Month Day
2008	2	2	54598.5488	000	0	Year Month Day
2007	1	`NULL`	1519224.956	100	1	Year Month
2007	2	`NULL`	2727337.9981	100	1	Year Month
2008	1	`NULL`	1954580.1658	100	1	Year Month
2008	2	`NULL`	3163370.5217	100	1	Year Month
2007	`NULL`	1	4203106.1979	010	2	Year Day
2007	`NULL`	2	43456.7562	010	2	Year Day
2008	`NULL`	1	5016894.0696	010	2	Year Day
2008	`NULL`	2	101056.6179	010	2	Year Day
2007	`NULL`	`NULL`	4246562.9541	110	3	Year
2008	`NULL`	`NULL`	5117950.6875	110	3	Year
`NULL`	1	1	3405574.7033	001	4	Month Day
`NULL`	1	2	68230.4185	001	4	Month Day
`NULL`	2	1	5814425.5642	001	4	Month Day
`NULL`	2	2	76282.9556	001	4	Month Day
`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 예제

관련 콘텐츠

피드백

추가 리소스