[SQLD]230308-09 SQLD

SQL 활용

서브쿼리

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

• SELECT 절: 스칼라 서브쿼리

주로 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;

• 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;

• WHERE, HAVING 절: 중첩 서브쿼리

WHERE절과 HAVING절에 사용 가능

메인 쿼리와의 관계에 따라 비연관 서브쿼리와 연관 서브쿼리로 나눌 수 있음

• 비연관 서브쿼리: 서브쿼리 내에 메인 쿼리의 컬럼 존재하지 않음
• 연관 서브쿼리: 서브쿼리 내에 메인 쿼리의 컬럼 존재

반환하는데이터 형태에 따라 단일 행 서브쿼리, 다중 행 서브쿼리 , 다중 컬럼 서브쿼리로 나눌 수 있음

• 단일 행 서브쿼리: 항상 1건 이하의 결과만 반환
• 다중 행 서브쿼리: 2건 이상의 행을 반환
• 다중 컬럼 서브쿼리: 여러컬럼의 데이터를 반환

뷰

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

테이블처럼 사용할 수 있고 인라인 뷰를 뷰 이름으로 대체 가능

뷰는 가상 테이블이기 때문에 실제 데이터를 저장하지 않고 데이터 조회 기능만 가짐

집합 연산자

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

UNION ALL / UNION

• UNION ALL: 두 쿼리의 결과를 그대로 합하는 것, 중복행도 출력
• UNION: 두 쿼리의 결과를 합한 후 중복을 제거하여 출력

INTERSECT

두 쿼리의 결과에서 공통된 부분만 중복을 제거하여 출력

MINUS / EXCEPT

쿼리에서 다른 쿼리와 겹치는 결과를 제거하여 출력

그룹 함수

데이터를 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로 그룹핑, 총 합계

CUBE

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

GROUP BY와 달리 조합 가능한 모든 그룹에 대한 소계를 집계

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

GROUPING SETS

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

• 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 등과 함께 쓰임

그룹핑 기준이 되는 컬럼 포함 원하는 위치에 NULL이 아닌 원하는 텍스트를 출력 가능

윈도우 함수

순위 함수

• RANK: 순위를 매기면서 같은 순위가 존재하면 그 수만큼 다음 순위 건너뜀
• DENSE_RANK: 순위를 매기면서 같은 순위가 있어도 다음 순위 건너뛰지 않음
• ROW_NUMBER: 순위를 매기면서 같은 값이라도 다른 순위를 부여

집계 함수

• SUM: 데이터의 합계, 인자값은 숫자형만 가능
• MAX, MIN: 데이터의 최댓값, 최솟값
• AVG: 데이터의 평균값
• COUNT: 데이터의 건수

행 순서 함수

• FIRST_VALUE: 파티션 별 가장 선두에 위치한 데이터를 구하는 함수
• LAST_VALUE: 파티션 별 가장 끝에 위치한 데이터를 구하는 함수
• LAG: 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수
• LEAD: 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수

비율 함수

• RATIO_TO_REPORT: 파티션 별 합계에서 차지하는 비율을 구하는 함수
• PERCENT_RANK: 파티션의 맨위를 0, 맨아래를 1로 놓고 현재 행의 백분위 순위값을 구하는 함수
• CUME_DIST: 파티션에서의 누적 백분율을 구하는 함수
• NTILE: 주어진 수만큼 행들을 N등분 한 후 현재 행에 해당하는 등급을 구하는 함수

Top-N 쿼리

ROWNUM

Oracle에서 ROWNUM은 실제로 존재하지 않는 슈도 컬럼

SELECT ROWNUM,
			 NAME,
			 KOR,
			 ENG,
			 MATH
  FROM (
		SELECT NAME,
					 KOR,
					 ENG,
					 MATH
			FROM EXAM_SCORE
		 ORDER BY KOR DESC, ENG DESC, MATH DESC )
WHERE ROWNUM <= 5;

윈도우 함수의 순위 함수

SELECT * FROM (
		SELECT RANK() OVER(ORDER BY KOR DESC, ENG DESC, MATH DESC) AS RANK,
					 NAME,
					 KOR,
					 ENG,
					 MATH
			FROM EXAM_SCORE)
WHERE RANK <= 5;

셀프 조인

나 자신과의 조인, FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에 ALIAS를 표시

SELECT A.CATEGORY_TYPE,
			 A.CATEGORY_NAME,
			 B.CATEGORY_TYPE,
			 B.CATEGORY_NAME,
	FROM CATEGORY A,
			 CATEGORY B
WHERE A.CATEGORY_NAME = B.PARENT_CATEGORY
  AND A.CATEGORY_TYPE = '대';

계층 쿼리

계층 구조를 이루는 컬럼이 존재할 경우 데이터 출력에 사용

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

 

참고자료: 2022 유선배 SQL개발자(SQLD) 과외노트

http://www.yes24.com/Product/Goods/115027455

2023 유선배 SQL개발자(SQLD) 과외노트 - YES24