피벗은 한 열에 포함된 여러 정보를 각각의 열 정보로 변환하는 것을 의미한다.
ex) 계절 컬럼 -> 봄, 여름, 가을, 겨울 컬럼으로 변환
집계함수 sum()과 if(조건, 참, 거짓)을 함께 사용하여 피벗을 구현
-- 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 |
-- 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 |
겨울 | 0 | 82 | 56 | 0 | 0 | 0 | 0 | 55 |
봄 | 0 | 0 | 19 | 13 | 66 | 0 | 71 | 0 |
여름 | 10 | 0 | 0 | 0 | 26 | 0 | 0 | 0 |
-- 사용자 변수 @
SET @json = '{ "usertbl" :
[
{"name":"최산", "height":177},
{"name":"강여상", "height":175},
{"name":"송민기", "height":184}
]
}';
변수에 할당하는 JSON 데이터는 ''
로 감싸져 있어야 한다.
SELECT JSON_VALID(@json);
파라미터로 들어간 JSON 변수가 올바른 JSON 형태인지 검사한다.
올바르다면 1, 아니라면 0을 반환
SELECT JSON_SEARCH(@json, one, '최산'); -- "$.usertbl[0].name"
JSON변수에 저장된 JSON으로부터 특정한 값에 해당하는 데이터 선택자 반환.
만약 갯수가 one
이라면 최초 1개를, all
이라면 모든 데이터 선택자를 반환.
반환되는 값은 ""
로 감싸져 있다.
SELECT JSON_EXTRACT(@json, '$.usertbl[1].name'); -- "강여상"
JSON_SEARCH()와 반대로 동작
JSON변수에 저장된 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');
실제 변수값을 바꾸는 경우 위와 같이 할당
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');
실제 변수값을 바꾸는 경우 위와 같이 할당
SELECT JSON_REMOVE(@JSON, '$.usertbl[0]');
-- {"usertbl": [{"name": "강여상", "height": 175}, {"name": "송민기", "height": 184}]}
JSON변수와 선택자를 사용해 해당 위치에 존재하는 값 삭제
SELECT와 함께 사용 시 JSON변수 내의 값이 바뀌는 것은 아님.
SET @json = JSON_REMOVE(@json, '$.usertbl[0]');
실제 변수값을 바꾸는 경우 위와 같이 할당