함수: 데이터를 쉽게 가공하기

4장 함수 : 함수로 데이터를 쉽게 가공하기

01 함수란?

유용한 기능을 미리 만들어 놓은 것, SQL에서는 다양한 함수를 제공한다.

02 단일 행 함수 : 데이터 값을 하나씩 계산하고 조작하기

4.2.1 문자 타입 함수 p92
총 12개
1. LOWER, UPPER, INITCAP
2. SUBSTR : 지정한 길이만큼 문자열 추출하기
3. REPLACE: 특정 문자 찾아 바꾸기
4. LPAD, RPAD: 특정 문자로 자릿수 채우기
5. LTRIM, RTRIM: 특정 문자 삭제하기
6. TRIM: 공백 제거하기
(※DUAL 테이블, p98)

4.2.2 숫자 타입 함수 p99
총 8개
1. ROUND: 숫자 반올림하기
2.TRUNC: 숫자 절삭하기

4.2.3 날짜 타입 함수 P102
총6개
1. MONTHS_BETWEEN: 두 날짜 사이의 개월 수 계산하기
2. ADD_MONTHS: 월에 날짜 더하기
3. NEXT_DAY: 돌아오는 요일의 날짜 계산하기
4. LAST_DAY: 돌아오는 월의 마지막 날짜 계산하기
5~6. ROUND, TRUNC 날짜를 반올림하거나 절삭하기

4.2.4 변환 함수 p109
1. 자동 데이터 타입 변환
2. 수동 데이터 타입 변환: TO_CHAR, TO_NUMBER, TO_DATE
3. 날짜 및 시간 형식 변환하기: TO_CHAR
1. 날짜 지정형식 (총 15개,p112~113)
2. 시간 지정형식(총4개, p113~114)
3. 기타 형식(총2개, p114)
4. 숫자 형식 변환하기(총3개, p115)
1. TO_CHAR 숫자 지정 형식 (총6개, p115)
2. TO_NUMBER
3. TO_DATE (p116)

4.2.5 일반 함수 p116

1. NVL: Null값 처리하기

• Null값은 숫자인 0과 문자인 공백과는 다른 값이 없음을 나타내는 값
• 할당되지 않았거나 알려지지 않아 적용이 불가능한 값
• null값을 포함하는 산술연산의 결과는 null

2. DECODE: 조건 논리 처리하기 p119~121
   (※DECODE(성별,'M', '남성', '여성'))

3. CASE 표현식: 복잡한 조건 논리 처리하기

4. RANK, DENSE_RANK, ROW_NUMBER: 데이터 값에 순위 매기기
   RANK() OVER [PARTITION BY 열 이름] (ORDER BY 열 이름 ASC/DESC)
   DENSE_RANK() OVER [PARTITION BY 열 이름] (ORDER BY 열 이름 ASC/DESC)
   ROW_NUMBER() OVER [PARTITION BY 열 이름] (ORDER BY 열 이름 ASC/DESC)

---

4.2.1 문자 타입 함수 p92

p92~93, 1. LOWER, UPPER, INITCAP
[예제 4-1] HR.employees 테이블에서 last_name을 소문자와 대문자로 각각 출려학

SELECT last_name,
LOWER(last_name) LOWER적용,
UPPER(last_name) UPPER적용,
email,
INITCAP(email) INITCAP적용
FROM HR.employees;

p93~94, 2. SUBSTR : 지정한 길이만큼 문자열 추출하기
[예제 4-2]HR.employees 테이블에서 job_id 데이터 값의 첫째자리부터 시작해서 두개의 문자를 출력하세요.

SELECT job_id, SUBSTR(job_id, 1, 2) 적용결과
FROM HR.employees;

p94~95, 3. REPLACE: 특정 문자 찾아 바꾸기
[예제 4-3]HR.employees 테이블에서 job_id 문자열 값이 ACCOUNT면 ACCNT로 출력하세요.

SELECT job_id, REPLACE(job_id, 'ACCOUNT', 'ACCNT') 적용결과
FROM HR.employees;

p95~96 4. LPAD, RPAD: 특정 문자로 자릿수 채우기
[예제 4-4]HR.employees 테이블에서 first_name에 대해 12자리의 문자열 자리를 만들되
first_name의 데이터 값이 12보다 작으면 왼쪽에서부터 *을 채워서 출력하세요.

SELECT first_name, LPAD(first_name, 12, '*') LPAD적용결과
FROM HR.employees;

