함수 #3

2010/05/20

--분석함수로 쓰는 AVG (AVG,LANK..많이쓰임)

SELECT employee_id
      , AVG(salary) OVER()
FROM employees

-- 분석함수 -> 집계함수

SELECT DISTINCT department_id
      , AVG(salary) OVER(PARTITION BY department_id)
FROM employees

--각 사원의 부서내 월급 순위를 알아보자
-- 출력결과> 사원번호, 월급, 순위
SELECT employee_id
      , salary
      , department_id
      , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)
        AS rnk
FROM EMPLOYEES

--MAX MIN AVG SUM COUNT
--RANK DENSE_RANK PERCENT_RANK ROW_NUMBER NTILE LAG LEAD

--분석함수
--사원 테이블에서 월급이 많은 순서대로 등위를 산출하자
-- Inline View

SELECT *
FROM
(SELECT employee_id
        ,salary
        ,RANK() OVER(ORDER BY salary DESC) AS rnk
FROM employees)
WHERE rnk <= 10;

--PARTITION BY

SELECT *
FROM
(SELECT employee_id
      , salary
      , department_id
      , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)
        AS rnk
FROM employees)
WHERE rnk = 1;

-- 부서별 평균월급보다 많이 받는 사원을 출력해보자
-- 출력결과> 사번, 월급, 평균월급

SELECT *
FROM
(SELECT employee_id, salary,
          CEIL(AVG(salary) OVER(PARTITION BY department_id)) as avgsal
 FROM employees)
WHERE salary > avgsal;

-- DENSE_RANK, PERCENT_RANK

SELECT employee_id
      , hire_date
      , DENSE_RANK() OVER(ORDER BY hire_date ASC)
FROM employees

SELECT employee_id
      , hire_date
      , ROUND(PERCENT_RANK() OVER(ORDER BY hire_date ASC)*100,2) AS prnk
FROM employees

-- 직무별로 월급 상위 10%이상인 사람의 정보를 나타내자
-- 출력결과> 사번, 직무, 월급, prnk

SELECT *
FROM
(SELECT employee_id
      , job_id
      , salary
      , ROUND(PERCENT_RANK() OVER(PARTITION BY job_id ORDER BY salary DESC)
              *100,2) AS prnk
FROM employees)
WHERE prnk  <= 10;

-- ROW_NUMBER
SELECT employee_id
      ,
FROM employees

-- NTILE
SELECT employee_id
      , salary
      , NTILE(4) OVER(ORDER BY salary DESC)
FROM employees

-- 전후의 컬럼 비교
-- LAG, LEAD
SELECT employee_id
      , LAG(employee_id, 1) OVER (ORDER BY employee_id) AS LAG
      , LEAD(employee_id, 1) OVER (ORDER BY employee_id) AS LEAD
FROM employees
WHERE department_id = 30;

-- 출력값 > 부서번호 중복되는 번호는 생략
SELECT department_id
      , job_id
      , COUNT(*)
FROM employees
WHERE department_id IN(50,80)
GROUP BY department_id, job_id
ORDER BY department_id;

--다음과 같은 형태로
--  dep_id      job_id      인원
--   50         SH_CLERK      20

SELECT NULLIF(department_id, LAG(department_id,1) OVER(ORDER BY department_id))
       , job_id
       , count(*)
FROM employees
WHERE department_id IN (50, 80)
GROUP BY department_id, job_id
ORDER BY department_id;

CREATE TABLE stock(
item VARCHAR2(15),
profit NUMBER
);

INSERT INTO stock
VALUES('2005.12',25.8);

INSERT INTO stock
VALUES('2006.12',0.6);

INSERT INTO stock
VALUES('2007.12',-37.9);

INSERT INTO stock
VALUES('2008.12',57.8);

INSERT INTO stock
VALUES('2009.12',26.9);

SELECT *
FROM STOCK

--토비스의 당기 순이익 변화율을 알아보자
--출력결과> ITEM, 당기 순이익 변화율

SELECT ABS(-10) FROM dual; --절대값

--순이익 변화율:(올해 순이익 - 전년 순이익) / ABS(작년 순이익)

SELECT ITEM
      , ROUND(profit - LAG(profit, 1) OVER(ORDER BY item)
      / ABS(LAG(profit, 1) OVER(ORDER BY item))*100,2) AS ch_ratio
FROM stock

--join

--9i 이전
SELECT employee_id
      , last_name
      , e.department_id
      , department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 80;

--위와 다른형태 조인

SELECT employee_id
      , last_name
      , e.department_id
      , department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

-- 모든 부서의 위치 정보를 얻어보자
-- 출력결과> 부서번호, 부서이름, 도시이름

SELECT department_id
      , department_name
      , city
FROM departments d, locations l
WHERE d.location_id = l.location_id;

SELECT department_id
      , department_name
      , city
FROM departments d JOIN  locations l
ON d.location_id = l.location_id;

-- 2000년 1/2분기에 입사안 사원 정보를 가져오자.
-- 출력결과> 사원번호, 이름(employees table), 부서이름(departments table), 직무이름(jobs table)

SELECT employee_id
      , last_name
      , department_name
      , job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND TO_CHAR(hire_date,'YYYY-MM') BETWEEN '2000-01' AND '2000-06';

SELECT employee_id
      , last_name
      , department_name
      , job_title
FROM employees e JOIN  departments d
ON e.department_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id
WHERE TO_CHAR(hire_date,'YYYY-MM') BETWEEN '2000-01' AND '2000-06';

-- 이름(성) 이 'Smith', 'King'인 사원의 정보를 얻자
-- 출력결과> 사원번호, 이름(성), 월급, (해당직무)최소 월급, (해당직무)최대 월급
--           부서이름

SELECT employee_id
      , last_name
      , salary
      , min_salary
      , max_salary
      , department_name
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
JOIN departments d
ON e.department_id = d.department_id
WHERE last_name IN ('Smith', 'King');

-- OUTER JOIN
-- 명시적 JOIN : null을 가진 칼럼 방향을 가리켜라
-- 비명시적 JOIN : null을 가지지 못한 컬럼을 (+) 도와라

SELECT employee_id
      , last_name
      , hire_date
      , e.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE TO_CHAR(hire_date,'YYYY-MM') BETWEEN '1999-01' AND '1999-06'

-- 부서와 부서 관리자 정보를 얻어보자
-- 단 관리자가 없는 부서는 'No Manager'라고 표시하자.
-- 출력결과> 부서이름, 관리자 이름(성)

SELECT department_name
      , NVL(last_name, 'No Manager')
     
FROM departments d , employees e
WHERE d.manager_id = e.employee_id(+)

--SELF JOIN
-- 각 사원의 매니저 이름(성)을 알아보자
-- 단 매니저가 없는 사원은 'Boss' 라고 표시하자
-- 출력결과> 사원번호, 이름, 매니저이름

SELECT e1.employee_id
      , e1.last_name
      , NVL(e2.last_name, 'Boss')
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id(+)