Day15_오라클 DB 기본함수2 (날짜 , TYPE 변환 , 그룹화)

2023.09.01

 

Day15 네 줄 요약

- 현재 날짜를 구할 땐 SYSDATE

- TO_CHAR( ) 함수를 통해 문자열 DATA로 변환

- 조회할 때 그룹화 함수가 COLUMN에 포함되어 있는 경우, 일반 COLUMN은 GROUP BY로 묶어야 함

- GROUP BY로 묶은 경우, 조건식은 HAVING( ) 을 통해 선언

 

---

 

날짜 관련 함수

 

현재날짜 SYSDATE

기본값으로 'YY/MM/DD'

-- 날짜 형식 바꾸기 (YY/MM/DD -> YYYY/MM/DD)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
SELECT SYSDATE FROM DUAL;

 

 

날짜 더하기 빼기

-- 날짜를 더하고 빼기
-- DATE + 숫자 : 숫자만큼 이후의 날짜
-- DATE - 숫자 : 숫자만큼 이전의 날짜
-- DATE1 - DATE2 : 두 날짜간 일수 차이 계산
-- DATE1 + DATE2 : 연산 불가
SELECT 
SYSDATE AS  "NOW(오늘)",
SYSDATE +1 AS "TOMORROW(내일)",
SYSDATE -1 AS "YESTERDAY(어제)"
FROM DUAL;

 

 

두 날짜 사이의 개월수 / 일수 계산

MONTHS_BETWEEN(나중날짜, 이전날짜) : 두 날짜 사이가 몇 개월인지 확인

         - 실수로 나오기 때문에 FLOOR 처리 필요

SELECT FLOOR(MONTHS_BETWEEN('2024/02/14', '2023/08/11')) AS 개월수 FROM DUAL;

 

 

개월수 더하기

ADD_MONTHS(기준날짜, 더해줄 개월수)

SELECT ADD_MONTHS(SYSDATE, 6) AS "6개월 뒤" FROM DUAL;

 

 

다가올 '요일'에 해당하는 날짜 구하기

NEXT_DAY(기준날짜, 찾고 싶은 요일(한글가능))

SELECT SYSDATE AS 오늘, NEXT_DAY(SYSDATE, '화요일') AS 다음주화요일 FROM DUAL;

 

 

해당 월의 마지막 날 구하기

LAST_DAY(날짜)

SELECT LAST_DAY(SYSDATE) AS "이번달 마지막 날" FROM DUAL;

 

 

날짜 형식 바꾸기

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH:MI:SS';      -- SYSDATE FORMAT 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';    -- 24H 체계로 변경

 

---

 

문자 / 날짜 / 숫자 / NULL TYPE 변환

 

TO_CHAR( )

숫자 또는 날짜 데이터를 문자형 데이터(NVARCHAR2)로 변환

SELECT TO_CHAR(SYSDATE, 'MM-DD DAY') AS "TO_CHAR",
TO_CHAR(SYSDATE, 'YYMMDD') AS 생년월일,
TO_CHAR(SYSDATE, 'MON') AS "MONTH",   -- 현재 해당 월을 출력(9월)
TO_CHAR(SYSDATE, 'D') AS "TODAY",     -- 몇 번째 요일인지 출력 (일요일부터 1)
TO_CHAR(TO_DATE('2023/08/11'), 'DD') AS "DAY FROM MONTH",   -- 해당 월을 기준으로 몇 일째인지
TO_CHAR(SYSDATE, 'DDD') AS "DAY FROM YEAR",   -- 해당 년도를 기준으로 몇 일째인지
TO_CHAR(SYSDATE, 'AM') AS "AM OR PM" FROM DUAL;    -- 오전 or 오후 여부 확인 ('PM'도 결과 동일)

 

 

TO_DATE( )

문자 데이터를 날짜형 데이터(DATE)로 변환

SELECT TO_DATE('2024/08/11') - TO_DATE('2023/09/01') FROM DUAL;

 

 

TO_NUMBER( )

문자 데이터를 숫자형 데이터(NUMBER)로 변환

SELECT TO_NUMBER('123') - TO_NUMBER('100') FROM DUAL;	--'123', '100'은 문자열 데이터

 

 

NVL / NVL2

NVL (컬럼이름, 'NULL일 경우' 바꿀 값)

NVL2 (컬럼이름, 'NULL이 아닐 경우' 바꿀 값, 'NULL일 경우' 바꿀 값) 

