[SQL] 함수 (통계 / 수학 / 날짜 / 시간 / 문자 / 기타)

Gabriela·2023년 7월 10일

sql

[SQL] 데이터베이스

목록 보기

8/13

‣ 함수

모든 함수에서 NULL값은 제외한다.

‣ 통계함수

(집계함수)

1. SUM(표현식)   : 합계
2. AVG(표현식)   : 평균
3. MAX(표현식)   : 최댓값
4. MIN(표현식)   : 최솟값
5. COUNT(표현식) : 갯수

‣ 수학함수

POWER(A, B) : A의 B제곱
SQRT(A) : A의 제곱근(루트 A)
ABS(A) : A의 절대값
MOD(A, B) : A를 B로 나눈 나머지
- 어떤 값을 순환 시킬 때 많이 사용됨
SIGN(A) : A가 양수이면 1, 음수이면 -1, 0이면 0을 반환
CEIL(A) : 실수 A를 정수로 올림
FLOOR(A) : 실수 A를 정수로 내림
TRUNC(A, [DIGIT]) : 실수 A를 DIGIT 자릿수로 절사, DIGIT 생략하면 정수로 절사
ROUND(A, [DIGIT]) : 실수 A를 DIGIT 자릿수로 반올림, DIGIT 생략하면 정수로 반올림

‣ 날짜 / 시간 함수

한국 시간 : UTC 기준 +9시간

SYSDATE : 오라클이 설치된 서버의 현재 날짜와 시간 (DATE 타입)
SYSTIMESTAMP : 오라클이 설치된 서버의 현재 날짜와 시간 (TIMESTAMP 타입)
CURRENT_DATE : SESSIONTIMEZONE의 현재 날짜와 시간 (DATE 타입)
CURRENT_TIMESTAMP : SESSIONTIMEZONE의 현재 날짜와 시간 (TIMESTAMP 타입)
EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } FROM DATE )
: 지정된 DATE에서 필요한 정보 추출
NEXT_DAY(DATE, { 일 | 월 | 화 | 수 | 목 | 금 | 토 })
: 지정된 DATE의 다음 WEEKDAY(일~월) 반환
LAST_DAY(DATE) : 지정된 DATE의 해당 월 말일 반환
ADD_MONTHS(DATE, N) : 지정된 DATE의 N개월 후 날짜
MONTHS_BETWEEN(DATE1, DATE2) : 두 날짜(최근 DATE1, 이전 DATE2) 사이에 경과한 개월 수

‣ 문자 함수

UPPER(STRING) : STRING을 모두 대문자로 변환
LOWER(STRING) : STRING을 모두 소문자로 변환
INITCAP(STRING) : STRING의 첫 글자는 대문자 나머지 글자는 소문자로 변환
LENGTH(STRING) : STRING의 글자 수
CONCAT(STRING1, STRING2) : STRING1, STRING2를 연결
SUBSTR(STRING, BEGIN, LENGTH) : STRING의 BEGIN 위치부터 LENGTH만큼 가져옴
INSTR(STRING, FIND) : STRING에서 FIND의 위치를 가져옴
LPAD(STRING, WIDTH, CHAR) : WIDTH에 맞춰 STRING의 왼쪽에 CHAR를 채움
RPAD(STRING, WIDTH, CHAR) : WIDTH에 맞춰 STRING의 오른쪽에 CHAR를 채움
LTRIM(STRING, [CHAR]) : STRING의 왼쪽 CHAR 제거, CHAR 생략 시 공백 제거
RTRIM(STRING, [CHAR]) : STRING의 오른쪽 CHAR 제거, CHAR 생략 시 공백 제거
TRIM(STRING) : STRING의 양쪽 공백 제거

‣ 기타 함수

1. 순위 구하기 RANK

RANK() OVER(ORDER BY 칼럼 ASC) : 낮은 값이 1등
RANK() OVER(ORDER BY 칼럼 DESC) : 높은 값이 1등
RANK 는 동점자 처리를 함 : 같은 값은 같은 순위

SELECT EMPLOYEE_ID
     , SALARY
     , RANK() OVER(ORDER BY SALARY DESC) AS 연봉순위 -- 연봉 내림차순 정렬 후 순위 매기기(동점자는 같은 순위를 가짐)
  FROM EMPLOYEES;

SELECT EMPLOYEE_ID
     , HIRE_DATE
     , RANK() OVER(ORDER BY HIRE_DATE) AS 입사순위   -- 고용일 오름차순 정렬 후 순위 매기기
  FROM EMPLOYEES;

2. 행 번호 구하기 ROW_NUMBER

