파트 5-4. 피벗, JSON

in MySQL on Theory

1. 피벗

피벗은 한 열에 포함된 여러 값들을 여러 열로 변환하여 테이블을 반환 식을 회전하고 필요하면 집계까지 수행하는 것을 말한다.

그림1

use sqldb;
CREATE TABLE pivottest (
    uname CHAR(3),
    season CHAR(2),
    amount INT
);

-- 콤마로 구분해서 한 번에 insert 가능
INSERT  INTO  pivotTest VALUES
	('김범수' , '겨울',  10) , ('윤종신' , '여름',  15) , ('김범수' , '가을',  25) , ('김범수' , '봄',    3) ,
	('김범수' , '봄',    37) , ('윤종신' , '겨울',  40) , ('김범수' , '여름',  14) ,('김범수' , '겨울',  22) ,
	('윤종신' , '여름',  64) ;
SELECT * FROM pivotTest;

-- group by uname -> uname을 기준으로 묶는다.
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 ;


2. JSON 데이터

JSON은 현대의 웹과 모바일 응용 프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷을 말하는데, 속성(KEY)과 값(VALUE)으로 쌍을 이루며 구성되어 있다. 마치 파이썬의 딕셔너리와 같다.

그림2

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

그림3

MySQL에서는 테이블과 같이 사용하는게 편리하고 쉽지만 다른 곳으로, 다양한 어플리케이션에 전달하기 위해서는 JSON 데이터 형식으로 전달하는게 요즘 표준적으로 사용하는 방식이다.
uesertbl에서 키가 180 이상인 사람의 이름과 키를 나타내며 이것을 JSON으로 변환하려면 JSON_OBJECT()나 JSON_ARRAY()함수를 이용하면 된다.

USE sqldb;
-- 'name'은 키값 name은 value값이 되는 것이다.
select json_object('name',name,'height',height) as 'json 값'
from usertbl where height>=180;

그림4


JSON을 MYSQL에서 제공하는 다양한 내장함수를 이용해서 운영할 수 있다.
’{ “테이블이름” : [데이터]}’ 형식으로 가장 많이 사용되니 알아두자. 데이터는 테이블의 데이터가 된다.

-- @json 변수에 json데이터를 대입
SET @JSON ='{"usertbl" :
	[
    {"name":"임재범","height":182},
    {"name":"이승기","height":182},
    {"name":"성시경","height":186}
    ]
}';
SELECT json_valid(@json) as json_valid;
select json_search(@json,'one','성시경') as json_search;
select json_extract(@json,'$.usertbl[2].name') as json_search;
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;

그림5

위치를 지정할 때 $. 을 사용한다.

  • json_valid() : 문자열이 json 형식을 만족하면 1, 그렇지 않으면 0을 반환
  • json_search() : 세 번째 라파미터에 주어진 문자열의 위치를 반환, ‘one’은 처음으로 매치되는 하나만을 반환, ‘all’은 매치되는 모든것을 반환
  • json_extract() : earch와 반대로 지정된 위치의 값을 추출
  • json_insert() : 새로운 값을 추가, 새로운 키값과 벨류값을 추가한다고 생각하면 된다.
  • json_replace() : 값을 변경
  • json_remove() : 지정된 항목을 삭제


© 2021. By Backtony