mysql 실무에 유용한 sql 로직 모음 #02

mysql 실무에 유용한 sql 로직 모음 #02

mysql 실무에 유용한 sql 로직 모음 #02

지난 1편에 이어서 mysql 실무에 유용한 sql 로직 모음 2탄을 준비했습니다.
지난번과 마찬가지로 케이스 별로 유용한 sql 로직들에 대한 실습과 설명으로 진행해 나가겠습니다.
Let's Go!

데이터를 가공하여 join 하기

테이블 두개를 join 해야하는데, 뭔가 연결고리가 있긴한데, 딱떨어지는게 없을때.. 데이터를 가공하여 join을 할 수 있답니다. 먼저 아래와 같이 두개의 테이블이 있습니다.

join1

join2

위에 있는 두테이블을 join 해야 하는데, 우선 join1 테이블의 text 컬럼과 join2 테이블의 text 컬럼을 보면 완전히 매칭이 되지 않습니다. 이럴때 어떻게 join을 하여 데이터를 연결 할 수 있을까요?
먼저 sql을 보고, 그리고 설명을 드리겠습니다.

select *
from join1 j1
         join join2 j2
              on j1.text = substring_index(j2.text, '-', 1);

위와 같이 정상적인 join 방법과 유사하게 시작을 합니다.
select, from, join 절까지 완성이 되었구요. 중요한것은 join key를 명시하는 on절입니다. 여기서 보시면 on j1.text = substring_index(j2.text, '-', 1) 라고 되어 있습니다.

substring_index 함수는 문자열을 특정한 기호를 이용하여 분리하는 함수 입니다. 예컨데, 위 데이터와 같이 "대한민국-서울" 이라는 데이터가 있다면, "-" 기호를 기준으로 앞,뒤로 데이터를 분리 하는 것 이죠.

그래서 결국, substring_index(j2.text, '-', 1) 의 결과는 분리한 데이터에서 첫번째 텍스트를 반환하는데, "대한민국-서울" 에서 "-"로 분리하고 난 첫번째 텍스트 조각인 "대한민국"만 반환을 하게 됩니다. 그럼 자연스럽게 join1 테이블의 text 데이터와 일치하게 되는것 이죠. 이렇게도 조인을 할 수 있답니다.

그 결과는 다음과 같이 조인이 된 것을 볼 수 있습니다.

데이터를 가공하여 join 하기 결과

 

 

 

행에 있는 데이터를 구분자로 나눠서 열로 만들어 넣기

다음은 행에 있는 데이터를 구분자로 나눠서 열로 만들어보는 SQL 입니다.
우선 다음과 같은 테이블이 있습니다.

pivot_test 테이블

이런형태의 데이터를 아래와 같이 바꾸는것이 목표 입니다.

to-be

자, 이런 형태를 만드는데는 다양한 방법이 있겠지만, 저는 with문과 join을 사용해서 만들어 보도록 하겠습니다.
미리 말씀 드리지만, with문에 있는 내용은, 지금현태 쪼개어야 할 데이터가 5개를 넘지 않기 때문에 저렇게 사용 하는것이고, 그게 아니라 많다면 따로 테이블을 만드는것도 방법 입니다.

먼저 SQL을 공개하고, 설명을 드리는 방식으로 진행 하겠습니다.

with recursive number as (
    select 1 as nb from dual union all
    select 2 as nb from dual union all
    select 2 as nb from dual union all
    select 3 as nb from dual union all
    select 3 as nb from dual union all
    select 3 as nb from dual union all
    select 4 as nb from dual union all
    select 4 as nb from dual union all
    select 4 as nb from dual union all
    select 4 as nb from dual union all
    select 5 as nb from dual union all
    select 5 as nb from dual union all
    select 5 as nb from dual union all
    select 5 as nb from dual union all
    select 5 as nb from dual
)

select pt.id
     , pt.country
     , substring_index(substring_index(pt.cities, ',', row_number() over (partition by pt.country)), ',', -1) as city
from (select id
           , country
           , cities
           , length(cities) - length(replace(cities, ',', '')) + 1 as lgt
      from pivot_test) pt
         join number n
              on pt.lgt = n.nb
order by pt.id;

먼저, 위드문을 볼께요. 여기에는 딱히 특별한 것은 없구요, 1은 한번, 2는 두번.... 5는 다섯번 넣은 단순한 구조 입니다. 나중에 mail SQL에서 join을 통해 데이터를 불리기 위한 장치 입니다.

다음은 main SQL에서 inline view(PT) 부분을 보시겠습니다.
여기에서, id, country, cities를 출력하고, 그다음 행에서 다음과 같이 기술 되어 있습니다.

