부트캠프 사전퀘스트를 풀고 정답지와 비교하면서 알게 된 유용한 연산자와 함수 정리
문제 1. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!
//나의 코드
SELECT *
FROM sparta_students
WHERE enrollment_year = 2021 or enrollment_year = 2023
//정답지 코드
SELECT *
FROM sparta_students
WHERE enrollment_year IN (2021, 2023)
강의에서 본 것도 같지만 기억이 안나는 이슈로 활용하지 못했던 IN 연산자
조회하고자 하는 값이 여러개일 때 위와 같이 IN 연산자를 활용하면 간단하게 해결 가능하다.
IN (조회할 값)
문제 2. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!
//나의 코드
SELECT *
FROM team_projects
WHERE substr(start_date, 1, 4) = 2022
//정답지 코드
SELECT *
FROM team_projects
WHERE YEAR(start_date) = 2022;
여태까지 substr 함수를 이용해서 필요한 부분만 잘라서 사용했는데, Year(), Month(), Day() 함수를 활용하면 년, 월, 일 부분만 정수로 추출이 가능하다.
YEAR(date)
문제 3. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!
//나의 코드
SELECT *
FROM team_projects
WHERE end_date is null
//정답지 코드
SELECT *
FROM team_projects
WHERE CURDATE() BETWEEN start_date AND end_date;
정답지 로직을 생각하긴 했지만, 현재 시점의 날짜를 구하는 함수를 몰라서 일단 짠 코드가 end_date is null 로 조회하는 것이었다.
만약 진행 중인 프로젝트여도 마감일이 존재하거나, 더미값을 넣어놨다면 아무 데이터도 조회되지 않을 거기 때문에 정답지대로 작성하는 게 맞는 것 같다.
CURDATE() 는 현재 날짜를 반환하는 함수 (YYYY-MM-DD 출력)
CURTIME() 은 현재 시간을 반환하는 함수 (hh:mm:ss 출력)
NOW() 는 현재 날짜와 시간을 반환하는 함수 (YYYY-MM-DD hh:mm:ss 출력)
구글링을 통해 알게 된 추가 함수 SUBDATE(date, days) 는 date값에서 days값 만큼의 전 일 수의 날짜를 반환할 수 있다.
ex. subdate(curdate(), 7) 은 현재에서 일주일 전 날짜를 반환한다.
문제 4. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!
//나의 코드
SELECT name, date(end_date)-date(start_date) '지속 기간'
FROM team_projects
//정답지 코드
SELECT name, DATEDIFF(end_date, start_date) AS '지속 기간'
FROM team_projects;
date 함수를 사용하면 결과값의 형식이 문자열이 아닌 date 형식이기 때문에 마이너스 연산자를 사용해도 되겠지..? 싶은 마음에 시도해본 나의 코드이다.
정답지 코드에서는 datediff() 를 사용했다.
DATEDIFF(date1, date2) 형식으로, 자동으로 date1-date2 값을 반환해준다. (일 수 차이 계산)
만약 일 수가 아니라 달 수, 연 수, 분 수 등을 알고 싶다면 TIMESTAMPDIFF(unit, date1, date2) 를 사용해주면 된다.
unit에는 month, minute 등과 같이 단위 명이 들어간다.
TIMESTAMPDIFF 함수는 date2-date1 값을 반환한다.
문제 3, 문제 4에서 통용되게 사용할 수 있는 함수가 있다.
바로 DATE_SUB(), DATE_ADD() 함수이다.
DATE_SUB(기준 날짜, interval 정수 단위) / DATE_ADD(기준 날짜, interval 정수 단위)
예를 들어 DATE_SUB(CURDATE(), INTERVAL 5 YEAR)는 현재 날짜에서 5년을 뺀 날짜이다.
정수 위치에 음수를 사용하면 서로 반대되는 함수로도 이용할 수 있다.
DATE_SUB(CURDATE(), INTERVAL 5 YEAR) = DATE_ADD(CURDATE(), INTERVAL -5 YEAR)
문제 5. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요!
//나의 코드
SELECT name
FROM lol_users
WHERE MAX(join_date)
//정답지 코드
SELECT name
FROM lol_users
ORDER BY join_date DESC LIMIT 1;
이 문제는 내가 짠 코드를 DBeaver에서 저장된 테이블로 테이블과 컬럼을 바꿔서 실행했을 때 에러가 뜨길래 나름대로 max 함수를 써보고 싶다는 생각에 재시도하느라 시간이 꽤 소요된 문제다.
일단 에러가 떴다는 생각에 에러 메세지를 확인하지 않고(고쳐야 할 문제점 발견!) 다른 쿼리를 짜서 시도했다.
select name, max(join_date)
from lol_users
실행하면 결과물은 출력이 되는데, name과 join_date가 하나의 레코드에서 출력되는 것이 아니라
전체 레코드 조회를 했을 때 첫 번째 레코드의 name과 가장 큰 join_date 값이 하나의 레코드로 출력되는 것이었다.
사실 당연한 결과였다.
max() 함수 때문에 레코드는 하나가 출력이 되지만, select 문에 작성했으니 name과 max(join_date)간에는 아무런 연관이 없는 것이다.
이에 나는 역시 where 절이 필요하다는 생각에 where 절을 추가한 코드를 작성했고, 또다시 첫 번째로 작성했던 코드의 에러와 같은 에러를 만났다.
select name
from lol_users
where join_date = max(join_date)
SQL Error [1111] [HY000]: Invalid use of group function 바로 이 에러다.
그제서야 나는 에러를 검색해봤고, 간단히 말하자면 집계함수(여러 행으로부터 하나의 결과값을 반환하는 함수)는 where 절에 사용할 수 없다는 것이다.
이 문제는 where절 대신 having절을 사용하면 된다고 하는데 캠프에서 준 강의에는 having절에 대한 내용이 없기 때문에, having절을 사용해서 재시도를 하진 않았다.
거기다 배웠던 SQL을 많이 잊어버린 상태라 심화 SQL에 대해 다시 공부할 필요를 느꼈다.
정답지처럼 join_date가 큰 순서로 레코드를 정렬하고 그 중 하나의 레코드만 출력하면 되는 간단한 문제였지만, 문제를 보자마자 max() 함수를 사용하면 되겠다는 생각에 사로잡혀서 멀리 돌아오게 되었다.
이 문제를 통해 얻은 교훈은 에러 메세지를 꼭 확인하자와 내가 짠 로직이 옳은 방법인지 다시 한 번 생각하자이다.
문제 6. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!
//나의 코드
SELECT *
FROM lol_users
GROUP BY region
ORDER BY rating DESC
//정답지 코드
SELECT *
FROM lol_users
ORDER BY region, rating DESC;
이 문제는 정답지 코드처럼 order by만 사용하여 지역별, 레이팅별 정렬을 했어야 하는데,
아무 생각없이 지역별로 group by를 해서 레코드가 9개만 출력되었다.
게다가 무작위로 특정 지역의 레코드의 rating이 0값을 가져서 뭐 때문인지 조건을 바꿔가며 실행을 해봤는데 결론은 집계 함수없이 group by를 사용했기 때문이었다.
집계 함수 없이 group by절을 사용했더니 전체 레코드 중 지역마다 첫 번째 레코드 값이 삽입되어 있었다.
하필 특정 지역의 첫 번째 레코드가 rating값이 0이어서 더욱 이상하게 느꼈던 것 같다.
결론은 특정 상황이 아니라면 집계 함수 없이 group by절을 사용하지 말자!
문제 7. 익명화된 유저들의 아이디(created_at)와 유저가 획득한 날짜별 평균 포인트(average_points)가 포함된 테이블을 만들어봅시다!
//나의 코드
select date(substr(created_at, 1, 10)) as created_at, round(avg(point)) as average_points
from point_users
group by 1
//정답 코드
SELECT
DATE(p.created_at) AS created_at,
ROUND(AVG(p.point)) AS average_points
FROM
point_users p
GROUP BY
DATE(p.created_at);
- DATE() 함수는 데이터가 날짜+시간 형식이어도 날짜 값만 반환해준다.
- ROUND() 함수는 반올림한 값을 반환한다.
'언어, 프레임워크 > SQL' 카테고리의 다른 글
| [mysql] having절, 서브 쿼리 실습 (0) | 2025.01.20 |
|---|---|
| [mysql] window 함수(rank, sum) (0) | 2025.01.17 |
| [mysql] max()로 pivot table 만들기 (0) | 2025.01.17 |
| [mysql] locate(), instr(), substring_index() 함수로 문자열의 일부만 가져오기 (0) | 2025.01.16 |
