[SQL] 코딩 테스트를 위한 SQL 필수 함수 정리

SQL 이미지

SQL

0. 들어가며

 금융권 코딩테스트 또는 공기업 코딩테스트를 보게 되면 알고리즘 문제 말고도 항상 SQL 문제가 2문제 정도 끼어 있습니다. 알고리즘 문제는 스터디를 통해서 매일 풀지만, SQL에 대한 문제들은 항상 코테 2~3일 전날부터 풀곤 하는 제 모습... 매번 같은 함수에 대해서 찾아보는 시간을 줄이고자 이렇게 글로 작성해 봅니다! 이번 "농협경제지주" 코딩테스트를 준비하면서 정리한 함수들을 소개해보려고 합니다:>

 

 개인적으로 필수함수라고 생각하고, 이 함수들만 알고 있다면(기본적인 쿼리문을 작성할 수 있다는 가정 하에...) 프로그래머스 SQL Kit 문제들은 어렵지 않게 풀 수 있을 거라고 생각합니다.

프로그래머스 SQL Kit 풀이 인증

당연히 저는 SQL Kit 문제들을 다 풀어봤기에 당당하게 말씀드려봅니다. 그럼 렛츠꼬~

1. 필수 함수 및 문법 정리 (10개, MySQL ver)

1.1 반올림 함수 -> ROUND(값, 반올림까지 해서 어느 자리까지 나타낼 것인지 그 위치값)

 알고리즘 문제를 풀 때에도 가끔 사용하는 함수인 "ROUND() 함수"입니다. ROUND() 함수에서는 저도 처음에 파라미터 값이 헷갈렸는데, 반올림 대상에 해당하는 값을 먼저 적고, 해당 값에 대해서 어느 자리에서 반올림해서 어떤 위치값까지 나타낼 것인가 그 위치를 적어주면 됩니다. 즉, ROUND(값, 2)이라고 한다면, 특정 값에 대해서 반올림해서 소수점 2자리까지 나타낸다라는 의미가 됩니다.

 

 저도 처음 알았던 사실은 두 번째 파라미터의 "기본값이 0"입니다. 즉, 1이 소수점 첫 번째 자리를 의미하기 때문에 자연스럽게 0은 정수 부분 1의 자리를 나타냅니다. 이 원리를 통해서 두 번째 파라미터 값을 음수로 둔다면, 정수 범위에서도 반올림을 진행할 수 있습니다. 즉, ROUND(값, -2)라고 한다면, 특정 값에 대해서 반올림해서 백의 자리까지 나타내는 것이죠! 예시는 아래와 같습니다.

SELECT ROUND(123.25, 1) FROM DUAL;
-- 123.3
 
SELECT ROUND(1234.56789 ,1) FROM DUAL
-- 1234.6
 
SELECT ROUND(1234.56789 ,4) FROM DUAL
-- 1234.5679

SELECT ROUND(1234.56789) FROM DUAL
-- 파라미터가 없다면, SELECT ROUND(1234.56789, 0) FROM DUAL과 같다.
-- 1235
-- 두번째 파라미터 = 0  -> 반올림해서 정수 부분 1의 자리까지 나타낸다.
-- 두번째 파라미터 = -1 -> 반올림해서 정수 부분 10의 자리까지 나타낸다.
-- 두번째 파라미터 = -2 -> 반올림해서 정수 부분 100의 자리까지 나타낸다. 
-- ...
 
SELECT ROUND(1234.56789 ,-1) FROM DUAL
-- 1230

SELECT ROUND(1235.56789, -1) FROM DUAL
-- 1240
 
SELECT ROUND(1234.56789 ,-2) FROM DUAL
-- 1200

1.2 영어 소문자, 대문자 변경 함수 -> LOWER(), UPPER()

 파이썬 함수에서 쓰이는 lower(), upper() 함수라고 생각해도 됩니다. 즉, lower() 함수는 특정 문자열에 대해서 전부 소문자로 변환해 주고, upper() 함수는 특정 문자열에 대해서 전부 대문자로 변환해 줍니다.

SELECT LOWER("CaSe"), UPPER("cAsE") 
FROM DUAL;
-- LOWER("CaSe") = "case"
-- UPPER("cAsE") = "CASE"

