Oracle 소계쿼리의 모든 것: 데이터 분석의 강력한 도구

DB/Oracle

Oracle 소계쿼리의 모든 것: 데이터 분석의 강력한 도구

shaprimanDev 2025. 5. 11. 20:28

Oracle 데이터베이스는 소계와 합계를 계산하기 위한 강력하고 다양한 기능을 제공합니다. Oracle은 데이터 분석을 위한 선두 주자로서, 풍부한 분석 함수와 집계 기능을 통해 복잡한 비즈니스 질문에 빠르게 답변할 수 있습니다. 이 문서에서는 Oracle에서 소계쿼리를 구현하는 다양한 방법과 고급 기능들을 살펴보겠습니다.

Oracle에서의 소계쿼리 개요

Oracle에서는 데이터의 다양한 레벨에서 소계와 합계를 계산하기 위해 여러 방법을 제공합니다. 주요 기능으로는 ROLLUP, CUBE, GROUPING SETS이 있으며, 이들은 각각 다른 방식으로 다차원 데이터를 집계합니다.

Oracle 소계쿼리 구문 요약:

GROUP BY ROLLUP(col1, col2, ...): 계층적 소계
GROUP BY CUBE(col1, col2, ...): 다차원 소계
GROUP BY GROUPING SETS((col1, col2), (col1), ...): 선택적 소계

참고: 이러한 기능들은 Oracle 9i부터 지원되었으며, Oracle 10g와 11g에서 확장되고 개선되었습니다.

ROLLUP을 사용한 계층적 소계

Oracle의 ROLLUP은 계층적인 순서로 소계를 생성합니다. ROLLUP은 지정된 열에 대해 오른쪽에서 왼쪽으로 레벨을 감소시키며 집계를 수행합니다.

SELECT 
    Region, 
    Category, 
    SubCategory, 
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    ROLLUP(Region, Category, SubCategory);

이 쿼리는 다음과 같은 집계 레벨을 생성합니다:

Region, Category, SubCategory별 합계
Region, Category별 합계 (SubCategory는 NULL)
Region별 합계 (Category, SubCategory는 NULL)
전체 합계 (모든 열이 NULL)

부분 ROLLUP

Oracle에서는 부분 ROLLUP을 지원합니다. 이를 통해 특정 열에 대해서만 계층적 소계를 생성할 수 있습니다:

SELECT 
    Year, 
    Region, 
    Category, 
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    Year, -- 이 열에 대한 소계는 생성하지 않음
    ROLLUP(Region, Category);

이 쿼리는 Year에 대한 소계는 생성하지 않고, 각 Year 내에서 Region과 Category에 대한 소계만 생성합니다.

CUBE를 사용한 다차원 소계

Oracle의 CUBE는 지정된 모든 열의 가능한 조합에 대한 소계를 생성합니다. ROLLUP이 계층적 집계를 제공한다면, CUBE는 모든 차원 조합에 대한 분석을 제공합니다.

SELECT 
    Region, 
    Channel, 
    Category, 
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    CUBE(Region, Channel, Category);

이 쿼리는 다음을 포함하는 총 8가지(2^3) 조합의 집계를 생성합니다:

Region, Channel, Category별 합계
Region, Channel별 합계 (Category는 NULL)
Region, Category별 합계 (Channel은 NULL)
Channel, Category별 합계 (Region은 NULL)
Region별 합계 (Channel, Category는 NULL)
Channel별 합계 (Region, Category는 NULL)
Category별 합계 (Region, Channel은 NULL)
전체 합계 (모든 열이 NULL)

부분 CUBE

Oracle에서는 부분 CUBE도 지원합니다:

SELECT 
    Year, 
    Region, 
    Product, 
    Channel, 
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    Year, -- 이 열에 대한 소계는 생성하지 않음
    CUBE(Region, Product, Channel);

이 쿼리는 Year에 대한 소계는 생성하지 않고, 각 Year 내에서 Region, Product, Channel의 모든 조합에 대한 소계를 생성합니다.

TIP: CUBE는 2^n(n은 열의 수)개의 조합을 생성하므로, 열이 많을 경우 결과 집합이 매우 커질 수 있습니다. 이러한 경우 GROUPING SETS를 사용하여 필요한 조합만 지정하는 것이 효율적입니다.

