MySQL Pivot, JSON

Pivot

피벗은 한 열에 포함된 여러 정보를 각각의 열 정보로 변환하는 것을 의미한다.

ex) 계절 컬럼 -> 봄, 여름, 가을, 겨울 컬럼으로 변환

집계함수 sum()과 if(조건, 참, 거짓)을 함께 사용하여 피벗을 구현

계절 이름으로 pivot을 구현하는 경우

-- season 컬럼의 계절 이름 정보를 각각의 column으로 분리

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; -- 각각의 사용자 이름으로 집계

uname	봄	여름	가을	겨울	합계
박성화	0	10	0	0	10
김홍중	0	0	0	82	82
최산	66	26	0	0	92
송민기	0	0	35	0	35
정윤호	19	0	0	56	75
강여상	13	0	99	0	112
정우영	71	0	0	0	71
최종호	0	0	0	55	55

사용자 이름으로 pivot을 구현하는 경우

 -- uname 컬럼의 사용자 이름 정보를 각각의 column으로 분리

select season, sum(if(uname = '박성화', amount, 0)) as '박성화',
    sum(if(uname = '김홍중', amount, 0)) as '김홍중',
    sum(if(uname = '정윤호', amount, 0)) as '정윤호',
    sum(if(uname = '강여상', amount, 0)) as '강여상',
    sum(if(uname = '최산', amount, 0)) as '최산',
    sum(if(uname = '송민기', amount, 0)) as '송민기',
    sum(if(uname = '정우영', amount, 0)) as '정우영',
    sum(if(uname = '최종호', amount, 0)) as '최종호',
sum(amount) as '합계'
from pivottest
group by season order by season; -- 각각의 계절 이름으로 집계

|season |박성화|김홍중|정윤호|강여상|최산|송민기|정우영|최종호|합계|
|--|--|--|--|--|--|--|--|--|
|가을 |0| 0 |0 |99 |0| 35 |0| 0 |134|
|겨울 |0| 82 |56 |0 |0| 0 |0| 55| 193|
|봄 |0 |0 |19 |13 |66 |0 |71 |0| 169|
|여름| 10| 0 |0| 0 |26|0| 0| 0 |36|

JSON

JSON을 변수에 저장

-- 사용자 변수 @
SET @json = '{ "usertbl" : 
 [
     {"name":"최산", "height":177},
    {"name":"강여상", "height":175},
    {"name":"송민기", "height":184}
 ]
}'; 

변수에 할당하는 JSON 데이터는 ''로 감싸져 있어야 한다.

JSON_VALID(JSON변수)

SELECT JSON_VALID(@json);

파라미터로 들어간 JSON 변수가 올바른 JSON 형태인지 검사한다.
올바르다면 1, 아니라면 0을 반환

JSON_SEARCH(JSON변수, 갯수, 값)

SELECT JSON_SEARCH(@json, one, '최산'); -- "$.usertbl[0].name"

JSON변수에 저장된 JSON으로부터 특정한 값에 해당하는 데이터 선택자 반환.
만약 갯수가 one이라면 최초 1개를, all이라면 모든 데이터 선택자를 반환.
반환되는 값은 ""로 감싸져 있다.

JSON_EXTRACT(JSON변수, 선택자)

SELECT JSON_EXTRACT(@json, '$.usertbl[1].name'); -- "강여상"

JSON_SEARCH()와 반대로 동작
JSON변수에 저장된 JSON으로부터 특정한 선택자에 해당하는 값을 반환.
반환되는 값은 ""로 감싸져 있다.

JSON_INSERT(JSON변수, 선택자, 값)

SELECT JSON_INSERT(@json, '$.usertbl[0].mDate', '2024-07-10'); 
-- {"usertbl": [{"name": "최산", "mDate": "2024-07-10", "height": 177}, {"name": "강여상", "height": 175}, {"name": "송민기", "height": 184}]}

JSON변수와 선택자를 사용하여 해당 위치에 값을 삽입한 JSON을 반환.
SELECT와 함께 사용 시 JSON변수 내의 값이 바뀌는 것은 아님.

SET @json = JSON_INSERT(@json, '$.usertbl[0].mDate', '2024-07-10');

실제 변수값을 바꾸는 경우 위와 같이 할당

JSON_REPLACE(JSON변수, 선택자, 값)

SELECT JSON_REPLACE(@json, '$.usertbl[0].height', '180');
-- {"usertbl": [{"name": "최산", "height": "180"}, {"name": "강여상", "height": 175}, {"name": "송민기", "height": 184}]}

JSON변수와 선택자를 사용해 해당 위치에 존재하는 값을 대체한 JSON을 반환.
SELECT와 함께 사용 시 JSON변수 내의 값이 바뀌는 것은 아님.

SET @json = JSON_REPLACE(@json, '$.usertbl[0].height', '180');

실제 변수값을 바꾸는 경우 위와 같이 할당

JSON_REMOVE(JSON변수, 선택자)

SELECT JSON_REMOVE(@JSON, '$.usertbl[0]');
-- {"usertbl": [{"name": "강여상", "height": 175}, {"name": "송민기", "height": 184}]}

JSON변수와 선택자를 사용해 해당 위치에 존재하는 값 삭제
SELECT와 함께 사용 시 JSON변수 내의 값이 바뀌는 것은 아님.

SET @json = JSON_REMOVE(@json, '$.usertbl[0]');

실제 변수값을 바꾸는 경우 위와 같이 할당