Oracle RATIO_TO_REPORT 전체실적에서 개인실적 비율구하기, WITH절 사용하기

8. 부서번호가 50인 사원들 중 이 부서의 평균 급여액보다 낮은 월급을 받는 사원 명단을 추출하시오

SELECT a.employee_id, a.last_name, a.salary

FROM ( SELECT employee_id, manager_id, salary, last_name

FROM employees WHERE department_id = 50 ) a，

 

(SELECT AVG (salary) avg_salary

FROM employees WHERE department_id = 50 ) b

WHERE a.salary < b.avg_salary

 

 

10. 9번의 출력문을 다시 with을 사용하여 다시 작성하시오

WITH a AS  (SELECT employee_id, manager_id, salary, last_name

                         FROM employees

                         WHERE department id = 50 )，

 

             b AS (SELECT AVG(salary) avg_salary

                         FROM employees

                         WHERE department id = 50)

SELECT a.employee id, a.last_name, a.salary

FROM a， b

WHERE a. salary < b.avg_salary

 

 

11. 전체 부서별 평균 급여액보다 부서별 급여 합계액이 큰 부서의 명단을 추출해 보자 단 서브쿼리

SELECT d.d_dep, d.SUM_sal

FROM

(SELECT a.department_name d_dep, ROUND(SUM(b.salary)) SUM_sal

FROM departments a

, employees b

WHERE a.department_id = b.department_id

GROUP BY a.department_name

) d,

(SELECT ROUND(SUM(sum(b.salary)) / COUNT(*)) AVG_sal

FROM departments a

, employees b

WHERE a.department_id = b.department_id

GROUP BY a.department_name

) c

WHERE d.SUM_sal > c.AVG_sal;

 

 

12 위의 방법을 다시 WITH를 이용하여 다시 작성하시오

WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total

                                            FROM employees e, departments d

                                            WHERE e.department_id = d.department_id

                                            GROUP BY department_name ),

            avg_cost AS (SELECT SUM(dept_total) /COUNT (*) avg

                                       FROM dept_costs)

 

SELECT dept_costs.*

FROM dept_costs, avg_cost

WHERE dept_costs.dept_total > avg_cost.avg

 

 

13. WITH은 SELECT 절과 같이 쓰여 아주 유용한 기능을 제공한다.

10장에서 계층형 쿼리에 대해 학습했는데, 계층형 쿼리를 이용하여 EMPLOYEES 테이블에 있는 사원들을 계층적으로 조회하였다

 

SELECT LEVEL, LPAD(' ', 4* (LEVEL -1)) || first_name || ' ' || last_name "성명"

FROM employees

START WITH MANAGER_ID IS NULL

CONNECT BY manager_id = PRIOR employee_id;

 

계층형 정보를 꼭 CONNECT BY 절을 사용해서만 구현할 수 있는 것은 아니다. WITH 구문을 사용하여 위의 결과와 동일한 데이터를 추출하는 쿼리를 작성해 보자.

 

(힌트: 실제 EMPLOYEES 테이블의 사원 계층은 총 4레벨까지 존재하므로， 레벨 별로 인라인 뷰를 4개 생성해서 WITH을 사용해 생성한 인라인 뷰에서 다른 인라인 뷰를 참조하는 형태로 작성하자. 문제는 난 이도가 꽤 높다. 따라서 이 문제를 풀 정도먼 WITH 구문을 완전히 이해했다고 볼 수 있다 )

 

 

15. 0RDERS 테이블을 참조하여 연도별로 주문건수와 총 주문금액을 구하는 쿼리를 작성해 보자.

 

 

 

16. 1번 쿼리를 기준으로 해서 다음과 같은 형태의 정보를 조회하는 쿼리를 작성해 보자.

기준연도 주문건수 주문총액 이전연도_주문총액 이후연도_주문총액

xxxx 00 0000 0000000 000000

SELECT order_date, order_status, order_total,

SUM(order_total) OVER ( ORDER BY order_date

                                                    RANGE interval '1' year preceding ) AS 이전연도_총합계,

SUM(order_total) OVER ( ORDER BY order_date

                                                    RANGE BETWEEN CURRENT row AND interval '1' year following) AS 이후년도_총합계

FROM ORDERS

 

단 이전연도와 이후연도는 기준연도에서 1년을 더하거나 뺀 연도가 아니라

실제 집계된 데이터가 존재하는 연도를 말한다.

 

 

17. 1999년도 전체 영업실적 중 각 개인별 실적 비율을 구해보자

전체 실적금액으로 각 개인별 실적금액을 니누면 된다 윈도우 함수를 중 분석함수를 사용해서 풀어보자

SELECT emp.last_name 이름, SUM (ord.ORDER_total) 개인별실적,

ROUND(SUM(ord.order_total) / SUM(SUM(ord.order_total)) OVER( PARTITION BY TO_CHAR(ord.order_date, 'YYYY' ) ) ,2) 개인별비율

FROM orders ord, employees emp

WHERE TO_CHAR(ord.ORDER_date， 'YYYY') = '1999'

AND ord. sales_rep_id = emp. employee_id

GROUP BY emp.last_name, TO_CHAR(ord.order_date, 'YYYY')

ORDER BY emp.last_name;

 

 

18. 위의 결과물과 같은 출력값을 가질 수 있게 RATIO_TO_REPORT 함수를 써서 출력하시오

SELECT emp.last_name 이름, SUM(ord.order_total) 개인별실적,

ROUND (RATIO_TO_REPORT(SUM(ord.order_total)) OVER (PARTITION BY TO_chAR(ord.order_date, 'YYYY' )),2) RATIO

FROM orders ord, employees emp

WHERE TO_CHAR(ORD.ORDER_DATE, 'YYYY') = '1999'

AND ord.sales_rep_id = emp.employee_id

GROUP BY emp.last_NAME, TO_CHAR(ord.ORDER_DATE, 'YYYY')

ORDER BY emp.last_name;