ROW_NUMBER() OVER (ORDER BY 칼럼 ASC)
ROW_NUMBER() OVER (ORDER BY 칼럼 DESC)

SELECT EMPLOYEE_ID
     , SALARY
     , ROW_NUMBER() OVER(ORDER BY SALARY DESC)  -- 연봉 내림차순 정렬 후 번호 매기기(동점자 처리 방식 없음)
  FROM EMPLOYEES;

Tip ) 실생활에서 기능 구현 시, RANK보다 LOW를 더 자주 사용

3. 암호화 함수

SELECT STANDARD_HASH('1111', 'SHA1')    -- 암호화 알고리즘 SHA1
     , STANDARD_HASH('1111', 'SHA256')  -- 암호화 알고리즘 SHA256
     , STANDARD_HASH('1111', 'SHA384')  -- 암호화 알고리즘 SHA384
     , STANDARD_HASH('1111', 'SHA512')  -- 암호화 알고리즘 SHA512
     , STANDARD_HASH('1111', 'MD5')     -- 암호화 알고리즘 MD5
  FROM DUAL;

4. 분기 처리 함수 (DECODE 함수)

조인(Join) 사용을 줄일 수 있음

SELECT EMPLOYEE_ID
     , DEPARTMENT_ID
     , DECODE(DEPARTMENT_ID
        , 10, 'Administration'
        , 20, 'Marketing'
        , 30, 'Purchasing'
        , 40, 'Human Resources'
        , 50, 'Shipping'
        , 60, 'IT') AS DEPARTMENT_NAME
  FROM EMPLOYEES;

5. 분기 처리 표현식

SELECT EMPLOYEE_ID 
     , DEPARTMENT_ID 
     , CASE
         WHEN DEPARTMENT_ID = 10 THEN 'Administration'
         WHEN DEPARTMENT_ID = 20 THEN 'Marketing'
         WHEN DEPARTMENT_ID = 30 THEN 'Purchasing'
         WHEN DEPARTMENT_ID = 40 THEN 'Human Resources'
         WHEN DEPARTMENT_ID = 50 THEN 'Shipping'
         WHEN DEPARTMENT_ID = 60 THEN 'IT'
        ELSE 'Unknown'
       END AS DEPARTMENT_NAME
  FROM EMPLOYEES;

(⇒ 이 두개가 오라클에서 자바의 IF 함수와 같은 역할이라고 보면 됨)

⇢통계함수 예제

SUM

-- 1. 사원 테이블에서 전체 사원의 연봉 합계 조회하기
SELECT SUM(SALARY) AS 연봉합계
  FROM EMPLOYEES;

AVG

-- 2. 사원 테이블에서 전체 사원의 커미션퍼센트의 평균 조회하기
-- 커미션이 없는 사원은 제외하고 조회하기 
SELECT AVG(COMMISSION_PCT) AS 커미션퍼센트평균
  FROM EMPLOYEES
 WHERE COMMISSION_PCT IS NOT NULL; -- 커미션이 없는 사원은 제외하는 조건식이지만 사실 필요하지 않다. 
 -- AVG 함수는 자체적으로 NULL을 제외한다.

MAX (MIN)

-- 3. 사원 테이블에서 전체 사원의 최대 연봉 조회하기
SELECT MAX(SALARY) AS 최대연봉
  FROM EMPLOYEES;

-- 4. 사원 테이블에서 전체 사원의 최대 커미션 조회하기
-- 커미션 = 연봉 * 커미션퍼센트
SELECT MAX(SALARY * COMMISSION_PCT) AS 최대커미션
  FROM EMPLOYEES;

-- 5. 사원 테이블에서 전체 사원 중 가장 나중에 입사한 사원의 입사일 조회하기
SELECT MAX(HIRE_DATE) AS 최근고용일
  FROM EMPLOYEES;

COUNT

전체 개수를 구할 때
두 구문 모두 괜찮지만 2번을 조금 더 권장

-- 6. 전체 사원 수 조회하기 
--(전체 개수를 구할 때)
-- 1) NOT NULL이 확실한 칼럼(대표적으로 PK)으로 개수를 구한다.
SELECT COUNT(EMPLOYEE_ID) AS 전체사원수
  FROM EMPLOYEES;

-- 2) 모든 칼럼으로 개수를 구한다. 
SELECT COUNT(*) AS 전체사원수
  FROM EMPLOYEES;

DISTINCT

중복제거 사용

--7. 사원들이 근무하는 부서의 개수 조회하기
SELECT COUNT(DISTINCT DEPARTMENT_ID) AS 부서수
  FROM EMPLOYEES;

