[SQLD] SQL 활용

certification/SQLD

[SQLD] SQL 활용

gaing 2024. 11. 16. 00:54

서브쿼리(Subquery)

하나의 쿼리 안에 존재하는 또 다른 쿼리

서브쿼리는 위치에 따라 나눌 수 있다.

SELECT 절
- 스칼라 서브쿼리(Scalar Subquery)
FROM 절
- 인라인 뷰(Inline View)
WHERE 절, HAVING 절
- 중첩 서브쿼리(Nested Subquery)

서브쿼리는 ORDER BY 절, INSERT 문의 VALUE 절 등에 사용이 가능하다.

메인쿼리의 컬럼이 포함된 서브쿼리를 연관 서브커리, 메인쿼리의 컬럼이 포함되지 않은 서브쿼리를 비연관 서브쿼리라고 한다.

다중 행 서브쿼리의 경우 '=' 조건과 함께 사용할 수 없다.

다중 컬럼 서브쿼리의 경우 IN 절과 함께 사용할 수 있다.

스칼라 서브쿼리(Scalar Subquery)

주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.

컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야 하며 그렇지 않은 경우 에러를 발생시킨다.

SELECT 절, UPDATE 문의 SET 절, ORDER BY 절에 위치할 수 있다.

# 스칼라 서브쿼리를 이용하여 PRODUCT_REVIEW 테이블에 존재하지 않는 데이터 출력
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 절에 사용할 수 있다.

메인쿼리와의 관계에 따라 다음과 같이 나눌 수 있다.

비연관 서브쿼리(Uncorrelated Subquery)
- 메인쿼리와 관계를 맺고 잊지 않음
연관 서브쿼리(Correlated Subquery)
- 메인쿼리와 관계를 맺고 있음

비연관 서브쿼리(Un-correlated Subquery)

서브쿼리 내에 메인쿼리의 컬럼이 존재하지 않는다.

SELECT NAME, JOB, BIRTHDAY, AGENCY_CODE
  FROM ENTERTAINER
 WHERE AGENCY_CODE = (SELECT AGENCY_CODE
                        FROM AGENCY
                       WHERE AGENCY_NAME = '굿엔터'); # 비연관 서브쿼리
                       # 메인에 AGENCY_NAME 존재 X

연관 서브쿼리(Correlated Subquery)

서브쿼리 내에 메인쿼리의 컬럼이 존재한다.

SELECT ORDER_NO,
       DRING_CODE,
       ORDER_CNT
  FROM CAFE_ORDER A
 WHERE ORDER_CNT = (SELECT MAX(ORDER_CNT)
                     FROM CAFE_ORDER B
                     WHERE B.DRING_CODE = A.DRINK_CODE); # 연관 서브쿼리
                     # 메인에 DRINK_CODE 존재 O

반환하는 데이터 형태에 따라 다음과 같이 나눌 수 있다.

단일 행(Single Row) 서브쿼리
- 서브쿼리가 1건 이하의 데이터를 반환한다.
- 단일 행 비교 연산자와 함께 사용한다.
- ex. =, <, >, <=, >=, <>
다중 행(Multi Row) 서브쿼리
- 서브쿼리가 여러 건의 데이터를 반환한다.
- 다중 행 비교 연산자와 함께 사용한다.
- ex. IN, ALL, ANY, SOME, EXISTS
다중 컬럼(Multi Column) 서브쿼리
- 서브쿼리가 여러 컬럼의 데이터를 반환한다.

단일 행 서브쿼리(Single Row Subquery)

항상 1건 이하의 결과만 반환한다.

# 가장 가격이 높은 상품의 정보 조회
SELECT * FROM PRODUCT
 WHERE PRICE = (SELECT MAX(PRICE) FROM PRODUCT);

다중 행 서브쿼리(Multi Row Subquery)

2건 이상의 행을 반환한다.

# 리뷰가 존재하는 상품의 정보 조회
SELECT * FROM PRODUCT
 WHERE PRODUCT_CODE IN (SELECT PRODUCT_CODE FROM PRODUCT_REVIEW);

다중 컬럼 서브쿼리(Multi Column Subquery)

/* MAX_SALARY가 10,000인 직업을 가지고 있으며
   실제 SALARY가 MAX_SALARY와 일치하는 직원의 정보 조회 */
   
SELECT *
  FROM EMPLOYEES
 WHERE (JOB_ID, SALARY) IN (SELECT JOB_ID, MAX_SALARY
                              FROM JOBS
                             WHERE MAX_SALARY = 10000);

뷰(View)

특정 SELECT 문에 이름을 붙여서 재사용 가능하도록 저장해놓은 오브젝트

SQL에서 테이블처럼 사용할 수 있으며 앞서 배운 인라인 뷰를 뷰로 정의한다고 가정해보면

쿼리 작성 시 인라인 뷰가 들어가는 위치에 뷰 이름만 기술하게 될 것이다.

