[TIL] DB-02 - 단일행 함수, 흐름제어 함수, 복수행 함수

1. 오늘 학습한 주제

• 함수의 개념과 분류(단일행 / 복수행)
• 문자열 함수 — LENGTH, TRIM, LPAD, SUBSTRING, INSTR, REPLACE
• CAST()를 이용한 타입 변환
• 숫자 함수 — CEILING, FLOOR, ROUND, TRUNCATE
• 날짜 함수 — NOW, ADDDATE, DATEDIFF, YEAR/MONTH/DAY
• 흐름제어 함수 — IF, CASE ~ WHEN ~ THEN ~ END
• 복수행 함수 — COUNT, MIN, MAX, AVG, SUM
• ORDER BY

2. 배운 내용

함수

함수?
- 하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리한 작은 서브프로그램
- 호출하고 반환
- 유형
	- 단일행 함수
	- 복수행 함수(그룹) : min, max, sum, count, avg, etc...(window function)
		- GROUP BY와 같이 쓰이는 경우가 일반적

행 하나마다 결과가 하나 나오는 것이 단일행 함수, 여러 행을 받아 하나의 결과를 내는 것이 복수행 함수다.

문자열 함수 — 길이와 대소문자, 공백

SELECT LENGTH('후두다닥')
		,LENGTH('MEDTATE')
		,CHAR_LENGTH('후두다닥');

LENGTH와 CHAR_LENGTH가 나뉘어 있는 이유는 세는 단위가 다르기 때문이다. 한글이 들어가면 두 값이 달라진다.

SELECT EMP_NAME
		,LENGTH(EMP_NAME)
FROM employee;

SELECT UPPER('medtate')
		,LOWER('medtate');

TRIM 계열은 양쪽 / 왼쪽 / 오른쪽으로 나뉜다. LENGTH로 감싸 결과를 확인했다.

SELECT LENGTH(TRIM('    TRIM    '))
		,LENGTH(LTRIM('    TRIM    '))
		,LENGTH(RTRIM('    TRIM    '));

문자열을 채우는 함수

LPAD, RPAD는 지정한 길이가 될 때까지 지정한 문자로 채운다.

SELECT LPAD('5', 3, ' ')
		,LENGTH(LPAD('5', 3, ' '))
		,RPAD('5', 3, ' ')
		,LENGTH(RPAD('5', 3, ' '));

SELECT EMAIL
		,LENGTH(EMAIL)
		,LPAD(EMAIL, 20, '#')
FROM employee;

SELECT CHAR(65)
		,CONCAT('후두다닥', '님');

부분 문자열 — SUBSTRING, LEFT, RIGHT

SELECT SUBSTRING('ABCDEF', 1, 2)
		,LEFT('ABCDEF', 2)
		,RIGHT('ABCDEF', 2);

SUBSTRING은 시작 위치와 길이를 받는다. FROM ... FOR ... 형태와 콤마 형태 두 가지로 쓸 수 있다.

SELECT SUBSTRING('THIS IS INSPIRE CAMP' FROM 9 FOR 7)
		,SUBSTRING('THIS IS INSPIRE CAMP', 9, 7)
		,SUBSTRING_INDEX('www.lgcns.com', '.', -1);

SUBSTRING_INDEX는 구분자를 기준으로 잘라낸다. 세 번째 인자가 음수면 뒤에서부터 센다.

INSTR

문자열에서 부분 문자열의 인덱스를 반환한다.

SELECT INSTR('LGCNS CAMP', 'CAMP');

함수는 중첩할 수 있다. 메일 아이디만 추출하는 문제를 두 가지로 풀었다.

-- Q) . 앞의 문자 'c' 인덱스 번지를 검색
-- Q) 메일 아이디만 추출
-- hint) 함수는 함수를 중첩할 수 있음
SELECT EMAIL
		,INSTR(EMAIL, 'c.')
		,SUBSTRING_INDEX(EMAIL, '@', 1)
FROM employee;

