[SQLD] SQL 코딩테스트에 자주 출제했던 함수들

데이터 가공 문제

날짜-시간 데이터

1. 날짜 형식 변경

먼저 해당 필드명의 형식을 바꾸는 문제이다. 만약 `2023-12-25 00:00:00`의 날짜 형식을 바꾸고 싶으면 `DATE_FORMAT` 함수를 이용해 날짜 형식을 변경할 수 있다.

# DATE_FORMAT(필드명, 형식)
DATE_FORMAT(CREATED_DATE, "%Y-%m-%d")

2. 날짜 차이 계산

두 날짜 필드간의 차이를  계산하고 싶은 경우 `DATEDIFF(필드명1, 필드명2)`함수로 계산할 수 있다.

여기서 `CASE ~ END` 구문은 프로그래밍 언어에 조건문과 같은 의미이다. 해당 조건이 맞으면(`WHEN`) `THEN`을 반환하고 틀리면 `ELSE`를 반환한다.

# 30일을 기준으로 장기대여/단기대여 분류해 RENT_TYPE에 저장
CASE
	WHEN DATEDIFF(ENDTIME, STARTTIME)+1 >= 30 THEN '장기대여'
    ELSE '단기대여'
END RENT_TYPE

마지막에 `+1`를 추가하는 이유는 2일로 계산하기 위해서이다.

 

3. 날짜 범위 계산

해당 날짜가 포함한 범위를 계산할 수 있다. `BETWEEN ~ AND`를 `CASE WHEN ~ THEN ~ ELSE ~ END` 구문과 함께 사용하면 된다.

-- 2022-10-16일에 차를 빌릴 수 있는지 여부
SELECT CAR_ID,
        MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN "대여중" ELSE "대여 가능" END) AS AVAILABILTY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

 

함수	설명
NOW(), SYSDATE	현재 날짜와 시간 계산하는 함수
EXTRACT()	날짜에서 데이터를 출력하는 함수
CURDATE(), CURTIME()	현재 날짜 혹은 현재 시간을 출력하는 함수
YEAR(), MONTH()	연도만 반환하는 함수, 월만 반환하는 함수

문자열 가공

1. 특정 문자열 찾기

해당 문자열을 찾고자 할때는 주로 `LIKE` 명령어를 사용한다.

• `%` : 0개 이상의 문자열
• `_` : 1개의 문자열

# 언어가 파이썬인 데이터 찾기
WHERE LANGAUGE LIKE "%파이썬%"

2. 특정 문자열 사용

해당 필드에 있는 문자열 중 특정한 문자열만 사용하고자 할 때 `LEFT(필드, 시작, 끝)`혹은 `SUBSTRING(필드, 끝)` 함수를 사용한다. LEFT 대신 RIGHT를 사용할 수 있다.

# 필드의 문자열 중 첫번쨰 문자부터 두번쨰 문자까지만 추출한다.
SUBSTRING(PRODUCT_CODE, 1, 2)
LEFT(PRODUCT_CODE, 2)

 

3. 문자열 합치기