CREATE OR REPLACE VIEW DEPT_MEMBER AS
    SELECT A.DEPARTMENT_ID,
    	   A.DEPARTMENT_NAME,
           B.FIRST_NAME,
           B.LAST_NAME
      FORM DEPARTMENTS A
      LEFT OUTER JOIN EMPLOYEES B
        ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

뷰는 가상 테이블이라는 것을 혼돈하지 말아야 한다.

따라서 실제 데이터를 저장하지는 않고 해당 데이터를 조회해오는 SELECT 문만 가지고 있다.

# 부서별 인원을 카운트하여 인원이 많은 부서부터 정렬
SELECT DEPARTMENT_NAME, COUNT(*)
  FROM DEPT_MEMBER
 GROUP BY DEPARTMENT_NAME
 ORDER BY COUNT(*) DESC;
 
# VIEW 삭제
DROP VIEW DEPT_MEMBER;

뷰의 특징

사용자는 내부적으로 뷰를 생성하는 SQL을 볼 수 없으므로 ~~투명성~~을 가진다고는 할 수 없다.

보안성
- 보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안을 유지할 수 있다.
독립성
- 테이블 스키마가 변경되었을 경우 애플리케이션은 변경하지 않고 관련 뷰만 수정한다.
편리성
- 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용할 수 있다.

집합 연산자

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

UNION ALL
- 각 쿼리의 결과 집합의 합집합
- 중복된 행도 그대로 출력된다.
UNION
- 각 쿼리의 결과 집합의 합집합
- 중복된 행은 한 줄로 출력된다.
INTERSECT
- 각 쿼리의 결과 집합의 교집합
- 중복된 행은 한 줄로 출력된다.
MINUS/EXCEPT
- 앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합
- 중복된 행은 한 줄로 출력된다.

UNION ALL

QUERY1의 결과와 QUERY2의 결과를 그대로 합하는 것이다.

중복된 행도 그대로 출력된다.

SELECT * FROM RUNNING_MAN
UNION ALL
SELECT * FROM INFINITE_CHALLENGE;

UNION

QUERY1의 결과와 QUERY2의 결과를 합한 후 중복을 제거하여 출력한다.

SELECT * FROM RUNNING_MAN
UNION
SELECT * FROM INFINITE_CHALLENGE;

각 쿼리의 결과 집합의 합집합에 중복된 행이 없을 때는 UNION ALL과 UNION 모두 같은 결과를 도출하지만,

UNION을 사용할 때 데이터베이스 내부적으로 중복된 행을 제거하는 과정을 거쳐야 하므로 성능상 불리할 수 있다.

INTERSECT

QUERY1의 결과와 QUERY2의 결과에서 공통된 부분만 중복을 제거하여 출력한다.

SELECT * FORM RUNNING_MAN
INTERSECT
SELECT * FROM INFINITE_CHALLENGE;

MINUS / EXCEPT

QUERY1의 결과에서 QUERY2의 결과를 제거하고 출력한다.

SELECT * FROM RUNNING_MAN
MINUS
SELECT * FROM INFINITE_CHALLENGE;

그룹 함수

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

역할에 따라 다음과 같이 구분할 수 있다.

집계 함수
- COUNT, SUM, AVG, MAX, MIN 등
소계(총계) 함수
- ROLLUP, CUBE, GROUPING SETS 등

ROLLUP

소그룹 간의 소계 및 총계를 계산하는 함수이다.

ROLLUP (A)
- A로 그룹핑
- 총합계
ROLLUP (A, B)
- A, B로 그룹핑
- A로 그룹핑
- 총합계
ROLLUP(A, B, C)
- A, B, C로 그룹핑
- A, B로 그룹핑
- A로 그룹핑
- 총합계

# 날짜별, 판매 음료별, 판매 사원별 그룹핑
SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM, REG_NAME)
 ORDER BY ORDER_DT

CUBE

소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수이다.

GROUP BY가 일방향으로 그룹핑하며 소계를 구했다면 CUBE는 조합할 수 있는 모든 그룹에 대한 소계를 집계한다.

CUBE (A)
- A로 그룹핑
- 총합계
CUBE (A, B)
- A, B로 그룹핑
- A로 그룹핑
- B로 그룹핑
- 총합계
CUBE(A, B, C)
- A, B, C로 그룹핑
- A, B로 그룹핑
- A, C로 그룹핑
- B, C로 그룹핑
- A로 그룹핑
- B로 그룹핑
- C로 그룹핑
- 총합계

GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수이다.

인자값으로 ROLLUP이나 CUBE를 사용할 수도 있다.