-- 이렇게도 가능
SELECT EMAIL
		,INSTR(EMAIL, 'c.')
		,LEFT(EMAIL, INSTR(EMAIL, '@') - 1)
FROM employee;

INSTR가 반환하는 것은 @의 위치이므로, 아이디만 남기려면 - 1을 해야 한다.

반복과 치환

SELECT REPEAT('LGCNS', 3)
		,REPLACE('오늘은 즐거운 함수 수업', '함수', '메서드');

-- Q) 입사 연도만 추출
SELECT CONCAT(SUBSTRING_INDEX(HIRE_DATE, '-', 1), '년') AS `입사 연도`
FROM employee;

SELECT CONCAT(LEFT(EMP_NO, 8), '******')
FROM employee;

주민번호 뒷자리를 가리는 형태다. 앞 8자리를 남기고 나머지를 *로 대체한다.

CASTING

-- CASTING : CAST(TYPE AS TYPE)
SELECT SUBSTRING(EMP_NO, 1, 6)
		,SUBSTRING(EMP_NO, 8, 7)
		,CAST(SUBSTRING(EMP_NO, 1, 6) AS INT) + CAST(SUBSTRING(EMP_NO, 8, 7) AS INT)
FROM employee;

SUBSTRING의 결과는 문자열이므로 산술 연산을 하려면 숫자로 바꿔야 한다.

평균 구매 개수에도 적용했다.

USE SQLDB;

-- 고객의 평균 구매 개수를 검색한다면?
SELECT AVG(AMOUNT)
FROM buytbl;

SELECT CAST(AVG(AMOUNT) AS INT)
FROM buytbl;

SELECT CAST(AVG(AMOUNT) AS SIGNED INTEGER)
FROM buytbl;

INT와 SIGNED INTEGER 둘 다 정수로 변환한다. MariaDB는 INT를 변환 타입으로 받지만, 표준에 해당하는 표기는 SIGNED 쪽이라 다른 DBMS로 옮기면 동작이 달라질 수 있다.

반대로 숫자를 문자열로 바꿔 문장을 만드는 자리에도 쓴다.

-- Q) 구매번호, 총 금액(PRICE * AMOUNT = ), 구매액을 검색한다면?
-- CAST() 함수 활용
SELECT num AS 구매번호
		,CONCAT(CAST(PRICE AS VARCHAR(10)), ' * ', CAST(AMOUNT AS VARCHAR(10)), ' = ') AS `총 금액`
		,PRICE * AMOUNT AS 구매액
FROM buytbl;

숫자 함수

SELECT ABS(-100)
		,CEILING(4.7)
		,CEILING(4.1)
		,FLOOR(4.7)
		,FLOOR(4.1)
		,ROUND(1234.5678, 2)
		,ROUND(1234.5678, -2)
		,TRUNCATE(1234.5678, 2)
		,TRUNCATE(1234.5678, -2)
		,GREATEST(10, 20, 100, 30)
		,LEAST(10, 20, 100, 30);

ROUND는 반올림, TRUNCATE는 버림이다. 두 번째 인자가 음수면 소수점 위쪽 자리를 처리한다.

날짜 함수

SELECT NOW()
		,SYSDATE()
		,CURDATE()
		,CURTIME()
		,ADDDATE(CURDATE(), 	INTERVAL 30 YEAR)
		,ADDDATE(CURDATE(), 	INTERVAL 2 MONTH)
		,ADDDATE(CURDATE(), 	INTERVAL 2 DAY)
		,SUBDATE(NOW(), 		INTERVAL 30 DAY)
		,SUBTIME(NOW(), 		'13:00:00');

ADDDATE, SUBDATE는 INTERVAL로 단위를 지정한다. 단위만 바꾸면 년/월/일을 같은 형태로 처리할 수 있다.

날짜 컬럼에 직접 연산도 가능하다.

-- 날짜타입 컬럼에 연산?
SELECT HIRE_DATE
		,CAST(HIRE_DATE + 1 AS DATE)
