Database/MySQL,MariaDB

[MySQL,MariaDB]피벗(Pivot) 사용하기

  • -
반응형

관계형 테이블은 행과 열로 이루어져 있습니다.

이번 글은 테이블의 행과 열의 위치를 바꾸는 피벗에 대한 포스팅입니다.

 

예제는 MariaDB(MySQL 가능)로 실습하였습니다.

오라클 11g부터는 피벗 기능을 자체적으로 제공하는데 거기에 대해서는 이후에 따로 포스팅할 예정입니다.

여기 피벗을 이용하려는 테이블 두 개가 있습니다.

 

PM_CALENDAR

PM_CALENDAR 테이블에는 2021년도까지의 날짜 데이터가 입력되어 있습니다.

  • DATE : DATE 타입의 날짜 데이터
  • DATECHAR : VARCHAR 타입의 날짜 데이터

PM_CALENDAR 데이터가 이런식으로..

 

PM_COMMUTE

PM_COMMUTE 테이블은 사람들의 출, 퇴근 기록이 담겨있습니다.

여기서 사용할 필드는 USERCD, WORKDAY, TODATE, FROMDATE 입니다.

  • USERCD : 사람들의 고유번호
  • WORKDAY : 근태날짜
  • TODATE : 출근시간
  • FROMDATE : 퇴근시간

테스트 데이터

 

이 두 테이블을 조인해서 데이터를 보고자 합니다.

출, 퇴근 기록이 없더라도 날짜는 찍혀야하기 때문에 Outer Join을 사용합니다.

 SELECT DISTINCT 	 
       B.USERCD
	  , B.WORKDAY
	  , A.DATECHAR
	  , B.TODATE
	  , B.FROMDATE
 FROM PM_CALENDAR A
 	   LEFT OUTER JOIN PM_COMMUTE B
 	   ON A.DATECHAR = B.WORKDAY
      AND B.CMPCD = 'P0001'
WHERE A.DATECHAR BETWEEN '20200420' AND '20200430'
ORDER BY A.DATECHAR

PM_CALENDAR에 Left Outer Join을 걸었고, 날짜는 테스트 데이터가 있는 20200420 부터 20200430 까지만 조회하였습니다. 결과는 정상적으로 나왔습니다.

 

 

하지만 데이터를 보게 되면, 동일한 날짜로 N명의 사람들의 기록이 보이기 때문에 뭔가 조금 아쉽죠. 피벗을 사용한다면 데이터를 아래 그림과 같이 볼 수 있게 됩니다.

 

보다 직관적인 형태

피벗을 위해 IF 함수와 집계 함수를 이용하여 쿼리를 작성하였습니다.

 

1) 피벗 1 - PM_CALENDAR

먼저, 날짜 데이터가 있는 PM_CALENDAR 데이터를 피벗시켰습니다.

SELECT 
      '' AS USERCD			
      , MAX(IF(B.ROWNUM = '20', B.WORKDAY, 0)) AS WORKDAY20
      , MAX(IF(B.ROWNUM = '21', B.WORKDAY, 0)) AS WORKDAY21
      , MAX(IF(B.ROWNUM = '22', B.WORKDAY, 0)) AS WORKDAY22
      , MAX(IF(B.ROWNUM = '23', B.WORKDAY, 0)) AS WORKDAY23
      , MAX(IF(B.ROWNUM = '24', B.WORKDAY, 0)) AS WORKDAY24
      , MAX(IF(B.ROWNUM = '25', B.WORKDAY, 0)) AS WORKDAY25
      , MAX(IF(B.ROWNUM = '26', B.WORKDAY, 0)) AS WORKDAY26
      , MAX(IF(B.ROWNUM = '27', B.WORKDAY, 0)) AS WORKDAY27
      , MAX(IF(B.ROWNUM = '28', B.WORKDAY, 0)) AS WORKDAY28				
      , MAX(IF(B.ROWNUM = '29', B.WORKDAY, 0)) AS WORKDAY29			
      , MAX(IF(B.ROWNUM = '30', B.WORKDAY, 0)) AS WORKDAY30		
      , MAX(IF(B.ROWNUM = '31', B.WORKDAY, 0)) AS WORKDAY31		
FROM (
		SELECT 
			CONCAT(SUBSTRING(A.DATECHAR,5,2),'.',SUBSTRING(A.DATECHAR,7,2)) WORKDAY
	    , SUBSTRING(A.DATECHAR,7,2) AS ROWNUM
		FROM PM_CALENDAR A
		WHERE A.DATECHAR BETWEEN '20200420' AND '20200430'
	  ) B

IF 함수와 집계 함수를 이용해 피벗을 시킬 때, 키(Key)가 되는 것은 Rownum입니다.

 

 

여기 데이터를 볼 때, (PM_CALENDAR 테이블의) DATECHAR는 날짜(ex, 20200401, 20200402, ...)이고 이 날짜는 1부터 31 사이라는 규칙을 갖게 됩니다. 그래서 DATECHAR를 자르면 Rownum의 개념으로 사용할 수 있다. IF 함수를 적용하여 Rownum을 비교하면 한다면 피벗이 가능합니다.

 

 

2) 피벗 2 - PM_COMMUTE

다음으로 사람들의 출, 퇴근 데이터가 있는 PM_COMMUTE 데이터를 피벗시켰습니다. (두 데이터를 UNION ALL로 합칠 계획입니다)