GROUPING SETS (A, B)
- A로 그룹핑
- B로 그룹핑
GROUPING SETS (A, B, ( ))
- A로 그룹핑
- B로 그룹핑
- 총합계
GROUPING SETS (A, ROLLUP(B))
- A로 그룹핑
- B로 그룹핑
- 총합계
GROUPING SETS (A, ROLLUP(B, C))
- A로 그룹핑
- B, C로 그룹핑
- B로 그룹핑
- 총합계
GROUPING SETS (A, B, ROLLUP(C))
- A로 그룹핑
- B로 그룹핑
- C로 그룹핑
- 총합계

GROUPING

ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 Row를 구분할 수 있게 해준다.

소계를 나타내는 Row에서 그룹핑의 기준이 되는 컬럼을 제외하고는 모두 NULL 값으로 표현되었지만

GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트를 출력할 수 있다.

SELECT CASE GROUPING(ORDER_DT)
            WHEN 1 THEN 'TOTAL' ELSE ORDER_DT
       ENE AS ORDER_DT,
       COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT)
 ORDER BY ORDER_DT;

윈도우 함수

OVER 키워드와 함께 사용되며 역할에 따라 구분할 수 있다.

순위 함수
- RANK, DENSE_RANK, ROW_NUMBER
집계 함수
- SUM, MAX, MIN, AVG, COUNT
행 순서 함수
- FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수
- CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

순위 함수

RANK

순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다.

1 → 2 → 2 → 4 → 5 → 5 → 7 ...

# 날짜별로 주문 건수를 카운트해서 순위를 매긴 것
SELECT ORDER_DT,
       COUNT(*),
       RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT;

# 부서별로 급여가 높은 사원부터 순위를 매긴 것
SELECT FIRST_NAME,
       LAST_NAME,
       DEPARTMENT_ID,
       SALARY,
       RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK
  FROM EMPLOYEES;

DENSE_RANK

순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다.

1 → 2 → 2 → 3 → 4 ...

# 날짜별로 주문 건수를 카운트해서 순위를 매긴 것
SELECT ORDER_DT,
       COUNT(*),
       DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS DENSE_RANK
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT;

ROW_NUMBER

순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.

1 → 2 → 3 → 4 → 5

# 날짜별 주문 건수를 카운트 해서 순위를 매김
SELECT ORDER_DT,
       COUNT(*)
       ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS ROW_NUMBER
  FROM STARBUCKS_ORDER
 GROUP BY ORDER_DT;

집계 함수

SUM

데이터의 합계를 구하는 함수이다.

인자값으로는 숫자형만 올 수 있다.

# 모든 사람들의 점수를 합한 결과
SELECT SUM(SCORE) AS TOTAL_SCORE FROM SQLD;

# 개인별 총점수 구하기
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       SUM(SCORE) OVER(PARTITION BY STUDENT_NAME) AS TOTAL_SCORE
  FROM SQLD;

# Oracle
# OVER 절 내에 ORDER BY 절을 써서 데이터 누적값 구할 수 있음
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       SUM(SCORE) OVER(PARTITION BY STUDENT_NAME
                           ORDER BY SUBJECT DESC
                           RANGE UNBOUNDED PRECENDING) AS TOTAL_SCORE
  FROM SQLD;

# SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주게 되면
# RANGE UNBOUNDED PRECENDING 구문 없이도 누적합 집계
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       SUM(SCORE) OVER(ORDER BY SCORE DESC) AS SUM_SCORE
  FROM SQLD
 WHERE SUBJECT = 'SQL 기본 및 활용';

MAX

데이터의 최댓값을 구하는 함수이다.

# SQLD 시험 성적 데이터에 MAX 함수를 적용해 본 것
SELECT MAX(SCORE) AS MAX_SCORE FROM SQLD;

# 과목별 최대 점수 구하기
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       MAX(SCORE) OVER(PARTITION BY SUBJECT) AS MAX_SCORE
  FROM SQLD;

MIN

데이터의 최솟값을 구하는 함수이다.

# SQLD 시험 성적 데이터에 MIN 함수를 적용
SELECT MIN(SCORE) AS MIN_SCORE FROM SQLD;

# 과목별 최소 점수 구하기
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       MIN(SCORE) OVER(PARTITION BY SUBJECT) AS MIN_SCORE
  FROM SQLD;

AVG

데이터의 평균값을 구하는 함수이다.

# SQLD 시험 성적 데이터에 AVG 함수 적용
SELECT AVG(SCORE) AS AVG_SCORE FROM SQLD;

# 과목별 평균 점수 구하기
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       ROUND(AVG(SCORE) OVER(PARTITION BY SUBJECT)) AS AVG_SCORE
  FROM SQLD;

COUNT

데이터의 건수를 구하는 함수이다.

# SQLD 시험 성적 데이터에 COUNT 함수 적용
SELECT COUNT(*) AS SCORE_COUNT FROM SQLD;

# 과목별로 PASS한 건수 구하기
SELECT STUDENT_NAME,
       SUBJECT,
       SCORE,
       COUNT(*) OVER(PARTITION BY SUBJECT) AS PASS_COUNT
  FROM SQLD
 WHERE RESULT = 'PASS';