hyeonga_code
Database_67_Subquery 응용_인라인 뷰_In-Line View_Top-N 분석, WITH 절 본문
Database_67_Subquery 응용_인라인 뷰_In-Line View_Top-N 분석, WITH 절
hyeonga 2023. 8. 20. 10:59-- 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;
'Oracle Database' 카테고리의 다른 글
Database_70_윈도우 함수_그룹 내 순위, 그룹 내 집계, 그룹 내 행 순서, 그룹 내 비율 (0) | 2023.08.22 |
---|---|
Database_68_계층 쿼리 함수_계층 질의, Level 의사열, TOP-DOWN, BOTTOM-UP, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT (0) | 2023.08.21 |
Database_66_Subquery 응용_다중 열 서브쿼리, 쌍 비교, 비쌍 비교, 스칼라 서브쿼리, Correlated 상호관련 서브쿼리, EXISTS 연산 (0) | 2023.08.20 |
Database_65_DML 응용_FLASHBACK (0) | 2023.08.20 |
Database_64_DML 응용_MERGE UPSERT (0) | 2023.08.20 |