1.3 조건절 문법 -> CASE WHEN ~ THEN ~ ELSE ~ END 문 

 프로그래머스에서 SQL 문제를 풀다 보면, 이렇게 케이스를 나눠서 표시해야 하는 경우가 종종 있습니다. 코테에서도 종종 나오는 문법인데요. 알고리즘 문제를 풀 때, if문을 생각하시면 됩니다. 

https://school.programmers.co.kr/learn/courses/30/lessons/131113

프로그래머스

 이 문법을 이해하고, 위 문제를 연습 삼아 풀어보시면 좋을 것 같습니다! 보통 case when 문법을 사용하면 컬럼명이 매우 길어지는데, 그래서 저는 별명을 지어주는 "as 절"을 습관처럼 달아놓습니다! END 뒤에 as 절을 사용하는 것을 잊지 마세요.

CASE 
	WHEN 조건1 THEN 반환값1
	WHEN 조건2 THEN 반환값2 
	ELSE 반환값3 
END AS '별명'

1.4 NULL인 데이터 대체 함수 -> IFNULL(값, NULL인 경우 대체할 값)

 바로 위에서 살펴본 case when 절은 조건들이 다양하거나 null 처리를 하지 않아도 되는 상황에서 많이 쓰는데, 간혹 null인 값을 어떤 숫자로 대체해서 평균을 구한다거나, null인 이름은 특정 문자열로 대체해서 나타내야 하는 문제가 있습니다. 이럴 때, case when 절을 써도 되지만 너무 문장이 길어지는 귀찮음(?)이 있습니다. 이렇게 null에 대한 분기처리를 해야 하는 경우에 사용할 수 있는 함수가 바로 "IFNULL(컬럼, NULL인 경우 대체할 값)"입니다.

https://school.programmers.co.kr/learn/courses/30/lessons/59410

코딩테스트 연습 - NULL 처리하기

위 문제를 풀어보면서 이 함수를 익혀봅시다.

select animal_type, ifnull(name, 'No name') as 'name', sex_upon_intake
from animal_ins
--- 두 결과는 같다. 
select animal_type, 
	case when name is null then "No name" 
		else name 
	end as "NAME", 
	SEX_UPON_INTAKE
from ANIMAL_INS
order by animal_id

1.5 임시 테이블 만들어 활용할 수 있는 문법 -> WITH ~ AS () 문

 문제를 풀다 보면, 서브쿼리를 사용해서 문제를 풀어야 하거나, 하나의 쿼리가 엄청 복잡해져서 이해 흐름이 깨지는 경우가 있습니다. 이런 경우, 시간이 조금 지나서 해당 쿼리를 보면 이해가 잘 가지 않는데요. 이런 경우에 WITH ~ AS () 문을 활용해서 서브 쿼리를 별도의 임시 테이블로 만들어 놓은 다음에 임시 테이블을 활용하는 방법을 활용해 봅시다. 그러면 쿼리가 보다 더 간단해지는 마법을 경험할 수 있습니다.

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

 이는 마치 서비스 레이어를 구현하면서 기능 단위로 메서드를 구현하거나, 아니면 자주 쓰이는 메서드에 대해서 유틸 메서드로 만드는 것과 같은 원리라고 생각하는데, 문제를 풀다가 너무 복잡해지는 경우, 임시테이블로 분리하는 풀이도 한번 경험해 보시면 좋을 것 같습니다. 위 문제를 한번 풀어보는 것을 추천합니다! 

WITH tmp AS (
    select id, fish_type, ifnull(length, 10) as "length", time
    from fish_info
)

SELECT ROUND(AVG(length), 2) AS "AVERAGE_LENGTH"
FROM tmp

1.6 문자열 합치는 함수 -> CONCAT(문자열 1, 문자열 2, 문자열 3, ,,,)

 문자열을 합치는 함수는 바로 "CONCAT() 함수"입니다. 이 함수도 간단하지만, 간혹 어떤 컬럼값에 대해서 특정 문자열을 붙여서 표현하라고 할 때, 생각이 잘 나지 않는 함수입니다. 어떤 값에 단위를 붙여서 표현하라는 문제에서 많이 활용할 수 있는 함수입니다. 문자열을 합치는 다른 종류의 함수도 있지만, 저는 주로 여러 문자열을 합칠 수 있는 CONCAT() 함수를 활용하는 편입니다.

https://school.programmers.co.kr/learn/courses/30/lessons/298515

프로그래머스

위 문제를 한번 풀어보세요! 

