2021.2.16 SQL 함수
SQL 함수
1. 문자 함수
- LOWER('문자') : 소문자로 변환
- UPPER('문자') : 대문자로 변환
- INITCAP('문자') : 첫글자만 대문자
- CONCAT('문자1', '문자2') : 문자 연결
- LENGTH('문자') : 문자의 길이, 문자타입 컬럼명이면 해당 컬럼 속성값의 문자길이. 줄바꿈도 1로 계산
- SUBSTR(컬럼명, 시작위치, 갯수(생략가능)) : 속성값에서 시작위치부터 갯수만큼의 문자 선택, 시작위치가 음수면 오른쪽부터 계산
- INSTR(컬럼명, '문자', 시작위치, 몇번째 것) : 문자 위치 검색, 찾는 문자가 두개 이상 있는 경우에는 첫번째 위치 검색
- LPAD(컬럼, 숫자, 'x') : 숫자만큼의 공간에 컬럼 내용 채우고 남은건 x로 채운다.
- RPAD(컬럼, 숫자, 'x') : 숫자만큼의 공간에 컬럼 내용 채우고 남은건 x로 채운다.
- TRIM(컬럼명) : 앞뒤 공백을 잘라줌, 문자 중간의 공백은 유지
- REPLACE(컬럼명, '문자1', '문자2') : 문자1을 문자2로 변경, DB 내용은 변경없이 출력 내용만 변경된다.
2. 숫자 함수
- ROUND(컬럼명, 숫자) : 반올림, 숫자가 0보다 크면 소수자릿수를 의미, 0보다 작으면 정수자릿수를 의미
- TRUNC(컬럼명, 숫자) : 버림, 숫자가 0보다 크면 소수자릿수를 의미, 0보다 작으면 정수자릿수를 의미
- MOD(컬럼명, 숫자) : 속성값을 숫자로 나눈 값의 나머지
3. 날짜 함수
- SYSDATE : 오늘자 연/월/일
- 세기 년 월 일 시 분 초 : 7가지 정보 -> DATE 타입
- 날짜 타입의 컬럼은 연산 가능. 1/24/60 = 1분을 의미 -> 1/24/(60/10) = 10분
- 날짜 + 숫자 : 일자에 숫자가 연산이 된다.
- ADD_MONTHS(컬럼명, 숫자) : 월에 숫자가 더해진다. 음수넣으면 감산 가능
- LAST_DAY(컬럼명) : 해당 연, 월의 마지막 날을 선택
- 날짜1 - 날짜2 : 두 날짜 사이의 일수 계산
- MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜 사이의 월수 계산
- EXTRACT(YEAR(or MONTH or DAY) FROM 컬럼명) : 컬럼에서 연 or 월 or 일만 추출
- NEXT_DAY(날짜, 값) : 날짜 이후에 값에 해당하는 날을 출력(일 월 화 수 목 금 토 -> 1 2 3 4 5 6 7)
- ROUND(날짜, 'CC'(or 'YEAR' or 'MONTH' or 'DAY')) : 반올림
4. 변환 함수
- TO_CHAR(숫자, '숫자패턴') : 날짜나 숫자를 문자로 변환
- TO_NUMBER : 문자를 숫자로 변환
- TO_DATE : 문자를 날짜로 변환
<날짜 포맷의 단위>
- YEAR : 연도를 문자로 표기
- MONTH : 월을 문자로 표시
- DAY : 요일을 문자로 표시
- YYYY : 연에 대한 네자리 수 숫자값
- YY : 연에 대한 두자리 수 숫자값
- MM : 월에 대한 두자리 수 숫자값
- DD : 일에 대한 두자리 수 숫자값
- HH : 시간에 대한 두자리 수 숫자값, HH24 는 24시 체계
- MI : 분에 대한 두자리 수 숫자값
- SS : 초에 대한 두자리 수 숫자값
- DY : 요일을 표기하되 '요일'자 빼고 표기
<숫자 포맷의 단위>
- 9 : 숫자로 표현
- 0 : 0으로 출력
- L : 지역화폐기호 출력
- . : 소수점 출력
- , : 천의 자리 표시 출력
ex) SELECT TO_CHAR(컬럼명, 'HH24') AS HOUR, COUNT(TO_CHAR(컬럼명, 'HH24')) AS COUNT FROM 테이블명 GROUP BY TO_CHAR(컬럼명, 'HH24') HAVING TO_CHAR(컬럼명, 'HH24') >= 9 AND TO_CHAR(컬럼명, 'HH24') <= 19 ORDER BY TO_CHAR(컬럼명, 'HH24');
/* 컬럼명이 날짜 시간 다 나와있는 것이고 그것을 시간으로만 구분해서 숫자 구하고 싶은 경우 */
TO_CHAR 함수 사용법 참조 블로그
5. 기타 함수
- NVL(컬럼명, 값) : NULL인 경우 값을 출력, 값은 숫자여야 한다. ISNULL이라고도 한다
- NVL2(컬럼명, 값1, 값2) : NULL이 아니면 값1 출력, NULL이면 값2 출력, 값은 숫자아니여도 가능하다.
- NULLIF(표현식1, 표현식2) : 표현식1이 표현식2와 같으면 NULL을 리턴, 같지 않으면 표현식1을 리턴
- COALESCE(표현식1, 표현식2, ...) : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 리턴, 모두 NULL이면 NULL
- RANK() OVER(ORDER BY 컬럼명) : 정렬에 따라서 순위를 나타내준다.
6. 그룹 함수(통계함수)
다중 행 함수 : 여러 행의 값을 받아서 하나의 결과값 반환
NULL 값은 통계에서 제외된다.
- AVG : 평균
- COUNT : 행의 수, COUNT(*)로 사용하면 NULL값도 참조
- MAX : 최대값
- MIN : 최소값
- STDDEV : 표준편차
- SUM : 합계
- VARIANCE : 분산
cf) SELECT 절에 그룹함수 사용시, 일반컬럼은 반드시 GROUP BY 절에 기술해야 한다.
7. CASE 문
- 쿼리 안의 조건문
- CASE 컬럼명 WHEN 조건 THEN 내용 END
- CASE WHEN 컬럼명 조건 THEN 내용 END