[SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (3) IN 연산자, REPLACE, DATEDIFF와 TIMESTAMPDIFF, DATE_FORMAT

안녕하세요, 오늘은 MySQL 연습문제 풀이 마지막 포스팅입니다. 앞에서 공부했던 개념이나 문제 풀이가 궁금하신 분들은 아래 링크를 참고해주세요.

 

[SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (1) SELECT, ORDER BY, GROUP BY, HAVING, 서브 쿼리, 집계 함수

[SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (1) SELECT, ORDER BY, GROUP BY, HAVING, 서브 쿼리, 집계 함수

 

[SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (2) NULL처리, INNER JOIN과 LEFT JOIN, LIKE 연산자, REGEXP

[SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (2) NULL처리 INNER JOIN과 LEFT JOIN, LIKE 연산자, REGEXP

 

모든 문제의 저작권은 프로그래머스에게 있음을 밝힙니다.

---

테이블 구조

 

ANIMAL_INS 테이블

 

ANIMAL_OUTS 테이블

---

IN 연산자는 WHERE절 뒤에 사용하며 특정 필드의 로우 값과 일치하는 데이터를 조회할 때 사용합니다.

SELECT [필드명]
FROM [테이블명]
WHERE [특정 필드명] IN ([로우값1], [로우값2], [로우값3] ...)

 

여러 개의 필드 조건을 추가할 때는 이렇게 사용합니다. 예를 들어, 직업이 Manager 이면서 연봉이 5000인 경우와 직업이 Designer 이면서 연봉이 4000인 경우를 조회하고 싶다면

WHERE (JOB, SALARY) IN ( ('Manager', 5000), ('Designer', 4000) ) 이렇게 사용할 수 있습니다.

 

String, Date > 루시와 엘라 찾기

 

동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

 

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')
ORDER BY ANIMAL_ID

 

IN 연산자를 이용하여 NAME의 로우 값과 일치하는 조건을 추가하였습니다.

 

특정 이름 동물 결과 테이블

---

String, Date > 이름에 el이 들어가는 동물 찾기

 

보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.

 

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE ANIMAL_TYPE = "Dog" AND NAME LIKE "%EL%" 
ORDER BY NAME

 

우선 조건이 두 가지입니다. 첫 번째는 개입니다. 그래서 ANIMAL_TYPE이 Dog인 조건을 WHERE절에 추가했고 두 번째는 이름에 "EL"이 들어가야 합니다. 두 번째 조건은 AND 연산자와 LIKE 연산자를 함께 WHERE절에 추가했습니다.

 

이름에 EL 포함 결과 테이블

---

특정 문자열을 치환하고 싶은 경우 REPLACE 함수를 사용합니다.

 

SELECT REPLACE([필드명], '기존 문자열', '대체 문자열')
FROM [테이블명]

 

여러 개의 문자열을 대체하고 싶다면 중첩으로 REPLACE 함수를 사용합니다.

 

SELECT REPLACE(
	REPLACE([필드명], '[기존 문자열1]', '[대체 문자열]'),
	'[기존 문자열2]', '[대체 문자열]') 
FROM [테이블명]

 

하지만 REGEXP_REPLACE 함수를 사용하면 중복을 피할 수 있습니다.

위의 문법과 동일하게 문자열 1과 문자열 2를 대체 문자열로 치환하여 조회합니다.

SELECT REGEXP_REPLACE([필드명], '[기존 문자열1]|[기존 문자열2]', '[대체 문자열]')
FROM [테이블명]

 

String, Date > 중성화 여부 파악하기

 

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

 

우선 테이블에 있는 모든 데이터를 조회해보니 SEX_UPON_INTAKE이 'Spayed Female', 'Neutered Male', 'Intact Female', 'Intact Male' 네 가지가 있었습니다. 그래서 앞에 두 개는 O로 치환하고 뒤에 두 개는 X로 치환하면 되겠다고 생각하고 문제를 풀었습니다.

 

SELECT ANIMAL_ID, NAME, 
REGEXP_REPLACE(
    REGEXP_REPLACE(SEX_UPON_INTAKE,'Spayed Female|Neutered Male','O')
    ,'Intact Female|Intact Male','X') AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

우선 여러 개의 문자열 조건을 넣어야 하기 때문에 REGEXP_REPLACE 함수를 사용했고 O인 경우, X인 경우를 한 번에 치환해야 했기 때문에 중첩으로 사용했습니다.

 

더 간단한 풀이로는 IF문을 활용한 방법입니다. IF문의 기본 문법은 다음과 같습니다.

 

SELECT IF([조건문], '[참일때 결과값]','[거짓일때 결과값]')
FROM [테이블명]

 

문제에 적용해보겠습니다. SEX_UPON_INTAKE의 네 가지 경우를 참과 거짓으로 나누려면 어떻게 해야 할까요? Intact라는 문자열이 포함된 경우, 포함되지 않은 경우로 나눌 수 있을 것입니다. 그래서 조건문에 Like '%Intact%'를 추가했습니다.

 

SELECT ANIMAL_ID, NAME, 
IF(SEX_UPON_INTAKE LIKE 'Intact%', 'X', 'O') AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

중성화 여부 파악 결과 테이블

---

날짜 차이를 구하는 함수에는 두 가지가 있습니다.  단순히 일 차이를 구할 때는 DATEDIFF 함수를 사용하고 연, 분기, 월, 주, 일, 시, 분, 초를 지정하여 세밀한 차이를 구할 때는 TIMESTAMPDIFF 함수를 사용합니다.

 

DATEDIFF 함수를 사용하여 날짜 1 - 날짜 2를 구해보겠습니다.

 

DATEDIFF(날짜1, 날짜2)

 

TIMESTAMPDIFF 함수를 사용하여 날짜 1 - 날짜 2의 초 차이를 구해보겠습니다.

 

TIMESTAMPDIFF(SECOND, 날짜2, 날짜1)

 

여기서 주의할 점은 TIMESTAMPDIFF 함수에서는 뒤에 날짜에서 앞에 날짜의 차이로 계산됩니다. DATEDIFF와 반대죠. 그 외에도 YEAR, DAY, HOUR, MINUTE 등의 단위를 사용할 수 있습니다.

 

String, Date > 오랜 기간 보호한 동물(2)

 

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

 

우선 입양을 간 동물 중에서 보호 기간이 존재한다는 건 INS 테이블과 OUTS 테이블에 모두 정보가 있는 동물을 조회해야 함을 의미합니다. 그러므로 INNER JOIN을 사용해야 합니다. 또한 보호 기간을 구하려면 입양을 간 시간과 보호소에 들어온 시간의 차이를 계산해야 합니다. 저는 일 차이를 구하는 DATEDIFF 함수를 사용했습니다.

 

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS
    JOIN ANIMAL_OUTS
    ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
ORDER BY DATEDIFF(ANIMAL_OUTS.DATETIME,ANIMAL_INS.DATETIME) DESC
LIMIT 2

 

일 차이가 큰 순으로 정렬해야 하기 때문에 내림차순 정렬인 DESC를 사용했습니다.

 

다른 풀이로는 단순히 - 연산을 하는 것입니다. 연산자는 SELECT절과 ORDER BY절에서 모두 사용 가능합니다.

 

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

 

오랜 기간 보호 동물 결과 테이블

---

DATE_FORMAT 함수는 날짜(DATETIME)를 원하는 포맷에 맞게 바꿔주는 함수입니다.

SELECT DATE_FORMAT([DATETIME], [FORMAT])
FROM [테이블명]

 

DATETIME 타입과 DATE 타입의 차이점에 대해 설명하자면

DATETIME은 YYYY-MM-DD hh:mm:ss의 형태이고 DATE는 YYYY-MM-DD의 형태입니다.

DATE_FORMAT 함수를 사용하여 %Y-%m-%d %h:%m:%s 형태로 지정하면 시 분 초 값은 0으로 채워지게 됩니다.

 

DATE_FORMAT에서 자주 사용되는 FORMAT을 표로 정리해보았습니다.

 

FORMAT	출력 결과
%Y	Year 연도(1999, 2000, 2020)
%y	Year 연도(99, 00, 20)
%M	Month 월(Janeary, February ...)
%m	Month 월(01, 02, 03 ...)
%D	Day 일(1st, 2dn, 3rd ...)
%d	Day 일(00, 01, 02 ...)

 

String, Date > DATETIME에서 DATE로 형 변환

 

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다. 그리고, 들어온 날짜는 시각(시-분-초)을 제외한 날짜(년-월-일)만 보여주세요.

 

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

DATE_FORMAT 함수를 사용하여 원하는 포맷에 맞게 지정하였습니다.

 

DATE 형 변환 결과 테이블

 

이상으로 프로그래머스 SQL 모든 문제를 풀어보았습니다. 따라와 주신 분들께 감사드리고 도움이 되셨다면 하트와 구독 부탁드립니다. 감사합니다.

---

Reference

 

모든 문제는 programmers 사이트를 참고하였습니다.

 

프로그래머스 SQL 고득점 Kit 링크

코딩테스트 연습