[SQLD] 3. SQL 활용

SQLD를 취득하기 위해 공부하는 과정에 작성한 ‘SQL 활용’ 관련 정리이다.

서브 쿼리 (SubQuery) : 하나의 쿼리 내에 존재하는 또다른 쿼리

스칼라 서브 쿼리 (Scalar SubQuery) : 주로 SELECT에 위치하는 서브 쿼리

• 컬럼 대신 사용되므로 하나의 값만을 반환, 그렇지 않으면 에러 발생

SELECT M.PRODUCT_CODE,
    (SELECT S.PRODUCT_NAME
        FROM PRODUCT S
        WHERE S.PRODUCT_CODE = M.PRODUCT_CODE) AS PRODUCT_NAME,
    M.MEMBER_ID,
    M.CONTENT
FROM PRODUCT_REVIEW M;

인라인 뷰 (Inline View) : FROM처럼 테이블명이 오는 위치에 위치하는 서브 쿼리

SELECT M.PRODUCT_CODE, S.PRODUCT_NAME, S.PRICE, M.MEMBER_ID, M.CONTENT
FROM PRODUCT_REVIEW M,
    (SELECT PRODUCT_CODE, PRODUCT_NAME, PRICE FROM PRODUCT) S
WHERE M.PRODUCT_CODE = S.PRODUCT_CODE;

중첩 서브쿼리 (Nested SubQuery) : WHERE과 HAVING에 위치하는 쿼리

비연관 서브쿼리 (Un-Correlated SubQuery) : 서브쿼리 내에 메인 쿼리의 컬럼이 존재하지 않음

SELECT NAME, AGE, BIRTHDAY
    FROM STUDENT
    WHERE CODE = (SELECT CODE
        FROM SCHOOL
        WHERE SCHOOL_NAME = 'EIA');

연관 서브쿼리 (Correlated SubQuery) : 서브쿼리 내에 메인 쿼리의 컬럼이 존재함

SELECT ORDER_NO, DRINK_CODE, ORDER_CNT
    FROM CAFE_ORDER A
    WHERE ORDER_CNT = (SELECT MAX(ORDER_CNT)
        FROM CAFE_ORDER B
        WHERE B.DRINK_CODE = A.DRINK_CODE);

단일 행 서브쿼리 (Single Row SubQuery) : 서브쿼리가 1건 이하의 데이터 반환

• 단일 행 연산자와 함께 사용 : =, <, >, <=, >=, <>

SELECT * FORM PRODUCT
    WHERE PRICE = (SELECT MAX(PRICE) FROM PRODUCT);

다중 행 서브쿼리 (Multi Row SubQuery) : 서브쿼리가 여러 건의 데이터 반환

• 다중 행 연산자와 함께 사용 : IN, ALL, ANY, SOME, EXISTS

SELECT * FROM PRODUCT
    WHERE PRODUCT_ID IN (SELECT PRODUCT_CODE FROM PRODUCT_REVIEW);

다중 칼럼 서브쿼리 (Multi Col SubQuery) : 서브쿼리가 여러 칼럼의 데이터 반환

SELECT * FROM EMPLOYEES
    WHERE (JOB_ID, SALARY) IN (SELECT JOB_ID, MAX_SALARY
    FROM JOBS
    WHERE MAX_SALARY = 10000);

뷰 (View) : 특정 SELECT에 이름을 붙어 재사용 가능하도록 저장한 오브젝트

• SQL에서 테이블처럼 사용 가능한 가상의 테이블 : 실제 데이터 대신 SELECT만 가짐

CREATE OR REPLACE VIEW DEPT_MEMBERS AS
    SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.FULL_NAME
        FROM DEPARTMENT A
        LEFT OUTER JOIN EMPLOYEE B
            ON A.DEPARTMENT_ID = B.DEPARTMENT_ID; // 뷰 생성

DROP VIEW DEPT_TABLE; // 뷰 삭제

집합 연산자 : 각 쿼리의 결과 집합을 가지고 연산하는 명령어

SELECT * FROM STUDENT UNION ALL SELECT * FROM TEACHER; // 쿼리1, 쿼리2 결과의 합집합 (중복 포함)
SELECT * FROM STUDENT UNION SELECT * FROM TEACHER; // 쿼리1, 쿼리2 결과의 합집합 (중복 제거)
SELECT * FROM STUDENT INTERSECT SELECT * FROM TEACHER; // 쿼리1, 쿼리2 결과의 교집합 (중복 제거)
SELECT * FROM STUDENT MINUS SELECT * FROM TEACHER; // 쿼리1에서 쿼리2 결과 제거해 출력

그룹 함수 : 데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수

ROLLUP : 소그룹 간의 소계 및 총계를 계산하는 함수

SELECT ORDER_DT, COUNT(*)
    FROM MY_ORDER
    GROUP BY ROLLUP(ORDER_DT, (ORDER_ITEM, ORDER_ITEM_NAME))
    ORDER BY ORDER_DT;

CUBE : 소그룹 간의 소계 및 총계를 다차원적으로 계산하는 함수

SELECT ORDER_DT, COUNT(*)
    FROM MY_ORDER
    GROUP BY ORDER_DT
    ORDER BY ORDER_DT;

GROUPING SETS : 특정 항목에 대한 소계를 계산하는 함수

SELECT ORDER_DT, COUNT(*)
    FROM MY_ORDER
    GROUP BY GROUPING SETS(ORDER_DT, ORDER_ITEM)
    ORDER BY ORDER_DT;

GROUPING : 소계를 나타내는 ROW를 구분하는 기준

