[KH정보교육원] 20일차_SUBQUERY, RANK, CREATE

2021.11.8(월)

🌿 SUBQUERY

🌱 INLINE-VIEW

• FROM절에 서브쿼리 제시 → 서브쿼리를 수행한 결과(RESULT SET)를 테이블 대신 사용

-- 문제 = 보너스 포함 연봉이 3천만원 이상인 사원들의 사번, 이름, 보너스 포함 연봉, 부서코드 조회
-- 방법1)
SELECT EMP_ID, EMP_NAME, (SALARY * (1 + NVL(BONUS, 0))) * 12 "보너스 포함 연봉", BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE (SALARY * (1 + NVL(BONUS, 0))) * 12 >= 30000000;
-- 방법2) INLINE-VIEW(FROM절의 서브쿼리)를 사용 -> 서브쿼리의 RESULT SET을 하나의 테이블로 사용
SELECT EMP_NAME, "보너스 포함 연봉"
FROM (SELECT EMP_ID, EMP_NAME, (SALARY * (1 + NVL(BONUS, 0))) * 12 "보너스 포함 연봉", BONUS, DEPT_CODE
      FROM EMPLOYEE)
WHERE "보너스 포함 연봉" >= 30000000;

• INLINE-VIEW를 주로 사용하는 예: TOP-N분석(데이터베이스 상에 존재하는 자료 중 최상위 데이터 몇 개/최상위 몇 개 자료를 조회(+분석))
  e.g.1) 쇼핑몰 실시간 BEST(상품이 담기는 테이블에서 가장 많이 팔리는 것 추출/조회), BEST REVIEWS,
  e.g.2) PAGING 처리 = (게시글 번호, 게시 시간 등에 따라 정렬해서) 웹사이트 게시판 1페이지에 몇 개의 글이 들어갈 수 있는지

-- 문제 = 전 직원 중 급여가 가장 높은 5명 조회
SELECT ROWNUM, EMP_NAME, SALARY -- 실행순서3
FROM (SELECT *
      FROM EMPLOYEE
      ORDER BY SALARY DESC NULLS LAST) -- 실행순서1 = 급여 순서대로 정렬해서 테이블을 가져오자
WHERE ROWNUM <= 5 -- 실행순서2 -> FROM절의 조회 결과 상위 5개에 번호매김
ORDER BY SALARY DESC; -- 실행순서4

🌱 SELECT절 RANK

• 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수
• RANK() OVER(정렬기준): 공동 1위가 2명인 경우 그 다음 순위는 3위로 함
• DENSE_RANK() OVER(정렬기준): 공동 1위가 2명이라고 해도 그 다음 순위는 2위로 함

-- 사원들의 급여가 높은 순서대로 순위를 매겨서 사원명, 급여, 순위 조회
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC NULLS LAST) "급여 순위"
FROM EMPLOYEE; -- 18 19 19 21 22 23

SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC NULLS LAST) "급여 순위"
FROM EMPLOYEE; -- 18 19 19 20 21 22

SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC NULLS LAST) "급여 순위"
FROM EMPLOYEE
WHERE RANK() OVER(ORDER BY SALARY DESC <= 5; -- ORA-30483: window  functions are not allowed here

-- 사원들의 급여가 높은 순서 5위까지만 조회
SELECT *
FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC NULLS LAST) "급여 순위"
      FROM EMPLOYEE)
WHERE "급여 순위" <= 5;

-- 각 부서별 평균급여가 높은 3개의 부서의 부서코드, 평균 급여 조회
SELECT *
FROM (SELECT DEPT_CODE, ROUND(AVG(SALARY)), RANK() OVER(ORDER BY AVG(SALARY) DESC) "급여 순위"
      FROM EMPLOYEE
      GROUP BY DEPT_CODE)
WHERE "급여 순위" <= 3;

WINDOW FUNCTIONS(윈도우 함수): 행과 행 간의 관계를 정의하기 위해서 제공되는 함수 → 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등 조작 가능

• Window functions are allowed only in the SELECT list of a query.
• 윈도우 함수는 GROUP BY 구문과 병행하여 사용할 수 없음
• 윈도우 함수로 인해 결과 건수가 줄어들지는 않음
• 윈도우 함수의 PARTITION 구문과 GROUP BY 구문은 둘 다 파티션을 분할한다는 의미에서는 유사
• sum, max, min 등과 같은 집계 윈도우 함수를 사용할 때 윈도우 절과 함께 사용하면 집계 대상이 되는 레코드 범위를 지정할 수 있음
• 구조: SELECT WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 칼럼 ORDER BY WINDOWING절) FROM 테이블명;

🌿 DDL

• DATA 정의 언어
• ORACLE에서 제공하는 객체(OBJECT)를 새로이 만들고(CREATE), 구조를 변경(ALTER)하고, 구조 자체를 삭제(DROP)하는 명령문
• 구조 자체를 정의하는 언어 → 주로 DB관리자, DB설계자가 사용함

ORACLE에서의 객체 = 구조: 테이블(TABLE), VIEW, SEQUENCE, INDEX, PACKAGE, TRIGGER, PROCEDURE, 함수(FUNCTION), 동의어(SYNONYM), 사용자(USER)

🌱 CREATE

• TABLE(테이블) = 행(ROW), 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체, 모든 데이터는 테이블을 통해서 저장됨 → 데이터를 보관하고자 한다면 테이블을 만들어야 함
• 표현법:
  CREATE TABLE 테이블명 ( 컬럼명 자료형, 컬럼명 자료형, 컬럼명 자료형, 컬럼명 자료형, 컬럼명 자료형, ... );

📗 소감

- RANK() 함수 설명 때 드디어 웹페이지 구현에 대한 예시를 들어주셨다! 지금 배우는 내용이 어떻게 사용될지 조금이나마 알게 되어 기쁘다.

- SELECT 문법 뒤로 올수록 연습을 덜 해서인지 생소하다. 연습을 더 해야겠다!

📗 homework: N/A