FROM employee;

-- Q) 입사일을 기준으로 근속년수가 30년이 되는 일자를 검색한다면?
SELECT HIRE_DATE
		,ADDDATE(HIRE_DATE, INTERVAL 30 YEAR)
FROM employee;

두 날짜의 차이는 DATEDIFF로 구한다. 반환 단위가 일이므로 년 단위로 보려면 365로 나눈다.

-- Q) 오늘 날짜를 기준으로 근속 연수가 30년 이상인 사원은 모든 정보를 검색한다면?
-- hint)
-- SELECT DATEDIFF(CURDATE(), '2026-01-01');

SELECT *
FROM employee
WHERE DATEDIFF(CURDATE(), HIRE_DATE) >= 30 * 365;

SELECT EMP_NAME
		,HIRE_DATE
		,ROUND((DATEDIFF(CURDATE(), HIRE_DATE) / 365)) AS `근속년수`
FROM employee
WHERE DATEDIFF(CURDATE(), HIRE_DATE) / 365 >= 30;

365로 나누는 방식은 윤년을 반영하지 않으므로 경계에 있는 데이터에서는 오차가 생길 수 있다.

날짜 함수를 쓰는 DDL / DML

쿠폰 테이블을 만들어 발급일과 만료일을 넣었다.

-- D DEFINITION L
DROP TABLE coupon_tbl;
CREATE TABLE COUPON_TBL (
	CREATE_AT 	DATE NOT NULL PRIMARY KEY,
	END_AT 		DATE
);

-- DML
INSERT INTO coupon_tbl(CREATE_AT, END_AT)
VALUES(NOW(), ADDDATE(NOW(), INTERVAL 7 DAY));

만료일을 직접 계산해서 넣지 않고 ADDDATE로 발급일 + 7일을 만든다.

날짜의 일부만 꺼내기

SELECT 	HIRE_DATE
		  	,SUBSTRING(HIRE_DATE, 1, 4)
			,CAST(YEAR(HIRE_DATE) AS CHAR)
			,CAST(MONTH(HIRE_DATE) AS CHAR)
			,CAST(DAY(HIRE_DATE) AS CHAR)
			,CAST(HOUR(HIRE_DATE) AS CHAR)
			,CAST(MINUTE(HIRE_DATE) AS CHAR)
			,CAST(SECOND(HIRE_DATE) AS CHAR)
FROM 		employee;

SUBSTRING(HIRE_DATE, 1, 4)처럼 문자열로 취급해 자르는 방법과 YEAR()로 꺼내는 방법이 둘 다 가능하다.

요일은 숫자로 반환된다.

-- WEEKDAY() : 날짜의 요일을 숫자로 반환
-- DAYOFWEEK()

흐름제어 함수

IF, IFNULL, NULLIF, CASE ~ WHEN ~ THEN ~ END가 있다.

SELECT IF (100 > 200, 'TRUE', 'FALSE');
SELECT CASE 10
			WHEN 1 THEN '1'
			WHEN 2 THEN '2'
			ELSE 'NONE'
			END AS '구분';

CASE 표현식 WHEN 값 형태에서 WHEN 뒤에는 조건이 아니라 값이 들어간다. 조건을 쓰려면 CASE WHEN 조건 THEN 형태로 적어야 한다.

-- 성별?
-- Q) 부서번호가 50번인 사원의 이름, 주민번호, 성별 검색한다면?
-- WHEN 안에는 조건처리 불가능. EXACT VALUE만 들어갈 수 있음
SELECT EMP_NAME AS 이름
		,EMP_NO AS 주민번호
		,IF (SUBSTRING(EMP_NO, 8, 1) IN ('1', '3'), '남자', '여자') AS 성별
		,CASE WHEN
			SUBSTRING(EMP_NO, 8, 1) IN ('1', '3') THEN '남자'
			WHEN
				SUBSTRING(EMP_NO, 8, 1) IN ('2', '4') THEN '여자'
			ELSE '?'
		END AS 성별
