오라클 쿼리에서 PIVOT 사용하기: 다양한 유형과 실습

개발/오라클

오라클 쿼리에서 PIVOT 사용하기: 다양한 유형과 실습

꿈꾸는법사 2025. 1. 13. 08:21

오라클의 PIVOT 연산자는 행 데이터를 열로 변환하여 보다 직관적인 형식으로 데이터를 조회할 수 있게 해줍니다. 이는 데이터 분석, 보고서 생성 등에서 자주 사용됩니다. 본 문서에서는 오라클에서 PIVOT을 사용하는 다양한 유형의 예제를 자세히 설명합니다.

PIVOT의 기본 구조

SELECT *
FROM 테이블명
PIVOT (
  집계함수(컬럼명)
  FOR 피벗컬럼명 IN (값1, 값2, 값3, ...)
);

구성 요소

집계함수: SUM, MAX, MIN, AVG 등의 집계함수를 사용하여 데이터를 변환합니다.
피벗컬럼: 행 데이터를 열로 변환하는 기준 컬럼입니다.
IN 절: 열로 변환할 값을 지정합니다.

1. 기본 PIVOT 사용 예제

문제

아래의 SALES 테이블을 기준으로 YEAR 열을 기준으로 피벗을 적용합니다.

PRODUCT YEAR SALES

A	2021	100
A	2022	150
B	2021	200
B	2022	250

쿼리

SELECT *
FROM SALES
PIVOT (
  SUM(SALES)
  FOR YEAR IN (2021 AS "2021년", 2022 AS "2022년")
);

결과

PRODUCT 2021년 2022년

A	100	150
B	200	250

2. 다중 집계함수를 사용하는 PIVOT

문제

한 번의 피벗으로 SUM과 AVG를 계산합니다.

쿼리

SELECT *
FROM SALES
PIVOT (
  SUM(SALES) AS TOTAL,
  AVG(SALES) AS AVG_SALES
  FOR YEAR IN (2021, 2022)
);

결과

PRODUCT 2021_TOTAL 2021_AVG_SALES 2022_TOTAL 2022_AVG_SALES

A	100	100	150	150
B	200	200	250	250

3. PIVOT에 알리아스 적용

문제

피벗된 열 이름에 알리아스를 추가하여 가독성을 높입니다.

쿼리

SELECT *
FROM SALES
PIVOT (
  SUM(SALES) AS SALES_SUM
  FOR YEAR IN (2021 AS "YEAR_2021", 2022 AS "YEAR_2022")
);

결과

PRODUCT YEAR_2021_SALES_SUM YEAR_2022_SALES_SUM

A	100	150
B	200	250

4. 다중 PIVOT 구현

문제

YEAR와 REGION 두 개의 피벗 컬럼을 동시에 처리합니다.

PRODUCT YEAR REGION SALES

A	2021	EAST	100
A	2021	WEST	120
A	2022	EAST	150
B	2021	WEST	200

쿼리

SELECT *
FROM (
  SELECT PRODUCT, YEAR, REGION, SALES
  FROM SALES
)
PIVOT (
  SUM(SALES)
  FOR REGION IN ('EAST' AS EAST_REGION, 'WEST' AS WEST_REGION)
);

결과

PRODUCT YEAR EAST_REGION WEST_REGION

A	2021	100	120
A	2022	150	NULL
B	2021	NULL	200

5. 동적 PIVOT 사용

오라클의 기본 PIVOT 구문에서는 IN 절에 고정된 값을 지정해야 합니다. 그러나 피벗 컬럼의 값이 동적으로 변할 경우 동적 SQL을 사용해야 합니다.

문제

YEAR 값이 동적으로 변경되는 상황을 처리합니다.

쿼리

DECLARE
  sql_query VARCHAR2(4000);
BEGIN
  SELECT LISTAGG(YEAR, ', ') WITHIN GROUP (ORDER BY YEAR)
  INTO sql_query
  FROM (SELECT DISTINCT YEAR FROM SALES);

  sql_query := 'SELECT * FROM SALES PIVOT (SUM(SALES) FOR YEAR IN (' || sql_query || '))';
  
  EXECUTE IMMEDIATE sql_query;
END;

결과

결과는 YEAR 데이터에 따라 자동으로 생성됩니다.

6. 복잡한 데이터 구조를 피벗

문제

DEPARTMENT, YEAR, SALES 데이터에서 특정 부서와 연도를 기준으로 데이터를 변환합니다.

DEPARTMENT YEAR SALES

HR	2021	300
HR	2022	400
IT	2021	500
IT	2022	600

쿼리

SELECT *
FROM (
  SELECT DEPARTMENT, YEAR, SALES
  FROM SALES
)
PIVOT (
  SUM(SALES)
  FOR YEAR IN (2021, 2022)
);

결과

DEPARTMENT 2021 2022

HR	300	400
IT	500	600

7. PIVOT에서 NULL 처리

문제

NULL 값을 특정 값으로 대체하여 처리합니다.

쿼리

SELECT NVL(2021, 0) AS "YEAR_2021",
       NVL(2022, 0) AS "YEAR_2022"
FROM SALES
PIVOT (
  SUM(SALES)
  FOR YEAR IN (2021, 2022)
);

결과

PRODUCT YEAR_2021 YEAR_2022

A	100	150
B	200	250

8. PIVOT과 JOIN 사용

문제

PRODUCT 정보를 포함한 별도의 테이블과 JOIN하여 피벗을 구현합니다.

PRODUCT CATEGORY

A	ELECTRONICS
B	FURNITURE

쿼리

SELECT P.CATEGORY, P.PRODUCT, S.*
FROM PRODUCTS P
JOIN (
  SELECT *
  FROM SALES
  PIVOT (
    SUM(SALES)
    FOR YEAR IN (2021, 2022)
  )
) S ON P.PRODUCT = S.PRODUCT;

결과

CATEGORY PRODUCT 2021 2022

ELECTRONICS	A	100	150
FURNITURE	B	200	250

결론

오라클의 PIVOT 연산자는 데이터 분석 및 시각화에서 매우 유용한 도구입니다. 기본적인 사용법에서 시작하여 다중 집계 함수, 동적 PIVOT, NULL 처리, JOIN과의 조합 등 다양한 활용 방법을 익히는 것이 중요합니다. 이 문서에서 다룬 예제들을 활용하여 다양한 시나리오에서 데이터를 효율적으로 처리해 보세요.

저작자표시