select concat(max(length), "cm") as "max_length"
from fish_info

1.7 문자열 자르는 함수 -> SUBSTR(문자열, 시작 인덱스, 시작 인덱스로부터 나타낼 문자의 개수)

 이 문자는 특정 문자열을 쪼개는 함수입니다. 즉, 어떤 문자를 앞에서부터 몇 개의 문자열을 잘라서 표현하거나, 주어진 문자열을 문제에서 요구한 특정 형태로 변환해야 하는 경우에 사용할 수 있습니다. 아래 문제와 같이 전화번호 11자리를 주고, 000-0000-0000 꼴로 나타내어야 하는 경우, 주어진 문자열을 3자리, 4자리, 4자리로 잘라야 합니다. substr() 함수를 사용하기에 아주 딱 맞는 상황이라고 볼 수 있죠! 

https://school.programmers.co.kr/learn/courses/30/lessons/164670

프로그래머스

저는 substr() 함수를 보통 3개의 파라미터를 활용해서 사용하는데, 파라미터 2개를 활용해서도 사용할 수 있습니다. 이에 대해서는 아래 Reference에 잘 정리해 놓은 블로그를 참고하시면 될 것 같습니다! 저는 실전용으로만 정리하도록 하겠습니다 :> 그럼 한번 적용해 봅시다!

select ugu.user_id as "user_id", ugu.nickname as "nickname", 
    concat(ugu.city, " ", ugu.street_address1, " ", ugu.street_address2) as "전체주소",
    concat(substr(ugu.tlno, 1, 3), "-", substr(ugu.tlno, 4, 4), "-", substr(ugu.tlno, 8, 4)) as "전화번호"
from used_goods_board ugb 
join used_goods_user ugu on ugb.writer_id = ugu.user_id 
group by ugb.writer_id 
having count(ugb.writer_id) >= 3
order by ugu.user_id desc;

1.8 날짜 포맷팅 함수 -> DATE_FORMAT(날짜 데이터, 표현식)

 가끔 문제에서 주어진 테이블의 날짜 데이터가 시간까지 표현되어 있는데, 출력은 연월일만 출력해야 하는 경우가 있습니다. 이럴 경우에 사용할 수 있는 함수가 바로 DATE_FORMAT() 함수입니다. 이때, 두 번째 파라미터에 표현식을 적절하게 넣어줘야 하는 것이 필요합니다. 이 또한, 아래 Reference에 자세하게 설명된 블로그를 적어놓도록 하겠습니다.

 

 저는 주로, "%Y-%m-%d" 표현식 말고는 사용한 적이 없는 것 같습니다. 해당 표현식은 "2026-02-22"와 같이 많이 보이는 익숙한 날짜를 나타내는 표현식입니다. 프로그래머스 문제를 풀다 보면 종종 쓸 때가 있기 때문에 알아둡시다! 

https://school.programmers.co.kr/learn/courses/30/lessons/59414

프로그래머스

위 문제를 풀어봅시다!

-- %Y-%m-%d 형식은 "2022-04-21"과 같은 형태로 나타난다.
select order_id, product_id, date_format(out_date,"%Y-%m-%d") as "out_date", 
case when out_date <= "2022-05-01" then "출고완료"
    when out_date > "2022-05-01" then "출고대기"
    else "출고미정"
end as "출고여부"
from food_order
order by order_id

1.9 날짜와 날짜 사이값 구하는 함수 -> DATEDIFF(끝 날짜, 시작 날짜)

 날짜와 날짜 사이에 며칠이 있는 지 그 값을 구해야 하는 경우가 있습니다. 예를 들어, "2026-01-01"와 "2026-02-22" 사이에 몇일이 지났는지 바로 알 수 있을까요? 물론, 주먹 꽉 쥐고, 볼록한 곳, 오목한 곳 체크하면서 날짜를 계산할 수 있지만,,,, "2025-05-25"와 "2026-02-22"인 경우에도 계산을 빠르게 할 수 있을까요? 코테는 시간이 촉박합니다... 그럴 경우에 사용할 수 있는 함수가 바로 "DATEDIFF()"함수입니다. 

 

 여기서 "DATEDIFF(start_date, end_date)"라고 적으면, 음수를 반환한다. 그리고, start_date == end_date 이면 0을 반환하기 때문에 start_date ~ end_date 값을 1로 처리하면, datediff(end_date, start_date) + 1 처리를 해줘야 한다.

