[DB][SQL][국비교육] Day 25

🔍 rownum 키워드

조회된 결과에 행 번호를 지정하여 붙여주는 키워드 👉🏻 조회결과에 가상컬럼으로 추가됨
가상컬럼 : 실제 테이블에는 없지만 조회할 때 가상으로 만들어 추가되는 컬럼

• *과 함께 사용시 E.*과 같이 Alias를 붙여서 작성해야 함

SELECT rownum, empno, ename
FROM emp
WHERE deptno = 30;


--  잘못된 사용법
SELECT
    rownum rnum
--    , *     --rownum 키워드로 모든 행에 번호데이터가 채워지기 전에 * 기호가 rownum컬럼을 추가하여 조회하라는 뜻이 됨
    , E.*   --컬럼값이 이미 채워져있는 테이블의 모든 컬럼(E.*)로 변경
FROM emp E;


--  잘못된 사용법
--  rownum이 다 붙은 후에 정렬이 실행됨 -> 서브쿼리로 정렬 먼저 수행
SELECT 
    rownum sal_rank
    , E.*
FROM emp
ORDER BY sal DESC NULLS LAST;

SELECT
    rownum sal_rank
    , E.*
FROM (
    SELECT * FROM emp ORDER BY sal DESC NULLS LAST
)E;


--  잘못된 사용법
--  rownum은 1부터 1씩 증가시키면서 행번호를 붙임 -> 서브쿼리로 rownum부터 실행
SELECT rownum, E.* FROM emp E
WHERE rownum <=5;                   --1부터 5가 될 때까지 차례대로 출력 가능
--WHERE rownum BETWEEN 3 and 5;     --1보다 큰 행번호부터 조회할 수 없음


SELECT * FROM (
    SELECT rownum rnum, E.* FROM emp E
)
WHERE rnum BETWEEN 3 and 5;

---

🔍 TOP-N 분석 쿼리

조회 결과에 순위(랭킹)를 적용하여 상위 또는 하위 데이터를 찾아내는 것 👉🏻 서브쿼리를 이용하여 원하는 순위의 데이터 추출

• 인라인뷰, rownum, ORDER BY, WHERE를 적절히 섞어서 구현

--  급여를 많이 받는 7명
SELECT * FROM (
    SELECT rownum rnum, E.* FROM (      --행번호 부여(급여 랭킹 순위 부여)
        SELECT * FROM emp
        WHERE deptno IN(20, 30)         --조회 조건 부여
        ORDER BY sal DESC NULLS LAST    --급여순 내림차순 정렬
    ) E
) R
WHERE rnum BETWEEN 1 AND 7;             --TOP 7 적용

---

🔍 over() 함수

ORDER BY, GROUP BY를 적용하는 구문을 작성할 때 서브쿼리를 사용해야만 하는 상황을 개선하기 위한 함수

• PARTITION BY : GROUP BY처럼 사용
• OVER( ) 앞에 그룹함수 사용
  • 집계함수 : count, max, min, sum, avg
  • 분석함수 : row_number, rank, dense_rank

--  sal기준으로 정렬, 각 행의 누적된 합계를 구하기
SELECT
    empno, ename, sal, deptno
    , sum(sal) OVER( ORDER BY sal DESC NULLS LAST) sum_sal
FROM emp;

--  deptno기준으로 그룹화, 그룹별 합계
SELECT
    empno, ename, sal, deptno
    , sum(sal) OVER( PARTITION BY deptno) sum_sal
FROM emp;    

--  deptno기준으로 그룹핑, 그룹별 sal기준 정렬하여 누적합계
SELECT
    empno, ename, sal, deptno
    , sum(sal) OVER( PARTITION BY deptno ORDER BY sal DESC NULLS LAST) sum_sal
FROM emp;

➰ OVER()에서 사용되는 분석함수

• ROW_NUMBER()
  조회된 순서에 맞게 행 번호를 부여

• RANK()
  같은 값이면 같은 등수를 부여하고 같은 등수의 개수만큼 다음 순위를 건너뜀
  1 2 2 2 2 6 7 8

• DENSE_RANK()
  같은 값이면 같은 등수를 부여하고 같은 등수의 개수와 상관없이 바로 다음 순위를 순서대로 지정
  1 2 2 2 2 3 4 5

SELECT
    row_number() OVER( ORDER BY sal DESC NULLS LAST, ename) rnum
    , rank() OVER( ORDER BY sal ASC ) rank
    , dense_rank() OVER( ORDER BY sal ASC) dense_rank
    , empno, ename, sal, deptno
