5월 31일 월요일 (15일차) - 인라인뷰,탑앤분석ㅇ

@_@·2021년 5월 31일
데이터베이스빅데이터과정
0

Database

목록 보기
28/28

<목차>
ㅇ 인라인 뷰 (셀렉트 문장의 끝판왕)
ㅇ rownum
ㅇ TOP-N분석



ㅇ 서브쿼리의 역할

  • WHERE절 HAVING절에서 사용될 때는 값
  • FROM절에서 사용될 때는 인라인 뷰 (가상 테이블)


ㅇ Inline view 인라인 뷰

  • 인라인 뷰 : sql문에서 사용 가능한 별칭을 사용하는 서브쿼리
  • 대표적으로 FROM절에서 사용되는 서브쿼리는 인라인 뷰의 한 종류
  • 인라인 뷰는 스키마 객체가 아니다.
    = 가상테이블이다.
    = 이 문장이 실행되는 동안만 테이블로 사용하겠다.
    = 셀렉트 문장이 실행되는 동안만 서브쿼리의 결과를 (가상)테이블로 사용해
  • (이해 잘 된 문장) FROM절에 서브쿼리를 만들어 이 서브쿼리에 별칭을 부여할 경우 인라인 뷰가 생성됩니다.
  • (내 이해) FROM절에 원래는 테이블을 쓰는데
    서브쿼리 이용해서 가상의 테이블 만들 수 있다는 것
    & 별칭 넣어서 가상테이블의 이름으로 사용
    얘는 사실 테이블이 아니라 뷰인 거고. 인라인 뷰

CF. 엄청 어려워ㅜㅜ 셀렉트 문장의 많은 경험이 있는 사람들이 사용할 수 있는 정도로)


ㅇ 인라인 뷰 예제

SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, MAX(salary) maxsal
		   FROM employees
		   GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal
  • 서브쿼리를 b라는 가상의 테이블로 사용하겠다.
    • 서브쿼리 해석 : 부서번호 별 최대급여
  • 본쿼리 SELECT절의 앞의 3개는 a테이블, maxsal은 (가상의) b테이블에서 나온 거니까 => 조인조건
  • WHERE절에 조인조건 적어주고, 각 컬럼들 접두어 붙여주기
  • AND 조건은 : b테이블의 maxsal보다 적은 급여 받는 사원들 찾아라.

(내생각. 결국은 각 부서 최대급여 받는 사원들 제외하고 모두 찾는 건가?!)

  • 인라인 뷰 이용하면 내가 원하는 조건 꾸려줄 수 있어
    훨씬 다양한 결과 만들어 낼 수 있어
  • FROM절에 테-테, 테-뷰, 뷰-뷰, 인-뷰, 인-인 등등 다 가능
    (이 부분 수업 다시 듣기 - 필기 맞는지 확인)


ㅇ rownum

  • 출력이 된 행의 번호를 돌려주는 컬럼
  • 원래 로우넘버에는 어떠한 값도 들어 있지 않아
    우리가 만든 테이블에 저장되어 있는 데이터가 아냐
  • 시스데이트 같은 역할
  • 행의 순서를 제어할 수 있고 WHERE 절에 몇 번까지만 출력하겠다 명시 가능
    • WHERE rownum <(=) 숫자 : 꼴로 사용
SELECT rownum, last_name, salary
FROM employees
WHERE rownum > 4  수보다 크다는 조건 주면 결과 안 나와
SQL>/
no rows selected
  • 로우넘버는 행이 출력돼야지만 리턴해줌


ㅇ TOP-N 분석

  • 예) 급여 많이 받는 상위 3사람, 급여 조금 받는 하위 3사람

ㅇ 실험 : 급여 조금 받는 순서대로 3명 찾으려고 시도

SELECT rownum, last_name, salary
FROM employees
WHERE rownum < 4
ORDER BY salary
  • 결과
  • ROWNUMLAST_NAMESALARY
    2Kochhar17000
    3De Haan17000
    1King24000
  • 작은 값이 아니라 그냥 맨 위에 3명 안에서 정렬됐네?
  • ORDER BY 절이 맨 마지막에 실행되기 때문에
    • 출력 결과에서 1, 2, 3 ~ 번호가 쭉 정해면서 WHERE절에 의해 그중 3개만 출력되고 마지막으로 그 안에서 정렬이 이루어진 거야!



ㅇ TOP-N 분석을 하기 위해서는?!

  • 웨어절이 먼저 실행되기 때문에 인라인 뷰와 로우넘버 다 사용해야 가능
  • FROM절에서 인라인 뷰를 사용해서 먼저 정렬을 시키고
    거기에 로우넘버를 붙여서 WHERE절로 상위 3개만 출력되도록
SELECT rownum, last_name, salary
FROM (SELECT last_name, salary
      FROM employees
      ORDER BY salary)
WHERE rownum < 4
  • WHERE절과 HAVING절에서 사용되는 서브쿼리는 '값'의 역할을 해서 오더바이절 사용 안 했어 - 정렬 의미 없잖아. 성능만 떨어지고
  • FROM절에서 사용되는 서브쿼리는 ORDER BY절이 중요한 역할을 수행해

ㅇ 꼭 기억해야 할 사항

  • 맨 처음 SELECT절의 last_name, salary는 employees 테이블이 아니라 FROM절의 서브쿼리로 만든 인라인 뷰에서 가져온 컬럼이야.
  • FROM절에 employees 테이블은 없는 거야



