목차
개요
SQL 함수란?
정의 함수는 데이터를 가공하는 약속된 문법이라고 할 수 있어요
종류 크게 단일행 함수와 다중행 함수가 있으며,
단일행 함수와 다중행 함수가 있으며, 범위 함수는 모든절에서 사용할 수 있어요 (SELECT절, WHERE절, GROUP BY절, ORDER BY절)
단일행함수
단일행함수란?
정의 데이터 타입이나 목적에 따른 연산을 하기 위한 함수
종류 문자함수 / 숫자함수 / 날짜함수 / 변환함수 / 흐름제어함수
여기서잠깐!
SQL에서 다루는 데이터 타입은 총 3가지예요 → 문자 / 숫자 / 날짜
이를 각기 다른 데이터 타입으로 변환하는 것이 변환함수가 되겠어요.
1. 문자함수
정의 입력값으로 문자형 데이터를 받는 함수
1-1 데이터관리및가공을위한함수
LENGTH() | 바이트 기준 문자 수 | LENGTH(’안녕하세요’) |
CHARACTER_LENGTH(),
CHAR_LENGTH() | 글자 기준 문자 수 | CHAR_LENGTH(’안녕하세요’) |
CONCAT() | 문자열 연결 | CONCAT(’CELL’, ‘PHONE’) = cellphone |
CONAT_WS() | 구분자를 통한 문자열 연결 | CONCAT_WS(’,’, ‘수박’, ‘사과’) = 수박,사과 |
FORMAT( , 0) | 숫자 포매팅 | FORMAT(12345, 0) = 12,345 |
LOWER() | 소문자 변환 | LOWER(’aaa’) |
UPPER() | 대문자 변환 | UPPER(’AAA’) |
예제1 주문 테이블에서 택배 발송을 위해 주소 데이터를 추출하고자 합니다. 주문 주소(order_address)와 우편번호(postal_code)를 콤마(‘, ’)로 이어붙여봅시다.
예제2 상품 테이블에서 sale_price를 추출하고자 합니다. 숫자에 대해 3자리(천단위) 마다 콤마를 넣어서 가독성을 높여봅시다.
1-2 데이터추출을위한함수
SUBSTRING()
SUBSTR() | 문자열의 일부를 추출 | SUBSTR(’비즈니스데이터분석’, 3) = 니
SUBSTR(’비즈니스데이터분석’, 3, 2) = 니스 |
RIGHT() | 문자열의 오른쪽부터 숫자만큼 반환 | RIGHT(’abc’, 2) |
LEFT() | 문자열의 왼쪽부터 숫자만큼 반환 | LEFT(’abc’, 1) |
TRIM() | 문자열의 앞이나 뒤 혹은 양쪽 모두의 특정 문자열을 제거 | TRIM(LEADING, ‘!’ FROM ‘!안녕하세요!’)
TRIM(TAILING, ‘!’ FROM ‘안녕하세요!’)
TRIM(BOTH, ‘!’ FROM ‘!안녕하세요!!’) |
RTRIM() | 문자열의 오른쪽 공백 (스페이스) 제거 | RTRIM(’right ’) |
LTRIM() | 문자열의 왼쪽 공백 (스페이스) 제거 | LTRIM(’ left’) |
RPAD() | 지정한 길이만큼 오른쪽부터 특정 문자로 채움 | RPAD(’HAP’, 6, ‘*’) = HAP*** |
LPAD() | 지정한 길이만큼 왼쪽부터 특정 문자로 채움 | LPAD(’10’, 5, ‘0’) |
REPLACE() | 문자열 데이터 치환 (도메인 주소 변경 등) | REPLACE(BAD, ‘A’, ‘E’) |
SUBSTRING_INDEX() | 특정 문자를 기준으로 SPLIT 하여 해당 값을 분할 | SUBSTRING_INDEX(‘ABC@daum.net’, ‘@’, 1) |
예제 지불 정보 테이블에서 크레딧 카드 비밀번호를 추출하고자 합니다. 이때 개인정보 보호를 위해 뒤에 두 자리를 ** 으로 채워넣고자 합니다
2. 숫자함수
정의 입력값으로 숫자형 데이터를 받는 함수
MOD() | MOD(분자, 분모)에서 분자를 분모로 나눈 나머지 값 | mod(11,3) |
+-*/ | 사칙연산 | +-*/ |
ABS() | 절대값 | ABS(-11) |
CEIL() | 소수점 이하 올림 | CEIL(0.1) |
FLOOR() | 소수점 이하 버림 | FLOOR(1.1) |
ROUND() | 자릿수 기준 반올림 | ROUND(10.01,
0) |
TRUNCATE() | 자릿수 기준 버림 | TRUNCATE(10.9,0)
TRUNCATE(2131,-1) |
GREATEST() | 가장 큰 값을 변환 | GREATEST(20, 44, 39, 20) |
LEAST() | 가장 작은 값을 변환 | LEAST(0, 10, 14, 20) |
예시1 상품코드 vitamin_c_001의 월 구독 상품의 단일 판매당 이윤(sale_price - unit_cost)
3. 날짜함수
정의 입력값으로 날짜형 데이터를 받는 함수
3-1 날짜 가공 및 변환 함수
DATE_FORMAT() | 데이터의 표현 방식 설정
(반환되는 값은 문자!) | DATE_FORMAT(NOW(), '%Y-%m-%d')
DATE_FORMAT(NOW(), '%Y-%m') |
NOW()
CURRENT_TIMESTAMP() | 현재시간 | NOW()
CURRENT_TIMESTAMP() |
DAYNAME() | 날짜의 요일 정보를 이름으로 표현 | DAYNAME(NOW()) |
DAYOFWEEK() | 날짜의 요일 정보를 숫자로 표현 | DAYOFWEEK(NOW())
*1=일요일, 7=토요일 |
LAST_DAY() | 날짜가 속한 달의 마지막 날짜 | LAST_DAY(NOW()) |
YEARWEEK() | 날짜의 년도와 해당 년도의 주차 수를 표현 | YEARWEEK(NOW()) |
YEAR() | 날짜에서 년도 정보 추출 | YEAR(NOW()) |
MONTH() | 날짜에서 월 정보 추출 | MONTH(NOW()) |
DAY() | 날짜에서 일 정보 추출 | DAY(NOW()) |
3-2 기간 관련 함수
ADDDATE()
DATE_ADD() | 날짜에서 n일을 더하는 함수 | DATE_ADD(NOW(), INTERVAL 1 SECOND)
DATE_ADD(NOW(), INTERVAL 1 MINUTE)
DATE_ADD(NOW(), INTERVAL 1 HOUR)
DATE_ADD(NOW(), INTERVAL 1 DAY)
DATE_ADD(NOW(), INTERVAL 1 MONTH)
DATE_ADD(NOW(), INTERVAL 1 YEAR) |
SUBDATE()
DATE_SUB() | 날짜에서 n일을 빼는 함수 | DATE_SUB(NOW(), INTERVAL n DAY) |
ADDTIME() | 기준시간에서 시간을 더하는 함수 | ADDTIME('2022-11-04 14:00:00', '02:30:59') |
SUBTIME() | 기준시간에서 시간을 빼는 함수 | SUBTIME('2022-11-04 14:00:00', '02:30:59') |
DATE_DIFF() | 두 날짜 차이를 일 단위로 계산 | DATEDIFF('2022-11-05','2022-01-01') |
PERIOD_DIFF() | 두 년월 정보의 차이를 월 단위로 계산 | PERIOD_DIFF('202201','202211'); |
TIMEDIFF() | 두 날짜 차이를 시간 단위로 계산 | TIMEDIFF('2022-11-05 16:00:00', '2022-11-05 13:00:00'); |
예제1 지난 6개월동안 신규로 가입한 유저의 아이디
예제2 최근 로그인이 6개월 이전인 유저의 아이디
4. 변환함수
정의 데이터 타입을 변경시키는 함수
CAST | 함수의 입력에 맞는 데이터 타입으로 변환 | CAST(’1111’ AS UNSIGNED) |
DATE | TIMESTAMP를 DATE로 변환 | DATE() |
참고 변환가능한 데이터 타입의 종류
char | date | datetime | time | signed | unsigned |
⇒ 실무에서 쓰일 일이 많지 않아 설명만 간단히 하고 넘어갑니다.
5. 흐름제어함수
정의 조건에 따른 제어
CASE | 조건에 따라서 값을 지정해주는 함수
(여러 조건 및 여러 값도 가능) | CASE
WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
ELSE 값3
END |
IF | 조건에 따라서 참과 거짓 값을 지정해주는 함수 | IF(조건문, 참값, 거짓값) |
IFNULL | NULL 값을 처리하는 함수 | IFNULL(컬럼, null일 경우의 대체값) |
참고 NULL에 대한 이해
•
NULL값과 EMPTY값은 엄연히 다른 것.
•
EMPTY = 빈값도 값은 값
•
NULL은 아예 존재조차 하지 않는 상태를 뜻해요.
→ EMPTY와 NULL값의 차이를 이해해 보아요.
예제 회원 분류 등급 체계가 기존 [VVIP-VIP-Family-일반] 에서 [VVIP-VIP-GOLD-SILVER] 로 변경되었습니다.
참고 AS(alias)의 이해
추출한 데이터의 칼럼명을 새로이 지정할 수 있어요. 정말 자주 사용되는 구문이니 꼭 기억해 두도록 해요!
다중행함수
다중행함수란?
정의 여러 데이터의 결합이나 배치 연산을 위한 함수
종류 집계함수 / 그룹함수 / 윈도우함수
1. 집계함수
정의 여러 행들이 결합되어 연산되는 함수
COUNT(*) | NULL 값을 포함한 모든 행의 수를 출력 |
COUNT | 표현식의 값이 NULL을 제외한 모든 행의 수를 출력 |
SUM | 표현식의 NULL 값을 제외한 합계를 출력 |
AVG | 표현식의 NULL 값을 제외한 평균을 출력 |
MAX | 표현식의 최대값을 출력 |
MIN | 표현식의 최소값을 출력 |
STDDEV | 표현식의 표준편차를 출력 |
VARIAN | 표현식의 분산을 출력 |
예제1 전체 활성고객 / 의 수는?
예제2 비활성 고객이면서 네이버를 통해 소셜로그인 한 고객의 수는?
2. 그룹함수
데이터를 그룹화 하는 GROUP BY 함수에 대해 알아봅시다. 나머지는 실무에서 쓰일 일이 거의 없어요.
예시1 회원 등급별 고객의 수는?
3. 윈도우함수
실무에서 전혀 다루지 않아 바로 넘어갑니다. 자격증에서는 비중이 꽤 되니 추후 다시 공부하기를 바라요.
SQL 문법 총정리
구문 작성 원칙과 SQL의 실제 연산의 순서는 달라요
구문 작성 순서
1.
SELECT
2.
FROM
3.
WHERE
4.
GROUP BY
5.
HAVING
6.
ORDER BY
실제 연산 순서
1.
FROM (발췌 대상 테이블을 참조)
2.
WHERE (발췌 대상데이터에 조건 걸기)
3.
GROUP BY (행들을 소그룹화)
4.
HAVING (그룹 조건에 맞는 것만 출력)
5.
SELECT (데이터를 출력)
6.
ORDER BY (데이터를 정렬)