SELECT ORDER_DT, GROUPING(ORDER_DT), COUNT(*)
    FROM MY_ORDER
    GROUP BY ROLLUP(ORDER_DT)
    ORDER BY ORDER_DT;

윈도우 함수 : OVER 키워드와 함께 사용되는 함수

순위 함수 : 윈도우 함수 중 순위를 매기는 함수

RANK : 순위를 매길 때 같은 순위가 존재하면 존재하는 수만큼 건너뜀

SELECT ORDER_DT, COUNT(*) RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
    FROM MY_ORDER
    GROUP BY ORDER_DT;

DENSE_RANK : 순위를 매길 때 같은 순위가 존재하더라도 건너뛰지 않고 이어서 매김

SELECT ORDER_DT, COUNT(*) DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
    FROM MY_ORDER
    GROUP BY ORDER_DT;

ROW_NUMBER : 순위를 매길 때 동일한 값이알도 각기 다른 순위를 부여

SELECT ORDER_DT, COUNT(*) ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS RANK
    FROM MY_ORDER
    GROUP BY ORDER_DT;

집계 함수 : 윈도우 함수 중 값을 집계하는 함수

SUM : 데이터의 합계를 구하는 함수

SELECT SUM(SCORE) AS TOTAL_SCORE FROM SQLD;

MAX : 데이터의 최댓값를 구하는 함수

SELECT MAX(SCORE) AS TOTAL_SCORE FROM SQLD;

MIN : 데이터의 최솟값을 구하는 함수

SELECT MIN(SCORE) AS TOTAL_SCORE FROM SQLD;

AVG : 데이터의 평균값을 구하는 함수

SELECT AVG(SCORE) AS TOTAL_SCORE FROM SQLD;

COUNT : 데이터의 건수를 구하는 함수

SELECT COUNT(*) OVER(
    PARTITION BY SUBJECT,
    ORDER BY SCORE DESC
    RANGE UNBOUNDED PRECEDING) AS HIGHER_COUNT

행 순서 함수

FIRST_VALUE : 파티션별 가장 선두에 위치한 데이터를 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE,
    FIRST_VALUE(SCORE) OVER (ORDER BY SCORE) AS FIRST_VALUE
FROM SQLD;

LAST_VALUE : 파티션별 가장 끝에 위치한 데이터를 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE,
    LAST_VALUE(SCORE) OVER (ORDER BY SCORE) AS LAST_VALUE
FROM SQLD;

LAG : 파티션별로 특정 수만큼 앞선 데이터를 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE,
    LAG(SCORE, 3) OVER (ORDER BY SCORE) AS LAG
FROM SQLD;

LEAD : 파티션별로 특정 수만큼 뒤에 있는 데이터를 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE,
    LEAD(SCORE, 3) OVER (ORDER BY SCORE) AS LAG
FROM SQLD;

비율 함수

RATIO_TO_REPORT : 파티션별 합계에서 차지하는 비율을 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE, SUM(SCORE) AS SUM, 
    RATIO_TO_REPORT(SCORE) OVER() AS RATIO_TO_REPORT
FROM SQLD;

PERCENT_RANK : 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백 분위 순위 값을 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE, SUM(SCORE) AS SUM, 
    PERCENT_RANK OVER(ORDER BY SCORE) AS PERCENT_RANK
FROM SQLD;

CUME_DIST : 해당 파티션에서의 누적 백분율을 구하는 함수 (결과값 : 0~1)

SELECT STUDENT_NAME, SUBJECT, SCORE, SUM(SCORE) AS SUM, 
    CUME_DIST OVER(ORDER BY SCORE)/COUNT(*) OVER() AS CUME_DIST
FROM SQLD;

NITILE : 주어진 수만큼 행들을 N등분한 후 현재 행에 해당하는 등급을 구하는 함수

SELECT STUDENT_NAME, SUBJECT, SCORE, SUM(SCORE) AS SUM, 
    NITILE(1) OVER(ORDER BY SCORE DESC) AS NITILE
FROM SQLD;

Top-N 쿼리 : 시작 지점부터 N까지의 범위를 추출하는 쿼리

ROWNUM : 실제론 존재하지 않는 수도 컮럼 (Pseudo Column), 행이 반환될 때마다 순번이 1씩 증가

SELECT ROWNUM,
    NAME,
    SCORE
    FROM EXAM;

셀프 조인 (Self Join) : 나 자신과의 Join (ALIAS를 통한 별칭 부여는 필수!)

SELECT A.TYPE, A.NAME, B.TYPE, B.NAME
    FROM CATEGORY A, CATEGORY B
    WHERE A.CATEGORY_NAME = B.PARENT_CATEGORY;

계층 쿼리 : 테이블에 계층 구조를 이루는 컬럼이 존재할 때 데이터 출력

• LEVEL : 현재의 DEPTH 반환, 루트 노드는 1
• SYS_CONNECT_BY_PATH (컬럼,구분자) : 루트 노드부터 현재 노드까지의 경로 출력
• START WITH : 경로가 시작되는 루트 노드 생성
• CONNECT BY : 조건을 만족하는 데이터가 없을 때까지 루트로부터 자식 노드 생성
• PRIOR : 바로 앞에 있는 부모 노드의 값 반환
• CONNECTED_BY_ROOT (컬럼) : 루트 노드의 주어진 컬럼 값 반환
• CONNECT_BY_ISLEAF : 가장 하위 노드인 경우 1을 반환, 그 외에는 0 반환

SELECT LEVEL, SYS_CONNECT_BY_PATH('['||TYPE||']' || TYPE, '-') AS PATH FROM CATEGORY
START WITH PARENT_CATEGORY IS NULL
CONNECT BY PRIOR NAME = PARENT_CATEGORY;