100강 SQL 함수 정리 : PostgreSQL Crosstab Function

 PostgreSQL 기초강의, PostgreSQL 기초강좌

100강 SQL 함수 정리 : PostgreSQL Crosstab Function   

 

SQL 함수 정리 100강 시작합니다.

 

이번 시간에는

▶ PostgreSQL Crosstab Function

에 대해서 알아보는 시간입니다.

함수 정리

이번 시간에는 PostgreSQL Crosstab() 함수를 사용하여 세로행으로 구성된 테이블의 데이터를 가로 열로 PIVOT 하는 방법에 대해서 배웁니다.

오라클에서는 11g 버전부터 PIVOT, UNPIVOT 함수가 제공되어 이전에 다른 방법으로 수행하던 것을 PIVOT 함수를 이용해서 간단히 수행할 수 있게 되었습니다.

PostgreSQL에서도 CROSSTAB 함수를 이용하여 PIVOT 기능을 구현할 수 있습니다.

 

PostgreSQL 기초강좌는 100강으로 마칩니다. PL/pgSQL, DB admin관련, 다른 프로그램과의 API 연결 등은 기회가 되면 다시 찾아뵙도록 하겠습니다. 논리 DB설계와 SQL튜닝 등을 주로 했던터라 DB ADMIN 부분은 잘 모르기도 하고요. SQL 강의를 올리면서 어렵다고 하시는 댓글을 많이 보았습니다. 그래서 다음 강의는 컴퓨터를 이해하는 강의로 해볼까 합니다. 저도 컴퓨터를 전공하지 않은 비전공자였습니다. 이 업에 종사하시는 분들중 비전공자가 더 많은 것도 사실이고요. 컴퓨터에 대한 이해가 선행되면 보다 더 쉽게 프로그램이나 SQL을 이해하실 수 있으리라 생각됩니다.

---

실습을 위한 테이블 생성   

학생 ID, 과목별로 성적을 관리하는 샘플 테이블을 만들어 보겠습니다.

DROP TABLE IF EXISTS score_long;
CREATE TABLE score_long (
     id int NOT null ,
     class_nm TEXT ,
     score int );    

 

테이블에 샘플 데이터를 삽입합니다.

INSERT INTO score_long VALUES 
 (1,'english' , 100)
, (1,'korean' , 100)
, (1,'math', 100)
, (2,'english', 75)
, (2,'korean', 80)
, (2,'math', 85)
, (3,'english', 98)
, (3,'korean' , 100)
, (3,'math', 60)
, (4,'english', 75)
, (4,'korean', 80)
, (4,'math', 98)
; 

 

데이터를 확인해 봅니다.

SELECT * FROM score_long;   

샘플데이터

---

PostgreSQL Crosstab() 함수를 이용해 테이블 피봇하기   

세로로 긴 테이블을 가로로 넓은 테이블로 pivot 할 때 사용되는 crosstab() 함수는 PostgreSQL 버전 8.3 이 배포되었을 때 처음 소개되었던 tablefunc extension에 포함되었습니다. 

 

따라서 tablefunc extension 을 처음 사용하는 경우라면

CREATE EXTENSION IF NOT EXISTS tablefunc;  

실행하여 tablefunc 모듈을 설치해야 합니다.

 

crosstab() 함수는 SELECT 문의 FROM 절에 사용이 되므로 처음 사용하는 분이라면 좀 낯설게 여길 수도 있겠습니다. 

 

crosstab() 함수에서 SELECT 문은 3개의 칼럼을 반환합니다. 

• (칼럼 1) 첫번째 칼럼은 각 관측치를 구분하는 ID (identifier) 칼럼입니다. 위의 예에서는 학생들의 ID가 이에 해당합니다.
• (칼럼 2) 두번째 칼럼은 pivot table에서의 범주(categories)에 해당하는 칼럼입니다. pivot을 하게 되면 각 칼럼으로 변환이 됩니다. 위의 예에서는 과목명(class_nm) 칼럼이 이에 해당합니다. 
• (칼럼 3) 세번째 칼럼은 pivot table의 각 셀에 할당이 될 값(value)에 해당하는 칼럼입니다. 위의 예에서는 점수(score) 칼럼이 이에 해당합니다. 

crosstab() 함수안에 SQL query로 위의 3개 칼럼을 select 한 결과를, AS final_result()에서 pivot table에서 재표현할 칼럼 이름과 데이터 유형을 정의해 주면 됩니다. 

 

이제 SQL문을 보도록 하겠습니다.

SELECT *
FROM 
CROSSTAB(
'SELECT id, class_nm, score 
   FROM score_long 
   ORDER BY id, class_nm') 
AS FINAL_RESULT(id int, english_score int, korean_score int, math_score int); 

결과

---

Crosstab() 함수 정리   

• crosstab은 rows를 columns로 바꾸는 query 
• crosstab은 aggregate가 이미 되어 있는 테이블에서만 pivot이 가능함
• pivot과 동시에 aggregate해주는 기능이 없으므로 먼저 table을 aggregate한 후 crosstab에 적용해야 한다.
• cross tab을 사용하기 위해서 tablefunc 모듈을 설치해야 함
• CREATE EXTENSION IF NOT EXISTS tablefunc;

---

이상으로 crosstab() 함수를 이용하여 pivot하는 방법을 보았습니다.

PostgreSQL 기초강의는 100강으로 마무리합니다.

 

다음 '컴퓨터의 이해' 강의로 찾아 뵙겠습니다.

감사합니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.