Database_67_Subquery 응용_인라인 뷰_In-Line View_Top-N 분석, WITH 절

-- Advanced Subquery
    -- In-line View_인라인 뷰
        -- SELECT 문의 FROM 절에 있는 서브쿼리입니다.
        -- FROM 절에 서브쿼리를 작성하여 별칭을 부여하면 인라인 뷰가 생성됩니다.
        -- SELECT 문의 FROM 절에 있는 서브쿼리는 해당 SELECT 문에 대해서만 데이터 소스를 정의합니다.
        -- 테이블 또는 뷰를 사용하는 방식과 유사합니다.
        -- 스키마 객체가 아닙니다.

SELECT department_id, TRUNC(AVG(salary)) salavg
FROM employees
GROUP BY department_id;

    
-- 소속 부서의 평균 급여보다 많은 급어를 받는 사원의 평균 급여를 조회합니다.
SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a JOIN ( SELECT department_id, TRUNC(AVG(salary)) salavg
                                    FROM employees
                                    GROUP BY department_id) b
ON (a.department_id=b.department_id)
WHERE a.salary>b.salavg;

        -- Top-N 분석
            /*
            SELECT [ column_list ], ROWNUM : 행을 패치한 순서입니다.
            FROM ( SELECT [ column_list ]
                        FROM table
                        ORDER BY Top-N_column [ ASC | DESC ] )
            WHERE ROWNUM<= n;
                -- 일반적인 서브쿼리는 ORDER BY를 사용해도 출력에 영향이 없으므로 사용하지 않는 것이 원칙입니다.
                -- Top-N 분석에서는 필수적으로 ORDER BY절을 작성해야 합니다.
            */
            -- 열에서 가장 큰/작은 N개의 값을 요청합니다.
            -- 테이블에서 조건에 맞는 최상위/최하위 레코드 N개를 반환합니다.
            -- 사용 유형    
                -- 최상위 소득자 N명
                -- 최근 입사자 N명
                -- 최고 판매 실적 N명
                -- 최다 판매 상품 N개
            
SELECT  ROWNUM as RANK, last_name, salary
FROM ( SELECT last_name, salary
            FROM employees
            ORDER BY salary DESC )
WHERE ROWNUM<=3;

SELECT ROWNUM as SENIOR, e.last_name, e.hire_date
FROM ( SELECT last_name, hire_date
            FROM employees
            ORDER BY hire_date) e
WHERE ROWNUM<=4;

        -- WITH 절
            -- 복합 쿼리 내에서 여러 번 발생하는 동일한 쿼리 블록을 SELECT 문에서 사용할 수 있습니다.
            -- 질의 블록의 결과를 검색한 다음 이를 사용자 임시 테이블스페이스에 저장합니다.12`   1   `21 `2  
            -- 같은 쿼리 블록을 여러 번 참조하거나 조인 및 집계를 해야 하는 경우 매우 유용합니다.
            -- 이점
                -- 질의를 읽기 쉽게 도와줍니다.
                    -- 메인쿼리가 깔끔해집니다.
                -- 해당 절이 질의에서 여러 번 사용되어도 한 번만 평가하므로 성능이 향상됩니다.
/*
? WITH 절을 사용하여 전체 부서의 평균 총 급여보다 총 급여가 많은 부서의 부서 이름 및 총 급여를 표시하는 질의를 작성합니다.
? 이 문제 해결을 위해 다음 중간 계산이 필요합니다.
    1. WITH 절을 사용하여 모든 부서의 총 급여를 계산한 다음 결과를 저장합니다.
    2. WITH 절을 사용하여 전체 부서의 평균 총 급여를 계산한 다음 결과를 저장합니다.
    3. 1단계에서 계산한 총 급여를 2단계에서 계산한 평균 총 급여와 비교합니다. 
        특정 부서의 총 급여가 전체 부서의 평균 총 급여보다 많으면 해당 부서 이름 및 총 급여를 표시합니다.
*/

WITH
dept_costs AS (
    SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM employees e JOIN departments d
    ON ( e.department_id=d.department_id)
    GROUP BY d.department_name),
avg_cost AS (
    SELECT SUM(dept_total) / COUNT(*) AS dept_avg
    FROM dept_costs )
SELECT *
FROM dept_costs
WHERE dept_total>( SELECT dept_avg
                            FROM avg_cost )
ORDER BY department_name;