p96~97 5. LTRIM, RTRIM: 특정 문자 삭제하기
[예제 4-5] HR.employees 테이블에서 job_id의 데이터 값에 대해 왼쪽 방향부터 'F'문자를 만나면
삭제하고 또 오른쪽 방향부터 'T'문자를 만나면 삭제해 보세요.

SELECT job_id,
LTRIM(job_id, 'F') LTRIM적용결과,
RTRIM(job_id, 'T') RTRIM적용결과
FROM HR.employees;

p98, 6. TRIM: 공백 제거하기
SELECT 'start'||TRIM(' - space - ')||'end' 제거된_공백
FROM dual;
※DUAL 테이블

4.2.2 숫자 타입 함수 p99

p99~100 1. ROUND: 숫자 반올림하기
[예제 4-6]HR.employees 테이블에서 salary를 30으로 나눈 후 나눈 값의 소수점 첫째 자리, 소수점 둘째자리,
정수 첫째 자리에서 반올림 한 값을 출력하세요.

SELECT salary,
salary/30 일급,
ROUND(salary/30,0) 적용결과0,
ROUND(salary/30,1) 적용결과1,
ROUND(salary/30,-1) 적용결과MINUS1
FROM HR.employees;

p101, 2.TRUNC: 숫자 절삭하기
[예제 4-7]HR.employees 테이블에서 salary를 30으로 나눈 후 나눈 값의 소수점 첫째 자리, 소수점 둘째자리,
정수 첫째 자리에서 절삭해서 출력하세요.

SELECT salary,
salary/30 일급,
TRUNC(salary/30,0) 적용결과0,
TRUNC(salary/30,1) 적용결과1,
TRUNC(salary/30,-1) 적용결과MINUS1
FROM HR.employees;

4.2.3 날짜 타입 함수 P102

p102~103, Q. 오늘날짜에서 1을 더한 값, 1을 뺀 값, 2017년 12월 2일에서 2017년 12월 1일을 뺀 값,
오늘 날짜에서 13시간을 더한 값을 출력해 보세요.
(※SYSDATE함수: 오라클 데이터베이스 시스템에 설치되어 있는 시스템의 현재 날짜와 시간을 반환하는 함수)

SELECT TO_CHAR(SYSDATE,'YY/MM/DD/HH24:MI') 오늘날짜,
SYSDATE + 1 더하기1,
SYSDATE -1 빼기1,
TO_DATE('20171202')-TO_DATE('20171201') 날짜빼기,
SYSDATE + 13/24 시간더하기
FROM DUAL;

p104, 1. MONTHS_BETWEEN: 두 날짜 사이의 개월 수 계산하기
[예제 4-8]HR.employees 테이블에서 department_id가 100인 직원에 대해 오늘 날짜, hire_date,
오늘 날짜와 hire_date의 개월 수를 출력하세요.

SELECT SYSDATE, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) 적용결과
FROM employees
WHERE department_id = 100;

p105, 2. ADD_MONTHS: 월에 날짜 더하기
[예제 4-9]HR.employees 테이블에서 employee_id가 100과 106 사이인 직원의 hire_date에
3개월을 더한 값, hire_date에 3개월을 뺀 값을 출력하세요.

SELECT hiredate,
ADD_MONTHS(hire_date, 3) 더하기적용결과,
ADDMONTHS(hire_date, -3) 빼기적용결과
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

p106, 3. NEXT_DAY: 돌아오는 요일의 날짜 계산하기
[예제 4-10]HR.employees 테이블에서 employee_id가 100과 106 사이인 직원의 hire_date에서
가장 가까운 금요일의 날짜가 언제인지 문자로 지정해서 출력하고 숫자로도 지정해서 출력하세요.

SELECT hiredate,
NEXT_DAY(hire_date,'금요일') 적용결과문자지정,
NEXTDAY(hire_date, 6) 적용결과숫자지정
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

p107, 4. LAST_DAY: 돌아오는 월의 마지막 날짜 계산하기
[예제 4-11]HR.employees 테이블에서 employee_id가 100과 106 사이인 직원의 hire_date기준으로
해당 월의 마지막 날짜를 출력하세요.

SELECT hire_date,
LAST_DAY(hire_date) 적용결과
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

p107~108, 5~6. ROUND, TRUNC 날짜를 반올림하거나 절삭하기
[예제 4-12] HR.employees 테이블에서 employee_id가 100과 106 사이인 직원의 hire_date에 대해
월 기준 반올림, 연 기준 반올림, 월 기준 절삭, 연 기준 절삭을 적용하여 출력하세요.