GROUPING SETS을 사용한 선택적 소계

Oracle의 GROUPING SETS는 가장 유연한 집계 방법으로, 원하는 특정 그룹화 조합만 선택적으로 생성할 수 있습니다.

SELECT 
    Region, 
    Product, 
    Channel, 
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    GROUPING SETS(
        (Region, Product, Channel), -- 가장 상세한 수준
        (Region, Product),          -- 지역 및 제품별
        (Channel),                  -- 채널별
        ()                          -- 전체 합계
    );

이 쿼리는 지정된 4가지 그룹화 조합에 대한 결과만 생성합니다. CUBE와 달리 필요한 조합만 정확히 지정할 수 있어 더 효율적입니다.

GROUPING 관련 함수

Oracle은 소계 행을 식별하고 포맷하기 위한 여러 함수를 제공합니다.

GROUPING 함수

GROUPING(column) 함수는 해당 열이 집계를 위해 NULL로 설정되었는지(값 1 반환) 아니면 실제 데이터의 NULL인지(값 0 반환) 식별합니다.

SELECT 
    CASE WHEN GROUPING(Region) = 1 THEN '전체 지역' ELSE Region END AS Region,
    CASE WHEN GROUPING(Category) = 1 THEN '전체 카테고리' ELSE Category END AS Category,
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    ROLLUP(Region, Category);

GROUPING_ID 함수

GROUPING_ID(col1, col2, ...) 함수는 여러 열의 GROUPING 결과를 단일 정수 값으로 반환합니다. 이는 비트 벡터로 표현되어 어떤 열이 집계되었는지 식별하는 데 유용합니다.

SELECT 
    Region, 
    Category, 
    SubCategory,
    GROUPING_ID(Region, Category, SubCategory) AS GID,
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    ROLLUP(Region, Category, SubCategory);

TIP: GROUPING_ID 값은 이진법으로 해석할 수 있습니다. 예를 들어, GID=3(이진수 011)은 SubCategory와 Category가 집계되었고 Region은 실제 값임을 의미합니다.

GROUP_ID 함수

Oracle은 GROUP_ID() 함수도 제공하는데, 이는 중복 그룹을 식별하는 데 사용됩니다. 일반적으로 GROUPING SETS, ROLLUP 또는 CUBE를 함께 사용할 때 발생할 수 있는 중복 행을 제거하는 데 유용합니다.

SELECT 
    Region, 
    Category, 
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    GROUPING SETS(
        (Region, Category),
        (Region, Category)  -- 중복된 그룹화 집합
    )
HAVING 
    GROUP_ID() = 0;  -- 첫 번째 중복 그룹만 유지

Oracle에서의 복합 집계 예제

Oracle에서는 ROLLUP, CUBE, GROUPING SETS을 조합하여 복잡한 집계 시나리오를 구현할 수 있습니다.

SELECT 
    Year,
    Quarter,
    Region, 
    Category,
    SUM(Sales) AS TotalSales
FROM 
    SalesData
GROUP BY 
    GROUPING SETS(
        ROLLUP(Year, Quarter),     -- 시간 차원의 계층적 집계
        CUBE(Region, Category)     -- 제품 및 지역 차원의 모든 조합
    );

이 쿼리는 시간 차원에 대한 계층적 집계와 지역 및 제품 차원에 대한 다차원 집계를 결합합니다.

실무 활용 예시: 재무 분석

1. 시간 계층별 부서 예산 분석

SELECT 
    CASE WHEN GROUPING(Fiscal_Year) = 1 THEN '모든 연도' ELSE TO_CHAR(Fiscal_Year) END AS Year,
    CASE WHEN GROUPING(Quarter) = 1 THEN '전체 분기' ELSE Quarter END AS Quarter,
    CASE WHEN GROUPING(Department) = 1 THEN '전체 부서' ELSE Department END AS Department,
    SUM(Budget) AS TotalBudget,
    SUM(Actual) AS TotalSpent,
    ROUND(SUM(Actual) / NULLIF(SUM(Budget), 0) * 100, 2) AS UtilizationPct
FROM 
    FinancialData
WHERE 
    Fiscal_Year BETWEEN 2022 AND 2024