https://school.programmers.co.kr/learn/courses/30/lessons/157342

프로그래머스

위 문제를 한번 풀어봅시다! 

select car_id, round(avg(datediff(end_date, start_date) + 1), 1) as "AVERAGE_DURATION"
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
having avg(datediff(end_date, start_date) + 1) >= 7.0
order by round(avg(datediff(end_date, start_date) + 1), 1) desc, car_id desc;

1.10 소수점 절삭 함수 -> TRUNCATE(값, 나타낼 위치의 값)

 소숫점 문제를 다루다가 가끔 소수를 정수로 나타내야 하는 경우가 있습니다. 이럴 때, 활용할 수 있는 함수가 바로 TRUNCATE() 함수입니다. ROUND() 함수와 마찬가지로 파라미터가 2개가 있고, 두 번째 파라미터에 음수를 적을 수 있습니다. 이럴 경우에는 정수 부분을 잘라내는 것이 아니라 0으로 표시합니다.

 

 TRUNCATE() 함수는 특히, 테이블 구조를 정의하는 "SQL DDL"로 분류되는 같은 함수가 있습니다. 즉, "TRUNCATE"는 테이블을 초기화하는 함수로도 사용할 수 있습니다. 나중에 혹시나 헷갈릴 수도 있으니, 이런 배경지식 또한 가져가시면 좋을 것 같습니다.

SELECT TRUNCATE(1234.56789 ,1) FROM DUAL;
-- 1234.5
 
SELECT TRUNCATE(1234.56789 ,4) FROM DUAL;
-- 1234.5678

SELECT TRUNCATE(1234.56789 ,0) FROM DUAL;
-- 1234 (소수 -> 정수로 변환할 때, 활용 가능)
 
SELECT TRUNCATE(1234.56789 ,-1) FROM DUAL;
-- 1230
 
SELECT TRUNCATE(1234.56789 ,-2) FROM DUAL;
-- 1200

1.11 CTE 활용 시, 오류가 발생할 수 있는 소소한 상황 소개

 문제를 풀다가, 아무리 생각해도 정답인데 자꾸 오류가 나서 채점조차 되지 않는 경우가 발생했습니다. 원인을 조사해 보니... CTE문 안에 습관적으로 세미콜론(;)을 작성해서 오류가 발생했던 것입니다. 세미콜론은 전체 쿼리문을 마무리할 때에만 작성해야 합니다. 즉, 아무리 임시 테이블을 만드는 CTE 문이라도 세미콜론을 넣으면 안 됩니다! 이것은 제가 기억하기 위해 소소한 꿀팁으로 소개드립니다 ㅎㅎㅎ.

with max_size_data as (
    select year(DIFFERENTIATION_DATE) as "year", max(size_of_colony) as "max_size"
    from ecoli_data 
    group by year(DIFFERENTIATION_DATE); -- <-- 오류 발생 부분
)

select msd.year as "year", abs(msd.max_size - ed.size_of_colony) as "year_dev", ed.id as "id"
from ecoli_data ed join max_size_data msd on year(ed.DIFFERENTIATION_DATE) = msd.year
order by msd.year, abs(msd.max_size - ed.size_of_colony);

2. 정리하며 

 지금까지 총 10개의 함수에 대해서 알아보고, 오류가 발생했던 소소한 상황에 대해서도 같이 정리해 보았습니다. 이 정도 함수만 알아도 대부분의 SQL 문제를 풀이할 때, 함수가 몰라서 풀 수 없는 문제는... 없을 거라고 생각합니다. 물론, SUM(), AVG(), COUNT()와 집계함수나 JOIN을 잘 활용해야겠지만,,, 프로그래머스에서 SQL 고득점 KIT문제들만 다 풀어보면 아마 코테 준비하는데 부족함은 없을 것 같습니다. 꾸준히 풀어보시고, 원하는 결과받으시길 바랍니다! 

 

 다음에는 LEFT JOIN에 대해서 한번 다뤄볼까 합니다. 실무에서 많이 쓰인다고 하지만, 왜 많이 쓰일까에 대해서 조금 알아보고 있습니다. 그럼 저는 다음 글에서 뵙도록 하겠습니다! 2월도 화이팅 :> 

3. Reference 

• SUBSTR 함수
• DATE_FORMAT 함수
• ROUND, TRUNCATE 함수