큐브를 사용한 데이터 요약
CUBE 연산자는 다차원 큐브인 결과 집합을 생성합니다. 다차원 큐브는 팩트 데이터의 확장이거나 각 이벤트를 기록하는 데이터입니다. 확장은 사용자가 분석하려는 열을 기반으로 합니다. 이러한 열을 차원이라고 합니다. 큐브는 가능한 모든 차원 조합의 교차 집계가 포함된 결과 집합입니다.
CUBE 연산자는 SELECT 문의 GROUP BY 절에 지정됩니다. SELECT 목록에는 차원 열과 집계 함수 식이 포함됩니다. GROUP BY는 차원 열과 WITH CUBE 키워드를 지정합니다. 결과 집합에는 차원 열 값의 가능한 모든 조합 및 해당 차원 값의 조합과 일치하는 기본 행의 집계 값이 포함됩니다.
예를 들어 Inventory라는 단순 테이블에 다음과 같은 값이 있습니다.
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
다음 쿼리는 Item과 Color의 가능한 모든 조합에 대한 Quantity 부분합이 포함된 결과 집합을 반환합니다.
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
결과 집합은 다음과 같습니다.
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
결과 집합에서 다음 행을 주의깊게 살펴봅니다.
Chair (null) 311.00
이 행은 Item 차원의 값이 Chair인 모든 행의 부분합을 보고합니다. Color 차원에 대해 null 값이 반환되어 이 행에서 보고한 집계에 Color 차원 값이 있는 행이 포함됨을 보여 줍니다.
Table (null) 347.00
이 행도 비슷하지만 Item 차원이 Table인 모든 행의 부분합을 보고합니다.
(null) (null) 658.00
이 행은 큐브의 총계를 보고합니다. Item 차원과 Color 차원의 값이 모두 null입니다. 이는 두 차원의 모든 값이 행에 요약되어 있음을 보여 줍니다.
(null) Blue 225.00
(null) Red 433.00
이 두 행은 Color 차원의 부분합을 보고합니다. 두 행의 Item 차원 값이 모두 null이므로 Item 차원 값이 있는 행에 대한 집계 데이터임을 보여 줍니다.
GROUPING을 사용한 Null 값 구분
CUBE 연산에서 생성되는 Null 값에는 한 가지 문제가 있습니다. CUBE 연산에서 생성되는 NULL을 실제 데이터에서 반환되는 NULL과 어떻게 구분하냐는 것입니다. GROUPING 함수를 사용하여 이 문제를 해결할 수 있습니다. GROUPING 함수는 열 값의 출처가 팩트 데이터면 0을 반환하고 열 값이 CUBE 연산에서 생성된 NULL이면 1을 반환합니다. CUBE 연산에서 생성된 NULL은 모든 값을 나타냅니다. GROUPING 함수를 사용하여 생성된 NULL을 문자열 ALL로 대체하는 SELECT 문을 작성할 수 있습니다. 팩트 데이터의 NULL은 데이터 값을 알 수 없음을 의미하므로 팩트 데이터의 NULL 대신 문자열 UNKNOWN을 반환하도록 SELECT 문을 작성할 수도 있습니다. 예를 들면 다음과 같습니다.
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
다차원 큐브
CUBE 연산자를 사용하여 n차원 큐브나 원하는 차원의 큐브를 생성할 수 있습니다. 다음과 같이 1차원 큐브를 사용하여 합계를 생성할 수 있습니다.
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
이 SELECT 문은 각 Item 값의 부분합과 모든 Item 값의 총계가 포함된 결과 집합을 반환합니다.
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
다차원 CUBE가 포함된 SELECT 문은 모든 차원의 모든 값 조합에 대해 행을 생성하므로 큰 결과 집합을 생성할 수 있습니다. 그러나 데이터가 너무 많기 때문에 이렇게 큰 결과 집합은 읽고 이해하기 어렵습니다. 이 문제의 해결 방법 중 하나는 SELECT 문을 뷰로 변환하는 것입니다.
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
그런 다음 뷰를 사용하여 원하는 차원 값만 쿼리할 수 있습니다.
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)