0806_Oracle : 단일행함수, 집계함수

ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_CURRENCY = '￦';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

SELECT * FROM emp;

-- TO_NUMBER 문자열을 숫자로 변경
SELECT '23,123' + 10 FROM dual; -- 에러
SELECT REPLACE('23,123', ',') + 10 FROM dual; 
-- 이것도 되지만
SELECT TO_NUMBER('23,123','99,999') + 10 FROM dual;
-- 이것을 통해서도 가능

-- TO_DATE 문자열을 날짜로 변경
SELECT TO_DATE('900101') FROM dual;
-- 가능할 수도 있고 오류가 발생할 수도 있다.
SELECT TO_DATE('900101', 'RRMMDD') FROM dual;
-- RR은 조심해야함. 문제가 발생될 수 있다.

-- TO_TIMESTAMP 
-- 문자열을 TIMESTAMP로 변환
-- DATE는 YYYY-MM-DD HH24:MI:SS 까지만 표현 가능
-- 밀리초는 TIMESTAMP 사용
-- SYSDATE - > DATE 형, SYSTIMESTAMP -> TIMESTAMP 형
SELECT SYSDATE, SYSTIMESTAMP FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
        TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF4') 
FROM dual;
-- .FF4 밀리세컨드를 소수점 4자리까지 표현
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
        TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') 
FROM dual;

SELECT TO_TIMESTAMP('2021-08-06 12:24:35.200', 'YYYY-MM-DD HH24:MI:SS.FF3')
FROM dual;

-- NULL 
-- NVL (expr1, expr2) expr1이 NULL이면 expr2로 치환
-- NVL2 (expr1, expr2, expr3) expr1이 NULL이 아니면 expr2, NULL이면 expr3
SELECT name, NVL2(tel, tel, '전화없음')tel FROM emp;
SELECT name, NVL2(tel, '있다', '없다')tel FROM emp;

-- NULLIF(expr1, expr2) : expr1과 expr2가 같으면 NULL 다르면 expr1 반환
SELECT NULLIF(1, 1), NULLIF(1, 2) FROM dual;

-- COALESCE(expr [, expr ] ...) null 이 아닌 처음 값
SELECT COALESCE(null, 1, 2) FROM dual;

-- COUNT : 자료의 개수
SELECT COUNT(*) FROM emp; -- 모든 행의 수(전체 개수)
SELECT COUNT( empNO ) FROM emp; -- NULL은 COUNT하지 않음
-- *나 PRIMARY KEY, NOT NULL 컬럼을 이용하여 COUNT하면 전체 행수가 출력

SELECT COUNT( tel ) FROM emp; -- NULL은 COUNT하지 않음.

SELECT name, COUNT( empNO ) FROM emp; -- 에러
-- GROUP BY를 사용하지 않는 경우 일반 컬럼과 집계함수는 함께 사용 불가

-- 서울 사람 인원 수
SELECT COUNT( empNO ) FROM emp WHERE city = '서울';

-- 조건 만족하는 행수가 하나도 없으면 COUNT는 0 출력
SELECT COUNT ( empNO ) FROM emp WHERE 1 = 2;

SELECT COUNT(dept) FROM emp;
SELECT DISTINCT dept FROM emp;
SELECT COUNT ( DISTINCT dept ) FROM emp;

-- 전체인원수, 남자인원수, 여자인원수
SELECT COUNT(empNo) 전체인원, 
    COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, 1) ) 여자인원,
    COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) 남자인원
FROM emp;

SELECT COUNT(empNo) 전체인원, 
    COUNT ( NULLIF(MOD(SUBSTR(rrn, 8, 1), 2), 1) ) 여자인원,
    COUNT ( NULLIF(MOD(SUBSTR(rrn, 8, 1), 2), 0) ) 남자인원
FROM emp;

SELECT COUNT(empNo) 전체인원 FROM emp;

-- 서울 남자 인원수
SELECT COUNT(empNo) 서울남자
FROM EMP
WHERE city = '서울' AND MOD(SUBSTR(rrn, 8, 1), 2) = 1;

SELECT COUNT( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) 서울남자
FROM EMP
WHERE city = '서울'; -- 효율이 안좋음
-- WHERE 로 짤라버리고 연산하는 것이 더 효율적이다.


SELECT '전체' 구분, COUNT(*) 인원 FROM emp
    UNION ALL
SELECT '남자' 구분, COUNT(*) 인원 FROM EMP WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 1
    UNION ALL
SELECT '여자' 구분, COUNT(*) 인원 FROM emp WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
ORDER BY 인원 DESC;

-- MAX 최대값
-- MIN 최소값
-- AVG 평균
-- SUM 합
SELECT SUM(sal), AVG(sal), MAX(sal), MIN(sal)
FROM emp;

