13-4.피봇

1.PIVOT

PIVOT 연산자는 행으로 나열되어 있는 데이터를 열로 나열하여 보기 쉽게 가공한다. 시간순으로 차곡차곡 쌓이는 값이나 대규모 인원의 정보는 세로로 길어 한눈에 알아보기 어렵다. 스크롤해 가며 봐야 할 정도로 양이 많으면 동시에 두 값을 비교하기도 어렵다. 피봇은 세로행을 가로열로 회전시켜 가독성을 향상시킨다.

원본 테이블에서 특정 필드의 값이었던 1월, 2월, 3월이 피봇 테이블에는 별도의 열로 나타난다. 뿐만 아니라 3월처럼 중복된 데이터는 하나로 합쳐 집계해 준다. 이런 동작이 가능하려면 회전할 필드, 값의 종류, 중복시 집계 방법 등의 상세한 지정이 필요하다.

PIVOT은 구문이 복잡하고 회전 결과가 직관적이지 않아 결과를 봐도 동작을 이해하기 쉽지 않다. 반드시 실습을 통해 실행 과정을 분석하고 연구해 봐야 한다. 이런 기능이 왜 필요하고 어떤 유용성이 있는지 단순한 테이블부터 단계적으로 실행해 보자.

CREATE TABLE tSeason

(

item VARCHAR(10),

season VARCHAR(10),

sale INT

);

INSERT INTO tSeason VALUES ('냉면', '봄', 20);

INSERT INTO tSeason VALUES ('냉면', '여름', 50);

INSERT INTO tSeason VALUES ('냉면', '가을', 30);

INSERT INTO tSeason VALUES ('냉면', '겨울', 10);

INSERT INTO tSeason VALUES ('짬뽕', '봄', 30);

INSERT INTO tSeason VALUES ('짬뽕', '여름', 10);

INSERT INTO tSeason VALUES ('짬뽕', '가을', 20);

INSERT INTO tSeason VALUES ('짬뽕', '겨울', 40);

두 개의 상품에 대해 계절별 판매량을 기록한 테이블이다. 판매량이 수직으로 나열되어 있어 한눈에 보이지 않고 상품간이나 계절별 비교가 쉽지 않다. 여름철 냉면과 짬뽕의 매출을 비교하려면 아래 위로 시선을 옮겨 가며 봐야 한다. 상품이 10개 정도 되고 계절별이 아닌 월별로 판매량을 기록해 놓았다면 육안으로 파악하기 쉽지 않다.

세로 축에 상품을 적고 가로 축에 계절을 적은 후 두 축이 만나는 칸에 판매량을 적어 간략한 표 형태로 가공하면 눈에 쏙 들어온다. 이런 식으로 데이터의 형태를 변형하는 명령이 PIVOT이다. 전체 형식은 굉장히 복잡하지만 간단하게 요약하면 다음과 같다.

SELECT 필드목록 FROM 테이블

PIVOT(집계함수 FOR 대상필드 IN (필드값 목록)) 별명

대상 필드의 값 목록을 새로운 열로 만들고 각 열에 대해 집계 함수를 호출하여 결과셋을 만든다. 실제 동작을 보기 전에는 이해하기 쉽지 않은데 다음 쿼리를 실행해 보자.

SELECT * FROM tSeason

PIVOT (MAX(sale) FOR season IN ('봄', '여름', '가을', '겨울'));

tSeason 테이블의 season 열에 있는 봄, 여름, 가을, 겨울 계절명을 각각의 열로 만들어 가로로 나열하고 상품별 판매량을 MAX 집계 함수로 구해 그 열의 값으로 출력한다. 계절이 새로운 필드가 되는데 한글을 필드명으로 쓸 수 없어 필드값을 따옴표로 감싸야 한다.

계절별 판매량을 한눈에 알아볼 수 있고 상품 간의 비교도 쉽다. 세로로 나열된 4계절을 가로로 돌려 2행으로 압축했지만 값은 여전히 2행 4열로 8개이다. 위치만 바뀌었을 뿐 내용은 변함없으며 똑같은 데이터를 보기 좋게 가공한다. 구문이 조금 복잡한데 그림으로 풀어 보면 다음과 같다.

