9.그룹함수

/*
<그룹함수>
    - 대량의 데이터들로 집계나 통계같은 작업을 처리해야하는 경우 사용하는 함수
    - 모든 그룹함수는 NULL값을 자동으로 제외함
      ->NVL() 함수와 함께 사용하는것을 권장 함
    - WHERE절에 사용 불가 -> 그룹함수는 허가되지 않습니다 에러
    <집계함수>
    - 그룹별 산출한 결과 값의 중간 집계를 계산해 주는 함수
    1) SUM(NUMBER)
        - 해당 컬럼의 총 합계를 반환
    2) AVG(NUMBER)
        - 해당 컬럼의 평균을 반환
    3) MIN(모든타입) / MAX(모든타입)
        - MIN : 해당 컬럼의 값 중 가장 작은 값 반환
        - MAX : 해당 컬럼의 값 중 가장 큰 값 반환
    4) COUNT(*|컬럼명)
        - 결과행의 개수를 세서 반환
        - COUNT(*) : 조회결과에 해당하는 모든 행의 개수를 반환
        - COUNT(컬럼) : 재시한 컬럼값이 NULL이 아닌 행의 개수를 반환
        - COUNT(DISTINCT 컬럼명) : 해당 컬럼의 중복을 제거한 후 행의 개수를 반환
    5) VARIANCE() : 분산 STDDEV() :표준편차
    <그룹함수>
    1) GROUP BY절
        - 특정 그룹으로 묶어 데이터 집계 시 사용
        - WHERE절과 ORDER BY절 사이에 위치
        - 집계함수와 함께 사용함
        - SELECT절에는 그룹함수와 GROUP BY절에 명시된 컬럼과 표현식만 사용 가능
    2) HAVING 절
        - GROUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할
          (그룹에 대한 조건을 제시, 그룹함수의 결과를 가지고 비교를 수행)
        - HAVING 다음에는 SELECT 리스트에 사용했던 집계함수를 이용한 조건을 명시해야함
        - HAVING절로 필터를 걸면 ROLLUP 소계가 필터 걸기 전 소계가 나온다.
          (실행순서를 보면 소계가 먼저 계산된 후 HAVING절로 필터를 걸기 때문인 것 같음)
          (그래서 조건식으로 필터는 되지만 소계값은 필터 걸기 전 값이 나옴)
        ** 실행순서 **
            5)SELECT    조회하고자 하는 컬럼명|계산식|함수 [AS 별칭]
            1)FROM      조회하고자 하는 테이블명
            2)WHERE     조건식
            3)GROUP BY  그룹 기준에 해당하는 컬럼명|계산식|함수식
            4)HVING     그룹에 대한 조건식
            6)ORDER BY  정렬기준에 해당하는 컬럼명|별칭|커럼순번
    3) ROLLUP(expr1, expr2,...)
        - GROUP BY절에서 사용
        - expr로 명시한 표현식을 기준으로 집계한 결과, 추가 정보 집계(소계, 총계)
        - expr로 명시한 표현식 수와 순서에 따라 레벨 별로 집계
        - expr 개수가 n개라면 n+1레벨까지, 하위에서 상위 레벨 순으로 집계
    4) CUBE(expr1, expr2,...)
        - 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 생성
    <GROUPING>
    - 롤업이나 큐브에 의해 산출된 값이 해당 컬럼의 집합의 산출물이면 0을 반환, 아니면 1을 반환
    <집합 연산자>
    - 여러개의 쿼리문을 가지고 하나의 쿼리문을 만드는 연산자
    1) 합집합
        - UNION : 두 쿼리문을 수행한 결과를 더한 후 중복되는 행 제거
        - UNION ALL : 중복 허용
    2) 교집합
        - INTERSECT : 두 쿼리문을 수행한 결과값에 중복된 결과 값만 추출
    3) 차집합
        - MINUS : 선행 결과집합에서 후행 결과집합을 뺀 나머지 결과값만 추출
*/
-- 부서코드가 20인 사원을 조회
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE department_id=20;
-- 급여가 5000~6000인 사원 조회
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE salary BETWEEN 5000 AND 6000;
-- 합집합
-- 202번 사원이 중복 총 4건 출력
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE department_id=20
UNION
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE salary BETWEEN 5000 AND 6000;
-- 위랑 같은 결과
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE department_id=20 OR salary BETWEEN 5000 AND 6000;
--합집합 중복허용
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE department_id=20
UNION ALL
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE salary BETWEEN 5000 AND 6000;
-- 교집합
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE department_id=20
INTERSECT
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE salary BETWEEN 5000 AND 6000;
-- 차집합
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE department_id=20
MINUS
SELECT employee_id, emp_name, department_id, salary
FROM emp
WHERE salary BETWEEN 5000 AND 6000;
-- 직급별 급여의 합계를 조회
-- 마지막 행에 전체 총 급여의 합계까지 조회
SELECT job_id, SUM(salary) 
FROM emp
GROUP BY CUBE(job_id) 
ORDER BY 1;