FROM employee
WHERE DEPT_ID = '50';

같은 결과를 IF와 CASE 두 가지로 만들었다. IF는 참/거짓 둘로만 갈리고, CASE는 ELSE까지 두어 셋 이상으로 나눌 수 있다.

-- Q) 사원테이블에서 직급(JOB_ID)이 'J4' 사원의 이름, 사번, 사수번호(MGR) 검색한다면?
-- 조건) 사수번호가 없는 사원 MGR_ID 컬럼에 '관리자' 출력

SELECT EMP_NAME AS 이름
		,EMP_NO AS 사번
		,IF (MGR_ID = '', '관리자', MGR_ID) AS `사수번호(MGR)`
		,IF (CHAR_LENGTH(MGR_ID) <> 0, MGR_ID, '관리자')
FROM employee
WHERE JOB_ID = 'J4';

MGR_ID = ''는 값이 빈 문자열인 경우를 거른다. 값이 NULL이라면 비교 결과가 NULL이 되어 걸리지 않으므로, 컬럼이 어떤 상태로 비어 있는지에 따라 IFNULL이나 IS NULL 쪽을 써야 할 수 있다.

-- Q) 급여등급을 나눠보고 싶다
-- 300 이하면 초급, 400 이하면 중급, 초과하면 고급
-- 사원번호, 이름, 급여, 급여등급 검색한다면?
-- P.S. CASE문은 ANSI 표준

SELECT EMP_NO
		,EMP_NAME
		,SALARY
		,CASE
			WHEN SALARY <= 3000000 THEN '초급'
			WHEN SALARY <= 4000000 THEN '중급'
			ELSE '고급'
			END AS 급여등급
FROM employee;

WHEN은 위에서부터 순서대로 평가되므로, 3000000 이하는 첫 조건에서 걸러지고 두 번째 조건에는 도달하지 않는다.

-- Q) 남자사원에 대한 정보만 출력?
SELECT EMP_NO
		,EMP_NAME
		,'남자' AS 성별
FROM employee
WHERE SUBSTRING(EMP_NO, 8, 1) = '1';

WHERE로 이미 남자만 걸렀으므로 SELECT 절에는 상수를 그대로 적었다.

복수행 함수

-- 여러행의 결과를 입력으로해서 하나 또는 그 이상의 결과를 반환
-- WHERE 절에는 복수행 함수 X
-- SELECT 절에서는 사용 가능하나, 일반 컬럼은 사용할 수 없음

WHERE는 그룹이 만들어지기 전에 행을 거르는 자리라 복수행 함수를 쓸 수 없다.

-- Q) 사원 수를 확인하고 싶다면?
SELECT EMP_NAME
		,COUNT(*)
		,COUNT(BONUS_PCT)
		,COUNT(IFNULL(BONUS_PCT, 0))
		,MIN(SALARY)
		,MAX(SALARY)
		,AVG(SALARY)
		,SUM(SALARY)
FROM employee;

COUNT(*)는 행 전체를 세고, COUNT(컬럼)은 그 컬럼이 NULL이 아닌 행만 센다. IFNULL(BONUS_PCT, 0)으로 감싸면 NULL이 0으로 바뀌므로 COUNT(*)와 같은 값이 나온다.

다만 위 쿼리는 집계 함수와 일반 컬럼 EMP_NAME을 같이 적고 있다. MariaDB의 기본 sql_mode에는 ONLY_FULL_GROUP_BY가 빠져 있어 에러 없이 실행되지만, 이때 EMP_NAME은 여러 행 중 하나가 임의로 뽑힌 값이라 집계 결과와 짝이 맞지 않는다. 필기의 "일반 컬럼은 사용할 수 없음"은 이 의미로 이해했다.

ORDER BY

-- ORDER BY [기준컬럼 | 표현식 | 컬럼인덱스 | 컬럼 별칭] ASC | DESC

별칭을 정렬 기준으로 쓸 수 있다.

