[2-2과목] SQL 활용

SQLD

[2-2과목] SQL 활용

Donghun Kang 2024. 11. 7. 11:25

2-9. 서브쿼리

서브쿼리(Subquery)

- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
- 반드시 괄호로 묶어야 함

서브쿼리 사용 가능한 곳

- SELECT 절

- FROM 절

- WHERE 절

- HAVING 절

- ORDER BY 절

- 기타 DML(INSERT, DELETE, UPDATE) 절

=> GROUP BY 절 사용 불가

서브쿼리 종류

1. 비연관 서브쿼리

- 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태의 서브쿼리

- 메인쿼리에 서브쿼리가 실행된 결과 값을 제공하기 위한 목적으로 사용

2. 연관 서브쿼리

- 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브쿼리

- 일반적으로 메인쿼리가 먼저 수행된 후 서브쿼리에서 조건이 맞는지 확인하고자 할때 사용

3. 스칼라 서브쿼리

- SELECT에 사용하는 서브쿼리

- 서브쿼리 결과를 마치 하나의 컬럼처럼 사용하기 위해 주로 사용

4. 인라인 뷰

- FROM절에 사용하는 서브쿼리

- 서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용

- 서브쿼리 결과를 메인 쿼리의 어느 절에서도 사용할 수 있다.

- 테이블명이 존재하지 않기 때문에 다른 테이블과 조인 시 반드시 테이블 별칭 명시(단독으로 사용하는 경우 불필요)

- 모든 연산자 사용 가능

5. WHERE절 서브쿼리

- 가장 일반적인 서브쿼리

- 값을 전달하기 위한 목적으로 주로 사용(상수항의 대체)

- return 데이터 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리, 상호연관 서브쿼리로 구분

1) 단일행 서브쿼리

- 서브쿼리 결과가 1개의 행이 return되는 형태

연산자	의미
=	같다
<>	같지 않다
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다

2) 다중행 서브쿼리

- 서브쿼리 결과가 여러 행이 return되는 형태

연산자	의미
IN	같은 값을 찾음
> ANY	최소값을 반환
< ANY	최대값을 반환
< ALL	최소값을 반환
> ALL	최대값을 반환

3) 다중컬럼 서브쿼리

- 서브쿼리 결과가 여러 컬럼이 return되는 형태

4) 상호연관 서브쿼리

- 메인쿼리와 서브쿼리의 비교를 수행하는 형태

# 서브쿼리 주의사항

- 특별한 경우(TOP-N분석 등)을 제외하고는 서브쿼리 절에 ORDER BY절을 사용 불가

- 단일 행 서브쿼리와 다중 행 서브쿼리에 따라 연산자의 선택이 중

2-10. 집합 연산자

집합 연산자

- UNION ALL / UNION / INTERSECT / MINUS / EXCEPT 연산
- 두 집합의 컬럼이 동일하게 구성되어야 한다.(각 컬럼의 데이터 타입과 순서 일치 필요)

UNION(합집합)

- QUERY1의 결과와 QUERY2의 결과를 합한 후 중복을 제거하여 출력
- 중복된 데이터를 제거하기 위해 내부적으로 정렬 수행
- 중복된 데이터가 없을 경우 UNION ALL 사용 (불필요한 정렬 발생할 수 있기 때)

UNION ALL

- QUERY1의 결과와 QUERY2의 결과를 그대로 합하는 것.
- 중복된 행도 그대로 출력

INTERSECT(교집합)

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

MINUS / EXCEPT

- QUERY1의 결과에서 QUERY2의 결과를 제거하고 출력
- A-B와 B-A는 다르므로 집합의 순서 주의

# 집합 연산자 사용시 주의사항

- 두 집합의 컬럼 수 일치

- 두 집합의 컬럼 순서 일치

- 두 집합의 각 컬럼의 데이터 타입 일치

- 각 컬럼의 사이즈는 달라도 됨

- 개별 SELECT문에 ORDER BY 전달 불가(GROUP BY 전달 가능)

2-11. 그룹 함수

그룹 함수

- GROUP BY절에서 사용하는 함수
- 여러 GROUP BY 결과를 동시에 출력(합집합)하는 기능
- 그룹핑 할 그룹을 정의

GROUPING SETS(A, B, ...)

- A별, B별 그룹 연산 결과 출력
- 나열 순서 중요하지 x
- 기본 출력에 전체 총계는 출력되지 x
- NULL 혹은 () 사용하여 총 합 출력 가능

EX) GROPING SETS(A, B) => A로 그룹핑 / B로 그룹핑

EX) GROPING SETS(A, B, ()) => A로 그룹핑 / B로 그룹핑 / 총합계

EX) GROPING SETS(A, ROLLUP(B,C)) => A로 그룹핑 / (B,C)로 그룹핑 / B로 그룹핑 / 총합계

ROLL UP(A, B)

- A별, (A,B)별, 전체 그룹 연산 결과 출력
- 나열 대상의 순서가 중요
- 기본적으로 전체 총계가 출력

EX) ROLL UP(A, B) => A로 그룹핑 / (A,B)로 그룹핑 / 총합계

EX) ROLL UP(A, B, C) => (A,B,C)로 그룹핑 / (A,B)로 그룹핑 / A로 그룹핑 / 총합계

CUBE(A, B)

- A별, B별, (A, B)별, 전체 그룹 연산 결과 출력
- 그룹으로 묶을 대상의 나열 순서 중요하지 X
- 기본적으로 전체 총계가 출력

EX) CUBE(A, B) => A로 그룹핑 / B로 그룹핑 / (A,B)로 그룹핑 / 총합계

EX) CUBE(A, B, C) => A로 그룹핑 / B로 그룹핑 / C로 그룹핑 / (A,B)로 그룹핑 / (B,C)로 그룹핑 / (A,C)로 그룹핑 / (A,B,C)로 그룹핑 / 총합계