-- 부서 코드도 같고 직급 코드도 같은 사원들을 그룹 지어서 급여의 합계를 조회
SELECT department_id,job_id,SUM(salary) 
FROM emp
GROUP BY ROLLUP(department_id, job_id) 
ORDER BY 1;

SELECT department_id,job_id,SUM(salary) 
FROM emp
GROUP BY CUBE(department_id, job_id) 
ORDER BY 1;

-- 직급별 총 급여의 합이 10000이상인 직급을 조회
SELECT job_id, SUM(salary)
FROM emp
GROUP BY job_id
HAVING SUM(salary)>10000;

-- 부서별 평균 급여가 7000 이상인 부서와 평균 급여를 조회
-- 평균 급여는 소수점 제거, 세자리 콤마
SELECT department_id AS 부서, TO_CHAR(FLOOR(AVG(salary)),'999,999') AS 평균급여, COUNT(*) AS 사원수
FROM emp
GROUP BY department_id
HAVING AVG(salary)>=7000
ORDER BY 부서;

-- 부서별 보너스를 받는 사원이 없는 부서들만 조회
SELECT department_id, COUNT(commission_pct)
FROM emp
GROUP BY department_id
HAVING COUNT(commission_pct)=0;

-- emp 사원의 총 급여, 전체 사원을 하나의 그룹으로 묶어 총합을 구한 결과
SELECT SUM(salary), AVG(salary), MAX(employee_id), MIN(salary)
FROM emp;

-- 부서별 사원의 급여 합계
SELECT department_id 부서, TO_CHAR(SUM(salary),'999,999') 급여합계, TO_CHAR(FLOOR(AVG(salary)),'999,999') 평균, 
TO_CHAR(MAX(salary),'999,999') 최고,TO_CHAR(MIN(salary),'999,999') 최저, 
LPAD(COUNT(*)||'명',8) 사원수, LPAD(DECODE(COUNT(commission_pct),'0',' ',(COUNT(commission_pct)||'명')), 8) bonus
FROM emp 
group by department_id
ORDER BY department_id;

SELECT job_id 직급, TO_CHAR(FLOOR(AVG(salary)),'999,999') as 급여평균, COUNT(*)
FROM emp
GROUP BY job_id;

SELECT COUNT(*)
FROM emp;

-- 고객테이블에서 성별별 고객수를 구별 고객의 수를 내림차순으로 정렬
SELECT count(*)
FROM customers;

-- 소계를 구하는 함수
SELECT DECODE(CUST_GENDER, 'F', '여자', 'M', '남자',' ') 성별, COUNT(*) 고객수
FROM customers
GROUP BY ROLLUP(cust_gender);

--여러 컬럼을 제시해서 그룹의 기준을 설정, 부서별 직급별 사원수와 급여의 총합
SELECT department_id, job_id, COUNT(*), SUM(salary)
FROM  emp

GROUP BY department_id, job_id
ORDER BY department_id, job_id;


-- 컬럼을 명시하면 널 카운트 X
SELECT COUNT(department_id)
FROM emp;

SELECT * 
FROM emp
WHERE department_id IS NULL;

-- 중복 제거
SELECT COUNT(DISTINCT department_id)
FROM emp;

-- NVL 함수 이용해서 NULL값을 치환 후 조회
SELECT COUNT(DISTINCT NVL(department_id,'0'))
FROM emp;

-- 사원의 급여 합계, 중복을 제거한 급여 합계
SELECT SUM(DISTINCT salary), SUM(salary)
FROM emp;

SELECT VARIANCE(salary), STDDEV(salary)
FROM emp;

-- 대출상태
SELECT * FROM kor_loan_status;

-- 기간이 2013년도인 데이터를 조회
-- 기간별 대출종류별 잔액의 합계
SELECT period,gubun,SUM(loan_jan_amt)
FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY period,gubun
ORDER BY period;

-- 기간별 지역별 잔고의 합계
-- 합계가 100,000 이상인 데이터를 조회
SELECT DECODE(period,'','합계',period),region,SUM(loan_jan_amt)
FROM kor_loan_status
WHERE SUBSTR(period,1,4)=2013
GROUP BY ROLLUP(period, region)
HAVING SUM(loan_jan_amt) BETWEEN 100000 AND 500000
ORDER BY period, region;