length(cities) - length(replace(cities, ',', '')) + 1

이 부분은 cities 컬럼에 있는 도시의 수를 구하기 위한 건데요. 전체 length에서 콤마를 제외한 length를 빼고, 거기에 1을 더했습니다. 그럼 cities 컬럼내 도시의 수가 나오게 되겠죠.

그리고나서, 그 숫자와 위에서 선언한 with문인 number의 nb 컬럼과 join을 해서 그 숫자만큼 데이터 row가 늘어나도록 만들었습니다. 이까지 하면 아래와 같은 형태가 되는것 이겠죠?

중간 과정 데이터

여기에서 이젠 cities 컬럼값중 콤마로 구분된 도시명을 하나씩 출력 되도록 하면 됩니다. 그럴때는 substring_index를 중첩으로 쓰면 됩니다. SQL을 보시면서 이해해 보시면 됩니다. (다음에 자세히 정리를 해볼께요!) 그럼 최종적으로 위에서 미리 봤던 결과가 나오게 되는것 입니다.

 

 

 

CTAS 다양하게 사용하기 *주의점

CTAS는 많이 사용하는 내용중에 하나이나, 아직 모르시는 분들을 위해서 추가를 했습니다.
알면 매우 편하닌깐요.

우선 먼저 재료가 있어야겠죠?! 위에서 쓰던 테이블을 가지고 진행을 하겠습니다.

pivot 테이블

위 테이블이 있다고 하고, 이와 똑같이 생긴 테이블을 하나 만들어 보도록 하겠습니다.
물론 해당 테이블의 DDL을 추출하여 생성을 해도 되지만, 간단하게 만들어 볼께요.

create table pivot_test_2 as select * from pivot_test;

위와같이 아주 간단한 CTAS 구문으로 테이블을 똑같이 생성 할 수 있습니다. 그리고 데이터도 모두 옮겨져 있습니다.

CTAS

그쵸? 데이터도 있습니다. 이번엔 CTAS로 데이터는 빼고, 테이블 구조만 만드는 방법을 알아 보겠습니다.
다음 구문을 보시죠. 간단합니다. pivot_test_3을 생성 합니다.

create table pivot_test_3 as select * from pivot_test where 1=2;

자, 위와 같지만, where 1=2 라는게 붙어 있습니다. where 절을 사용하여 데이터를 출력하지 않은것이죠.
한번 select 해서 확인 해볼께요.

CTAS 결과

 

 

 

데이터는 없이 생성이 된것을 확인 할 수있습니다.
하지만 우리가 여기에서 주의 할 점이 있습니다. 이렇게 테이블을 복제 하게되면 단점이 있습니다.
그건 바로 primary key나 index, partition 등이 따라오지 않는 다는것 이죠.

차이점

그림을 보면 pivot_test 테이블에는 primary와 index가 있지만, 나머지 pivot_test_2, pivot_test_3 에는 없는 것을 확인 할 수가 있습니다. 그럼 이런것 까지 모두 가지고 오는 방법은 뭐가 있을까요? 없을까요?

네, 있습니다.

create table pivot_test_4 like pivot_test;

like 구문입니다. like 구문을 이용해서 위와같이 pivot_test_4를 생성 했습니다.
그리고 데이터도 한번 확인 해볼께요!

모든 구조 복사

위와 같이 모든 구조가 다 복사가 되었구요.
데이터는 없는것을 확인 했습니다.
그냥 구조만 동일하게 구성이 됩니다. 인덱스 명까지 말이죠.

자, 오늘도 이렇게 3가지 팁을 드려봅니다.
DB라는게 알면 알수록 알아야 하는게 많은것 같습니다.
여러분들도 하나씩 하나씩 저와 함께 공부를 해보시죠!! 
감사합니다.

by.sTricky

2021.10.27 - [Database/mariaDB administrator] - mysql 실무에 유용한 sql 로직 모음 #01

mysql 실무에 유용한 sql 로직 모음 #01

2021.09.09 - [Database/프로그래머스SQL] - 프로그래머스 SQL 코딩 테스트 select 문제 풀이

프로그래머스 SQL 코딩 테스트 select 문제 풀이

2021.07.15 - [Database/mariaDB administrator] - mysql mariadb update join SQL 문법 쉬운 설명

mysql mariadb update join SQL 문법 쉬운 설명

2021.05.31 - [Database/mariaDB administrator] - mariadb mysql 특정 스키마 테이블 ddl 추출 프로그램 배포

mariadb mysql 특정 스키마 테이블 ddl 추출 프로그램 배포

2020.12.11 - [Database/mariaDB administrator] - MySQL select 결과 csv 파일로 저장 하기

MySQL select 결과 csv 파일로 저장 하기