⇢수학함수 예제

1. 절대값

SELECT ABS(-5)  -- -5의 절대값 5
  FROM DUAL;

2. 제곱근(루트)

SELECT SQRT(25)  -- 루트 25
  FROM DUAL;

3. 부호 판별

SELECT SIGN(5)   -- 양수는 1
     , SIGN(-5)  -- 음수는 -1
     , SIGN(0)   -- 0은 0
  FROM DUAL;

4. 제곱

SELECT POWER(2, 10)  -- 2의 10제곱 1024
  FROM DUAL;

5. 나머지

어떤 값을 순환 시킬 때 많이 사용됨

SELECT MOD(5, 3)  -- 5를 3으로 나눈 나머지 2
  FROM DUAL;

6. 정수로 올림

SELECT CEIL(1.1)   -- 2  (1.1보다 큰 정수)
     , CEIL(-1.1)  -- -1 (-1.1 보다 큰 정수)
  FROM DUAL;

7. 정수로 내림

SELECT FLOOR(1.9)   -- 1  (1.9보다 작은 정수)
     , FLOOR(-1.9)  -- -2 (-1.9보다 작은 정수)
  FROM DUAL;

8. 원하는 자릿수로 반올림

SELECT ROUND(123.456)      -- 123    (정수로 반올림)
     , ROUND(123.456, 1)   -- 123.5  (소수 1자리로 반올림)
     , ROUND(123.456, 2)   -- 123.46 (소수 2자리로 반올림)
     , ROUND(123.456, -1)  -- 120    (일의 자리에서 반올림)
     , ROUND(123.456, -2)  -- 100    (십의 자리에서 반올림)
  FROM DUAL;

9. 원하는 자릿수로 절사

SELECT TRUNC(123.456)      -- 123    (정수로 절사)
     , TRUNC(123.456, 1)   -- 123.4  (소수 1자리로 절사)
     , TRUNC(123.456, 2)   -- 123.45 (소수 2자리로 절사)
     , TRUNC(123.456, -1)  -- 120    (일의 자리에서 절사)
     , TRUNC(123.456, -2)  -- 100    (십의 자리에서 절사)
  FROM DUAL;

Tip
- 원단위 절사
- 1의자리 절사
- 위 두가지가 실생활에서 제일 많이 쓰임

⇢날짜 / 시간 함수 예제

1. 현재 날짜 및 시간

[SYSDATE / SYSTIMESTAMP / SESSIONTIMEZONE / CURRENT ]
한국 시간 : UTC 기준 +9시간

-- 오라클이 설치된 서버 기준 시간
SELECT SYSDATE       -- DATE 형식
     , SYSTIMESTAMP  -- TIMESTAMP 형식
  FROM DUAL;

-- 세션타임존 기준 시간
SELECT SESSIONTIMEZONE
     , CURRENT_DATE       -- DATE 형식
     , CURRENT_TIMESTAMP  -- TIMESTAMP 형식
  FROM DUAL;

2. 날짜를 원하는 형식으로 조회하기

[ TO_CHAR(SYSDATE, ‘ ‘ ) / TO_CHAR(SYSTIMESTAMP, ‘ ‘ ) ]
[ ’YYYY-MM-DD’ / ‘AM HH : MI : SS’ / ‘HH24 : MI : SS.FF3’ ]
TO_DATE ⇒ 원하는 형식 조회가 아닌 해석

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD AM HH:MI:SS')
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
     , TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3')  -- 밀리초(천분의 1초) 포함
  FROM DUAL;

3. DATE 형식의 날짜 연산

[ TO_CHAR(SYSDATE + , ‘ ‘) ]
날짜 지정 시 시간 지정을 안 하면 자정이 됨 = 0시
경과한 일수 사용 서비스 웹 구현
- 예시) 비밀번호 변경 창

--    1) 1일을 숫자 1로 처리한다.
--    2) 1=1일, 1/24=1시간, 1/24/60=1분, 1/24/60/60=1초
SELECT TO_CHAR(SYSDATE + 1,          'YYYY-MM-DD AM HH:MI:SS')  -- 1일 후
     , TO_CHAR(SYSDATE + 1/24,       'YYYY-MM-DD AM HH:MI:SS')  -- 1시간 후
     , TO_CHAR(SYSDATE + 1/24/60,    'YYYY-MM-DD AM HH:MI:SS')  -- 1분 후
     , TO_CHAR(SYSDATE + 1/24/60/60, 'YYYY-MM-DD AM HH:MI:SS')  -- 1초 후
  FROM DUAL;

