[모두의 SQL] 4장. 함수: 함수로 데이터 가공

• 함수: 사용자가 입력값 X 삽입 시 정해놓은 출력값 Y 출력
• 오라클 데이터베이스 시스템의 함수
  • 문자/숫자/날짜 값 조작 가능
  • 각 데이터 타입끼리 변환 가능 (단일 행 함수 기능)
  • 복수의 행을 조합해 그룹당 하나의 결과로도 출력 가능 (그룹 함수 기능)
• 함수 사용 목적
  • 데이터 값 계산, 조작 (단일 행 함수)
  • 행의 그룹에 대해 계산, 요약 (그룹 함수)
  • 열의 데이터 타입 변환, 데이터 타입 상호 변환
• 단일 행 함수: 한번에 하나의 데이터를 처리하는 함수
• 그룹 함수
  • 여러 건의 데이터를 동시에 처리해 해당 그룹에 해당하는 결과를 반환
  • GROUP BY 절과 함께 쓰이며, 요약된 정보를 출력해야할 때 유용
• 데이터 타입
  • 문자 CHAR(n)
    • n 크기만큼 고정 길이의 문자 타입 저장
    • 최대 2000바이트까지 저장 가능
  • 문자 VARCHAR2(n)
    • n 크기만큼 가변 길이의 문자 타입 저장
    • 최대 4000바이트까지 저장 가능
  • 숫자 NUMBER(p,s)
    • 숫자 타입 저장 (p: 정수 자릿수, s: 소수 자릿수)
  • 날짜 DATE
    • 날짜 타입 저장
    • 9999년 12월 31일까지 저장 가능
• 단일 행 함수의 종류
  • 문자 타입 함수: 문자 입력 → 문자/숫자 반환
  • 숫자 타입 함수: 숫자 입력 → 숫자 반환
  • 날짜 타입 함수
    • 날짜에 대해 연산
    • 숫자를 반환하는 MONTHS_BETWEEN 함수를 제외한 모든 날짜 타입 함수는 날짜 값 반환
  • 변환 타입 함수: 임의의 데이터 타입의 값 → 다른 데이터 타입 변환
  • 일반 함수: 그 외 NVL, DECODE, CASE WHEN, 순위 함수 등
• 단일 행 함수의 특징
  • 각 행에 대해 수행
  • 데이터 타입에 맞는 함수를 사용해야 함
  • 행별로 하나의 결과를 반환
  • SELECT, WHERE, ORDER BY 절 등에서 사용 가능
  • 함수 속의 함수처럼 중첩 사용 가능
  • 중첩 사용할 경우 가장 안쪽(하위) 단계에서 바깥쪽(상위) 단계 순으로 진행
• 문자타입 함수 종류
  • LOWER: 값을 소문자로 변환
  • UPPER: 값을 대문자로 변환
  • INITCAP: 첫번째 글자만 대문자로 변환
  • SUBSTR: 문자열 중 일부분 선택
  • REPLACE: 특정 문자열을 찾아 변환
  • CONCAT: 두 문자열 연결 (|| 연산자와 같음)
  • LENGTH: 문자열의 길이를 구함
  • INSTR: 명명된 문자의 위치를 구함
  • LPAD: 왼쪽부터 특정 문자로 자리를 채움
  • RPAD: 오른쪽부터 특정 문자로 자리를 채움
  • LTRIM: 주어진 문자열의 왼쪽 문자를 지움
  • RTRIM: 주어진 문자열의 오른쪽 문자를 지움
• 숫자타입 함수 종류
  • ROUND: 숫자 반올림, 0이 소수점 첫째 자리
  • TRUNC: 숫자 절삭, 0이 소수점 첫째 자리
  • MOD: 나누기 후 나머지를 구함
  • CEIL: 숫자를 정수로 올림
  • FLOOR: 숫자를 정수로 내림
  • SIGN: 양수(1), 음수(-1), 0인지를 구분해 출력
  • POWER: 거듭제곱 출력
  • SQRT: 제곱근 출력
• 날짜 연산 규칙
  • Date + Number
    • 날짜에 일수를 더함
    • Date 반환
  • Date - Number
    • 날짜에서 일수를 뺌
    • Date 반환
  • Date - Date
    • 날짜에서 날짜 뺌
    • 일수 반환
  • Date + Number / 2
    • 날짜에 시간을 더할 때는 시간을 24로 나누어서 날짜에 더함
    • Date 반환
• 날짜 함수 종류
  • MONTHS_BETWEEN: 두 날짜 사이의 월 수 계산
  • ADD_MONTHS: 월을 날짜에 더함
  • NEXT_DAY: 명시된 날짜부터 돌아오는 요일에 대한 날짜 출력
  • LAST_DAY: 월의 마지막 날 계산
  • ROUND: 날짜를 가장 가까운 연도 또는 월로 반올림
  • TRUNC: 날짜를 가장 가까운 연도 또는 월로 절삭
