안녕하세요.
https://blog.naver.com/sysysy0302여니입니다 :)
/*
5. 인라인뷰(INLINE VIEW)
FROM 절에 서브쿼리를 제시하면
서브쿼리를 실행한 결과값인 RESULT SET을 테이블을 대신해서 사용하겠다
*/
-- 보너스 포함 연봉이 3000만원 이상인 사원들의 사번, 이름, 보너스포함연봉, 부서코드를 조회
SELECT EMP_ID, EMP_NAME, (SALARY + SALARY * NVL(BONUS,0)) * 12 "보너스 포함 연봉", DEPT_CODE
FROM EMPLOYEE
WHERE (SALARY + SALARY * NVL(BONUS,0)) * 12 >= 30000000;
--> 인라인 뷰를 사용 : 사원명만 골라내기 (보너스 포함 연봉이 3000만원 이상인 사원들의 이름만)
SELECT EMP_NAME --6
FROM ( --1
SELECT EMP_ID, EMP_NAME, (SALARY + SALARY * NVL(BONUS,0)) * 12 AS "보너스 포함 연봉", DEPT_CODE --4
FROM EMPLOYEE --2
WHERE ((SALARY + SALARY * NVL(BONUS,0) * 12 >= 30000000) --3
))
WHERE DEPT_CODE IS NULL; --5
-- 인라인뷰를 주로 사용하는 예
-- TOP_N분석: 데이터베이스 상에 존재하는 자료중 최상위 N개의 자료를 보기 위해 사용하는 기능
-- 전 직원중 급여가 가장 높은 상위 5명(순위, 사원명, 급여)
-- *ROWNUM : 오라클에서 제공해주는 칼럼, 죄회된 순서대로 1부터 순서대로 부여해주는 칼럼
--SELECT ROWNUM, EMP_NAME, SALARY -- 실행순서2
--FROM EMPLOYEE -- 조회된 순서대로 1부터 순서 부여됨 -- 실행순서1
--WHERE ROWNUM <=5
--ORDER BY SALARY DESC; -- 정렬 기준이 엉킴 -- 실행순서3
SELECT ROWNUM, EMP_NAME, SALARY -- ROWNUM으로 순번 부여하기
FROM (
SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC
)-- ORDER BY 로 정렬 먼저시키고,
WHERE ROWNUM <=5;
-- 각 부서별 평균급여가 높은 3개의 부서의 부서코드, 평균급여 조회
-- 1) 각 부서별 평균 급여 => 높은 순서대로 추려서
SELECT DEPT_CODE, ROUND(AVG(SALARY)) AS "평균"
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY 평균 DESC;
-- 2) 순번 부여, 상위 3개만 추리기
-- SELECT ROWNUM "순위", S.*
-- SELECT ROWNUM "순위" , DEPT_CODE, "ROUND(AVG(SALARY))" -- 별칭부여 안했을때(가독성 떨어짐)
SELECT ROWNUM "순위" , DEPT_CODE, 평균급여
FROM (
SELECT DEPT_CODE, ROUND(AVG(SALARY))AS "평균급여"
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY 평균 DESC) S -- 평균 -> 2로 변경 가능
WHERE ROWNUM <=3;
-- ROWNUM칼럼을 이용해서 순위를 매길 수 있다.
-- 다만, 정렬이 되지 않은 상태에서는 순위를 매기면 의미가 없으므로 정렬을 먼저 시키고 순위를 나중에 매겨야한다.
-- 우선적으로 인라인뷰로 ORDER BY를 정렬을 하고, 메인쿼리에서 순서를 붙인다.
-- 가장 최근에 입사한 사원 5명 조회(사원명, 급여, 입사일)
-- 입사일 기준 미래 ~ 과거(내림차순), 순번 부여후 5명 조회
SELECT ROWNUM, E.*
FROM (
SELECT EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
ORDER BY HIRE_DATE DESC
) E
WHERE ROWNUM <=5;
/*
6. 순위 매기는 함수(WINDOW FUNCTION)
RANK() OVER(정렬기준)
DENSE_RANK() OVER(정렬기준)
- RANK() OVER(정렬기준) : 공동 1위가 3명이라고 한다면 그 다음순위는 4위로 하겠다.
- DENSE_RANK() OVER(정렬기준) : 공동 1위가 3명이라고 한다면 그 다음순위는 무조건 2위로 하겠다.
정렬 기준 : ORDER BY절(정렬기준 칼럼이름, 오름차순/내림차순), NULL FIRST/NULL LAST 옵션은 기술이 불가능.
SELECT 절에서만 기술 가능함.
*/
-- 사원들의 급여가 높은 순서대로 매겨서 사원명, 급여, 순위 조회 : RANK() OVER()
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE; -- 공동 19위 2명 , 다음순위는 20위가 없고 21위
SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE; -- 공동 19위 2명, 그 다음순위는 20위
-- 5위 까지만 출력하고 싶다. X 안됨
-- ; ROWNUM이 더 많이 쓰임, RANK식은 WHERE 조건절을 쓸 수 없어서 불편
SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE
WHERE DENSE_RANK() OVER(ORDER BY SALARY DESC) <= 5; -- 윈도우 함수를 WHERE 조건절에 기술할수 없다.(에러남)
-->> 인라인뷰로 전환
--1) RANK 함수로 순위를 매기고(정렬까지 완료시키기)
SELECT EMP_NAME, SALARY,RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE;
--2) 1) 1번 결과물을 토대로 조회하기 (5위까지만)
SELECT E.*
FROM (SELECT EMP_NAME, SALARY,RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE) E
WHERE "순위" <=5;