원본 테이블의 season 열에 봄, 여름, 가을, 겨울 등의 값이 들어 있다. 이 각각의 값을 새로운 열로 정의하고 각 열에는 sale열의 최대값을 구해 표시한다. season 열의 세로에 있던 계절명이 가로 행으로 회전하여 각각의 열이 된다.

IN 절에 모든 필드값을 다 나열해야 하는 것은 아니다. 회전시킬 값만 나열하면 나머지는 피봇 대상에서 제외하며 순서도 마음대로 변경할 수 있다. 계절별 판매량에서 겨울을 빼 보고 순서도 바꿔 보자.

SELECT * FROM tSeason

PIVOT (MAX(sale) FOR season IN ('여름', '봄', '가을'));

여름, 봄, 가을에 대한 판매량만 나타나며 겨울은 통계에서 제외한다. 대상 필드와 값 목록을 변경하면 다른 방식으로 회전할 수도 있다. 계절명으로 피봇하는 대신 상품명으로 피봇해 보자. FOR의 컬럼명을 item으로 변경하고 IN의 값 종류를 냉면과 짬뽕으로 바꾼다.

SELECT * FROM tSeason

PIVOT (MAX(sale) FOR item IN ('냉면', '짬뽕'))

item열에 있던 냉면과 짬뽕이 가로열로 이동하여 상품별 판매량을 출력한다. 뭘 회전시키는가에 따라 도표의 모양이 달라진다. 2개의 상품을 가로로 돌리고 계절별로 행을 구성하면 4행 2열이 되며 값은 여전히 8개이다. 보여주는 방식만 다를 뿐 결국은 똑같은 정보를 표현한다.

대충 어떤 식으로 동작한다는 것은 알 수 있지만 역시 쉽지 않다. 피봇 기능이 도입되기 전에는 UNION, GROUP BY, CASE 등의 구문을 총동원해서 순서대로 조합해야 구현할 수 있었다. 이런 복잡한 동작을 단 한 줄로 압축해서 표현하니 어려울 수밖에 없다.

호환성 체크

PIVOT은 SQL 표준이어서 양쪽 다 잘 지원하지만 몇 가지 차이점이 있다. MariaDB는 아직 피봇을 지원하지 않는다. 똑같은 명령을 SQL Server에서 작성하면 다음과 같다.

SELECT * FROM tSeason

PIVOT (MAX(sale) FOR season IN (봄, 여름, 가을, 겨울)) pvt;

오라클에 비해 두 가지 차이점이 있다. ① 피봇 결과 테이블에 pvt 별명을 붙였다. 테이블 별명은 이후의 참조를 위해 필요하다. 오라클은 별명이 옵션이지만 SQL Server는 당장 쓰지 않더라도 별명이 필수이다. 항상 별명을 붙이면 호환성을 확보할 수 있다.

② IN절의 값 목록을 따옴표로 감싸지 않으며 한글도 그냥 쓰면 된다. 어차피 한글도 필드명으로 적합한 문자여서 굳이 문자열로 만들지 않아도 된다. 오라클은 값 목록을 따옴표로 감싸기 때문에 결과셋의 필드명에도 따옴표가 나타난다. 정 따옴표를 없애려면 값 목록에 별명을 주면 된다.

SELECT * FROM tSeason

PIVOT (MAX(sale) FOR season IN ('봄' AS 봄, '여름' AS 여름, '가을' AS 가을, '겨울' AS 겨울)) pvt;

이후의 실습은 오라클을 기준으로 하되 별명 pvt는 이후의 참조를 위해 항상 붙이기로 한다. 값 목록은 따옴표로 감싸되 SQL Server에서 실행할 때는 따옴표만 제거하면 된다. 그 외의 다른 문법은 모두 같아 이후의 예제는 SQL Server에서도 문제없이 동작한다.

2.피봇 집계