• 데이터 타입 변환은 오라클 데이터베이스 시스템에 의해 자동으로(암시적으로) 혹은 사용자에 의해서 수동으로(명시적으로) 실행될 수 있음
• 자동 데이터 타입 변환 유형
  • VARCHAR2/CHAR → NUMBER(숫자)
  • VARCHAR2/CHAR → DATE(날짜)
  • NUMBER → VARCHAR2(문자)
  • DATE → VARCHAR2(문자)
• 수동 데이터 타입 변환 함수
  • TO_CHAR: 숫자/문자/날짜 값을 지정 형식의 VARCHAR2 타입으로 변환
  • TO_NUMBER: 문자를 숫자타입으로 변환
  • TO_DATE: 날짜를 나타내는 문자열을 지정 형식의 날짜 타입으로 변환
• 날짜 지정 형식
  • CC: 세기
  • YYYY or YYY or YY or Y: 연도
  • Y,YYY: 콤마가 있는 연도
  • YEAR: 문자로 표현된 연도
  • BC or AD: BC/AD 지시자
  • Q: 분기
  • MM: 두 자리 값의 월
  • MONTH: 아홉 자리를 위해 공백을 추가한 월 이름
  • MON: 세 자리 약어로 된 월 이름 (영문 설정일 경우)
  • RM: 로마 숫자 월
  • WW or W: 연/월의 주
  • DDD or DD or D: 연/월/주의 일
  • DAY: 아홉자리를 위해 공백을 추가한 요일 이름
  • DY: 세 자리 약어로 된 요일 이름 (영문 설정일 경우)
  • J: Julian Day, BC 4713년 12월 31일 이후의 요일 수
• 시간 지정 형식
  • AM/PM: 오전 또는 오후 표시
  • HH/HH12 or HH24: 시간 표현(1~12시 또는 0~23시)
  • MI: 분(0~59)
  • SS: 초(0~59)
• 기타 형식
  • / . - : 사용 문자를 출력 결과에 표현
  • “문자”: 큰따옴표 안의 문자를 출력 결과에 표현
• 숫자 지정 형식
  • 9: 9로 출력 자릿수 지정
  • 0: 자릿수만큼 0을 출력
  • $: 달러 기호
  • L: 지역 화폐 기호(원)
  • .: 명시한 위칭 소수점
  • ,: 명시한 위치에 쉼표
• NULL 값의 특징
  • 할당되지 않았거나 알려져 있지 않아 적용이 불가능한 값
  • 0이나 공백과는 다름
  • NULL값을 포함하는 산술 연산의 결과는 NULL
• 일반 함수
  • NVL: null 값을 어떤 특정 값으로 변환하는 데 사용
  • NVL2: 열 이름1이 null이 아니면 열 이름2를 출력, null이면 열 이름3을 출력
  • DECODE: 특정 조건을 만족하는 경우에 값을 변경하여 출력하고자 할 때 유용
  • CASE: 복잡한 조건식을 여러 개 적용해야할 때 유용
  • RANK: 공통 순위를 출력하되 공통 순위만큼 건너뛰어 다음 순위 출력
  • DENSE_RANK: 공통 순위를 출력하되 건너뛰지 않고 바로 다음 순위 출력
  • ROW_NUMBER: 공통 순위 없이 출력
• 그룹함수
  • GROUP BY: 기준 열에 대해 같은 데이터 값끼리 그룹으로 묶고 묶은 행의 집합에 대해 그룹 함수 연산이 필요할 경우 사용
  • HAVING: 묶은 그룹에 대해 조건이 필요할 때 사용

  SELECT 그룹함수(열 이름)
  FROM 테이블 이름
  [WHERE 조건식]
  [ORDER BY 열 이름];
  

 

• 그룹함수 종류
  • COUNT: 행 개수를 셈, (*)의 경우 NULL 값도 개수로 셈
  • SUM: 합계, NULL 값을 제외하고 연산
  • AVG: 평균, NULL 값을 제외하고 연산
  • MAX: 최댓값, NULL 값을 제외하고 연산
  • MIN: 최솟값, NULL 값을 제외하고 연산
  • STDDEV: 표준편차, NULL 값을 제외하고 연산
  • VARIANCE: 분산, NULL 값을 제외하고 연산
• GROUP BY 절의 특
  • SELECT 절에 기준 열과 그룹함수가 같이 지정되면 GROUP BY 절에 기준 열 이름이 반드시 기술되어야 함 (SELECT 절에 그룹 함수만 기술되고 열 이름이 기술되지 않으면 GROUP BY 절을 반드시 기술할 필요는 없음)
  • WHERE 절 사용 시 행을 그룹으로 묶기 전 앞서 조건식 적용
  • SELECT 절에 그룹 함수를 사용하지 않아도 GROUP BY 절만으로도 사용 가능

  SELECT 그룹함수(열 이름)
  FROM 테이블 이름
  [WHERE 조건식]
  GROUP BY 열 이름
  [ORDER BY 열 이름];
  

•  
• HAVING
  • 일반적으로 GROUP BY 절 다음에 기술하는 것이 논리적이고 가독성도 좋다

 

 

 

 

모두의 SQL | 김도연 - 교보문고