SELECT EMP_NO
		,EMP_NAME
		,SALARY
		,CASE
			WHEN SALARY <= 3000000 THEN '초급'
			WHEN SALARY <= 4000000 THEN '중급'
			ELSE '고급'
			END AS 급여등급
FROM employee
ORDER BY 급여등급;

workbook

학사 테이블(tb_student, tb_professor, tb_grade)에 오늘 배운 함수를 적용했다.

정렬과 별칭.

-- Q1)
SELECT STUDENT_NO AS 학번
		,STUDENT_NAME AS 이름
		,ENTRANCE_DATE AS 입학년도
FROM tb_student
WHERE DEPARTMENT_NO = '002'
ORDER BY 입학년도;

이름이 세 글자가 아닌 교수를 찾는 문제는 CHAR_LENGTH를 썼다. 한글은 LENGTH로 세면 글자 수와 다른 값이 나온다.

-- Q2)
SELECT PROFESSOR_NAME
		,PROFESSOR_SSN
FROM tb_professor
WHERE CHAR_LENGTH(PROFESSOR_NAME) <> 3;

나이 계산은 주민번호 앞 두 자리를 잘라 숫자로 바꾼 뒤 올해에서 뺐다.

-- Q3)
SELECT PROFESSOR_NAME AS 교수이름
		,CAST(YEAR(SYSDATE()) AS INT) - 1900
		- CAST(SUBSTRING(PROFESSOR_SSN, 1, 2) AS INT) AS 나이
FROM tb_professor
WHERE SUBSTRING(PROFESSOR_SSN, 8, 1) = '1'
ORDER BY 나이;

1900을 더해 출생년도를 만드는 방식은 2000년 이후 출생자가 없다는 문제의 조건에 기대고 있다. 또 년도끼리만 빼는 계산이라 생일 경과 여부는 반영되지 않는다.

성을 제외한 이름 추출.

-- Q4)
SELECT SUBSTRING(PROFESSOR_NAME, 2, 2) AS 이름
FROM tb_professor;

재수생 판별은 입학년도에서 출생년도를 뺀 값이 19를 넘는지로 걸렀다.

-- Q5)
SELECT STUDENT_NO
		,STUDENT_NAME
FROM tb_student
WHERE YEAR(ENTRANCE_DATE) - 1900 - CAST(SUBSTRING(STUDENT_SSN, 1, 2) AS INT) > 19;

요일은 숫자로 나오므로 값을 보고 판단했다.

-- Q6)
SELECT WEEKDAY('20201225');
-- 결과가 4이므로, 금요일

WEEKDAY()는 월요일이 0이다. 반환값의 기준 요일이 함수마다 다르므로 DAYOFWEEK()와 섞어 쓰면 결과가 달라진다.

학번 첫 글자로 2000년 이전 입학자를 걸렀다.

-- Q8)
SELECT STUDENT_NO
		,STUDENT_NAME
FROM tb_student
WHERE SUBSTRING(STUDENT_NO, 1, 1) <> 'A';

평균과 개수는 복수행 함수를 썼다.

-- Q9)
SELECT ROUND(AVG(POINT), 1)
FROM tb_grade
WHERE STUDENT_NO = 'A517178';

-- Q11)
SELECT COUNT(*)
FROM tb_student
WHERE COACH_PROFESSOR_NO IS NULL;

3. 헷갈렸던 부분

SUBSTRING의 세 번째 인자를 끝 위치로 착각했다

성을 제외한 이름을 뽑는 문제에서 두 번째 글자부터 세 번째 글자까지를 잘라야 한다고 보고 이렇게 적었다.

-- SUBSTRING(PROFESSOR_NAME, 2, 3) 이어야한다고 착각함

세 번째 인자는 끝 위치가 아니라 잘라낼 길이다. 두 번째 글자부터 두 글자를 가져와야 하므로 2, 2가 맞다.

SELECT SUBSTRING(PROFESSOR_NAME, 2, 2) AS 이름
FROM tb_professor;