반응형
Microsoft SQL Server(MSSQL)에서 소계와 합계를 계산하는 것은 데이터 분석과 보고서 생성에 필수적인 기능입니다. 이 문서에서는 MSSQL에서 소계쿼리를 구현하는 다양한 방법과 고급 기능들을 살펴보겠습니다.
MSSQL에서의 소계쿼리란?
소계쿼리는 데이터를 서로 다른 수준에서 집계하여 세부 데이터와 함께 중간 합계 및 총계를 한번에 제공하는 쿼리입니다. MSSQL은 이를 위한 여러 기능을 제공하며, 가장 대표적인 것이 ROLLUP, CUBE, GROUPING SETS입니다.
ROLLUP을 사용한 소계
MSSQL에서 ROLLUP은 계층적 소계를 생성하는 데 사용됩니다. MySQL의 WITH ROLLUP과 유사하지만 구문에 차이가 있습니다.
SELECT
Category,
SubCategory,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
Category, SubCategory
WITH ROLLUP;MSSQL 2008 이상에서는 다음과 같이 GROUP BY와 ROLLUP을 함께 사용합니다:
SELECT
Category,
SubCategory,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
ROLLUP(Category, SubCategory);이 쿼리는 다음과 같은 결과를 생성합니다:
- 각 Category와 SubCategory 조합에 대한 합계
- 각 Category의 합계 (SubCategory는 NULL)
- 전체 합계 (Category와 SubCategory 모두 NULL)
GROUPING 함수
MSSQL에서는 GROUPING 함수를 사용하여 NULL 값이 실제 데이터의 NULL인지 아니면 소계를 위한 NULL인지 구분할 수 있습니다.
SELECT
CASE
WHEN GROUPING(Category) = 1 THEN '모든 카테고리'
ELSE Category
END AS Category,
CASE
WHEN GROUPING(SubCategory) = 1 THEN '모든 서브카테고리'
ELSE SubCategory
END AS SubCategory,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
ROLLUP(Category, SubCategory);추가로 MSSQL은 GROUPING_ID 함수를 제공하여 여러 열의 GROUPING 결과를 단일 정수 값으로 표현할 수 있습니다:
SELECT
Category,
SubCategory,
GROUPING_ID(Category, SubCategory) AS GroupingLevel,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
ROLLUP(Category, SubCategory);부분 ROLLUP
MSSQL에서는 부분 ROLLUP을 구현할 수 있습니다. 이는 일부 열에 대해서만 소계를 생성하고 싶을 때 유용합니다:
SELECT
Region,
Category,
SubCategory,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
Region, -- 이 열에 대한 소계는 생성하지 않음
ROLLUP(Category, SubCategory);이 쿼리는 Region에 대한 소계는 생성하지 않고, Category와 SubCategory에 대한 소계만 생성합니다.
CUBE를 사용한 다차원 소계
MSSQL의 CUBE는 ROLLUP보다 더 많은 조합의 소계를 생성합니다. CUBE는 지정된 모든 열의 가능한 조합에 대한 소계를 생성합니다.
SELECT
Region,
Category,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
CUBE(Region, Category);이 쿼리는 다음과 같은 결과를 생성합니다:
- 각 Region과 Category 조합에 대한 합계
- 각 Region의 합계 (Category는 NULL)
- 각 Category의 합계 (Region은 NULL)
- 전체 합계 (Region과 Category 모두 NULL)
TIP:
ROLLUP은 계층적 관계를 가진 데이터(예: 연도 > 분기 > 월)에 적합하고, CUBE는 서로 독립적인 차원에 대한 분석(예: 지역과 제품 카테고리)에 적합합니다.GROUPING SETS
MSSQL의 GROUPING SETS를 사용하면 원하는 특정 그룹화 조합만 선택적으로 생성할 수 있습니다. 이는 ROLLUP이나 CUBE보다 더 세밀한 제어가 가능합니다.
SELECT
Region,
Category,
Year,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
GROUPING SETS(
(Region, Category, Year), -- 가장 상세한 수준
(Region, Category), -- Region과 Category별 합계
(Year), -- Year별 합계
() -- 전체 합계
);이 쿼리는 지정된 4가지 그룹화 조합에 대한 결과만 생성합니다.
MSSQL 소계쿼리의 실무 활용 예시
1. 다년간 지역별 판매 분석
SELECT
CASE WHEN GROUPING(Year) = 1 THEN '전체 연도' ELSE CAST(Year AS VARCHAR) END AS Year,
CASE WHEN GROUPING(Region) = 1 THEN '전체 지역' ELSE Region END AS Region,
CASE WHEN GROUPING(Quarter) = 1 THEN '전체 분기' ELSE Quarter END AS Quarter,
SUM(Sales) AS TotalSales,
COUNT(DISTINCT CustomerID) AS CustomerCount,
AVG(SalesAmount) AS AvgSalesAmount
FROM
SalesData
WHERE
Year BETWEEN 2022 AND 2024
GROUP BY
ROLLUP(Year, Region, Quarter)
ORDER BY
GROUPING(Year), Year,
GROUPING(Region), Region,
GROUPING(Quarter), Quarter;2. 제품 카테고리 및 고객 유형별 분석
SELECT
CASE WHEN GROUPING(Category) = 1 THEN '전체 카테고리' ELSE Category END AS Category,
CASE WHEN GROUPING(CustomerType) = 1 THEN '전체 고객유형' ELSE CustomerType END AS CustomerType,
SUM(Sales) AS TotalSales,
COUNT(*) AS TransactionCount,
SUM(Sales) / COUNT(*) AS AvgTransactionValue
FROM
SalesData
GROUP BY
CUBE(Category, CustomerType)
ORDER BY
GROUPING(Category), Category,
GROUPING(CustomerType), CustomerType;3. 맞춤형 그룹화를 통한 판매 채널 분석
SELECT
CASE WHEN GROUPING(Year) = 1 THEN '전체 연도' ELSE CAST(Year AS VARCHAR) END AS Year,
CASE WHEN GROUPING(Channel) = 1 THEN '전체 채널' ELSE Channel END AS Channel,
CASE WHEN GROUPING(Region) = 1 THEN '전체 지역' ELSE Region END AS Region,
SUM(Sales) AS TotalSales
FROM
SalesData
GROUP BY
GROUPING SETS(
(Year, Channel, Region), -- 상세 수준
(Year, Channel), -- 연도 및 채널별
(Channel, Region), -- 채널 및 지역별
(Channel), -- 채널별
() -- 전체 합계
)
ORDER BY
GROUPING(Year), Year,
GROUPING(Channel), Channel,
GROUPING(Region), Region;피벗 테이블과 소계쿼리 결합하기
MSSQL에서는 PIVOT 연산자를 사용하여 행을 열로 변환할 수 있으며, 이를 소계쿼리와 결합할 수 있습니다.
-- 임시 테이블로 소계 결과 저장
WITH SalesRollup AS (
SELECT
CASE WHEN GROUPING(Category) = 1 THEN '총계' ELSE Category END AS Category,
Quarter,
Sales
FROM
SalesData
WHERE
Year = 2024
GROUP BY
ROLLUP(Category), Quarter
)
-- PIVOT을 사용하여 분기별 매출을 열로 변환
SELECT
Category,
[Q1] AS Q1_Sales,
[Q2] AS Q2_Sales,
[Q3] AS Q3_Sales,
[Q4] AS Q4_Sales,
ISNULL([Q1], 0) + ISNULL([Q2], 0) + ISNULL([Q3], 0) + ISNULL([Q4], 0) AS YearlyTotal
FROM
SalesRollup
PIVOT (
SUM(Sales)
FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable
ORDER BY
CASE WHEN Category = '총계' THEN 1 ELSE 0 END, Category;| Category | Q1_Sales | Q2_Sales | Q3_Sales | Q4_Sales | YearlyTotal |
|---|---|---|---|---|---|
| 전자기기 | 125000 | 138000 | 142000 | 185000 | 590000 |
| 가구 | 84000 | 92000 | 87000 | 110000 | 373000 |
| 의류 | 67000 | 75000 | 82000 | 115000 | 339000 |
| 총계 | 276000 | 305000 | 311000 | 410000 | 1302000 |
순위 및 윈도우 함수와 소계 결합하기
MSSQL의 윈도우 함수를 사용하면 소계와 함께 순위나 누적 합계 등의 고급 분석을 수행할 수 있습니다.
WITH SalesRollup AS (
SELECT
CASE WHEN GROUPING(Category) = 1 THEN '총계' ELSE Category END AS Category,
CASE WHEN GROUPING(SubCategory) = 1 THEN
CASE WHEN GROUPING(Category) = 1 THEN '총계' ELSE '소계' END
ELSE SubCategory END AS SubCategory,
SUM(Sales) AS TotalSales,
GROUPING_ID(Category, SubCategory) AS Level
FROM
SalesData
GROUP BY
ROLLUP(Category, SubCategory)
)
SELECT
Category,
SubCategory,
TotalSales,
CASE
WHEN Level = 0 THEN -- 가장 상세한 수준 (Category, SubCategory)
RANK() OVER(PARTITION BY Category ORDER BY TotalSales DESC)
ELSE NULL
END AS RankInCategory,
CASE
WHEN Level = 0 THEN -- 가장 상세한 수준 (Category, SubCategory)
FORMAT(TotalSales / SUM(TotalSales) OVER(PARTITION BY Category, GROUPING(SubCategory)), 'P2')
WHEN Level = 1 THEN -- Category 소계
FORMAT(TotalSales / SUM(TotalSales) OVER(PARTITION BY GROUPING(Category)), 'P2')
ELSE NULL
END AS ContributionPct
FROM
SalesRollup
ORDER BY
Level, Category, TotalSales DESC;이 쿼리는 각 카테고리 내에서 서브카테고리별 판매 순위와 기여도를 계산합니다.
MSSQL 소계쿼리 성능 최적화
MSSQL에서 소계쿼리의 성능을 최적화하기 위한 몇 가지 팁입니다:
- 필터링은 소계 계산 전에 수행하여 처리해야 할 데이터의 양을 줄입니다.
- 필요한 열만 포함하여 메모리 사용량을 최소화합니다.
- GROUP BY 열에 인덱스를 생성하면 그룹화 작업 성능이 향상됩니다.
- 매우 큰 데이터셋의 경우 다음과 같은 접근 방식을 고려하세요:
- 사전 계산된 집계 테이블 사용
- 파티셔닝된 테이블 활용
- 복잡한 쿼리를 여러 개의 작은 쿼리로 분할
OPTION (RECOMPILE)힌트를 사용하여 쿼리 계획을 최적화할 수 있습니다.
주의:
CUBE는 ROLLUP보다 더 많은 조합을 생성하므로 데이터가 많은 경우 리소스 사용량이 크게 증가할 수 있습니다. 필요한 경우에만 CUBE를 사용하고, 가능하면 GROUPING SETS로 필요한 조합만 정의하는 것이 좋습니다.MySQL과 MSSQL 소계쿼리 비교
| 기능 | MySQL | MSSQL |
|---|---|---|
| 기본 소계 구문 | GROUP BY ... WITH ROLLUP |
GROUP BY ROLLUP(...) |
| 다차원 소계 | 직접 지원하지 않음 (UNION ALL로 구현 가능) | GROUP BY CUBE(...) |
| 선택적 그룹화 | 직접 지원하지 않음 (UNION ALL로 구현 가능) | GROUP BY GROUPING SETS(...) |
| NULL 구분 | MySQL 8.0+: GROUPING() 함수 |
GROUPING() 및 GROUPING_ID() 함수 |
| 부분 소계 | 지원하지 않음 | 지원 (GROUP BY col1, ROLLUP(col2, col3)) |
| 피벗 테이블 | CASE 표현식으로 구현 (내장 함수 없음) | PIVOT 연산자 지원 |
결론
MSSQL은 MySQL보다 더 다양하고 강력한 소계 및 집계 기능을 제공합니다. ROLLUP, CUBE, GROUPING SETS 및 PIVOT과 같은 기능을 활용하면 복잡한 비즈니스 보고서를 효율적으로 생성할 수 있습니다. 각 기능의 특성과 성능 영향을 이해하고 적절하게 활용하면 데이터 분석 작업을 크게 향상시킬 수 있습니다.
반응형