SELECT sal FROM emp WHERE 1 = 2; -- 한줄도 출력 안됨

SELECT SUM(sal) FROM emp WHERE 1 = 2; 
-- NULL (한줄), 조건 만족하는 것이 없으면 한 줄로 NULL 출력
SELECT AVG(sal) FROM emp WHERE 1 = 2; 
-- NULL (한줄), 조건이 만족하는 것이 없으면 한 줄로 NULL 출력
SELECT NVL(AVG(sal), 0) FROM emp WHERE 1 = 2; -- 0

SELECT SUM(sal) 전체급여합,
    SUM ( DECODE ( MOD( SUBSTR(rrn, 8, 1), 2), 1, sal) ) 남자급여합,
    SUM ( DECODE ( MOD( SUBSTR(rrn, 8, 1), 2), 0, sal) ) 여자급여합
FROM emp;

SELECT '전체' 구분, SUM(sal) 합계 FROM EMP
    UNION ALL
SELECT '남자' 구분, SUM(sal) 합계 FROM emp WHERE MOD( SUBSTR( rrn, 8, 1), 2) = 1
    UNION ALL
SELECT '여자' 구분, SUM(sal) 합계 FROM emp WHERE MOD ( SUBSTR(rrn, 8, 1), 2) = 0;

SELECT COUNT(*) 전체,
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 1, 1)) "1월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 2, 1)) "2월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 3, 1)) "3월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 4, 1)) "4월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 5, 1)) "5월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 6, 1)) "6월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 7, 1)) "7월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 8, 1)) "8월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 9, 1)) "9월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 10, 1)) "10월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 11, 1)) "11월",
    COUNT( DECODE( TO_CHAR( hireDate, 'MM'), 12, 1)) "12월"
FROM emp;

SELECT MAX(sal) FROM emp;
SELECT name, sal FROM emp WHERE sal = MAX(sal); 
-- 에러. WHERE 절에는 그룹함수 사용 불가

SELECT name, SAL
FROM EMP
WHERE sal = (SELECT MAX(sal) FROM emp) ;

SELECT name, SAL
FROM EMP
WHERE sal < (SELECT AVG(sal) FROM emp) ; -- 평균보다 적게 받는 사람

-- GROUP BY 절 사용 예
SELECT SUM(sal) FROM emp; -- 전체 총합 한 컬럼
SELECT '영업부' 부서, SUM(sal) 합계 FROM emp WHERE dept = '영업부'
    UNION ALL
SELECT '개발부' 부서, SUM(sal) 합계 FROM emp WHERE dept = '개발부';

SELECT SUM(sal)
FROM EMP
GROUP BY dept;

SELECT dept, SUM(sal)
FROM EMP
GROUP BY dept; -- GROUP BY 에 있는 컬럼은 그룹함수와 같이 사용 가능

-- dept의 pos별 급여 총합
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY dept, pos
ORDER BY dept, pos;

-- 부서별 인원수(dept, 인원수)
SELECT dept, COUNT(dept)
FROM EMP
GROUP BY dept;

-- 부서별 여자 인원수(dept, 인원수)
SELECT DISTINCT dept FROM emp;

SELECT dept, COUNT(dept) 여자인원
FROM EMP
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
GROUP BY DEPT;
-- 문제 : 여자 인원수가 없는 부서는 출력되지 않음. (인사부)
-- 실행 순서 FROM 절 -> WHERE 절-> GROUP BY 절 -> HAVING 절-> SELECT 절 -> ORDER BY 절

SELECT dept, COUNT ( DECODE ( MOD ( SUBSTR ( rrn, 8, 1), 2), 0, 1)) 여자
FROM EMP
GROUP BY dept; -- 여자 인원수가 없는 부서도  출력


-- 개발부 인원수
SELECT dept, COUNT(dept)
FROM EMP
WHERE dept = '개발부'
GROUP BY dept;

SELECT COUNT(dept)
FROM EMP
WHERE dept = '개발부';


-- 부서별 전체, 남자, 여자 인원수
-- dept 전체 남자 여자
SELECT dept, COUNT(dept) "부서별 전체인원",
COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, 1) ) 여자인원,
COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) 남자인원
FROM EMP
GROUP BY dept;

-- 부서별 남자와 여자 비율(부서 인원수 대비)
SELECT dept, 
ROUND ( COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, 1) ) / COUNT(*) * 100 ) 여자비율,
ROUND ( COUNT ( DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, 1) ) / COUNT(*) * 100 ) 남자비율
FROM EMP
GROUP BY dept;

-- 부서별 남자와 여자 급여 총합, 평균
SELECT dept, DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자') 성별,
    SUM(sal) 총급여, TRUNC(AVG(sal)) "평균 급여"
FROM emp
GROUP BY dept, DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자')
ORDER BY dept, 성별;