INSERT INTO RASP VALUES('홍길동', 30, 'Y');
INSERT INTO RASP(RNAME) VALUES('둘리');
INSERT INTO RASP(RNAME) VALUES('초롱이');
SELECT * FROM RASP;
SELECT RNAME, NVL(RAGE, 999), NVL2(RFRIEND, '확인', '모름') AS 친구유무 FROM RASP;

NVL 전                                                                     NVL 후

 

---

 

DECODE / CASE-WHEN-THEN

 

Java에서 switch-case문과 유사

 

DECODE( )

SELECT  ENAME, DEPTNO,
DECODE(DEPTNO, 10, '인사팀', 20, '영업팀', 30, '홍보팀') AS 담당부서
FROM EMP;

 

 

CASE-WHEN-THEN

SELECT ENAME, DEPTNO,
CASE DEPTNO
WHEN 10 THEN '인사팀' WHEN 20 THEN '영업팀' WHEN 30 THEN '홍보팀'
END AS 담당부서
FROM EMP;

 

---

 

그룹화 함수 

 

그룹화 함수

SUM( ) : 지정한 컬럼 데이터의 합

AVG( ) : 지정한 컬럼 데이터의 평균

MAX( ) : 지정한 컬럼 데이터의 최댓값

MIN( ) : 지정한 컬럼 데이터의 최솟값

COUNT( ) : 지정한 컬럼 데이터의 개수

     - 보통 ' * ' 를 넣어서 사용

GROUP BY( ) : 특정 컬럼 데이터를 기준으로 각 데이터를 그룹으로 묶음

     - 어떤 것을 기준으로 묶을지 선택

     - 그룹을 묶게 되면 기존 컬럼 외에 다른 컬럼은 조회 불가능 (그룹화 함수 컬럼은 조회가능)

HAVING( ) : GROUP BY를 사용해 그룹화 된 결과 중에서 특정 그룹을 선별하는 조건식

     - GROUP의 WHERE 역할

 

 

예제

-- Q1. EMP 테이블에서 부서번호, 평균급여, 최고급여, 최저급여, 사원수 출력
-- 평균 급여는 소수점 둘 째자리까지, 각 부서번호 오름차순
SELECT  DEPTNO AS 부서번호, ROUND(AVG(SAL), 2) AS 평균급여,
MAX(SAL) AS 최고급여, MIN(SAL) AS 최저급여, COUNT(*) AS 사원수 FROM EMP
GROUP BY DEPTNO ORDER BY DEPTNO;

-- Q2. 같은 직무(JOB)에 종사하는 사원이 3명 이상인 직무와 인원수 출력
SELECT JOB, COUNT(*) AS 사원수 FROM EMP
GROUP BY JOB HAVING COUNT(JOB) >= 3;

-- Q3. 사원들의 입사년도를 기준으로 부서별로 몇 명이 입사했는지 출력
-- TO_CHAR(HIREDATE, 'YYYY')를 이용
SELECT TO_CHAR(HIREDATE, 'YYYY') AS 입사년도, DEPTNO AS 부서번호, COUNT(*) AS 사원수 FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO ORDER BY TO_CHAR(HIREDATE, 'YYYY');

-- Q4. 수당(COMM)을 받는 사원수와 받지 않는 사원수 출력
-- 수당을 받으면 Y, 받지 않으면 N으로 출력, 수당이 0인 사람도 받는다고 가정
SELECT NVL2(COMM,'Y','N') AS "수당 여부 (Y/N)", COUNT(*) AS 사원수 FROM EMP
GROUP BY NVL2(COMM, 'Y', 'N') ORDER BY NVL2(COMM, 'Y', 'N') DESC;

-- Q5. 각 부서의 입사년도별 사원수, 최고급여, 급여합, 평균급여를 출력하고, 각 부서별 ROLLUP ROW 출력
SELECT  DEPTNO AS 부서번호, SUBSTR(HIREDATE, 1, 4) AS 입사년도, COUNT(*) AS 사원수,
MAX(SAL) AS 최고급여, SUM(SAL) AS 급여합, ROUND(AVG(SAL)) AS 급여평균 FROM EMP
GROUP BY ROLLUP(DEPTNO,SUBSTR(HIREDATE, 1, 4)) ORDER BY DEPTNO;

 

 

결과

Q1

Q2

Q3

Q4

Q5