4day

-- 4일차
/*
  select
  from
  where
  group by
  having
  order by
*/
-- 집합함수, 분석함수
SELECT AVG(SALARY)-- , first_name 일반 컬럼은 집합함수와 같이 쓰지 못함
FROM EMPLOYEES;
-- group by : 특정한 컬럼으로 그룹을 지어주는 것
SELECT AVG(SALARY) 부서별평균월급, DEPARTMENT_ID, COUNT(*) 부서인원,
        max(salary) 부서최고월급, min(salary) 부서최저월급
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
-- having : 그룹에 대한 조건절
SELECT AVG(SALARY) 부서별평균월급, DEPARTMENT_ID, COUNT(*) 부서인원,
        max(salary) 부서최고월급, min(salary) 부서최저월급
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) > 2;
-- having에 department_id != 30 이런 조건을 걸 수 있으나
-- where에서 먼저 처리하는 것이 좋다.
-- 문제 : 입사 연도별 평균 연봉을 연봉의 오름차순으로 정렬하세요
--        연봉 : 월급 + 월급*커미션 hire_date에서 연도를 뽑아서 그룹
SELECT * FROM EMPLOYEES;

SELECT to_char(ROUND(AVG((SALARY+SALARY*NVL(COMMISSION_PCT,0))*12)), '$999,999') 연봉,
        EXTRACT(YEAR FROM HIRE_DATE) 연도
FROM EMPLOYEES
GROUP BY EXTRACT(YEAR FROM HIRE_DATE)
order by 연봉 desc;

select null + 3 from dual; -- null과 연산하면 무조건 null

-- dense_rank( 100(1) 90(2) 90(2) 80(3)  )
-- rank( 100(1) 90(2) 90(2) 80(4) )
SELECT DENSE_RANK(14000) WITHIN GROUP (ORDER BY SALARY DESC),
  rank(14000) within group (order by salary desc)
FROM EMPLOYEES;
SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC;
-- stddev표준편차, sum합계
SELECT STDDEV(SALARY), AVG(SALARY), SUM(SALARY) FROM EMPLOYEES;
-- 분석함수
SELECT RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC),
  salary, first_name, department_id
FROM EMPLOYEES;
-- dense_rank, rank, count, row_number
SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC), SALARY
from employees;
SELECT RANK() OVER (ORDER BY SALARY DESC), SALARY
FROM EMPLOYEES;
SELECT count(employee_id) OVER (ORDER BY SALARY DESC), SALARY
FROM EMPLOYEES;
SELECT row_number() OVER (ORDER BY SALARY DESC), SALARY
FROM EMPLOYEES;
-- 각 부서별 최고 월급
SELECT distinct department_id, first_value(salary || ' ' || first_name)
          over (partition by department_id order by salary desc)
FROM EMPLOYEES
ORDER BY 1;
-- last_value (반드시 windowing 절과 같이 써야함)
-- windowing clause : 범위를 지정, 결과가 변화됨
SELECT FIRST_NAME, SALARY, sum(salary) OVER (ORDER BY SALARY DESC
    -- rows between unbounded preceding and unbounded following) 전체범위
    rows between 1 preceding and 2 following) --위로 1줄, 아래로 2줄
    -- range between 1000 preceding and 1000 following)
    -- 월급에서 1000을 더한 값과 1000을 뺀 값의 범위에서 선택
FROM EMPLOYEES;
-- rollup, cube : group by랑 같이 써야 한다.
SELECT SUM(SALARY), DEPARTMENT_ID, JOB_ID,
    grouping(department_id), grouping(job_id)
FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
-- GROUP BY rollup(DEPARTMENT_ID, job_id)
ORDER BY 2;
-- UNION(합), UNION ALL, MINUS(차), INTERSECT(교)집합
-- 조건 : select절은 반드시 컬럼 갯수, 타입이 일치해야한다.
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30) -- 9명
union all
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 10000; -- 15명