PIVOT의 FOR 절은 대상 컬럼, IN 절은 그 컬럼에서 열로 만들 값의 목록이다. 그렇다면 집계 함수의 역할은 뭘까? 이 구문은 회전한 후 각 칸에 쓸 값을 지정하는데 위 예에서는 판매량을 표시하는 것이 적합하다. 그러나 그냥 sale이라고 적으면 에러이다.

SELECT * FROM tSeason

PIVOT (sale FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

원본 테이블에 모든 sale 값이 있다고 보장할 수 없고 또 반드시 하나만 있는 것도 아니다. tSeason 테이블은 상품별, 계절별로 판매량이 딱 하나씩 들어 있지만 통계란 누락될 수도 있고 같은 값을 여러 번 적을 수도 있다. 오히려 그런 경우가 더 일반적이다.

그래서 중복 판매량을 집계할 방법을 지정해야 한다. 위 예에서는 MAX를 사용했는데 AVG나 SUM, MIN을 사용해도 결과는 같다. 하나의 단일값에 대해 합계나 최대나 최소나 평균이나 뭘 구해도 똑같은 값이 나올 수밖에 없다.

SELECT * FROM tSeason

PIVOT (SUM(sale) FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

그러나 중복이나 누락이 있을 경우에는 집계 함수에 따라 결과가 달라진다. 이런 경우를 테스트해 보기 위해 데이터를 변경한 테이블을 새로 만들어 보자. 똑같은 구조로 tSeason2 테이블을 만들고 다음 데이터를 채워 넣는다.

INSERT INTO tSeason2 VALUES ('냉면', '봄', 20);

INSERT INTO tSeason2 VALUES ('냉면', '여름', 50);

INSERT INTO tSeason2 VALUES ('냉면', '가을', 30);

INSERT INTO tSeason2 VALUES ('짬뽕', '봄', 30);

INSERT INTO tSeason2 VALUES ('짬뽕', '가을', 20);

INSERT INTO tSeason2 VALUES ('짬뽕', '겨울', 40);

INSERT INTO tSeason2 VALUES ('짬뽕', '겨울', 30);

엄동설한에 냉면을 즐기는 사람은 드물고 여름에 뜨거운 짬뽕은 인기가 없다. 게다가 짬뽕의 겨울 판매량이 두 번 기록되어 있다. 이런 통계가 더 현실적이다. 계절별로 피봇하되 집계 함수를 MAX와 SUM으로 지정해 보자.

SELECT * FROM tSeason2 PIVOT (MAX(sale) FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

SELECT * FROM tSeason2 PIVOT (SUM(sale) FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

판매량이 하나 또는 없는 상품은 어떤 집계 함수를 쓰나 차이가 없지만 겨울 판매량이 40, 30으로 두 번 기록되어 있는 짬뽕의 경우는 다르다. MAX는 최대값인 40을 출력하고 SUM은 두 값을 더한 70을 출력한다. MIN 집계 함수를 쓰면 30으로 나오며 AVG 집계 함수를 쓰면 35가 된다. COUNT를 쓰면 판매량이 아닌 통계의 개수를 출력한다.

SELECT * FROM tSeason2 PIVOT (COUNT(sale) FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

정보가 없으면 0으로 표시하고 짬뽕의 겨울 판매량 정보는 2개 있다고 표시한다. tSeason, tSeason2 테이블을 통해 실습을 반복하여 집계 함수, FOR, IN 각 절의 역할과 의미에 대해 정확하게 정리하고 넘어가자. 기본 구문을 확실히 이해해야 이후의 문법이 헷갈리지 않는다.

3.통계 필드 선택

앞에서 사용한 예제 테이블은 피봇의 기본 동작을 직관적으로 설명하기 위해 최소한의 필드만 포함했다. 현실적인 통계 테이블은 필드 수가 더 많고 여러 가지 값이 마구 섞여 있어 거대하다. 이럴 경우 꼭 필요한 필드만 골라 통계를 작성하는 방법을 연구해 보자. 다음 샘플 테이블을 만든다.

CREATE TABLE tTraffic

(

line VARCHAR(10),

hour INT,

car VARCHAR(20),

traffic INT

);

INSERT INTO tTraffic VALUES ('경부', 1, '승용차', 40);

INSERT INTO tTraffic VALUES ('경부', 2, '승용차', 41);

INSERT INTO tTraffic VALUES ('경부', 3, '승용차', 42);

INSERT INTO tTraffic VALUES ('경부', 1, '트럭', 30);

INSERT INTO tTraffic VALUES ('경부', 3, '트럭', 32);

INSERT INTO tTraffic VALUES ('호남', 1, '승용차', 20);

INSERT INTO tTraffic VALUES ('호남', 2, '승용차', 10);

INSERT INTO tTraffic VALUES ('호남', 2, '승용차', 11);

INSERT INTO tTraffic VALUES ('호남', 3, '승용차', 22);

INSERT INTO tTraffic VALUES ('호남', 1, '트럭', 10);

INSERT INTO tTraffic VALUES ('호남', 2, '트럭', 11);

INSERT INTO tTraffic VALUES ('호남', 3, '트럭', 12);

이 테이블은 도로별, 시간별, 차종별 통행량을 저장한다. 여러 기준으로 통계를 내 보기 위해 경부선은 2시의 트럭 통행량이 없고 호남선은 2시의 승용차 통행량을 중복해 두었다. car 필드에 있는 승용차, 트럭값을 회전하고 통행량의 총합을 구해 차종별로 피봇해 보자.

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR car IN ('승용차', '트럭')) pvt;

PIVOT 연산자는 대상 테이블의 모든 컬럼중 피봇 대상 컬럼만 빼고 GROUP BY 연산을 수행한다. tTraffic의 4개 필드 중 피봇 대상인 car와 집계 대상인 traffic을 제외한 line, hour에 대해 그룹핑 처리한다. 그래서 집계 함수가 필수적이다.

원본에 중복값이 존재할 수 있어 어떤 방법으로든 합쳐야 하며 그 방법을 집계 함수가 결정한다. 위 피봇은 SUM 집계 함수를 사용했으며 그래서 호남선 2시 승용차 통행량을 합쳐 21로 표시한다. 최대 통행량을 뽑으려면 MAX를 쓰고 평균 통행량을 구하려면 AVG를 쓰면 된다.

다음은 도로명이나 시간을 기준으로 피봇해 보자. FOR에 원하는 기준 필드를 적고 IN에 대상 필드값을 나열한다. 도로별 통계를 뽑으려면 FOR 절에 line을 적고 IN 절에 line의 값인 경부, 호남을 적는다. 시간별 통계를 뽑을 때는 FOR 절에 hour를 적고 IN 절에 1, 2, 3 시간을 적는다.

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR line IN ('경부', '호남')) pvt;

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN ('1', '2', '3')) pvt;

1, 2, 3은 숫자여서 따옴표 없이 그냥 사용해도 상관없다. SQL Server는 숫자를 명칭으로 인정하지 않아 [1], [2], [3] 식으로 괄호를 감싸야 한다. 가로 피봇열이 늘어나면 세로 행 수는 줄어든다. 12개의 값을 도로명 2종류로 피봇하면 6행, 시간별 3종류로 피봇하면 4행이다.

이 예에서 보다시피 통계 기준이 여러 개인 테이블은 원하는 기준으로 통계를 자유롭게 만들 수 있다. 차종으로 피봇하면 도로별, 시간별 통계를 뽑고 도로를 기준으로 피봇하면 시간별, 차종별 통계를 산출하고 시간으로 피봇하면 도로별, 차종별 통계를 만든다.

피봇 대상만 빼고 남은 필드를 모두 그룹핑한다. 그렇다면 일부를 그룹핑에서 제외하려면 어떻게 할까? 예를 들어 차종으로 피봇하되 시간은 무시하고 도로별 통행량만 구하고 싶다고 하자. 피봇 대상 테이블의 필드 목록에 * 대신 시간 필드를 생략하고 나머지 필드만 적으면 될 거 같다.

SELECT line, car, traffic FROM tTraffic

PIVOT (SUM(traffic) FOR car IN ('승용차', '트럭')) pvt;

차종별로 피봇하여 통행량을 집계하되 line으로만 그룹핑하라는 뜻이다. car와 traffic은 피봇 대상이며 남은 필드는 line밖에 없으니 도로별로만 그룹핑하라는 명령이다. 경부, 호남선에 승용차와 트럭이 각각 몇 대나 통행했는지만 알고 싶은 것이다.

얼핏 보기에는 될 거 같고 누구나 이런 코드를 떠올리지만 명백한 에러이다. 그 이유는 SELECT보다 PIVOT이 먼저 처리되기 때문이다. SELECT는 명령이고 PIVOT은 연산자여서 PIVOT의 우선 순위가 더 높다. tTraffic에 대해 피봇을 먼저 처리하고 SELECT는 그 결과를 출력한다.

피봇에 의해 회전 대상인 car와 traffic 필드는 사라지고 대신 회전 결과로 승용차, 트럭 필드를 새로 만든다. car와 traffic은 피봇의 재료일 뿐 피봇 결과로 남아 있지 않아 SELECT 문 실행시에는 존재하지 않는다. SELECT 절의 필드 목록은 피봇에는 아무런 영향을 미치지 못하며 피봇 후의 출력 대상일 뿐이다.

이 문장에서 피봇 대상은 tTraffic 전체이다. 시간으로 그룹핑하지 않으려면 피봇하기 전에 hour를 제외해야 하며 그러려면 tTraffic 전체 테이블 대신 쓸 서브쿼리가 필요하다. 피봇 대상 테이블을 인라인뷰로 정의한 후 피봇하고 그 결과를 출력해야 한다. 그래서 피봇 문장은 통상 다음과 같은 모양으로 작성한다.

SELECT 출력대상 FROM

(

SELECT 피봇대상 FROM tTraffic

) prepvt

PIVOT (SUM(집계할필드) FOR 피봇필드 IN (열로 만들 값)) pvt;

서브쿼리에서 원하는 필드만 선정하여 prepvt 인라인뷰를 정의하고 이를 피봇한 후 외부쿼리에서 결과를 출력한다. tTraffic이 있던 자리에 prepvt이 들어갔을 뿐이다. prepvt이 SELECT * FROM tTraffic이라면 원본과 같아 피봇 결과도 같다.

그러나 인라인뷰로 표현하면 여러 가지 변형을 줄 여지가 생긴다. 인라인뷰의 SELECT *은 결국 모든 필드를 다 나열한 것과 같으니 SELECT line, hour, car, traffic이라고 쓴 것과 같다. 이 목록에서 원하는 필드를 생략하면 된다. 인라인뷰의 필드 목록에서 hour를 생략해 보자.

SELECT * FROM

(

SELECT line, car, traffic FROM tTraffic

) prepvt

PIVOT (SUM(traffic) FOR car IN ('승용차', '트럭')) pvt;

세 필드만 적으면 car와 traffic은 피봇에 의해 사라지고 line만 그룹핑 대상이다. 피봇이 line에 대해 그룹핑하여 집계 처리하고 외부쿼리는 그 결과를 모두 출력한다. 위 명령의 실행 순서는 다음과 같다. 각 부분이 단계별로 실행되며 정해진 역할이 있다.

명령이 길고 복잡하지만 결국은 테이블을 가공하여 출력하는 SELECT 문장일 뿐이다. 이 뒤에 WHERE 절이나 ORDER BY 절이 더 올 수 있으며 2번과 3번 사이에 끼어든다. 피봇한 결과도 하나의 테이블이므로 다른 테이블을 조인할 수도 있다.

왜 피봇 대상 테이블을 인라인뷰로 정의하는지 이해해야 자유자재로 피봇을 구사할 수 있다. 피봇 대상과 그룹핑 대상을 정확히 지정하기 위해서이다. 이왕 연구한 김에 더 깊이 들어가 보자. 인라인뷰로 피봇 대상을 선택하는 대신 전체 테이블을 피봇한 후 GROUP BY를 따로 수행할 수도 있다. 다음 코드도 똑같은 결과를 출력한다.

SELECT line, SUM(승용차), SUM(트럭) FROM tTraffic

PIVOT (SUM(traffic) FOR car IN ('승용차' AS 승용차, '트럭' AS 트럭)) pvt

GROUP BY line;

tTraffic 테이블 전체를 차종별로 피봇한 후 그 결과에 대해 line으로 그룹핑하면 도로별 통행량이 나온다. 서브쿼리가 없어 구문이 짧고 직관적이다. 하지만 GROUP BY를 직접 사용했으므로 SELECT 절에서 그룹핑 기준 필드 외에는 집계 함수만 써야 하는 불편함이 있다.

두 구문은 피봇할 때 딱 원하는 기준으로 그룹핑하면서 집계까지 할 것인지, 아니면 일단 피봇해 놓고 그룹핑하면서 집계를 직접 할 것인지가 다르다. 피봇 대상만 잘 지정하면 PIVOT 연산자가 그룹핑, 집계를 알아서 다 하도록 되어 있어 편리하고 한 번에 처리하니 속도도 빠르다.

여기까지 이해했으면 응용해 보자. 똑같은 방식으로 서브쿼리에서 line을 생략하면 시간별로만 그룹핑되어 시간별 통행량을 합산하여 출력한다. 아래 쿼리를 보기 전에 직접 실습해 보자. 전체 피봇 후 GROUP BY hour를 적용한 것과 같다.

SELECT * FROM

(

SELECT hour, car, traffic FROM tTraffic

) prepvt

PIVOT (SUM(traffic) FOR car IN ('승용차', '트럭')) pvt;

그렇다면 line, hour 둘 다 생략하면 어떻게 될까? 피봇의 재료 필드만 있고 그 외의 필드가 없으니 결과는 피봇 필드만 남는다. 그룹핑할 대상이 없으니 시간과 도로에 따른 통행량이 모두 합산되어 차종에 대한 전체 통행량만 나타난다.

SELECT * FROM

(

SELECT car, traffic FROM tTraffic

) prepvt

PIVOT (SUM(traffic) FOR car IN ('승용차', '트럭')) pvt;

피봇 명령은 통상 두 개의 SELECT를 중첩한다. 안쪽의 인라인뷰는 피봇 대상 테이블의 모양을 결정하고 외부쿼리는 피봇 결과셋 중 어떤 필드를 어떻게 출력할 것인가를 지정한다. 통상 외부쿼리에는 *를 쓰며 피봇 결과셋을 다 출력한다.

외부쿼리에서 일부 필드를 생략하면 해당 필드는 출력 대상에서 제외한다. 인라인뷰에 hour가 있으면 시간별로 그룹핑하지만 외부쿼리에 hour 필드를 빼면 시간값이 나타나지 않는다. 문법적으로는 합당하지만 이렇게 되면 각 행의 의미가 모호해져 통계로서의 의미가 없고 무슨 정보인지 알아보기도 어렵다.

SELECT line, 트럭, 승용차 FROM

(

SELECT line, hour, car, traffic FROM tTraffic

) prepvt

PIVOT (SUM(traffic) FOR car IN ('승용차' AS 승용차, '트럭' AS 트럭)) pvt;

인라인뷰에 포함하여 그룹핑한 필드는 최종 출력 대상에도 포함하는 것이 자연스럽다. 외부쿼리에서 필드의 순서를 조정하거나 포맷을 바꿀 수도 있다.

SELECT line || '선 ' || CAST(hour AS VARCHAR(10)) || '시' AS 구분, 트럭, 승용차 FROM

(

SELECT line, hour, car, traffic FROM tTraffic

) prepvt

PIVOT (SUM(traffic) FOR car IN ('승용차' AS 승용차, '트럭' AS 트럭)) pvt

ORDER BY line;

line과 hour를 조합하여 하나의 필드로 합치고 트럭과 승용차의 순서를 조정했다. 피봇 결과도 하나의 테이블이므로 SELECT 절에서 필드에 대한 모든 조작을 자유롭게 할 수 있다. 도로순으로 정렬하기 위해 ORDER BY line을 붙였다.

피봇을 하는 대상도 테이블이고 피봇을 한 결과도 테이블이다. 당장 필요치 않더라도 피봇 전후에 여러 가지 변형을 가하기 위해 인라인뷰를 구성하는 것이 좋다. 중간에 조인이 끼어들 수도 있고 피봇 결과를 UNION으로 합칠 수도 있다. 기본기만 충실하면 여러 가지 기법을 얼마든지 조합할 수 있다.

연습 문제

12.tSeason2를 피봇하여 계절별 판매량을 출력하면 여름, 겨울 등 판매량이 없는 칸은 NULL로 표시되는데 0을 대신 출력해 보자.

13.tMonthSale 테이블에서 년도별 총 판매량을 집계하되 년도를 열로 회전하여 표시하라.

4.피봇의 활용

공장 자동화나 계좌 거래 시스템은 데이터의 생성 빈도가 잦다. 생산 장비에는 갖가지 센서가 붙어 있고 종류도 천차 만별이라 열을 미리 디자인하기 어렵다. 이럴 때는 센서 이름과 함께 값을 레코드로 묶어 삽입한다. 실제 시스템은 복잡하므로 익숙한 테이블로 비슷한 상황을 만들어 보자.

CREATE TABLE tCityStat

(

name CHAR(10),

attr CHAR(10),

value INT

);

INSERT INTO tCityStat VALUES ('서울', 'area', 605);

INSERT INTO tCityStat VALUES ('서울', 'popu', 974);

INSERT INTO tCityStat VALUES ('서울', 'gu', 25);

INSERT INTO tCityStat VALUES ('인제', 'area', 1646);

INSERT INTO tCityStat VALUES ('인제', 'popu', 3);

INSERT INTO tCityStat VALUES ('인제', 'home', 15409);

INSERT INTO tCityStat VALUES ('홍천', 'area', 1819);

도시의 속성값이 센서로부터 들어오는 측정값이라고 가정하자. 속성의 종류는 도시마다 제각각이다. 서울은 자치구의 개수 속성이 있고 인제는 가구수 속성이 있다. 입력되는 값을 일단 쌓아 놓기만 하면 어떻게든 활용할 수 있다.

이 테이블에서 각 도시의 인구밀도를 계산해 보자. tCity 테이블이라면 인구를 면적으로 나누면 되지만 이런 구조에서는 두 값이 다른 행에 있어 계산식을 쓸 수 없다. 계산식은 같은 레코드 소속의 필드끼리나 상수를 연산하는 것이다. 떨어져 있는 행을 계산하려면 tCity 비슷한 구조로 만들어야 하며 이때 필요한 기술이 피봇이다.

SELECT * FROM tCityStat

PIVOT (MAX(value) FOR attr IN ('area' AS area, 'popu' AS popu)) pvt;

attr 컬럼의 area, popu값에 대해 피봇하면 면적, 인구를 열로 변환하고 도시명으로 그룹핑한다. MAX 함수로 집계하여 혹시 같은 도시에 대해 면적이나 인구 정보가 두 개 이상이면 그 중 최대값을 취한다. 홍천은 면적 정보만 있고 인구 정보가 없어 NULL이다.

피봇 대상이 아닌 지역구 개수, 가구수 속성은 집계에서 제외한다. 아무리 속성이 많아도 IN 목록에서 밝힌 값만 열로 전환한다. 피봇 결과는 하나의 테이블이며 관심 있는 두 속성을 한 레코드로 모았다. 이제 이 두 속성을 연산하여 필요한 값을 만들면 된다.

SELECT name, ROUND(popu * 10000 / area, 2) AS 인구밀도 FROM

(

SELECT * FROM tCityStat

PIVOT (MAX(value) FOR attr IN ('area' AS area, 'popu' AS popu)) pvt

) A;

피봇문을 서브쿼리로 감싸고 결과 테이블에서 area와 popu를 연산하여 인구밀도를 계산한다. 인구가 만명 단위여서 10000을 곱한 후 면적으로 나누고 소수점 이하 2자리까지만 취했다. 홍천은 인구 정보가 없어 인구밀도를 구할 수 없다. 필요한 값이 없는 도시를 제외하려면 PIVOT문 아래에 WHERE 조건을 지정한다.

WHERE popu IS NOT NULL AND area IS NOT NULL

인구와 면적중 하나라도 NULL이면 인구밀도를 구할 수 없다. 세로로 나열되어 있는 분리된 값을 연산하려면 한 행으로 모으기 위해 피봇이 꼭 필요하다. 피봇을 활용하는 전형적인 예이다.

5.UNPIVOT

UNPIVOT은 피봇의 반대 동작을 수행한다. 피봇이 값을 열로 바꾸는데 비해 언피봇은 열을 값으로 변환하여 레코드에 기록한다. 기본 형식은 다음과 같다.

UNPIVOT (값컬럼 FOR 대상컬럼 IN (언피봇 대상 컬럼 목록))

UNPIVOT을 실습해 보려면 피봇해 놓은 테이블이 있어야 한다. 다음 명령으로 앞에서 실습했던 피봇의 결과셋을 tSeasonPivot 테이블로 저장한다.

CREATE TABLE tSeasonPivot AS

SELECT * FROM tSeason

PIVOT (SUM(sale) FOR season IN ('봄' AS 봄, '여름' AS 여름, '가을' AS 가을, '겨울' AS 겨울));

필드명에 따옴표를 없애기 위해 일일이 별명을 주었다. SQL Server에서는 다음 명령으로 피봇 결과셋을 저장한다. 피봇문도 약간 차이가 있지만 테이블의 사본을 만드는 명령도 완전히 다르다.

SELECT * INTO tSeasonPivot FROM tSeason

PIVOT (SUM(sale) FOR season IN (봄, 여름, 가을, 겨울)) pvt;

피봇한 테이블을 다시 원래대로 돌릴 때 UNPIVOT 명령을 사용한다.

SELECT * FROM tSeasonPivot

UNPIVOT (sale FOR season IN (봄, 여름, 가을, 겨울)) unpvt;

봄, 여름, 가을, 겨울 필드를 season 필드의 값으로 만들고 계절별 판매량인 값은 sale 필드로 만든다. 원본과 피봇, 언피봇에 의해 테이블이 다음과 같이 바뀐다.

실습을 위해 임시 테이블까지 만드는 것은 너무 번거롭다. 피봇 결과를 인라인뷰로 만든 후 다시 피봇해도 결과는 같다. SQL Server는 피봇시 값 목록의 별명만 생략하면 된다.

SELECT * FROM

(

SELECT * FROM tSeason

PIVOT (SUM(sale) FOR season IN

('봄' AS 봄, '여름' AS 여름, '가을' AS 가을, '겨울' AS 겨울)) pvt

) A

UNPIVOT (sale FOR season IN (봄, 여름, 가을, 겨울)) unpvt;

피봇했다가 언피봇하면 레코드의 순서가 조금씩 바뀔 수는 있지만 결국 내용은 같다. UNPIVOT문에서 sale이나 season 컬럼명은 어디까지나 임의값이며 원본과 달라도 상관없다. 피봇 과정에서 sale, season 필드명은 사라져 버려 언피봇할 때 다시 컬럼명을 원하는대로 지정하면 된다.

중복이 없었던 tSeason 테이블은 언피봇하면 원래 값을 다 찾을 수 있지만 집계 함수를 적용하여 피봇한 결과셋은 원래 값을 찾을 수 없다. tSeason2에 대해 피봇했다가 언피봇을 수행해 보자. 위 쿼리문의 tSeason의 이름만 tSeason2로 변경하면 된다.

원래 테이블의 짬뽕 겨울 판매량 2개를 합쳐 70으로 피봇했다. 이렇게 합쳐버린 값은 원래의 판매량을 알 수 없어 언피봇하면 합쳐진 70으로만 나온다. 30 + 40을 70으로 집계해 버리면 원래 30 + 40이었는지 40 + 30이었는지 또는 10 + 20 + 15 + 25였는지 알 방법이 없다.

그래서 UNPIVOT은 PIVOT의 완전한 반대 연산자는 아니다. 합쳤다가 다시 되돌린다고 해서 원래값을 구하지 못하는 것은 수학적으로 지극히 당연한 현상이다. 동작만 반대일 뿐 원래의 결과셋은 정확히 알 수 없다.