FROM emp;

---

🔍 다중 행 서브쿼리

서브쿼리의 조회 결과가 2행(2건) 이상 나오는 서브쿼리
👉🏻 스칼라 서브쿼리에서는 적용불가
👉🏻 인라인 뷰에서는 사용가능

• WHERE절에서 사용할 때 다중 행 전용 연산자를 사용해야 함

➰ 다중 행 서브쿼리 전용 연산자의 종류

• IN
  다중 행 서브쿼리의 조회 결과를 전부 동등비교(=) 해보는 것과 같음 👉🏻 각 데이터의 비교결과를 OR연산으로 연결한 것

SELECT * FROM emp
WHERE sal IN (
    SELECT DISTINCT sal
    FROM emp
    WHERE job = 'SALESMAN'
--    ORDER BY sal      --서브쿼리에서 ORDER 사용 불가
);

• ANY(= SOME)
  비교 컬럼이 서브쿼리 결과 행중에서 어떤 값이라도 조건에 만족하면 조회됨 👉🏻 OR 연산자로 연결한 것과 비슷

SELECT * FROM emp
--WHERE sal = ANY (       --IN연산자와 같은 동작
--WHERE sal < ANY (          --최대값보다 작은 데이터 조회
WHERE sal > ANY (           --최소값보다 큰 데이터 조회
    SELECT DISTINCT sal
    FROM emp
    WHERE job = 'SALESMAN'
);

• ALL
  비교컬럼이 서브쿼리 결과 행 모두와 조건에 만족하면 조회됨 👉🏻 AND 연산자로 연결한 것과 비슷

SELECT * FROM emp
--WHERE sal = ALL (       --조회되는 데이터 없음
--WHERE sal < ALL (       --최소값보다 작은 데이터 조회
WHERE sal > ALL (       --최대값보다 큰 데이터 조회
    SELECT DISTINCT sal
    FROM emp
    WHERE job = 'SALESMAN'
);

• EXISTS
  서브쿼리의 조회 결과로 존재하는 행이라면 메인쿼리에서 조회되도록 상관관계를 이용하여 작성하는 연산자

SELECT * FROM emp E
WHERE EXISTS (
    SELECT *
    FROM emp M
    WHERE E.empno = M.mgr
);

💡 IN 연산자 vs EXISTS 연산자
IN연산자는 서브쿼리를 수행하고 조회될 행을 중간 데이터로 생성하고 메인쿼리와 직접 비교해봄
EXISTS연산자는 메인쿼리와 서브쿼리가 서로 연결되는 데이터가 존재하는지 확인만 하고 중간 데이터를 생성하지 않음
👉🏻 EXISTS는 비교를 위한 중간데이터를 생성하지 않아 IN보다 수행속도가 빠른 편임

---

🔍 집합 연산자

두개의 쿼리(SELECT)의 결과 집합(Result Set)을 하나의 결과 집합으로 만드는 연산자
❗ 각 SELECT 구문의 결과의 컬럼수, 컬럼이름, 자료형이 같아야 함

➰ 집합연산자의 종류

• UNION : 합집합 / 중복행을 허용하지 않음

• UNION ALL : 합집합 / 중복행을 허용

• INTERSECT : 교집합

• MINUS : 차집합

--  UNION : 합집합 / 중복행 허용하지 않음
SELECT empno, ename, job, deptno FROM emp   --14명
WHERE sal < 3000
UNION
SELECT empno, ename, job, deptno FROM emp   --10명
WHERE sal < 2000
ORDER BY empno;


--  UNION ALL : 합집합 / 중복행 허용
SELECT empno, ename, job, deptno FROM emp   --14명
WHERE sal < 3000
UNION ALL
SELECT empno, ename, job, deptno FROM emp   --10명
WHERE sal < 2000
ORDER BY empno;


--  INTERSECT : 교집합
SELECT empno, ename, job, deptno FROM emp   --14명
WHERE sal < 3000
INTERSECT
SELECT empno, ename, job, deptno FROM emp   --10명
WHERE sal < 2000
ORDER BY empno;


--  MINUS : 차집합 -> 쿼리의 순서가 중요! 
SELECT empno, ename, job, deptno FROM emp   --14명
WHERE sal < 3000
MINUS
SELECT empno, ename, job, deptno FROM emp   --10명
WHERE sal < 2000
ORDER BY empno;