Q. 처음부터 테이블 정렬되어 있으면 편하지 않을까?

  • 데이터 값이 바뀌면 정렬이 깨져 => 테이블 재구축해야 해
    (물건 사 올 때마다 수납 구조를 바꾸는 격)
  • I/O가 얼마나 많이 발생하겠어
  • 그래서 정렬은 인라인뷰 이용해서 가상의 테이블 사용하는 거야



ㅇ 실습1 : 50번 부서에 근무하는 사원 중 연봉을 많이 받는 상위 3명의 이름과 연봉을 구하시오.

  • 오답1 : 서브쿼리에서 '급여'로 정렬한 뒤 본쿼리에서 '연봉'을 출력해주는 방법 
SELECT rownum, last_name,  salary*12
FROM (SELECT last_name, salary
	FROM employees
	ORDER BY salary DESC)
WHERE rownum < 4
  • 이렇게 해도 결과는 똑같지만, 문제에서는 '연봉을 많이 받는 순서대로' 상위 3명을 구하라고 했으니까 문제에 맞게 '연봉'으로 정렬하자.
  • 오답2 : 그래서 연봉인 salary*12로 정렬하려 시도하겠지?
    => 이러면 오류 나
SELECT rownum, last_name, salary*12
FROM (SELECT last_name, salary*12
      FROM employees
      ORDER BY salary*12 DESC)
WHERE rownum < 4
  • 지금 FROM절에 있는 테이블은 임플로이스 테이블이 아니라 서브쿼리로 만든 인라인 뷰잖아.
  • 근데 본쿼리에서 'salary*12'는 임플로이스테이블에서 나온 salary에 산술연산자 *12를 하라는 거야
  • FROM절의 인라인 뷰에는 salary 컬럼 없어!
  • 그래서 서브쿼리 컬럼 이름에 알리아스를 붙여줌으로써, 그 컬럼을 셀렉트절에서 사용 가능한 상태로 만들어
  • 결론 : 서브쿼리에 알리아스를 사용하자

정답

SELECT rownum, last_name, annsal
FROM (SELECT last_name, salary*12 annsal
	FROM employees
	ORDER BY annsal DESC)
WHERE rownum < 4
  • ROWNUMLAST_NAMEANNSAL
    1Davies480000
    2Rajs360000
    3King288000
  • ORDER BY 절은 맨 마지막 실행이니 알리아스 가능


ㅇ 실습2 : 평균 급여가 적은 하위 3개 부서의 이름과 평균 급여를 출력하시오.

내 오답 1 : 부서 이름을 적어야 하는 데 부서 아이디 사용함.. 어쩐지 넘 빨리 끝나더라;;ㅋㅋㅋ

SELECT rownum, department_id, avg_sal
FROM (SELECT department_id, AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary))
WHERE rownum < 4
  • CF) 내가 자주 하는 실수 : 오더바이절에 salary 만 넣음. 우리가 원하는 정렬은 평균들의 정렬이니까 AVG(salary)를 정렬해야지!!!

내 답 : 결과는 같지만 조인이 본쿼리에 사용되어 성능 떨어져

SELECT rownum, d.department_name, a.avg_sal
FROM departments d, (SELECT department_id, AVG(salary) avg_sal
		     FROM employees
		     GROUP BY department_id
		     ORDER BY avg_sal) a
WHERE d.department_id = a.department_id
AND rownum < 4
  • 쌤 답은 쿼리를 한 번에 돌리게끔
    • 셀렉트 데이터 셋이라고 이야기 해
    • 쿼리가 데이터 셋을 만든거야
  • 내가 한 건 한 번 더 수행해야 하는 쿼리

정답

SELECT department_name, avg_sal
FROM (SELECT d.department_name, AVG(e.salary) avg_sal
	FROM departments d, employees e
	WHERE d.department_id = e.department_id
	GROUP BY d.department_id
	ORDER BY AVG(e.salary) ASC)
WHERE rownum < 4

(만드는 순서)

  • 서브쿼리(인라인 뷰) 먼저 만들기 : 부서 이름 별 평균 급여를 찾는 쿼리
    • 셀렉트 절 완성 : department_name, AVG(salary)
    • 프럼절 채우기 : 각각 d테이블, e테이블 필요
    • 조인 일어났으니까 조인조건 : d.d_id=e.d_id
    • 그룹함수 사용했으니 그룹바이절로 department_name 그룹화
    • 마지막으로 오더바이절로 평균 급여 작은 순서대로 정렬

서브쿼리

SELECT d.department_name, AVG(e.salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_id
ORDER BY AVG(e.salary) ASC
  • 이제 본쿼리 :
    • 셀렉트 리스트절에 부서 이름과 평균 급여
    • AVG(salary)는 특수문자 때문에 컬럼 이름으로 사용 못 하기 때문에
    • 서브쿼리에서 알리아스 넣어주기
    • 마지막으로 로우넘버로 조건절



CF) 내가 계속 헷갈려 하는 부분

  • 그룹함수 사용할 때 셀렉트 절에 AVG(~) 사용 가능
  • 근데 이걸 테이블이나 인라인 뷰 만드는 서브쿼리 안에서 쓰는 순간! 컬럼이름 규칙에 위배 돼
  • 이 때는 무조건 알리아스 처리해 줘야 해


profile
@_@
STEP BY STEP
이전 포스트

5월 31일 월요일 (15일차) - 뷰ㅇ

0개의 댓글