SQLD 2과목 SQL 활용 - PIVOT 절과 UNPIVOT 절

728x90

<목차>

※ 자료출처: 데이터 산업 진흥원(K-DATA) & 마이크로소프트

시작하기에 앞서..
해당 카테고리는 2024년에 시행되는 첫번째 시험부터 추가되는 시험 범위라, 자료를 찾기에 어려운 점이 있었다.
시험범위가 대대적으로 달라진 만큼, 현재 시점에서 자료 찾기는 어려울 수 있으나,
추후 수정된 참고서들이 나올테니 그때 내용에 보강이 필요하면 보완할 예정이다.

[보완내용]

[SQLD] PIVOT(보강)

해당 범위는 SQLD 2024년에 추가된 범위라 기존 내용이 많이 부족하여 추가하였습니다. 기존내용 SQLD 2과목 SQL 활용 - PIVOT 절과 UNPIVOT 절 서브쿼리 집합연산자 그룹 함수 윈도우 함수 TOP N 쿼리 계층

puppy-foot-it.tistory.com

[SQLD] PIVOT / UNPIVOT (보강-2)

기존내용 SQLD 2과목 SQL 활용 - PIVOT 절과 UNPIVOT 절 서브쿼리 집합연산자 그룹 함수 윈도우 함수 TOP N 쿼리 계층형 질의와 셀프 조인 PIVOT 절과 UNPIVOT 절 정규 표현식 ※ 자료출처: 데이터 산업 진흥

puppy-foot-it.tistory.com

7. PIVOT 절과 UNPIVOT 절

PIVOT과 UNPIVOT 연산자는 일반적으로 사용자가
다른 형식의 정보를 요구할 때 보고 목적으로 사용.

그러나 때때로 UNPIVOT은 쿼리 출력을 정규화 하는데 사용되기도 함.

PIVOT 절

세로로 나열된 table의 행(Row)을 가로열(Column)로 표시하는 것

https://www.mssqltips.com/sqlservertip/7233/sql-pivot-sql-unpivot-examples-transform-data/

PIVOT은 그룹화 열과 관련해 입력 테이블에서 그룹화 연산을 수행하고 각각의 그룹마다 한 개의 행을 반환
출력에는 INPUT_TABLE의 PIVOT_COLUMN에 나타나는 COLUMN_LIST 에 지정된 각 값에 대한 하나의 열이 포함.
식의 한 열에서 출력의 여러 열로 고유 값을 전환하여 테이블 반환 식 회전
최종 출력에서 원하는 나머지 열 값에 필요한 집계 실행

PIVOT 함수	설명
aggregate_function	- 하나 이상의 입력을 허용하는 시스템 또는 사용자 정의 집계 함수 - 집계 함수는 Null 값에 따라 결과가 달라지지 않아야 함 - count(*) 시스템 집계 함수 허용되지 않음
value_column	- PIVOT 연산자의 값 열 - UNPIVOT 과 함께 사용하는 경우 value_column 입력 table_source 기존 열의 이름이 될 수 없음
FOR pivot_column	- PIVOT 연산자의 피벗 열 - 암시적 또는 명시적으로 nvarchar() 로 변환할 수 있는 형식이어야 함. - UNPIVOT 이 사용되면 pivot_column은 table_source 에서 좁혀진 출력 열의 이름
IN (column_list)	- PIVOT 절에서 출력 테이블의 열 이름이 되는 pivot_column 값 나열 - 목록에서 피벗되는 입력 table_source가 이미 있는 열 이름 지정 불가 - UNPIVOT 절에서 단일 pivot_column 의 좁혀진 table_soruce 열 나열
table_alias	- 출력 테이블의 별칭 - 지정해줘야 함

[예제]

PurchaseOrderHeader 테이블을 AdventureWorks2022 쿼리하여 특정 직원이 주문한 구매 주문 수를 확인하려는 경우

USE AdventureWorks2022;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

<Oracle>

아래와 같은 테이블이 있다.

select cust_id, state_code, times_purchased

from customers

order by cust_id;

데이터가 값 행으로 표시되는 방식에 유의.