SELECT SYSDATE - TO_DATE('23/07/01', 'YY/MM/DD')
     , TRUNC(SYSDATE - TO_DATE('23/07/01', 'YY/MM/DD'))  -- 경과한 일수
  FROM DUAL;

4. TIMESTAMP 형식의 날짜 연산

--    1) INTERVAL 키워드를 이용한다.
--    2) YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 단위를 사용한다.
SELECT SYSTIMESTAMP + INTERVAL '1' YEAR   -- 1년 후
     , SYSTIMESTAMP + INTERVAL '1' MONTH  -- 1개월 후
     , SYSTIMESTAMP + INTERVAL '1' DAY    -- 1일 후
     , SYSTIMESTAMP + INTERVAL '1' HOUR   -- 1시간 후
     , SYSTIMESTAMP + INTERVAL '1' MINUTE -- 1분 후
     , SYSTIMESTAMP + INTERVAL '1' SECOND -- 1초 후
  FROM DUAL;

SELECT SYSTIMESTAMP - TO_TIMESTAMP('23/07/01', 'YY/MM/DD')  -- 경과한 기간이 TIMESTAMP 형식으로 반환
     , EXTRACT(DAY FROM SYSTIMESTAMP - TO_TIMESTAMP('23/07/01', 'YY/MM/DD'))  -- 경과한 기간에서 일수를 추출
  FROM DUAL;

5. 필요한 단위 추출하기

SELECT EXTRACT(YEAR FROM SYSDATE)   -- 년
     , EXTRACT(MONTH FROM SYSDATE)  -- 월
     , EXTRACT(DAY FROM SYSDATE)    -- 일
     , EXTRACT(HOUR FROM SYSTIMESTAMP)   -- 시, UTC(표준시) 기준
     , EXTRACT(HOUR FROM SYSTIMESTAMP)+9 -- 시, Asia/Seoul 기준
     , EXTRACT(MINUTE FROM SYSTIMESTAMP) -- 분
     , EXTRACT(SECOND FROM SYSTIMESTAMP) -- 초
     , TRUNC(EXTRACT(SECOND FROM SYSTIMESTAMP))
     , TO_CHAR(SYSDATE, 'YYYY')  -- TO_CHAR 함수를 추출용도로 사용
  FROM DUAL;

6. 요일을 기준으로 특정 날짜 구하기

SELECT NEXT_DAY(SYSDATE, '수')   -- 다음 수요일
     , NEXT_DAY(SYSDATE-8, '수') -- 이전 수요일(SYSDATE-7이 아님을 주의)
  FROM DUAL;

7. N개월 전후 날짜 구하기

SELECT ADD_MONTHS(SYSDATE, 1)   -- 1개월 후
     , ADD_MONTHS(SYSDATE, -1)  -- 1개월 전
     , ADD_MONTHS(SYSDATE, 5 * 12)  -- 5년 후
  FROM DUAL;

8. 경과한 개월 수 구하기

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('23/01/01', 'YY/MM/DD'))
  FROM DUAL;

⇢문자함수 예제

1. 대소문자 변환하기 / 2. 글자 수 / 3. 바이트 수

영문 글자 수 : 한 글자에 1바이트
한글 글자 수 : 한 글자에 2~3바이트

-- 1. 대소문자 변환하기
SELECT UPPER(EMAIL)    -- 모두 대문자
     , LOWER(EMAIL)    -- 모두 소문자
     , INITCAP(EMAIL)  -- 첫 글자만 대문자, 나머지는 소문자
  FROM EMPLOYEES;

-- 2. 글자 수 
SELECT FIRST_NAME
     , LENGTH(FIRST_NAME)
  FROM EMPLOYEES;

-- 3. 바이트 수
SELECT FIRST_NAME
     , LENGTHB(FIRST_NAME)
  FROM EMPLOYEES;

4. 연결하기 `||` `CONCAT`

CONCATENATE 를 줄여서 CONCAT 함수
인수 3개 이상은 CONCAT 함수 여러 개로 해결한다. (But, MySQL 은 그냥 CONCAT(A, B, C) 처럼 이어서 3개를 넣을 수도 있음.)

--    1) || 연산자 (오라클 전용이므로 다른 DB에서는 오류가 난다.)
--    2) CONCAT 함수
--       CONCAT(A, B) : 인수를 2개만 전달할 수 있다.
--       CONCAT(CONCAT(A, B), C) : 인수 3개 이상은 CONCAT 함수 여러개로 해결한다.
SELECT *
  FROM EMPLOYEES
 WHERE PHONE_NUMBER LIKE CONCAT('515', '%');

