[프로그래머스] 9주차 MySQL 스터디 1

1. SELECT - [조건에 맞는 개발자 찾기] _ LEVEL 2

SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다.

DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다.

DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.

결과는 ID를 기준으로 오름차순 정렬해 주세요.

 

[ 정답 쿼리 ]

SELECT DISTINCT id, email, first_name, last_name
FROM developers
JOIN skillcodes ON skill_code & code = code
WHERE name IN ('C#', 'Python')
ORDER BY 1;

 

[ 풀이 ]

• 여러가지 스킬코드를 가지고 있는 개발자가 있을 수 있기 때문에 DISTINCT 키워드로 중복없이 개발자 인적 정보 출력

---

2. SELECT - [잔챙이 잡은 수 구하기] _ LEVEL 1

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다.

단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH에 NULL 만 있는 경우는 없습니다.

잡은 물고기 중 길이가 10cm 이하인 물고기의 수를 출력하는 SQL 문을 작성해주세요.

물고기의 수를 나타내는 컬럼 명은 FISH_COUNT로 해주세요.

 

[ 정답 쿼리 ]

SELECT COUNT(*) FISH_COUNT
FROM fish_info
WHERE length IS NULL;

---

🌟 3. GROUP BY - [입양 시각 구하기(1)] _ LEVEL 2

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다.

09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.

이때 결과는 시간대 순으로 정렬해야 합니다.

 

[ 정답 쿼리 ]

SELECT 
    HOUR(datetime) hour
    , COUNT(DISTINCT animal_id) count
FROM animal_outs
WHERE HOUR(datetime) BETWEEN 9 AND 19
GROUP BY 1
ORDER BY 1;

출력 결과

---

🌟🌟🌟 4. GROUP BY - [입양 시각 구하기(2)] _ LEVEL 4

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.

이때 결과는 시간대 순으로 정렬해야 합니다.

 

[ 정답 쿼리 ]

WITH RECURSIVE seq AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1
    FROM seq
    WHERE hour < 23
)

SELECT S.hour, COUNT(animal_id) count
FROM animal_outs A 
RIGHT JOIN seq S ON HOUR(A.datetime) = S.hour
GROUP BY 1
ORDER BY 1;

 

출력 결과

[ 풀이 ]

• 이 문제의 포인트는 입양이 이뤄지지 않은 시간대에 대해서도 결과값이 출력되어야 한다는 것
• 그러기 위해서는 크게 두가지 방법이 가능
  1. 하드코딩으로 직접 테이블 생성
  2. CTE문(WITH RECURSIVE)을 활용하여 RIGHT JOIN
     • 재귀 CTE는 MySQL 8.0 부터 추가된 신기능으로 이에 관한 자세한 내용은 다음 문서에 정리해두었다.

RECURSIVE CTE(재귀 CTE)

 

---

5. SELECT - [가장 큰 물고기 10마리 구하기] _ LEVEL 1

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다.

FISH_INFO 테이블에서 가장 큰 물고기 10마리의 ID와 길이를 출력하는 SQL 문을 작성해주세요.

결과는 길이를 기준으로 내림차순 정렬하고, 길이가 같다면 물고기의 ID에 대해 오름차순 정렬해주세요. 단, 가장 큰 물고기 10마리 중 길이가 10cm 이하인 경우는 없습니다.

ID 컬럼명은 ID, 길이 컬럼명은 LENGTH로 해주세요.

 

[ 정답 쿼리 ]

SELECT id, length
FROM fish_info
ORDER BY 2 DESC, 1
LIMIT 10;

---

🌟 6. String, Date - [오랜 기간 보호한 동물(2)] _ LEVEL 3

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다.

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요.

이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

 

[ 정답 쿼리 ]

SELECT
    O.animal_id
    , O.name
FROM animal_outs O
JOIN animal_ins I ON O.animal_id = I.animal_id
ORDER BY DATEDIFF(O.datetime,I.datetime) DESC
LIMIT 2;

 

[ 풀이 ]

DATEDIFF 함수와 TIMEDIFF 함수의 결과 비교해보기

• TIMEDIFF()를 사용하면 틀리는 이유
  • 처음에 시간 차이를 조금 더 디테일하게 계산하고 싶어서 DATEDIFF()가 아닌 TIMEDIFF()함수를 사용했는데, 틀린 답안 처리가 되었다
  • MYSQL 이 받을 수 있는 TIME RANGE 가 -838:59:59 ~ 838:59:59 → 이 값을 넘어서는 일자들은 모두 838:59:59의 값을 가지게 됨
  • 참고로 838:59:59는 34일 22시간 정도에 해당되는 시간
  • 즉 DATE 자료형의 일수 차이가 한달 이상이라면 TIME RANGE에 걸리는 함수(TIMEDIFF(), TIME_TO_SEC(), SEC_TO_TIME())는 사용하면 예상치 못한 오류가 생길 수 있음 

 

 

만약 34일 22시간 이 넘는 시간 차에 대해서 시, 분, 초 단위로 계산하고자 한다면 TIMESTAMPDIFF() 함수를 이용하기! 

 

예시) '2016-01-27 12:27:00’ , '2017-01-10 10:44:00’ (대략 1년) 두 일시 사이의 기간을 초(SECOND)로 표현하고 싶을 때?

SELECT 
	TIMEDIFF('2017-01-10 10:44:00','2016-01-27 12:27:00') `TIMEDIFF()`
	, TIME_TO_SEC(TIMEDIFF('2017-01-10 10:44:00','2016-01-27 12:27:00')) `TIME_TO_SEC(TIMEDIFF())` 
    	, TIMESTAMPDIFF(SECOND, '2016-01-27 12:27:00', '2017-01-10 10:44:00') `TIMESTAMPDIFF(SECOND, ~)`;

 

TIMEDIFF()	TIME_TO_SEC(TIMEDIFF())	TIMESTAMPDIFF(SECOND, ~)
838:59:59	3,020,399	30,147,420