2-12. 윈도우 함수

WINDOW FUNCTION

- 서로 다른 행의 비교나 연산을 위해 만든 함수
- GROUP BY를 쓰지 않고 그룹 연산 가능
* PARTITION BY, ORDER BY, ROWS...절 전달 순서 중요(ORDER BY를 PARTITION BY절 전에 사용 불)

그룹 함수의 형태

- OVER절을 사용하여 윈도우 함수로 사용 가능
- 반드시 연산할 대상을 그룹함수의 입력값으로 전달

순위 함수

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

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

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

순위 함수	EX)
RANK	1, 2, 2, 4, 5, 5, 7 ...
DNESE_RANK	1, 2, 2, 3, 4, 4, 5 ...
ROW_NUMBER	1, 2, 3, 4, 5, 6, 7 ...

집계 함수

1. SUM: 데이터의 합계를 구하는 함수, 인자값으로는 숫자형만 올 수 있다.

2. MAX: 데이터의 최댓값을 구하는 함수

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

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

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

# 윈도우 함수의 연산 범위

BETWEEN A AND B

- 시작점 정의

범위	의미
CURRENT ROW	현재행부터
UNBOUNDED PRECEDING	처음부터(DEFAULT)
n PRECEDING	n 이전부터

- 마지막 시점 정의

범위	의미
CURRENT ROW	현재행까지(DEFAULT)
UNBOUNDED FOLLOWING	마지막까지
n FOLLOWING	n 이후까지

- ROW / RANGE

기준	의미
ROWS	- 값이 같더라도 각 행씩 연산 - 행 자체가 기준
RANGE	- 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산(DEFAULT) - 행이 가지고 있는 데이터 값이 기준

행 순서 함수

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

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

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

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

비율 함수

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

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

3. CUME_DIST: 해당 파티션에서의 누적 백분위를 구하는 함

2-13. TOP-N 쿼리

TOP-N QUERY

- 전체 결과에서 특정 N개 추출

추출 방법

ROWNUM

- 출력된 데이터 기준으로 행 번호 부여
- 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없다.('=' 연산 불가)
- 첫번째 행이 증가한 이후 할당되므로 '>' 연산 사용 불가(0은 가능)

FETCH절

- 출력될 행의 수를 제한하는 절
- ORACLE 12C 이상부터 제공(이전 버전에는 ROWNUM 주로 사용)
- SQL-Server 사용 가능
- ORDER BY절 뒤에 사용(내부 파싱 순서도 ORDER BY 뒤)

- OFFSET: 건너뛸 행의 수

- N: 출력할 행의 수

- FETCH: 출력할 행의 수를 전달하는 구문

- FIRST: OFFSET을 쓰지 않았을 때 처음부터 N행 출력 명령

- NEXT: OFFSET을 사용했을 경우 제외한 행 다음부터 N행 출력 명령

- ROW | ROWS: 행의 수에 따라 하나의 경우 단수, 여러값이면 복수형(특별히 구분하지 않아도 됨)

2-14. 계층형 질의

계층형 질의

- 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층을 표현하는 기법
- PRIOR의 위치에 따라 연결하는 데이터가 달라짐

** START WITH: 데이터를 출력할 시작 지정하는 조건
** CONNECT BY PRIOR: 행을 이어나갈 조건
** NOCYCLE: 순환이 발생하면 무한 루프가 될 수 있기 때문에 이를 방지하고자 사

계층형 질의 가상 컬럼

1. LEVEL: 각 DEPTH를 표현(시작점부터 1)

2. CONNECT_BY_ISLEAF: LEAF NODE(최하위노드) 여부 (참:1, 거짓:0)

계층형 질의 가상 함수

1. CONNECT_BY_ROOT 컬럼명: 루트노드의 해당하는컬럼값

2. SYS_CONNECT_BY_PATH(컬럼, 구분자): 이어지는 경로 출력

3. ORDER SIBLINGS BY 컬럼: 같은 LEVEL일 경우 정렬 수행

4. CONNECT_BY_ISCYCLE: 계층형 쿼리의 결과에서 순환이 발생했는지 여부

ORDER SIBLINGS BY를 사용하여 같은 레벨일 경우 DNAME 오름차순으로 정렬

1000번 직원의 매너지는 2000번 사윈인데, 2000번 사원도 1000번 직원의 매니저이다. => ERROR발

2-15. PIVOT과 UNPIVOT

데이터의 구조

1. LONG DATA(Tidy data): 하나의 속성이 하나의 컬럼으로 정의되어 값들이 여러 행으로 쌓이는 구조

2. WIDE DATA(Cross table): 행과 컬럼에 유의미한 정보 전달을 목적으로 작성하는 교차표

데이터 구조 변경

1. PIVOT: LONG => WIDE

2. UNPIVOT: WIDE => LONG

PIVOT

- 교차표를 만드는 기능
- FROM절에 STACK, UNSTACK, VALUE 컬럼명만 정의 필요
- PIVOT절에 UNSTACK, VALUE 컬럼명 정의
- PIVOT절 IN 연산자에 UNSTACK 컬럼 값을 정의

EX)

UNPIVOT

- WIDE 데이터를 LONG 데이터로 변경하는 문법
- STACK컬럼: 이미 UNSTACK되어 있는 여러 컬럼을 하나의 컬럼으로 STACK시 새로 만들 컬럼이름(사용자 정의)
- VALUE컬럼: 교차표에서 셀 자리(VALUE)값을 하나의 컬럼으로 표현하고자 할 때 새로 만들 컬럼명(사용자 정의)
- 값1, 값2: 실제 UNSACK되어 있는 컬럼 이름들