16. MySQL SQL 고급 - 피벗, JSON

피벗의 구현
피벗(Pivot)은 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환하여 테이블 반환 식을 회전하고 필요하면 집계까지 수행하는 것을 말한다.

왼쪽은 판매자 이름, 판매 계절, 판매 수량으로 구성된 테이블이다. 이를 각 판매자가 계절별로 몇 개 구매했는지 표로 나타내고 싶을 때 SUM()과 IF() 함수를 활용해서 피벗 테이블을 만들 수 있다.

<실습>

USE sqldb;
CREATE TABLE pivotTest
	( uName CHAR(3),
      season CHAR(2),
      amount INT );
      
INSERT INTO pivotTest VALUES
	('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25),
    ('김범수', '봄', 3), ('김범수', '봄', 37), ('윤종신', '겨울', 40),
    ('김범수', '여름', 14), ('김범수', '겨울', 22), ('윤종신', '여름', 64)
SELECT * FROM pivotTest;

SELECT uName,
	SUM(IF(season='봄', amount, 0)) AS '봄',
    SUM(IF(season='여름', amount, 0)) AS '여름',
    SUM(IF(season='가을', amount, 0)) AS '가을',
    SUM(IF(season='겨울', amount, 0)) AS '겨울',
    SUM(amount) AS '합계' FROM pivotTest GROUP BY uName;

JSON 데이터
JSON(JavaScript Object Notation)은 현대의 웹과 모바일 응용 프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷을 말하는데, 속성(Key)과 값(Value)으로 쌍을 이루며 구성되어 있다. JSON은 비록 JavaScript언어에서 파생되었지만 특정한 프로그래밍 언어에 종속되어 있지 않은 독립적인 데이터 포맷이라고 생각하면 된다. 즉, 그 포맷이 단순하고 공개되어 있기에 거의 대부분의 프로그래밍 언어에서 쉽게 읽거나 쓸 수 있도록 코딩할 수 있다.

JSON의 가장 단순한 형태의 예를 들면 다음과 같다. 다음은 한 명의 사용자를 JSON 형태로 표현한 것이다. 속성(Key)과 값(Value)으로 쌍을 이루는 것을 확인할 수 있다.

{
	"아이디" : "BBK" ,
    "이름" : "바비킴" ,
    "생년" : 1973 , 
    "지역" : "서울" ,
    "국번" : "010" ,
    "전화번호" : "00000000" ,
    "키" : 178 , 
    "가입일" : "2013.5.5"
}

MySQL은 JSON 관련된 다양한 내장 함수를 제공해서 다양한 조작이 가능하다. 우선 테이블의 데이터를 JSON 데이터로 표현하면 다음과 같다.

우선 왼쪽의 테이블은 userTbl에서 키가 180이상인 사람의 이름과 키를 나타내며 이것을 JSON으로 변환하려면 JSON_OBJECT() 나 JSON_ARRAY() 함수를 이용하면 된다.

USE sqldb;
SELECT JSON_OBJECT('name', name, 'height', height) AS 'JSON 값'
	FROM usertbl
   WHERE height >= 180; 

결과 값은 JSON 형태로 구성되었다. 이렇게 구성된 JSON을 MySQL에서 제공하는 다양한 내장함수를 사용해서 운영할 수 있다. JSON 관련 함수의 사용법을 확인해 보자.

SET @json='{ "usertbl" :
  [
  	{"name":"임재범","height":182},
    {"name":"이승기","height":182},
    {"name":"성시경","height":186}
  ]
}';
SELECT JSON_VALID(@json) AS JSON_VALID; -- 문자열이 JSON형식을 만족하면1, 그렇지 않으면 0 반환
SELECT JSON_SEARCH(@json, 'one', '성시경') AS JSON_SEARCH; -- one은 첫번째 나오는거 하나만 찾는거고 all은 모두 찾는 것
SELECT JSON_EXTRACT(@json, '$.usertbl[2].name') AS JSON_EXTRACT; -- 위치에 있는 데이터 값을 뽑아주는 것
SELECT JSON_INSERT(@json, '$.usertbl[0].mDate', '2009-09-09') AS JSON_INSERT; -- 삽입
SELECT JSON_REPLACE(@json, '$.usertbl[0].name', '홍길동') AS JSON_REPLACE; -- 바뀜
SELECT JSON_REMOVE(@json, '$.usertbl[0]') AS JSON_REMOVE; -- 행 삭제