프로그래머스 MySQL Lv.3 (1)

Journey to Data Analyst·2023년 4월 8일

mysql 데이터 분석 쿼리 프로그래머스

프로그래머스 MySQL

목록 보기

4/7

이번주에 프로그래머스 Lv.3을 다 풀었기 때문에
이번에도 2개의 포스팅으로 나누어 업로드 해볼 생각이다.
역시 Lv.3에 들어오니 문제가 ㅎㄷㄷ하게 바뀌는 중인데...

같이 한번 알아보자! Let's Go~

1. 오랜 기간 보호한 동물(2)

문제 설명

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며,
ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
INTAKE_CONDITION	VARCHAR(N)	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_INTAKE	VARCHAR(N)	FALSE

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.
ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.
ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

문제

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

예시

예를 들어, ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블이 다음과 같다면

ANIMAL_INS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A354597	Cat	2014-05-02 12:16:00	Normal	Ariel	Spayed Female
A362707	Dog	2016-01-27 12:27:00	Sick	Girly Girl	Spayed Female
A370507	Cat	2014-10-27 14:43:00	Normal	Emily	Spayed Female
A414513	Dog	2016-06-07 09:17:00	Normal	Rocky	Neutered Male

ANIMAL_OUTS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A354597	Cat	2014-06-03 12:30:00	Ariel	Spayed Female
A362707	Dog	2017-01-10 10:44:00	Girly Girl	Spayed Female
A370507	Cat	2015-08-15 09:24:00	Emily	Spayed Female

SQL문을 실행하면 다음과 같이 나와야 합니다.

ANIMAL_ID	NAME
A362707	Girly Girl
A370507	Emily

※ 입양을 간 동물이 2마리 이상인 경우만 입력으로 주어집니다.

본 문제는 Kaggle의 "Austin Animal Center Shelter Intakes and Outcomes"에서 제공하는 데이터를 사용하였으며 ODbL의 적용을 받습니다.

Answer

SELECT B.ANIMAL_ID, B.NAME FROM ANIMAL_INS A
RIGHT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2;

이번에는 입양을 간 동물 중, 보호기간이 가장 길었던 동물 "두 마리"의 아이디와 이름을 조회해야한다.

두 마리를 조회하는 것은 LIMIT 함수를 이용하여 쉽게 구할 수 있지만
"보호기간이 가장 긴"을 구하는 것이 좀 더 어려웠었다.
일단 위의 조건을 구해도 SELECT 문에 반영이 되지 않기 때문에 다른 방법을 생각해야했다.

그러다 다행히도 MySQL에서의 ORDER BY 함수는 SELECT 문에서 처럼
날짜를 뺄 수도 있다는 사실이 기억났다.
그러려면 2개의 날짜를 동시에 가져와야하기 때문에 JOIN을 하고
ORDER BY 에서 OUT 테이블의 날짜와 IN 테이블의 날짜를 빼주었다.

2. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

문제 설명

다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며
BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

Column name	Type	Nullable
BOARD_ID	VARCHAR(5)	FALSE
WRITER_ID	VARCHAR(50)	FALSE
TITLE	VARCHAR(100)	FALSE
CONTENTS	VARCHAR(1000)	FALSE
PRICE	NUMBER	FALSE
CREATED_DATE	DATE	FALSE
STATUS	VARCHAR(10)	FALSE
VIEWS	NUMBER	FALSE

USED_GOODS_FILE 테이블은 다음과 같으며
FILE_ID, FILE_EXT, FILE_NAME, BOARD_ID는 각각 파일 ID, 파일 확장자, 파일 이름, 게시글 ID를 의미합니다.

Column name	Type	Nullable
FILE_ID	VARCHAR(10)	FALSE
FILE_EXT	VARCHAR(5)	FALSE
FILE_NAME	VARCHAR(256)	FALSE
BOARD_ID	VARCHAR(10)	FALSE

문제

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요.
기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

예시

USED_GOODS_BOARD 테이블이 다음과 같고

BOARD_ID	WRITER_ID	TITLE	CONTENTS	PRICE	CREATED_DATE	STATUS	VIEWS
B0001	kwag98	반려견 배변패드 팝니다	정말 저렴히 판매합니다. 전부 미개봉 새상품입니다.	12000	2022-10-01	DONE	250
B0002	lee871201	국내산 볶음참깨	직접 농사지은 참깨입니다.	3000	2022-10-02	DONE	121
B0003	goung12	배드민턴 라켓	사놓고 방치만 해서 팝니다.	9000	2022-10-02	SALE	212
B0004	keel1990	디올 귀걸이	신세계강남점에서 구입. 정품 아닐시 백퍼센트 환불	130000	2022-10-02	SALE	199
B0005	haphli01	스팸클래식 팔아요	유통기한 2025년까지에요	10000	2022-10-02	SALE	121

USED_GOODS_FILE 테이블이 다음과 같을 때

FILE_ID	FILE_EXT	FILE_NAME	BOARD_ID
IMG_000001	.jpg	photo1	B0001
IMG_000002	.jpg	photo2	B0001
IMG_000003	.png	사진	B0002
IMG_000004	.jpg	사진	B0003
IMG_000005	.jpg	photo	B0004

SQL을 실행하면 다음과 같이 출력되어야 합니다.

FILE_PATH
/home/grep/src/B0001/IMG_000001photo1.jpg
/home/grep/src/B0001/IMG_000002photo2.jpg

Answer

SELECT 
    CONCAT("/home/grep/src/", b.BOARD_ID, "/", f.FILE_ID, f.FILE_NAME, f.FILE_EXT)
    AS FILE_PATH