식별자를 사용하여 필드의 문자열들을 합칠 수 있다. 식별자를 사용하지 않는 경우에는 `CONCAT()`을 사용하고 식별자를 사용하는 경우엔 `CONCAT_WS('식별자', ...)를 사용한다.

SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;

 

 

GROUP BY

GROUP BY는 특정 데이터가 같은 것끼리 그룹 지어 그룹 단위로 집계하는데 사용하는 함수이다. 

그룹으로 묶은 데이터는 집계함수를 이용해 묶은 값에 대해 반환할 수 있다.

GROUP BY를 할 때, 단일 열로 그룹을 묶는 방법과 여러 열을 그룹으로 묶는 방법이 있다. 

• 단일 열을 사용하는 경우 : 학과별 평균 점수
• 여러 열을 사용하는 경우 : 학과의 학년별 평균 점수, 동일한 소비자가 동일한 제품을 구한 경우,

SUB QUERY(서브 쿼리)

하나의 SQL문에 포함할 수 있는 또 다른 SQL QUERY문을 의미한다. `(괄호)`로 감싸서 사용할 수 있으며 복수행 혹은 단일행으로 작성할 수 있다. 서브쿼리 내부에 ORDER BY문 사용할 수 없으니 주의하자.  서비쿼리는 SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT VALUE, UPDATE SET에서 사용할 수 있다.

• 단일 행 서브쿼리 : 서브 쿼리 실행 결과가 항상 1건 이하인 경우를 말한다.
• 다중 행 서브쿼리 : 서브쿼리의 결과가 2개 이상의 경우 `IN, ALL, ANY, EXISTS`를 함께 사용하여 반환한다.
  • IN (서브쿼리) : 동일한 조건의 경우
  • ALL (서브쿼리) : 모든 값이 만족하는지
  • ANY (서브쿼리) : 어느 하나라도 만족하는지
  • EXIST : 값의 존재 여부
• 다중 컬럼 서브쿼리  : 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.
• 인라인 뷰 : FROM절에서만 사용되는 서브쿼리로 임시 테이블을 생성한다. 이외로 ORDER BY를 사용할 수 있다.

SELECT T1.C1, T1.C2, T1.C3
FROM T1 T1
WHERE (T1.C1, T1.C2) IN (SELECT T2.C1, T2.C2
                         FROM T2 T2
                         WHERE T2.C2 = T1.C2)    -- 메인 쿼리의 컬럼을 서브쿼리에 사용
ORDER BY T1.C1, T1.C2, T1.C3;

 

이외 연산 함수

• `ROUND(값, 소숫점 자리)` : 해당 필드를 소숫점 자리수에 맞춰 반올림하는 함수이다. 소수점 자리가 0인 경우 정수를 의미한다. 
• `TRUNCATE(값, 소수점 자리)` : 해당 필드를 소숫점 자리 수는 제거해버리고 정수만 남깁니다.
• `COUNT()` : 해당 필드의 개수를 추출한다. `SUM()`의 해당 데이터의 합을 추출하는 함수이니 헷갈리면 안된다.
• `DISTINCT` : 중복을 제거할 때 사용하는 함수이다.

ROUND(3.75, 0) → 4
TRUNCATE(3.75, 0) → 3

---

CASE 문법

CASE

어떤 값이나 컬럼에 대해 조건별로 값을 반환하는 문법이다. `IF`문과 비슷하다.

CASE 문법은 변수를 사용하는 방식(SIMPLE_CASE)과 사용하지 않는 방식(SERCHED_CASE)이 있다.

변수를 사용하는 방식

다음 코드를 보면, `JOB` 컬럼의 변수값이 `MANAGER`라면 `LEADER`로 반환하고 나머지는 `MEMBER`로 반환하는 식으로 작성했다.

SELECT ENAME,
	(
		CASE JOB
			WHEN 'MANAGER' THEN 'LEADER'
			ELSE 'MEMBER'
		END
	) AS JOB2
FROM emp

 

 

변수를 사용하지 않는 방식

이번엔 여러 조건을 이용해 `SAL`이 1500을 넘는다면 `LEAD`를 반환하기로 했다.

SELECT ENAME,
	(
		CASE
			WHEN SAL > 1500 THEN 'LEAD'
			ELSE 'NOT LEAD'
		END
	) AS SAL2
FROM EMP;

 

 

IF() 함수

또, 조건문에 따라 반환하는 값이 달라지는 문으로 `IF()`함수가 있다.

상위 코드를 다음 코드로 바꿀 수 있다.

SELECT ENAME, IF(SAL > 1500, 'LEAD', 'NOT LEAD') AS SAL2
FROM EMP;

 

비슷한 구문으로 `IFNULL()` 구문이 있는데 만약 값이 NULL 값이라면 대체할 값으로 출력한다는 함수이다.

SELECT ENAME, IFNULL(JOB, 'INTERN') AS JOB
FROM EMP;

---

SET 사용자 정의 변수

SET 대입 연산자

SET @{변수명} = {값};

SET @start = 1500, @end = 2500;

SELECT * FROM EMP 
WHERE SAL BETWEEN @start and @end;

 

 

With 절

with 절은 서브쿼리를 함수처럼 사용할 수 있도록한다.

View와 비슷한 부분이 있지만 with은 쿼리문 내에 정의되어 있어 쿼리가 실행할 때마다 파싱되고 실행 계획을 세운다.

WITH NEW_NAME AS (
	-- 서브쿼리를 작성하는 부분
)

SELECT * FROM NEW_NAME -- WITH 절을 메인쿼리에 사용

 

`Recursive With`은 자신을 호출하는 Recursive 방식으로 세가지로 나누어 작성한다. 

• 초기값을 정하는 초기 서브 쿼리
• UNION ALL
• 이후에 행위를 작성하는 Recursive 서브 쿼리

재귀함수이기 때문에 반드시 종료조건을 조건절로 작성해주어야 한다.

WITH RECURSIVE temp AS (
SELECT 1 AS month -- 초기 쿼리
UNION ALL
SELECT month + 1 FROM temp -- 이후의 행위
WHERE month < 8 -- 종료조건
)

SELECT month, COUNT(id) AS count
FROM temp
LEFT JOIN registered_member
ON MONTH(datetime) = temp.month
GROUP BY month
ORDER BY month;

---

---

☕️ 포스팅이 도움이 되었던 자료

• 서브쿼리란? - mozi tistory
• W3 Schools

오늘도 저의 포스트를 읽어주셔서 감사합니다.

설명이 부족하거나 이해하기 어렵거나 잘못된 부분이 있으면 부담없이 댓글로 남겨주시면 감사하겠습니다.