-- HAVING 절 사용 예 : GROUP BY 결과에 대한 조건, 그룹함수 사용 가능
-- 부서별 인원수
SELECT dept, COUNT(*)
FROM emp
GROUP BY dept;

-- 부서별 인원수가 7명 이상인 부서만 출력
SELECT dept, COUNT(*)
FROM emp
GROUP BY dept
HAVING COUNT(*) >= 7;

-- 부서별 여자인원수(dept 인원수)
SELECT dept, COUNT(dept) 여자인원수
FROM EMP
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
GROUP BY DEPT;

-- 부서별 여자인원수가 5명인 부서와 인원수 출력
SELECT dept, COUNT(dept) 여자인원수
FROM EMP
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
GROUP BY DEPT
HAVING COUNT(dept) >= 5;

-- SUBQUERY
-- SELECT, INSERT, UPDATE, DELETE 문 등에서 사용되는 SELECT문
-- SELECT 문에서는 SELECT절, FROM 절, WHERE 절 등에서 사용 가능
-- WHERE 절에서 사용하는 경우 하나의 컬러만 가능하다.
-- WHERE 절에서 사용하는 경우 >, >=, <, <=, =, != 등과 비교할 경우 한 행만 가능하다.
-- WHERE 절에서 사용하는 경우 IN, ANY 등과 비교할 경우 여러행도 가능하다.

SELECT name, SAL
FROM EMP
WHERE sal = (SELECT MAX(sal), MIN(sal) FROM emp); -- 에러
-- WHERE 절의 서브쿼리에 컬럼이 두개 있음.

SELECT name, SAL
FROM EMP
WHERE sal > (SELECT sal FROM emp WHERE city='서울'); -- 에러
-- WHERE 절의 서브쿼리에서 > 으로 비교하는데 여러행이므로

SELECT name, SAL, city
FROM EMP
WHERE sal IN (SELECT sal FROM emp WHERE city='서울'); -- 가능

-- name, sal 평균보다 많이 받는 사람
SELECT name, SAL
FROM EMP
WHERE sal > (SELECT AVG(sal) FROM emp) ; -- 에러

-- 서울 사람이 아닌 사람 중 서울 평균보다 많이 받는 사람
SELECT name, SAL, city
FROM EMP
WHERE city !='서울' AND sal > (SELECT AVG(sal) FROM emp WHERE city = '서울');

-- name, sal, 평균급여와 차이
SELECT name, sal, sal - AVG(sal) FROM emp; -- 에러

SELECT name, SAL, sal - (SELECT AVG(sal) FROM emp ) 차이
FROM emp;

-- sal+bonus 가 가장 많은 사람
SELECT name, sal, bonus, sal+bonus pay
FROM EMP
WHERE sal+bonus = ( SELECT MAX(sal+bonus) FROM emp );

-- 부서별 인원수가 가장 많은 부서명과 인원수
-- 부서별 인원수
SELECT dept, COUNT(*)
FROM EMP
GROUP BY dept;

SELECT MAX( COUNT(*) )
FROM EMP
GROUP BY dept;

SELECT dept, COUNT(*) 인원수
FROM EMP
GROUP BY dept
HAVING COUNT(*) = 
( SELECT MAX( COUNT(*) ) FROM emp GROUP BY dept);

-- 입사년도 (hireDate)별 인원수가 가장 많은 년도 및 인원수
-- 입사년도별 인원수
SELECT TO_CHAR(hireDate, 'YYYY') 년도, COUNT(*)
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY') ;

-- 가장 많은 년도 및 인원수
SELECT TO_CHAR(hireDate, 'YYYY') 년도, COUNT(*)
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY')
HAVING COUNT(*) = ( SELECT MAX ( COUNT(*) ) FROM emp GROUP BY TO_CHAR(hireDate, 'YYYY'));

-- 생일이 동일한 사람이 2명 이상인 경우의 name, birth 출력.
-- 단 출력은 생년월일 중 월일오름차순

SELECT name, TO_DATE( SUBSTR( rrn, 1, 6), 'RRMMDD') birth
FROM emp
ORDER BY TO_CHAR(birth, 'MMDD');

SELECT SUBSTR( rrn, 3, 4), COUNT(*)
FROM EMP
GROUP BY SUBSTR( rrn, 3, 4)
HAVING COUNT( SUBSTR ( rrn, 3, 4) ) >= 2;

SELECT name, TO_DATE( SUBSTR( rrn, 1, 6), 'RRMMDD') birth
FROM emp
WHERE SUBSTR(rrn, 3, 4) IN 
(
    SELECT SUBSTR( rrn, 3, 4)
    FROM EMP
    GROUP BY SUBSTR( rrn, 3, 4)
    HAVING COUNT(*) >= 2
)
ORDER BY TO_CHAR(birth, 'MMDD');