국비 TIL(Today I Learned) 20220705 오라클 집계함수, HAVING, 순차함수, 서브 쿼리

집계 함수

집계 : 데이터를 수집 가공

 

SELECT 집계함수, 그 외 조건에 맞는것 FROM 데이터

 

* 집계 함수 사용 시 SELECT에 올 수 있는 것은 함수들과 GROUP BY에서 사용된 컬럼들만 올 수 있다.

 

SUM(컬럼) : 합계, 값들의 총 합계

AVG(컬럼) : 평균, 값들의 평균

COUNT(컬럼 OR *) : 개수, ROW의 개수, 단, 지정 컬럼이 NULL이면 세지 않음

MAX(컬럼) :  최대값. 값들 중 최대값

MIN(컬럼) : 최소값. 값들 중 최소값

 

SELECT JOB, SUM(SAL) FROM EMP;  오류

데이터 취득 

          ↓

데이터 압축 → 집계 실행

 

SELECT  JOB, SUM(SAL) FROM EMP GROUP BY JOB;

데이터 취득

          ↓

기준점 추가 데이터 압축 → 기준점으로 집계 실행

 

GROUP BY : 집계함수 사용시 데이터 집계의 기준점을 추가한다.

 

순서

SELECT FROM WHERE GROUP BY HAVING ORDER BY

 

HAVING : 집계 함수를 활용한 조건을 지정할 수 있다.

SELECT JOB, SUM(SAL) AS SUM_SAL FROM EMP GROUP BY JOB HAVING SUM(SAL) <= 5000; -- HAVING 통하여 걸러냄

 

데이터베이스가 중요! 속도차이가 경력자랑 확 난다고 함

 

순차함수: 순서를 지정

순차함수() OVER( PARTITION BY 기준값

                               ORDER BY 정렬순서)

 

ROW_NUMBER() : 정렬 후 번호지정

RANK() : 중복은 같은 순위로 처리, 중복된 만큼 건너뜀

DENSE_RANK() : 중복은 같은 순위로 처리, 바로 이어서 다음

 

PARTITION BY 기준값: 같은 기준끼리 순서를 지정

 

ROWNUM : 데이터 조회 순서

-- ROWNUM : 데이터 조회 순서
SELECT ROWNUM, ENAME, JOB, SAL FROM EMP ORDER BY SAL DESC;
-- ROWNUM 잘 쓸라고 하면 서브쿼리랑 같이 써야함
SELECT ROWNUM, A.* FROM (SELECT ROWNUM, ENAME, JOB, SAL FROM EMP ORDER BY SAL DESC) A;

 

 

서브 쿼리(SubQuery) : 쿼리 안의 쿼리 VIEW의 재가공

작성 위치: (쿼리) -> 괄호로 구분

 

조건                                                                    

SELECT → 단일COLUMN, 단일ROW           

FROM → ANYTHING, FROM에서 많이 씀

WHERE  → 기본 단일COLUMN, 단일ROW, 단, IN사용시 단일COLUMN, 복수 ROW, FROM 에서 정 안되면 WHERE에서 쓰고 

 

 

-- 급여의 총합
SELECT SUM(SAL) FROM EMP;
-- 급여의 평균
SELECT AVG(SAL) FROM EMP;
-- 커미션의 ROW개수, NULL는 세지 않음
SELECT COUNT(COMM) FROM EMP;
-- 급여의 최대값
SELECT MAX(SAL)FROM EMP;
-- 급여의 최소값
SELECT MIN(SAL)FROM EMP;
-- 업무별(기준점) 급여 합계 
SELECT JOB, SUM(SAL) FROM EMP; -- 오류
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;
-- 순서 SELECT FROM WHERE GROUP BY ORDER BY
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB ORDER BY JOB ASC;
SELECT JOB, SUM(SAL) FROM EMP WHERE JOB = 'CLERK' GROUP BY JOB ORDER BY JOB ASC;

-- 급여 총합이 5000이하인 업무를 구하시오
SELECT JOB, SUM(SAL) AS SUM_SAL FROM EMP WHERE SUM_SAL <= 5000 GROUP BY JOB; -- 오류, 실행순서가 압축전에 WHERE 실행

SELECT JOB, SUM(SAL) AS SUM_SAL FROM EMP GROUP BY JOB HAVING SUM(SAL) <= 5000; -- HAVING 통하여 걸러냄

-- 각 업무별 급여의 최대급여와 최소급여를 구하고 그 차이를 구하시오.
SELECT JOB, MAX(SAL), MIN(SAL), MAX(SAL)- MIN(SAL) FROM EMP GROUP BY JOB;
-- 업무의 급여 평균이 1500이하인 업무의 사원수를 구하시오.
SELECT JOB, COUNT(JOB), AVG(SAL) AS AVG_SAL FROM EMP GROUP BY JOB HAVING AVG(SAL) <= 1500;
SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB HAVING AVG(SAL) <= 1500;

SELECT ENAME, SAL,
        ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RNUM,
        RANK() OVER(ORDER BY SAL DESC) AS RNK,
        DENSE_RANK() OVER(ORDER BY SAL DESC) AS DRUK
FROM EMP;

-- 업무별 급여 순위를 출력하시오.
SELECT ENAME, JOB, SAL,
        RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) AS RNK
FROM EMP
;
-- ROWNUM : 데이터 조회 순서
SELECT ROWNUM, ENAME, JOB, SAL FROM EMP ORDER BY SAL DESC;
SELECT ROWNUM, A.* FROM (SELECT ROWNUM, ENAME, JOB, SAL FROM EMP ORDER BY SAL DESC) A;

-- 월별 입사자수의 / 순위를 구하시오 
SELECT TO_CHAR(HIREDATE, 'MM') AS MM, COUNT(*) AS CNT,
        RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK 
FROM EMP GROUP BY TO_CHAR(HIREDATE, 'MM')
;

-- 부서(DEPTNO)별 입사일 순위로 사원정보를 구하시오.
-- 출력 : 부서번호, 이름, 입사일, 순위
SELECT DEPTNO, ENAME, HIREDATE,
        RANK() OVER(PARTITION BY DEPTNO ORDER BY HIREDATE ASC) AS RNK 
FROM EMP;

-- 업무별 급여 순위 1위를 출력하시오.
SELECT E.ENAME, E.JOB, E.SAL, E.RNK 
FROM (SELECT ENAME, JOB, SAL,
        RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) AS RNK
FROM EMP) E
WHERE E.RNK = 1;

-- 급여 평균 이상 급여를 받는 사원을 구하시오. -
SELECT AVG(SAL) AS AVG_SAL
FROM EMP; -- 칸이 한칸, 급여 평균이 조건 -> WHERE

SELECT ENAME, SAL, (SELECT AVG(SAL) AS AVG_SAL FROM EMP) AS AVG_SAl
FROM EMP E
WHERE E.SAL >= (SELECT AVG(SAL) AS AVG_SAL FROM EMP)
;


-- 입사자가 가장 많은 입사연도를 구하시오.
---> 입사연도별 입사자 수를 구하고 그 중에 가장 인원이 많은 연도를 구하라.
SELECT TO_CHAR(HIREDATE, 'YYYY') AS YYYY, COUNT(*) AS CNT,
        RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK 
FROM EMP GROUP BY TO_CHAR(HIREDATE, 'YYYY')
;

SELECT E.YYYY
FROM (SELECT TO_CHAR(HIREDATE, 'YYYY') AS YYYY, COUNT(*) AS CNT,
        RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK 
FROM EMP GROUP BY TO_CHAR(HIREDATE, 'YYYY')) E
WHERE E.RNK = 1;