뷰
다른 테이블이나 뷰에 포함된 데이터의 맞춤 표현(Stored Table, Virtual Table)
하나 또는 하나 이상의 테이블/뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체 --> 선택적인 정보만 제공 가능
자체적으로 데이터를 포함하지 않음
베이스 테이블: 뷰를 통해 보이는 데이터를 포함하고 있는 실제 테이블
특정 데이터를 숨기기 위한 방법으로 뷰를 사용하기도 한다
뷰와 테이블의 JOIN도 가능(INLINE VIEW)
- 단일뷰
- 하나의 테이블에서 일부를 가상화
- INSERT UPDATE DELECT 가능
- 하지만 읽기 전용이라는 콘셉트를 가지고 뷰를 만드므로 보편적으로 DML 작업을 하지 않음
- 복합 뷰
- 여러 테이블에서 일부씩을 참조해서 가상화
- INSERT UPDATE DELECT 불가능
뷰 생성
CREATE [OR REPLACE][FORCE | NOFORCE] VIEW view_name [(alias [, alias...])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]]
// 뷰 생성
CREATE OR REPLACE VIEW V_EMP ("Enm", "Gender", "Years") AS
SELECT EMP_NAME
, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남자', '여자')
, HIRE_DATE
FROM EMPLOYEE;subquery에 표현식이 있을 경우 별칭 필수
뷰 삭제
DROP VIEW view_name;
인라인 뷰
Top N 분석
조건에 맞는 최상위(또는 최상위) 레코드 N개를 식별해야 하는 경우에 사용
오라클 환경에서 Top N 분석 원리
원하는 순서대로 정렬
ROWNUM이라는 가상 컬럼을 이용하여 정렬 순서대로 순번 부여
부여된 순번을 이용하여 필요한 수만큼 식별
Top N 예제
ORDER BY를 통해 SALARY를 기준으로 정렬이 가능하나 ROWNUM이 뒤죽박죽 되어 상위를 출력하기는 부적합
// 정렬
-- 정렬 전SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT NVL(DEPT_ID, 'N/A') AS "Did"
, ROUND(AVG(SALARY), -3) AS "Davg"
FROM EMPLOYEE
GROUP BY DEPT_ID) INLV
JOIN EMPLOYEE ON (NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE SALARY > INLV."Davg";
-- 정렬 후SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT NVL(DEPT_ID, 'N/A') AS "Did"
, ROUND(AVG(SALARY), -3) AS "Davg"
FROM EMPLOYEE
GROUP BY DEPT_ID) INLV
JOIN EMPLOYEE ON (NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE SALARY > INLV."Davg"
ORDER BY 3 DESC;ROWNUM은 특정 레코드를 식별하지 못한다
단, 최상위 ROW는 식별 가능하다
// ROWNUM 식별
-- 특정 ROWNUM 식별 불가SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT NVL(DEPT_ID, 'N/A') AS "Did"
, ROUND(AVG(SALARY), -3) AS "Davg"
FROM EMPLOYEE
GROUP BY DEPT_ID) INLV
JOIN EMPLOYEE ON (NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE SALARY > INLV."Davg"
AND ROWNUM = 3;
-- 최상위 1개는 식별 가능SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT NVL(DEPT_ID, 'N/A') AS "Did"
, ROUND(AVG(SALARY), -3) AS "Davg"
FROM EMPLOYEE
GROUP BY DEPT_ID) INLV
JOIN EMPLOYEE ON (NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE SALARY > INLV."Davg"
AND ROWNUM = 3;단, ROWNUM의 범위를 지정하는 건 가능
// ROWNUM 범위지정
SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT NVL(DEPT_ID, 'N/A') AS "Did"
, ROUND(AVG(SALARY), -3) AS "Davg"
FROM EMPLOYEE
GROUP BY DEPT_ID) INLV
JOIN EMPLOYEE ON (NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE SALARY > INLV."Davg"
AND ROWNUM = 3;미리 정렬을 해야 한다
지금까지 짠 코드 전체를 FROM 절에 명세하는 INLINEW VIEW 처리
// INLINE VIEW에서 미리 정렬
SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT ROWNUM
, EMP_NAME
, SALARY
FROM (SELECT NVL(DEPT_ID, 'N/A') AS "Did"
, ROUND(AVG(SALARY), -3) AS "Davg"
FROM EMPLOYEE
GROUP BY DEPT_ID) INLV
JOIN EMPLOYEE ON (NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE SALARY > INLV."Davg"
ORDER BY 2 DESC)
WHERE ROWNUM < = 5;RANK
ROWNUM의 단점을 보완
특정한 레코드를 식별할 수 있다
ex) WHERE RANK = 2;
- RANK()
- 지정한 값의 순위를 반환
- RANK() OVER
- 지정한 값을 기준으로 순위를 지정
// RANK
-- RANKSELECT RANK(2300000) WITHIN GROUP (ORDER BY SALARY DESC) AS RANK
FROM EMPLOYEE;
-- RANK OVERSELECT EMP_NAME
, SALARY
, RANK() OVER (ORDER BY SALARY DESC) AS RANK
FROM EMPLOYEE;// 18번 문제
-- ROWNUM
SELECT STUDENT_NO
, STUDENT_NAME
FROM (SELECT STUDENT_NO, STUDENT_NAME, AVG(POINT)
FROM TB_DEPARTMENT
JOIN TB_CLASS USING(DEPARTMENT_NO)
JOIN TB_GRADE USING(CLASS_NO)
JOIN TB_STUDENT USING(STUDENT_NO)
WHERE DEPARTMENT_NAME = '국어국문학과'
GROUP BY STUDENT_NAME, STUDENT_NO
ORDER BY 3 DESC)
WHERE ROWNUM = 1;
-- RANK OVER
SELECT STUDENT_NO
, STUDENT_NAME
FROM (SELECT STUDENT_NO, STUDENT_NAME, AVG(POINT), RANK() OVER(ORDER BY AVG(POINT) DESC) AS RANK
FROM TB_DEPARTMENT
JOIN TB_CLASS USING(DEPARTMENT_NO)
JOIN TB_GRADE USING(CLASS_NO)
JOIN TB_STUDENT USING(STUDENT_NO)
WHERE DEPARTMENT_NAME = '국어국문학과'
GROUP BY STUDENT_NAME, STUDENT_NO
ORDER BY 3 DESC)
WHERE RANK = 1;출처: SHINSEGAE I&C 인턴십