SELECT hire_date,
ROUND(hire_date,'MONTH') 적용결과_ROUND_M,
ROUND(hire_date,'YEAR') 적용결과_ROUND_Y,
TRUNC(hire_date,'MONTH') 적용결과_TRUNC_M,
TRUNC(hire_date,'YEAR') 적용결과_TRUNC_Y
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

4.2.4 변환 함수 p109

p110, 1. 자동 데이터 타입 변환
SELECT 1 + '2'
FROM DUAL;

p111, 2. 수동 데이터 타입 변환
TO_CHAR, TO_NUMBER, TO_DATE

p112, 3. 날짜 및 시간 형식 변환하기: TO_CHAR

1. 날짜 지정형식 (총 15개,p112~113)

p113
SELECT TO_CHAR(SYSDATE,'YY'),
TO_CHAR(SYSDATE,'YYYY'),
TO_CHAR(SYSDATE,'MM'),
TO_CHAR(SYSDATE,'MON'),
TO_CHAR(SYSDATE,'YYYYMMDD') 응용적용1,
TO_CHAR(TO_DATE('20171008'),'YYYYMMDD') 응용적용2
FROM dual;

2. 시간 지정형식(총4개, p113~114)

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS PM') 시간형식,
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS PM') 날짜와시간조합
FROM dual;

3. 기타 형식(총2개,p114)

SELECT TO_CHAR(SYSDATE, 'HH-MI-SS PM') 시간형식,
TO_CHAR(SYSDATE, ' "날짜:" YYYY/MM/DD "시각:" HH:MI:SS PM' ) 날짜와시각표현
FROM dual;

4. 숫자 형식 변환하기(총3개, p115)

5. TO_CHAR 숫자 지정 형식 (총6개, p115)

6. TO_NUMBER
   SELECT TO_NUMBER('123')
   FROM dual;

7. TO_DATE (p116)
   SELECT TO_DATE('20171007','YYMMDD')
   FROM dual;

4.2.5 일반 함수 p116

p117, 1. NVL: Null값 처리하기

• Null값은 숫자인 0과 문자인 공백과는 다른 값이 없음을 나타내는 값
• 할당되지 않았거나 알려지지 않아 적용이 불가능한 값
• null값을 포함하는 산술연산의 결과는 null

SELECT *
FROM employees
ORDER BY commission_pct;

SELECT salary * commission_pct --salary와 commission_pct를 곱한 값
FROM employees
ORDER BY commission_pct;

p118
[예제 4-13]HR.employees 테이블에서 salary와 commission_pct를 곱하되
commission_pct가 null일 때는 1로 치환하여 commission_pct를 곱한 결과를 출력하세요.

SELECT salary * NVL(commission_pct, 1)
FROM employees
ORDER BY commission_pct;

p119~121, 2. DECODE: 조건 논리 처리하기
[예제 4-14]HR.employees 테이블에서 first_name, last_name, department_id, salary를 출력하되
department_id가 60인 경우에는 급여를 10%인상한 값을 계산하여 출력하고 나머지 경우에는 원래의 값을
출력하세요. 그리고department_id가 60인 경우에는 '10% 인상'을 출력하고 나머지 경우에는 '미인상'을
출력하세요.
(※DECODE(성별,'M', '남성', '여성'))

SELECT first_name,
last_name,
department_id,
salary 원래급여,
DECODE(department_id, 60, salary*1.1, salary) 조정된급여,
DECODE(department_id, 60, '10%인상', '미인상') 인상여부
FROM HR.employees;

p121 3. CASE 표현식: 복잡한 조건 논리 처리하기
[예제 4-15]

SELECT employee_id, first_name, last_name, salary,
CASE
WHEN salary >= 9000 THEN '상위급여'
WHEN salary BETWEEN 6000 AND 8999 THEN '중위급여'
ELSE '하위급여'
END AS 급여등급
FROM employees
WHERE job_id = 'IT_PROG';

p122~123, 4. RANK, DENSE_RANK, ROW_NUMBER: 데이터 값에 순위 매기기

RANK() OVER [PARTITION BY 열 이름] (ORDER BY 열 이름 ASC/DESC)
DENSE_RANK() OVER [PARTITION BY 열 이름] (ORDER BY 열 이름 ASC/DESC)
ROW_NUMBER() OVER [PARTITION BY 열 이름] (ORDER BY 열 이름 ASC/DESC)

[예제 4-16] RANK, DENSE_RANK, ROW_NUMBER 함수를 각각 이용해 HR.employees 테이블의
salary값이 높은 순서대로 순위를 매겨 출력해 보세요.

