MySQL 문제 모음 - MySQL munje mo-eum

*본 문제는 모 기업 코딩테스트에서 나온 문제를 제 방식대로 변형한 문제입니다.

문제. Customers 내에 구매한 고객(member_no)들에 대하여 고객별 마지막 구매 완료 주문건의 쿠폰할인률을 구하는 SQL 쿼리를 구하시오.

* 쿠폰 할인률(last_dc) % : 쿠폰할인액(coupon_price)/주문가격(order_price) *100

* 단, 쿠폰을 쓰지 않는 주문건의 할인률은 0으로 가정한다.

* 쿠폰 할인률의 소수점 자리수는 2자리까지 출력한다.

Customers 스키마 (예시 테이블)

* 해당 테이블은 임의로 값을 넣은 예시입니다.

나의 풀이 in MySQL

SELECT member_id, CONCAT(round(coupon_price/order_price*100,2),'%') AS last_dc
FROM (SELECT *, IFNULL(coupon_price, 0), MAX(order_time) FROM Customers 
	WHERE buy_yn = 'y' GROUP BY member_id) A

FROM에 서브 쿼리를 활용하여 풀었습니다. 문제의 조건에 부합하기 위해 여러 가지 함수를 사용했고 최대한 간결하게 표현했습니다.

먼저, 고객별 할인률을 조회해야하므로 고객 id와 할인률을 조건대로 만들었습니다. round는 소수점 자릿수까지 반올림해주는 함수, concat은 문자를 합쳐주는 함수입니다.

구매 완료인 고객 중이므로 WHERE문에 조건을 넣어주었고 고객별 가장 마지막 구매 시기인 조건에 맞게 GROUP BY와 MAX를 활용했습니다. 또한, coupon_price가 NA일 경우, IFNULL을 이용하여 NA일 때 0의 값을 넣어주었습니다.

코드 결과

* 해당 문제는 제가 여러 SQL 관련 코딩테스트를 치르며 문제와 테이블 모두 만든 문제입니다. 저처럼 SQL 관련 코딩테스트를 준비하시는 사람들에게 조금의 도움이라도 될까 생각하며 만들었습니다.

다른 문제들 보러가기

• SQL 코딩테스트 연습 예제 2
• SQL 코딩테스트 연습 예제 3

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

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

데이터를 가공하여 join 하기

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

위에 있는 두테이블을 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 데이터와 일치하게 되는것 이죠. 이렇게도 조인을 할 수 있답니다.

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

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

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

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

자, 이런 형태를 만드는데는 다양한 방법이 있겠지만, 저는 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는 많이 사용하는 내용중에 하나이나, 아직 모르시는 분들을 위해서 추가를 했습니다.
알면 매우 편하닌깐요.

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

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

create table pivot_test_2 as select * from pivot_test;

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

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

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

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

데이터는 없이 생성이 된것을 확인 할 수있습니다.
하지만 우리가 여기에서 주의 할 점이 있습니다. 이렇게 테이블을 복제 하게되면 단점이 있습니다.
그건 바로 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

mysql 실무에 유용한 sql 로직 모음 #01 안녕하세요. mysql 또는 mariadb를 사용하시면서 실무에서 만나게 되는 다양한 sql 로직들에 대해서 정리를 해보도록 하겠습니다. 물론, 오라클이나 mssql등에서도

stricky.tistory.com

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

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

프로그래머스 SQL 코딩 테스트 select 문제 풀이 SQL 연습하신다고 프로그래머스 SQL 풀어보시는 분들이 많이 계시네요. 오늘부터 하나씩 풀이를 써볼까 합니다. 자자, 그럼 레고레고~!!! select 모든

stricky.tistory.com

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

mysql mariadb update join SQL 문법 쉬운 설명

mysql mariadb update join SQL 문법 쉬운 설명 mysql이나 mariadb에서 update 하실때 join해서 update 하는 방법에 대해서 쉽고 간단하게 설명을 드리도록 하겠습니다. 기존에 오라클이나 PG등을 사용하신분들은.

stricky.tistory.com