[MySQL] 피벗 - 로우 데이터를 컬럼으로 옮기기

날마다 로그에서 데이터를 가져와서 DB에 넣어야 하는 배치성 쿼리를 짜고 있었다.

group by를 하는 쿼리가 있다보니 데이터가 row 단위로 나오는데, 문제는 일자별 조회를 하려면 row별로 있는 데이터가 컬럼으로 가야 하는 문제가 발생했다.

문제를 쉽게 하기 위해 예를 들면,

+------------+-----------+---------+

| baseDt     | page      | value   |

+------------+-----------+---------+

| 2017-04-13 |    A      |  10     |

+------------+-----------+---------+

| 2017-04-13 |    B      |  20     |

+------------+-----------+---------+

| 2017-04-13 |    C      |  30     |

+------------+-----------+---------+

같은 형태를

+------------+-----------+------------+-----------+

| baseDt     |    A      |     B      |     C     |

+------------+-----------+------------+-----------+

| 2017-04-13 |    10     |     20          30     |

+------------+-----------+------------+-----------+

로 바꾸는 것이 목표였다.

검색을 해보니 이런 유형을 '피벗'이라고 부르며 특정 벤더의 DB(예, MS-SQL)에서는 PIVOT이라는 키워드가 존재했다.

마야님의 도움을 받아 정리를 한다.

가상의 테이블을 만들기 위해 다음과 같은 쿼리를 이용한다.

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

쿼리를 실행해보면 위에서 예로 든 테이블이 조회된다.

보통 대각선으로 만든다음 합치는 과정을 하는데 UNION ALL이나 GROUP BY를 이용해서 할 수 있다.

대각선으로 만드는 쿼리는 아래와 같다.

SELECT     `date`

, CASE WHEN page = 'A' THEN `value` ELSE 0 END AS A

, CASE WHEN page = 'B' THEN `value` ELSE 0 END AS B

, CASE WHEN page = 'C' THEN `value` ELSE 0 END AS C

FROM     (

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

) z

실행을 해보면 아래와 같이 조회가 된다.

추가적으로 CASE WHEN THEN ELSE END를 IF() 함수로 대체가 가능하다.

SELECT     `date`

, IF(page = 'A', `value`, 0) AS A

, IF(page = 'B', `value`, 0) AS B

, IF(page = 'C', `value`, 0) AS C

FROM     (

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

) z

결과는 동일하다.

이제 group by를 하면 되는데 그냥 적용하면 이상하게 나온다.

SELECT     `date`

, IF(page = 'A', `value`, 0) AS A

, IF(page = 'B', `value`, 0) AS B

, IF(page = 'C', `value`, 0) AS C

FROM     (

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

) z

GROUP BY `date`

결과

제일 위의 열만 나온 것이다. group by는 집계함수(aggregate function)^[각주:1]랑 같이 써야 하는데, A, B, C열은 집계함수가 적용되지 않았다.

여기서는 가장 만만한 MAX 집계함수를 써서 제일 큰 값으로 이용한다. 혹시 값 중에 음수가0이 필요하다고 하면 무조건 0이 반환될테니 IF의 3번째 인자를 그 값보다 작게 만들면 된다.

SELECT     `date`

, MAX(IF(page = 'A', `value`, 0)) AS A

, MAX(IF(page = 'B', `value`, 0)) AS B

, MAX(IF(page = 'C', `value`, 0)) AS C

FROM     (

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

) z

GROUP BY `date`

결과: 

나중에 확인해보니 GROUP BY 는 필요없었다. (전제 조건은 데이터가 date가 하나만 있을 때이다. 나의 경우는 batch 성으로 하루치 데이터만 이미 filtering되고 있기 때문에 하나의 날짜라는 것이 보장되었기 때문이다.)

SELECT     `date`

, MAX(IF(page = 'A', `value`, 0)) AS A

, MAX(IF(page = 'B', `value`, 0)) AS B

, MAX(IF(page = 'C', `value`, 0)) AS C

FROM     (

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

) z;

추가 팁.

배치로 데이터를 계속 넣는다고 했다.

이럴 때 유용한 쿼리가 INSERT INTO 테이블 SELECT ... 이다.

INSERT INTO target (date, A, B, C) SELECT     `date`

, MAX(IF(page = 'A', `value`, 0)) AS A

, MAX(IF(page = 'B', `value`, 0)) AS B

, MAX(IF(page = 'C', `value`, 0)) AS C

FROM     (

SELECT '2017-01-01' AS `date`, 'A' AS page, 10 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'B' AS page, 20 AS `value`

UNION ALL

SELECT '2017-01-01' AS `date`, 'C' AS page, 30 AS `value`

) z

GROUP BY `date`

1. 집계 함수(aggregate function)는 하나의 칼럼의 여러 값을 읽어 하나의 값을 반환한다. [본문으로]