(PM_COMMUTE 테이블의) WORKDAY도 날짜(ex, 20200401, 20204002, ...) 데이터가 들어있고 위 DATECHAR와 같은 동일한 규칙을 가지기 때문에 Rownum의 개념으로 사용이 가능합니다.

SELECT 		
  	  A.USERCD			
	, MAX(IF(ROWNUM = '20', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY20
	, MAX(IF(ROWNUM = '21', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY21
	, MAX(IF(ROWNUM = '22', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY22
	, MAX(IF(ROWNUM = '23', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY23
	, MAX(IF(ROWNUM = '24', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY24
	, MAX(IF(ROWNUM = '25', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY25
	, MAX(IF(ROWNUM = '26', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY26
	, MAX(IF(ROWNUM = '27', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY27
	, MAX(IF(ROWNUM = '28', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY28
	, MAX(IF(ROWNUM = '29', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY29
	, MAX(IF(ROWNUM = '30', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY30
	, MAX(IF(ROWNUM = '31', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY31					
 FROM (
		 SELECT   	 
		       USERCD
			  , SUBSTRING(WORKDAY,7,2) AS ROWNUM
			  , IFNULL(DATE_FORMAT(TODATE, '%H:%i'),'') TODATE		
			  , IFNULL(DATE_FORMAT(FROMDATE, '%H:%i'),'') FROMDATE
		 FROM PM_COMMUTE
		WHERE WORKDAY BETWEEN '20200420' AND '20200430'
 		  AND CMPCD = 'P0001'
 	  ) A
GROUP BY A.USERCD

IF 함수와 집계 함수를 이용해 피벗을 시켰습니다.

 

 

3) UNION ALL

두 피벗된 테이블을 UNION ALL로 합쳐 원하는 결과를 만들었습니다.

SELECT 
			  '' AS USERCD			
			, MAX(IF(B.ROWNUM = '20', B.WORKDAY, 0)) AS WORKDAY20
			, MAX(IF(B.ROWNUM = '21', B.WORKDAY, 0)) AS WORKDAY21
			, MAX(IF(B.ROWNUM = '22', B.WORKDAY, 0)) AS WORKDAY22
			, MAX(IF(B.ROWNUM = '23', B.WORKDAY, 0)) AS WORKDAY23
			, MAX(IF(B.ROWNUM = '24', B.WORKDAY, 0)) AS WORKDAY24
			, MAX(IF(B.ROWNUM = '25', B.WORKDAY, 0)) AS WORKDAY25
			, MAX(IF(B.ROWNUM = '26', B.WORKDAY, 0)) AS WORKDAY26
			, MAX(IF(B.ROWNUM = '27', B.WORKDAY, 0)) AS WORKDAY27
			, MAX(IF(B.ROWNUM = '28', B.WORKDAY, 0)) AS WORKDAY28				
			, MAX(IF(B.ROWNUM = '29', B.WORKDAY, 0)) AS WORKDAY29			
			, MAX(IF(B.ROWNUM = '30', B.WORKDAY, 0)) AS WORKDAY30		
			, MAX(IF(B.ROWNUM = '31', B.WORKDAY, 0)) AS WORKDAY31		
		FROM (
				SELECT 
					CONCAT(SUBSTRING(A.DATECHAR,5,2),'.',SUBSTRING(A.DATECHAR,7,2)) WORKDAY
			    , SUBSTRING(A.DATECHAR,7,2) AS ROWNUM
				FROM PM_CALENDAR A
				WHERE A.DATECHAR BETWEEN '20200420' AND '20200430'
			  ) B
		UNION ALL
		SELECT 		
		  	  A.USERCD			
			, MAX(IF(ROWNUM = '20', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY20
			, MAX(IF(ROWNUM = '21', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY21
			, MAX(IF(ROWNUM = '22', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY22
			, MAX(IF(ROWNUM = '23', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY23
			, MAX(IF(ROWNUM = '24', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY24
			, MAX(IF(ROWNUM = '25', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY25
			, MAX(IF(ROWNUM = '26', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY26
			, MAX(IF(ROWNUM = '27', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY27
			, MAX(IF(ROWNUM = '28', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY28
			, MAX(IF(ROWNUM = '29', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY29
			, MAX(IF(ROWNUM = '30', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY30
			, MAX(IF(ROWNUM = '31', CONCAT(A.TODATE, '<br>/', A.FROMDATE), '-')) AS WORKDAY31					
		 FROM (
				 SELECT   	 
				       USERCD
					  , SUBSTRING(WORKDAY,7,2) AS ROWNUM
					  , IFNULL(DATE_FORMAT(TODATE, '%H:%i'),'') TODATE		
					  , IFNULL(DATE_FORMAT(FROMDATE, '%H:%i'),'') FROMDATE
				 FROM PM_COMMUTE
				WHERE WORKDAY BETWEEN '20200420' AND '20200430'
		 		  AND CMPCD = 'P0001'
		 	  ) A
GROUP BY A.USERCD

+ 피드백은 언제나 환영입니다 :)

반응형
저작자표시 비영리 변경금지

'Database > MySQL,MariaDB' 카테고리의 다른 글

[MySQL,MariaDB]암호화, 복호화  (5) 2021.07.30
[MySQL,MariaDB]Outer Join 조건 주기  (2) 2020.04.30
[MySQL,MariaDB]SQL 오류 (1093)  (0) 2020.04.29
[MySQL]현재 시간 입력  (0) 2018.09.12
Contents

연관 포스팅

댓글 2

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.

MENU

SHARE

티스토리툴바