GROUP BY 
    ROLLUP(Fiscal_Year, Quarter, Department)
ORDER BY 
    GROUPING(Fiscal_Year), Fiscal_Year,
    GROUPING(Quarter), Quarter,
    GROUPING(Department), Department;

2. 지역 및 제품별 판매 분석

SELECT 
    DECODE(GROUPING(Region), 1, '전체 지역', Region) AS Region,
    DECODE(GROUPING(Channel), 1, '전체 채널', Channel) AS Channel,
    DECODE(GROUPING(Category), 1, '전체 카테고리', Category) AS Category,
    SUM(Sales) AS TotalSales,
    COUNT(DISTINCT Customer_ID) AS CustomerCount,
    ROUND(SUM(Sales) / COUNT(DISTINCT Customer_ID), 2) AS AvgSalePerCustomer,
    RANK() OVER (
        PARTITION BY 
            GROUPING(Region), 
            GROUPING(Channel), 
            GROUPING(Category) 
        ORDER BY 
            SUM(Sales) DESC
    ) AS SalesRank
FROM 
    SalesData
WHERE 
    Sale_Date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-03-31', 'YYYY-MM-DD')
GROUP BY 
    CUBE(Region, Channel, Category)
ORDER BY 
    GROUPING(Region), Region,
    GROUPING(Channel), Channel,
    GROUPING(Category), Category;

3. 선택적 그룹화를 통한 매출 트렌드 분석

SELECT 
    CASE 
        WHEN GROUPING(Year) = 1 AND GROUPING(Month) = 1 THEN '전체 기간'
        WHEN GROUPING(Month) = 1 THEN Year || ' 전체'
        ELSE Year || '-' || LPAD(Month, 2, '0')
    END AS TimePeriod,
    CASE WHEN GROUPING(Product_Line) = 1 THEN '전체 제품라인' ELSE Product_Line END AS ProductLine,
    SUM(Sales) AS TotalSales,
    SUM(Sales) - LAG(SUM(Sales)) OVER (
        PARTITION BY GROUPING(Product_Line), Product_Line 
        ORDER BY Year, Month NULLS LAST
    ) AS SalesGrowth,
    ROUND((SUM(Sales) / LAG(SUM(Sales)) OVER (
        PARTITION BY GROUPING(Product_Line), Product_Line 
        ORDER BY Year, Month NULLS LAST
    ) - 1) * 100, 2) AS GrowthPct
FROM 
    SalesData
WHERE 
    Year BETWEEN 2022 AND 2024
GROUP BY 
    GROUPING SETS(
        (Year, Month, Product_Line),  -- 월별, 제품라인별
        (Year, Product_Line),         -- 연도별, 제품라인별
        (Product_Line),               -- 제품라인별 전체
        ()                            -- 전체 합계
    )
ORDER BY 
    Year NULLS LAST, Month NULLS LAST, 
    GROUPING(Product_Line), Product_Line;

Oracle의 PIVOT 및 UNPIVOT

Oracle 11g부터 도입된 PIVOT과 UNPIVOT 연산자를 사용하면 행과 열을 전환하는 작업을 쉽게 수행할 수 있으며, 이를 소계 쿼리와 결합할 수 있습니다.

-- 소계가 포함된 피벗 테이블 생성
WITH RollupData 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
)
SELECT * FROM RollupData
PIVOT (
    SUM(Sales)
    FOR Quarter IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
)
ORDER BY 
    CASE WHEN Category = '총계' THEN 1 ELSE 0 END, Category;

이 쿼리는 각 카테고리와 총계에 대해 분기별 매출을 열로 표시하는 피벗 테이블을 생성합니다.

Category	Q1	Q2	Q3	Q4
Electronics	125000	138000	142000	185000
Furniture	84000	92000	87000	110000
Clothing	67000	75000	82000	115000
총계	276000	305000	311000	410000

분석 함수와 소계 결합하기

Oracle은 풍부한 분석 함수(윈도우 함수)를 제공하며, 이를 소계 쿼리와 결합하여 고급 분석을 수행할 수 있습니다.