FROM USED_GOODS_BOARD b
JOIN USED_GOODS_FILE f
ON b.BOARD_ID = f.BOARD_ID
WHERE b.BOARD_ID = (SELECT b1.BOARD_ID FROM USED_GOODS_BOARD b1
                    ORDER BY b1.VIEWS DESC
                    LIMIT 1)
ORDER BY FILE_ID DESC;

일단 이 문제는 각 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 반환해야하는데 이를 반환하려면 드래곤볼 찾기 처럼 CONCAT으로 하나씩 결합해주는 것이 포인트라고 할 수 있겠다.

먼저 기본 경로는 /home/grep/src/라고 하였으니 그것을 입력해 준 다음
차례대로 파일 경로에 해당하는 것을 입력해준 다음
두 개의 테이블을 JOIN 해주었다.

그리고 이제 대망의 WHERE절인데 조회수가 가장 높은 게시물을 찾아야 하기 때문에 BOARD_ID 중에서 VIEWS가 가장 높은 것을 찾기 위해 Sub-Query로
작성해주었다.

3. 조건에 맞는 사용자 정보 조회하기

문제 설명

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며
BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

Column name	Type	Nullable
BOARD_ID	VARCHAR(5)	FALSE
WRITER_ID	VARCHAR(50)	FALSE
TITLE	VARCHAR(100)	FALSE
CONTENTS	VARCHAR(1000)	FALSE
PRICE	NUMBER	FALSE
CREATED_DATE	DATE	FALSE
STATUS	VARCHAR(10)	FALSE
VIEWS	NUMBER	FALSE

USED_GOODS_USER 테이블은 다음과 같으며
USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 의미합니다.

Column name	Type	Nullable
USER_ID	VARCHAR(50)	FALSE
NICKANME	VARCHAR(100)	FALSE
CITY	VARCHAR(100)	FALSE
STREET_ADDRESS1	VARCHAR(100)	FALSE
STREET_ADDRESS2	VARCHAR(100)	TRUE
TLNO	VARCHAR(20)	FALSE

문제

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요.
이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요.
결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

예시

USED_GOODS_BOARD 테이블이 다음과 같고

BOARD_ID	WRITER_ID	TITLE	CONTENTS	PRICE	CREATED_DATE	STATUS	VIEWS
B0001	dhfkzmf09	칼라거펠트 코트	양모 70%이상 코트입니다.	120000	2022-10-14	DONE	104
B0002	lee871201	국내산 볶음참깨	직접 농사지은 참깨입니다.	3000	2022-10-02	DONE	121
B0003	dhfkzmf09	나이키 숏패팅	사이즈는 M입니다.	40000	2022-10-17	DONE	98
B0004	kwag98	반려견 배변패드 팝니다	정말 저렴히 판매합니다. 전부 미개봉 새상품입니다.	12000	2022-10-01	DONE	250
B0005	dhfkzmf09	PS4	PS5 구매로인해 팝니다.	250000	2022-11-03	DONE	111

USED_GOODS_USER 테이블이 다음과 같을 때

USER_ID	NICKNAME	CITY	STREET_ADDRESS1	STREET_ADDRESS2	TLNO
dhfkzmf09	찐찐	성남시	분당구 수내로 13	A동 1107호	01053422914
dlPcks90	썹썹	성남시	분당구 수내로 74	401호	01034573944
cjfwls91	점심만금식	성남시	분당구 내정로 185	501호	01036344964
dlfghks94	희망	성남시	분당구 내정로 101	203동 102호	01032634154
rkdhs95	용기	성남시	분당구 수내로 23	501호	01074564564

SQL을 실행하면 다음과 같이 출력되어야 합니다.

USER_ID	NICKNAME	전체주소	전화번호
dhfkzmf09	찐찐	성남시 분당구 수내로 13 A동 1107호	010-5342-2914

Answer

SELECT USER_ID, NICKNAME, 
       CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소,
       CONCAT(LEFT(TLNO, 3), '-', SUBSTR(TLNO, 4, 4), '-', RIGHT(TLNO, 4)) AS 전화번호
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID FROM USED_GOODS_BOARD
                 GROUP BY 1
                 HAVING COUNT(*) >= 3)
ORDER BY USER_ID DESC;

이번에는 각 테이블에서 중고거래를 3회 진행한 사람의 정보를 반환하는 쿼리를 작성하는 것이었다.
먼저 USER_ID와 NICKNAME은 동일하게 반환하면 되지만 전체주소 & 전화번호는 역시 CONCAT함수를 사용하여 결합된 텍스트로 반환해야되는 문제였다.

여기서는 전화번호가 관건인데 원래 있던 전화번호 column에서 -를 포함하여 반환해야하기 때문에 문자열 추출에 대한 함수를 모두 사용하였다.
- 문자열을 왼쪽부터 지정한 숫자의 개수만큼 반환하는 LEFT
- 문자열을 지정한 숫자에 해당하는 글자부터 그 다음 지정한 숫자의 갯수만큼 반환하는 SUBSTR
- 문자열을 오른쪽부터 지정한 숫자의 개수만큼 반환하는 RIGHT
이렇게 모두 사용해준 다음 CONCAT으로 결합할 때 중간에 -을 첨가해주었다.

그리고 마지막으로 조건절에서 각 아이디 별로 중고거래를 3번 이상한 사람을 찾아야 하기 때문에 USER_ID가 WRITER_ID로 그룹핑하였을 때 중고거래를 3번 이상한 사람에 포함되어있는지 알아볼 수 있는 IN 서브쿼리를 활용하였다.

다음에는 Lv.3 2탄으로 돌아오겠다!

Journey to Data Analyst

성장하는 주니어 데이터 분석가(Tableau, SQL and Python)

이전 포스트

프로그래머스 MySQL Lv.2 (2)

다음 포스트

프로그래머스 MySQL Lv.3 (1)