SELECT employeeid,
salary,
RANK() OVER(ORDER BY salary DESC) RANK급여,
DENSERANK() OVER(ORDER BY salary DESC) DENSE_RANK급여,
ROWNUMBER() OVER(ORDER BY salary DESC) ROW_NUMBER급여
FROM HR.employees;

p124
[예제 4-17]RANK, DENSE_RANK, ROW_NUMBER 함수를 각각 이용해 HR.employees 테이블
직원이 속한 department_id 안에서 salary값이 높은 순서대로 순위를 매겨 출력해보세요.

SELECT A.employeeid,
A.department_id,
B.department_name,
salary,
RANK() OVER(PARTITION BY A.department_id ORDER BY salary DESC) RANK급여,
DENSERANK() OVER(PARTITION BY A.department_id ORDER BY salary DESC) DENSE_RANK급여,
ROWNUMBER() OVER(PARTITION BY A.department_id ORDER BY salary DESC) ROW_NUMBER급여
FROM employees A, departments B
WHERE A.department_id = B.department_id
ORDER BY B.department_id, A.salary DESC;

=> PARTITION BY절을 이용하여 department_id, 즉 부서별로 그룹화한 후 salary 값이 높은 순(내림차순)으로
순위를 매겼음, IT부서의 순위특징 보면됨, 또한 조인기법도 적용되었음(6장참고)

---

03 그룹 함수 : 그룹으로 요약하기

4.3.1 그룹 함수의 종류와 사용법
총 7개 (p126)
1. COUNT 함수
2.SUM, AVG함수
3. MAX, MIN함수 (※문자 데이터 타입은 알파벳 순으로 출력: 최솟값 a, 최댓값 z)
4.3.2. GROUP BY: 그룹으로 묶기
4.3.3 Having : 연산된 그룹 함수 결과에 조건 적용하기 p132

---

p126, 1. COUNT 함수
[예제 4-18]HR.employees 테이블에서 salary행 수가 몇 개인지 세어서 출력하세요.

SELECT COUNT(salary) salary행수
FROM HR.employees;

p127, 2.SUM, AVG함수
[예제 4-19]HR.employees 테이블에서 salary의 합계와 평균을 구해 보세요. 또한 AVG 함수를
사용하지 말고 salary의 평균을 구해 보세요.

SELECT SUM(salary) 합계, AVG(salary) 평균, SUM(salary)/COUNT(salary) 계산된평균
FROM HR.employees;

p127~128 3. MAX, MIN함수
[예제 4-20]HR.employees 테이블에서 salary의 최댓값과 최솟값, first_name의 최댓값과 최솟값을
출력하세요. (※문자 데이터 타입은 알파벳 순으로 출력: 최솟값 a, 최댓값 z)

SELECT MAX(salary) 최댓값, MIN(salary) 최솟값, MAX(first_name) 최대문자값, MIN(first_name) 최소문자값
FROM HR.employees;

4.3.2. GROUP BY: 그룹으로 묶기

p128~130
[예제 4-21]HR.employees 테이블에서 employee_id가 10 이상인 직원에 대해 job_id별로 그룹화하여
job_id별 총 급여와 job_id별 평균 급여를 구하고, job_id별 총 급여를 기준으로 내림차순 정렬하세요.

SELECT jobid 직무, SUM(salary) 직무별총급여, AVG(salary) 직무별평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id
ORDER BY 직무별총급여 DESC, 직무별_평균급여;

p131

SELECT jobid job_id대그룹,
managerid manager_id중그룹,
SUM(salary) 그룹핑총급여,
AVG(salary) 그룹핑평균급여
FROM employees
WHERE employeeid >= 10
GROUP BY job_id, manager_id
ORDER BY 그룹핑총급여 DESC, 그룹핑_평균급여;

4.3.3 Having : 연산된 그룹 함수 결과에 조건 적용하기 p132

[예제 4-22]HR.employees 테이블에서 employee_id가 10 이상인 직원에 대해 job_id별로 그룹화하여
job_id별 총 급여와 job_id별 평균 급여를 구하되, job_id별 총 급여가 30000보다 큰 값만 출력하세요.
출력 결과는 job_id별 총 급여를ㄹ 기준으로 내림차순 정렬하세요.

SELECT jobid 직무, SUM(salary) 직무별총급여, AVG(salary) 직무별평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id
HAVING SUM(salary) > 30000
ORDER BY 직무별총급여 DESC, 직무별_평균급여;