SELECT *
  FROM EMPLOYEES
 WHERE EMAIL LIKE CONCAT(CONCAT('%', 'A'), '%');  -- A를 포함('%' || 'A' || '%')

5. (문자열)일부만 반환하기 `SUPSTR`

6. 특정 문자의 위치 반환하기 `INSTR`

-- 5. 일부만 반환하기
SELECT SUBSTR(PHONE_NUMBER, 1, 3)  -- 전화번호 1번째 글자부터 3글자를 반환 (첫 3글자)
     , SUBSTR(PHONE_NUMBER, 5)     -- 전화번호 5번째 글자부터 끝까지 반환
  FROM EMPLOYEES;

-- 6. 특정 문자의 위치 반환하기
--    문자의 위치는 1부터 시작한다.
--    못 찾으면 0을 반환한다.
SELECT EMAIL
     , INSTR(EMAIL, 'A')
  FROM EMPLOYEES;

7. 바꾸기 `REPLACE`

SELECT EMAIL
     , REPLACE(EMAIL, 'A', '$')  -- 모든 A를 찾아서 $로 바꾸기
  FROM EMPLOYEES;

8. 채우기 `LPAD` / `RPAD`

채우기 : 중간 마스킹은 안 됨

--    1) LPAD(표현식, 전체폭, 채울문자) : 왼쪽
--    2) RPAD(표현식, 전체폭, 채울문자) : 오른쪽
SELECT DEPARTMENT_ID
     , LPAD(DEPARTMENT_ID, 3, 0)
     , EMAIL
     , RPAD(SUBSTR(EMAIL, 1 , 2), 5, '*')
  FROM EMPLOYEES;

9. 공백 제거 `LTRIM` / `RTIRIM` / `TRIM`

화이트스트림 현상이 나타나는 경우 유용
- 화이트스트림 : 불필요한 공백이 생기는 현상
공백 제거 : 중간 공백 제거는 안 됨

SELECT '[' || LTRIM('     HELLO     WORLD     ') || ']'  -- 왼쪽 공백만 제거
     , '[' || RTRIM('     HELLO     WORLD     ') || ']'  -- 오른쪽 공백만 제거
     , '[' ||  TRIM('     HELLO     WORLD     ') || ']'  -- 양쪽 공백 제거
  FROM DUAL;

Gabriela

개발이 세상에서 제일 재밌어요

이전 포스트

[SQL] 형변환 함수 / NULL 처리 함수 / 와일드카드

다음 포스트

[SQL] 함수 (통계 / 수학 / 날짜 / 시간 / 문자 / 기타)

[SQL] 데이터베이스

‣ 함수

‣ 통계함수

(집계함수)

‣ 수학함수

‣ 날짜 / 시간 함수

‣ 문자 함수

‣ 기타 함수

1. 순위 구하기 RANK

2. 행 번호 구하기 ROW_NUMBER

3. 암호화 함수

4. 분기 처리 함수 (DECODE 함수)

5. 분기 처리 표현식

⇢통계함수 예제

SUM

AVG

MAX (MIN)

COUNT

DISTINCT

⇢수학함수 예제

1. 절대값

2. 제곱근(루트)

3. 부호 판별

4. 제곱

5. 나머지

6. 정수로 올림

7. 정수로 내림

8. 원하는 자릿수로 반올림

9. 원하는 자릿수로 절사

⇢날짜 / 시간 함수 예제

1. 현재 날짜 및 시간

2. 날짜를 원하는 형식으로 조회하기

3. DATE 형식의 날짜 연산

4. TIMESTAMP 형식의 날짜 연산

5. 필요한 단위 추출하기

6. 요일을 기준으로 특정 날짜 구하기

7. N개월 전후 날짜 구하기

8. 경과한 개월 수 구하기

⇢문자함수 예제

1. 대소문자 변환하기 / 2. 글자 수 / 3. 바이트 수

4. 연결하기 || CONCAT

5. (문자열)일부만 반환하기 SUPSTR

6. 특정 문자의 위치 반환하기 INSTR

7. 바꾸기 REPLACE

8. 채우기 LPAD / RPAD

9. 공백 제거 LTRIM / RTIRIM / TRIM

[SQL] 형변환 함수 / NULL 처리 함수 / 와일드카드

[SQL] 조인 / 서브쿼리 / WITH문

0개의 댓글

4. 연결하기 `||` `CONCAT`

5. (문자열)일부만 반환하기 `SUPSTR`

6. 특정 문자의 위치 반환하기 `INSTR`

7. 바꾸기 `REPLACE`

8. 채우기 `LPAD` / `RPAD`

9. 공백 제거 `LTRIM` / `RTIRIM` / `TRIM`