[DB] RollUp에 대한 이해

MS SQL에서는 ROLLUP이라는 기능이 있습니다. 이 기능은 계층적으로 그룹화된 행을 조회 결과에 포함시켜줍니다.

이 RollUp에 대한 기본 규칙은 다음과 같습니다.

1. GROUP BY 절 외의 다른 곳에서 사용할 수 없습니다.
2. GROUP BY절의 종료 전에 ROLLUP 키워드를 사용하시면 됩니다.
3. GROUP BY절에 지정된 각 열에 ROLLUP을 지정하는 것이 아닙니다.

예제

Create Table And Insert Values Query

예제를 위해 Products Table를 생성하고 값을 넣습니다.

CREATE TABLE Products (Name NVARCHAR(10), Color NVARCHAR(10), Amount NUMERIC(20,2), Quantity INT);
GO
INSERT Products VALUES ('chiar', 'blue', 10, 20000);
INSERT Products VALUES ('chiar', 'red', 34, 24000);
INSERT Products VALUES ('chiar', 'green', 11, 22300);
INSERT Products VALUES ('chiar', 'gray', 7, 50000);
INSERT Products VALUES ('desk', 'blue', 6, 70000);
INSERT Products VALUES ('desk', 'red', 5, 30000);
INSERT Products VALUES ('desk', 'green', 5, 44000);
INSERT Products VALUES ('desk', 'silver', 3, 120000);
INSERT Products VALUES ('desk', 'gray', 6, 25000);

Result Of Query

Name	Color	Amount	Quantity
chiar	blue	10	20,000
chiar	red	34	24,000
chiar	green	11	22,300
chiar	gray	7	50,000
desk	blue	6	70,000
desk	red	5	30,000
desk	green	5	44,300
desk	silver	3	12,000
desk	gray	6	25,000

Rollup Testing

Query

SELECT Name, Type, Amount, Quantity 
FROM Products
GROUP BY Name, Type, Amount, Quantity WITH ROLLUP

Result Of Query

Name	Color	Amount	Quantity
chiar	blue	20000	10
chiar	gray	50000	7
chiar	green	22300	11
chiar	red	24000	34
desk	blue	70000	6
desk	gray	25000	6
desk	green	44000	5
desk	red	30000	5
desk	silver	120000	3
chiar	blue	20000	NULL
chiar	gray	50000	NULL
chiar	green	22300	NULL
chiar	red	24000	NULL
desk	blue	70000	NULL
desk	gray	25000	NULL
desk	silver	120000	NULL
desk	red	30000	NULL
desk	green	44000	NULL
chiar	blue	NULL	NULL
chiar	gray	NULL	NULL
chiar	green	NULL	NULL
chiar	red	NULL	NULL
desk	blue	NULL	NULL
desk	gray	NULL	NULL
desk	green	NULL	NULL
desk	red	NULL	NULL
desk	silver	NULL	NULL
chiar	NULL	NULL	NULL
desk	NULL	NULL	NULL
NULL	NULL	NULL	NULL

Analyze Result

위 결과는 난해하지만 자세히 보시면 다음과 같은 규칙을 발견하실 수 있습니다. 먼저 시작하기 전에 한가지 참고할 점은, 위 결과에서 NULL로 나타난 곳은 N개의 서로다른 데이터가 묶여있어 표현이 불가능하기에 NULL로 나타난 것입니다. 즉, 아래와 같은 데이터를 하나로 묶을 경우,

Name	Color	Amount	Quantity
chiar	blue	10	20,000
chiar	blue	34	24,000
chiar	blue	11	22,300

아래와 같이 나타납니다.

Name	Color	Amount	Quantity
chiar	blue	NULL	NULL

자 본론으로 돌아와서 Rollup Query 결과에 대한 규칙은 다음과 같습니다.

1. GROUP BY절에 열거된 모든 열에 대해 GROUP BY가 실행된 데이터가 결과에 포함됩니다. 붉은 색 행에 나타난 데이터가 바로 그 결과입니다.
2. GROUP BY절에 열거된 열 중에서 마지막 열이 제외된 상태로 GROUP BY가 실행된 데이터가 결과에 포함됩니다. 녹색 행에 나타난 데이터가 바로 그 결과입니다. 마지막 열은 NULL로 나타나있고 나머지 열은 자신들끼리 GROUP BY 처리되었습니다.
3. GROUP BY절에 열거된 열 중에서 마지막 두 열이 제외된 상태로 GROUP BY가 실행된 데이터가 결과에 포함됩니다. 파란 행에 나타난 데이터가 바로 그 결과입니다. 마지막 두 열은 NULL로 나타나있고 나머지 열은 자신들끼리 GROUP BY 처리되었습니다.
4. GROUP BY절에 열거된 열 중에서 마지막 세 열이 제외된 상태로 GROUP BY가 실행된 데이터가 결과에 포함됩니다. 갈색 행에 나타난 데이터가 바로 그 결과입니다. 마지막 세 열은 NULL로 나타나있고 나머지 열은 자신들끼리 GROUP BY 처리되었습니다.
5. GROUP BY절에 열거된 열과 상관없이 모든 행이 묶인 결과가 마지막 행에 포함됩니다.

규칙을 보면 눈치 채실 수 있겠지만, ROLLUP 키워드가 GROUP BY절의 마지막에 지정되면 GROUP BY에 지정된 컬럼들을 순차적으로 그룹화한 결과를 반환한다는 것을 알 수 있습니다.

ROLLUP으로부터 데이터 얻기

위에서도 언급했지만, 결과에서 NULL이 포함된 결과를 반환하는 행은 실제로 N개의 서로 다른 행이 그룹화된 행입니다. 즉, NULL이 포함된 열에 집계함수를 사용하여 그 결과를 받아 볼 수 있습니다.

Query

SELECT Name, Type, Amount, Quantity, SUM(Amount) AS SumOfAmount, AVG(Quantity) AS AverageOfQuantity
FROM Products
GROUP BY Name, Type, Amount, Quantity WITH ROLLUP

Result Of Query

Name	Color	Amount	Quantity	SumOfAmount	AverageOfQuantity
desk	silver	120000	3	120000	3
desk	red	30000	5	30000	5
desk	green	44000	5	44000	5
desk	blue	70000	6	70000	6
chiar	gray	50000	7	50000	7
chiar	blue	20000	10	20000	10
chiar	green	22300	11	22300	11
chiar	red	24000	34	24000	34
desk	gray	25000	NULL	25000	6
chiar	blue	20000	NULL	20000	10
chiar	green	22300	NULL	22300	11
chiar	red	24000	NULL	24000	34
desk	gray	25000	NULL	25000	6
desk	red	30000	NULL	30000	5
desk	green	44000	NULL	44000	5
chiar	gray	50000	NULL	50000	7
desk	blue	70000	NULL	70000	6
desk	silver	120000	NULL	120000	3
chiar	blue	NULL	NULL	20000	10
desk	blue	NULL	NULL	70000	6
chiar	gray	NULL	NULL	50000	7
desk	gray	NULL	NULL	25000	6
chiar	green	NULL	NULL	22300	11
desk	green	NULL	NULL	44000	5
chiar	red	NULL	NULL	24000	34
desk	red	NULL	NULL	30000	5
desk	silver	NULL	NULL	120000	3
chiar	NULL	NULL	NULL	116300	15
desk	NULL	NULL	NULL	289000	5
NULL	NULL	NULL	NULL	405300	9