WITH SalesRollup AS (
    SELECT 
        CASE WHEN GROUPING(Region) = 1 THEN '전체 지역' ELSE Region END AS Region,
        CASE WHEN GROUPING(Category) = 1 THEN '전체 카테고리' ELSE Category END AS Category,
        SUM(Sales) AS TotalSales,
        GROUPING_ID(Region, Category) AS GID
    FROM 
        SalesData
    WHERE 
        Year = 2024
    GROUP BY 
        ROLLUP(Region, Category)
)
SELECT 
    Region,
    Category,
    TotalSales,
    -- 전체 대비 비율
    CASE 
        WHEN GID < 3 THEN 
            ROUND(TotalSales / SUM(CASE WHEN GID = 3 THEN TotalSales END) OVER() * 100, 2)
        ELSE NULL
    END AS PctOfTotal,
    -- 지역 내 비율 (카테고리별)
    CASE 
        WHEN GID = 0 THEN 
            ROUND(TotalSales / SUM(CASE WHEN GID = 1 AND Region = Region THEN TotalSales END) OVER(PARTITION BY Region) * 100, 2)
        ELSE NULL
    END AS PctInRegion,
    -- 누적 합계 (지역 내)
    CASE 
        WHEN GID = 0 THEN 
            SUM(TotalSales) OVER(PARTITION BY Region ORDER BY TotalSales DESC)
        ELSE NULL
    END AS RunningTotalInRegion
FROM 
    SalesRollup
ORDER BY 
    GROUPING(Region), Region,
    GROUPING(Category), TotalSales DESC;

이 쿼리는 소계 행과 함께 전체 대비 비율, 지역 내 카테고리별 비율, 지역 내 누적 합계와 같은 고급 분석 지표를 계산합니다.

성능 최적화 기법

Oracle에서 소계쿼리의 성능을 최적화하기 위한 몇 가지 중요한 기법은 다음과 같습니다:

물리적 설계 최적화:
- GROUP BY 열에 인덱스 생성
- 파티셔닝을 활용하여 데이터 액세스 최소화
- 집계 열에 대한 함수 기반 인덱스 고려
통계 분석 활용:
- 정기적으로 DBMS_STATS.GATHER_TABLE_STATS 실행
- 히스토그램을 활용하여 데이터 분포에 따른 최적화
쿼리 튜닝:
- 필요한 열만 선택하여 처리할 데이터 양 최소화
- WHERE 절에서 가능한 한 많은 데이터 필터링
- 데이터 볼륨이 큰 경우 GROUPING SETS 사용 고려(CUBE 대신)
구체화된 뷰(Materialized Views) 활용:
- 자주 사용되는 집계 쿼리에 대해 구체화된 뷰 생성
- 자동 쿼리 재작성 활성화: ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
- 증분 리프레시 설정하여 데이터 최신성 유지

주의: Oracle의 ROLLUP, CUBE, GROUPING SETS은 임시 테이블 작업을 필요로 하므로, 대용량 데이터에서는 TEMP 테이블스페이스 크기와 PGA/SGA 메모리 설정에 주의해야 합니다. 특히 CUBE는 2^n 조합을 생성하므로 열 수가 많을 경우 성능 영향이 클 수 있습니다.

Oracle, MSSQL, MySQL 소계쿼리 비교

기능	Oracle	MSSQL	MySQL
기본 소계 구문	`GROUP BY ROLLUP(...)` Oracle 9i+	`GROUP BY ROLLUP(...)` SQL Server 2008+	`GROUP BY ... WITH ROLLUP` MySQL 5.0+
다차원 소계	`GROUP BY CUBE(...)` Oracle 9i+	`GROUP BY CUBE(...)` SQL Server 2008+	직접 지원하지 않음 (UNION ALL로 구현 가능)
선택적 그룹화	`GROUP BY GROUPING SETS(...)` Oracle 9i+	`GROUP BY GROUPING SETS(...)` SQL Server 2008+

저작자표시 비영리 변경금지 (새창열림)

현재글Oracle 소계쿼리의 모든 것: 데이터 분석의 강력한 도구

개발

Oracle, 개발소스, 이벤트, JavaScript, 기본문법, useEffect, 프로시저, 게시판, 오라클, React, TODOlist, 병렬처리, 힌트, python, 도구, 개발툴, 튜닝, 최적화, ai개발언어, redux,

Today :
Yesterday :

개발