Aggregation WITH ROLLUP
WITH ROLLUP 에 해당하는 부분을 SQL 2005 에서는 구분할 방법이 없었으며(2008 에서 고쳐짐), 본래의 Aggregate 는 병렬이나 HASH, STREAM 으로 풀 수 있으나, ROLLUP 에 해당하는 aggregation 부분은 병렬화 해서 풀 수 없고, STREAM Aggreation 만 할 수 있다.
이 포스트에서, WITH ROLLUP 이 어떻게 aggregation 과 함께 어떻게 동작 하는지 다룬다. WITH ROLLUP 절은 하나의 문장으로 다중 레벨 aggregation 을 가능하게 해준다. 예를 들자면, 아래과 같은 가상 판매 데이터가 있다고 가정해보자. (이건 PIVOT 연산자 시리즈에서 다룬 데이터와 같은 데이터이다.)
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
년별 총 판매량을 계산하기 위해 다음과 같은 간단한 aggregation 쿼리를 작성할 수 있다.
SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr
예상된 바와 같이 , 이 쿼리의 결과는 다음과 같이 년별로 한개씩 3개의 행을 출력한다.
Yr Sales
----------- ---------------------
2005 27000.00
2006 44000.00
2007 49000.00
쿼리 계획은 간단한 stream aggregate 이다.
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1010]=COUNT_BIG([Sales].[Sales]), [Expr1011]=SUM([Sales].[Sales])))
|--Sort(ORDER BY:([Sales].[Yr] ASC))
|--Table Scan(OBJECT:([Sales]))
년별 값 뿐만 아니라 전체 판매량도 구하고자 한다면, UNION ALL 쿼리를 쓸 수 있다.
SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr
UNION ALL
SELECT NULL, SUM(Sales) AS Sales
FROM Sales
이 쿼리는 동작하고 올바른 쿼리 결과를 보인다.
Yr Sales
----------- ---------------------
2005 27000.00
2006 44000.00
2007 49000.00
NULL 120000.00
하지만, 쿼리 계획은 두개의 scan 쿼리과 두개의 aggregations 로 동작한다. (하나는 년별 판매이고 하나는 전체 판매량이다.)
|--Concatenation
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END))
| |--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1023]=COUNT_BIG([Sales].[Sales]), [Expr1024]=SUM([Sales].[Sales])))
| |--Sort(ORDER BY:([Sales].[Yr] ASC))
| |--Table Scan(OBJECT:([Sales]))
|--Compute Scalar(DEFINE:([Expr1010]=NULL))
|--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END))
|--Stream Aggregate(DEFINE:([Expr1025]=COUNT_BIG([Sales].[Sales]), [Expr1026]=SUM([Sales].[Sales])))
|--Table Scan(OBJECT:([Sales]))
우리는 본래 쿼리에서 WITH ROLLUP 을 추가함으로 더 좋은 플랜을 만들수 있다.
SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr WITH ROLLUP
이쿼리는 더 간단하게 쓸 수 있고, 단지 한번의 scan 으로 더 효율적인 쿼리플랜이다.
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
|--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1005]=SUM([Expr1007]), [Expr1006]=SUM([Expr1008])))
|--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1007]=COUNT_BIG([Sales].[Sales]), [Expr1008]=SUM([Sales].[Sales])))
|--Sort(ORDER BY:([Sales].[Yr] ASC))
|--Table Scan(OBJECT:([Sales]))
이 쿼리의 최하단의 stream aggregate 는 원래의 ROLLUP 이 없을때의 stream aggregate 이다. 이건은 정상적인 aggregation 이다. 그리고 이것은 이 예제에서와 같이 stream aggregate 나 option (HASH GROUP) 을 추가함으로 hash aggregate 로도 풀 수 있다. 그리고 병렬 쿼리로도 풀 수 있다.
최상단의 stream aggregate 는 ROLLUP 을 위한 특별한 aggregate 이다. (불행히, SQL Server 2005 에서는 ROLLUP 을 수행하는 aggregation 을 구분할(분별할) 방법은 없다.) 이 이슈는 SQL Server 2008 의 그래픽 과 XML 실행계획에서 고쳐질 것이다. ROLLUP aggregate 는 항상 stream aggregate 이고 병렬화 할 수 없다. 간단한 예제에서, ROLLUP stream aggregate 는 판매 컬럼의 누적값을 유지해 단지 각각의 이전 aggregate 한 값을 돌려준다. 마지막 입력행을 계산한 후에, aggregate 는 마지막 sum 값을 계산해 한행 추가한다. All 값에 대한 컨셉이 SQL 에 없기 때문에, 마지막 Yr 컬럼은 NULL 값으로 셋팅 한다. GROUPING(Yr) 을 추가해 ROLLUP 행을 식별할 수 있다.
SELECT
CASE WHEN GROUPING(Yr) = 0
THEN CAST (Yr AS CHAR(5))
ELSE 'ALL'
END AS Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr WITH ROLLUP
Yr Sales
----- ---------------------
2005 27000.00
2006 44000.00
2007 49000.00
ALL 120000.00
또 다중 ROLLUP 레벨을 하나의 쿼리로 계산할 수 있다. 예들들면, 직원별로 먼저 판매량을 계산하고 년별 직원의 판매량을 계산할 수 있다.
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
EmpId Yr Sales
----------- ----------- ---------------------
1 2005 12000.00
1 2006 18000.00
1 2007 25000.00
1 NULL 55000.00
2 2005 15000.00
2 2006 6000.00
2 NULL 21000.00
3 2006 20000.00
3 2007 24000.00
3 NULL 44000.00
NULL NULL 120000.00
이 쿼리 계획은, Yr(원글에서는 EmpId 로 써 있는데 오타인듯 함) 컬럼으로 그룹하지 않고 EmpId 와 Yr 컬럼으로 그룹한것을 제외하고는 이전 계획과 동일하다. 이전의 쿼리계획과 비슷하게, 이 쿼리 계획은 두개의 stream aggregate 를 포함한다. 맨 하단의 stream aggregate 는 정상적인 것이고 최상위 것은 ROLLUP 을 계산하기 위한 것이다. 이 ROLLUP 은 두개의 aggregate 누적값을 구한다. 하나는 한명의 직원의 전체 년도에 대한 값이고, 다른 하나는 전체 직원은 전체 년도의 전체 값이다. 이 테이블은 ROLLUP 어떻게 계산되는지 보여준다.
다음 포스트에서 WITH CUBE 절을 살펴보겠다. ROLLUP 과 비교해 기능과 구현의 차이점을 다룬다.
by Craig Freedman