각 고객에 대해 레코드에는 고객의 집 상태와 고객이 매장에서 물건을 구매한 횟수가 표시

고객이 상점에서 더 많은 품목을 구매하면 times_purchased 열이 업데이트 됨.

이제 각 주의 구매 빈도(즉, 각 주에서 한 번, 두 번, 세 번 등의 품목을 구입한 고객 수)에 대한

보고서를 갖고 싶은 경우,일반 SQL에서는 다음 명령문을 실행.

select state_code, times_purchased, count(1) cnt

from customers

group by state_code, times_purchased;

출력은 다음과 같다

이것은 당신이 원하는 정보이지만 읽기가 조금 어렵다.

동일한 데이터를 표현하는 더 좋은 방법은

스프레드시트처럼 데이터를 수직으로 구성하고

수평으로 상태를 표시할 수 있는 크로스탭 보고서를 사용하는 것.

※ 기존에는 각 값에 대해 일종의 디코드 기능을 통해 이를 수행하고 각 고유 값을 별도의 열로 기록했으나,

이 기술은 매우 직관적이지 않음.

PIVOT 이라는 기능으로 새 연산자를 사용하여 크로스탭 형식으로 쿼리 표시 가능.

select * from (
   select times_purchased, state_code
   from customers t
)
pivot
(
   count(state_code)

   for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
/

구매 횟수 열을 머리글 행으로 바꾸려고 한다. 마치 열이 시계 반대 방향으로 90도 회전하여

머리글 행이 되는 것처럼 열이 행이 되는데, 이 비유적 회전에는 피벗점이 있어야 하며

이 경우 피벗점은 count(state_code) 표현식이 됨.

...
pivot
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
...

※ "for state_code ..."는 쿼리를 해당 값으로만 제한.

이 줄은 필요하므로 가능한 값을 미리 알아야 함.

열 헤더는 테이블 자체의 데이터(주 코드) 이므로 약어 대신 주 이름을 표시하고 싶다고 가정하면,

아래와 같이 쿼리의 FOR 절을 약간 조정해야 함.

select * from (
   select times_purchased as "Puchase Frequency", state_code
   from customers t
)
pivot
(
   count(state_code)
   for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1
/

UNPIVOT 절

UNPIVOT은 가로 표시된 것을 다시 세로로 표시하는 것

UNPIVOT은 테이블 반환 식의 열을 값으로 회전하여 PIVOT 과 반대 작업 수행
열을 행으로 회전하여 PIVOT 과 거의 반대되는 작업 수행
입력 테이블이 column_list의 여러 열에서 pivot_column 이라는 단일 열로 좁혀지도록 지정

[예제]

위의 예에서 생성된 테이블이 데이터베이스에 pvt로 저장되어 있는 상태에서 Emp1, Emp2, Emp3, Emp4 및 Emp5 열 식별자를 특정 공급업체에 해당하는 행 값으로 회전

▶ 두 개의 추가 열을 식별해야 합니다. 회전하는 열 값(Emp1Emp2,...)을 포함하는 열이 호출 [Employee] 되고 회전되는 열 아래에 현재 있는 값을 보유하는 열이 호출 [Orders]

-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,
    Emp3 INT, Emp4 INT, Emp5 INT);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

※ UNPIVOT의 반대는 PIVOT이 아님.

PIVOT: 집계를 수행하고 가능한 여러 행을 출력의 단일 행으로 병합.

UNPIVOT: 행이 병합되었기 대문에 원래 테이블 반환 식 결과를 재현하지 않음.

Null 값은 출력에서 사라짐.

[참고하면 좋을 게시글]

[SQL] PIVOT에 대해 알아보고, 꿀팁도 얻어가자.

PIVOT 이란? 식에 있는 한 열의 고유 값을 출력의 여러 열로 반환하여 테이블 반환 식을 순환합니다. 라고 MSDN에 설명되어져 있다. 하지만 조금 어렵게 느껴진다. 쉽게 설명해서, 식에 있는 한 열의

im-first-rate.tistory.com

이전글

계층형 질의와 셀프 조인 (2)

다음글

정규 표현식