SQLD 2과목 - SQL 활용

출처: https://www.youtube.com/watch?v=EXx6fjxycSY&t=3352s

 

서브 쿼리

- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL

- 반드시 괄호로 묶어야 함

- GROUP BY 절 빼고 모두 사용 가능하다.

 

서브쿼리 종류

- 동작하는 방식에 따라 비연관 쿼리와 연관 쿼리로 나뉨

- 위치에 따라 스칼라 서브쿼리(SELECT 절에서), 인라인뷰(FROM절에서), WHERE 절 서브쿼리(가장 일반적)

- 주의사항: ORDER BY절을 TOP-N 분석처럼 특별한 경우를 제외하고는 사용 불가

WHERE절 서브쿼리

1. 단일행 서브쿼리

2. 다중행 서브쿼리

- 비교 연산자 사용불가

3. 다중컬럼 서브쿼리

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

- 대소 비교 전달 불가

4. 상호연관 서브쿼리

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

 

인라인뷰

- 쿼리 안의 뷰 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용

- 메인쿼리 절의 어떤 절에서도 다 표현 가능

 

스칼라 서브쿼리

- SELECT 절에 사용하는 쿼리로, 마치 하나의 컬럼처럼 표현하기 위해 사용

- 각 행마다 스칼라 서브쿼리 결과가 하나여야 함

 

집합 연산자

- SELECT 문 결과를 하나의 집합으로 간주, 그 집합에 대한 합집합, 교집합, 차집합 연산

- 두 집합의 컬럼 수, 순서, 데이터 타입 일치해야 함 / 사이즈는 달라도 

 

합집합

1. UNION

- 중복된 데이터는 한 번만 출력

- 중복된 데이터를 제거하기 위해 내부적으로 정렬 수행

 

2. UNION ALL

- 중복된 데이터도 전체 출력

교집합

- 두 집합 사이에 INTERSECT

- 공통으로 포함된 데이터 가져올 때 사용

 

차집합

- A-B와 B-A는 다르기 때문에 주의

- 두 집합 사이에 MINUS

 

그룹함수

- 숫자함수 중 여러값을 전달하여 하나의 요약값을 출력하는 다중행 함수

- 수학/통계 함수들

- GROUP BY 절에 의해 그룹별 연산 결과를 리턴 함

- 반드시 한 컬럼만 전달

- NULL은 무시하고 연산

 

COUNT

- 행의 수를 세는 함수

- * 또는 하나의 컬럼만 전달 가능

SUM

- 총 합 출력

- 숫자 컬럼만 전달 가능

AVG

- 평균 출력

- 숫자 컬럼만 전달 가능

- NULL이 포함된 평균 계산시 주의 필

MIN / MAX

- 최대, 최소 출력

GROUP BY FUNCTION

1. GROUPING SETS(A, B, ...)

- A별 B별 그룹 연산 결과 출력

 

2. ROLLUP(A, B)

- A별, AB별, 전체 그룹 연산 결과 출력

- 나열 대상의 순서가 중요

- 기본적으로 전체 총 계가 출력

 

3. CUBE(A, B)

- A별, B별, AB별, 전체 그룹 연산 결과 출력

- 그룹으로 묶을 대상의 나열 순서 중요X

- 기본적으로 전체 총 계가 출력

WINDOW FUNCTION

- 서로 다른 행의 비교나 연산을 위해 만든 함수

- GROUP BY를 쓰지 않고 그룹 연산 가능

- LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK

그룹 함수의 형태 윈도우 함수

- SUM, COUNT, AVG, MIN, MAX 등

- OVER 절을 사용하여 윈도우 함수로 사용 가능

 

순위 관련 함수

1. RANK(순위)

1-1. RANK WITHIN GROUP

- 특정 값에 대한 순위 확인

- 윈도우 함수가 아닌 일반 함수

- 동순위 처리 후 그 다음 순위 결정 방식

 

2. DENSE_RANK

- 누적순위

- 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식

 

3.ROW_NUMBER()

- 연속된 행 번호

- 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리

 

비율관련 함수

1. RATIO_TO_REPORT

- 각 값의 비율 리턴

- ORDER BY 사용하면 안 됨

 

2. CUME_DIST()

- 각 값의 누적 비율 리턴

- ORDER BY 필수

 

3. PERCENT_RANK()

- PERCENTILE 분위 수 출력

- 전체 COUNT 중 상대적 위치 출력(0~1 범위 내)

- ORDER BY 필수

TOP N QUERY

- 페이징 처리를 효과적으로 수행하기 위해 사용

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

ROWNUM

- 출력된 데이터 기준으로 행 번호 부여

- 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음

- 첫번째 행이 증가한 이후 할당되므로 '>' 연산 사용 불가(0은 가능)

FETCH 절

- 출력된 행의 수를 제한하는 

- ORACLE 12C 이상부터 제

- SQL Server에서 사용 가능

- ORDER BY 절 뒤에 사용(내부 파싱 순서도 ORDER BY )

계층형 질의

- 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층을 표현하는 기법

- PRIOR 의 위치에 따라 연결하는 데이터가 달라짐

- START WITH와 CONNECT BY PRIOR

 PIVOT

- 교차표를 만드는 기능

- 고정할 STACK 컬럼과 펼칠 UNSTACK 컬럼 값들 VALUE 컬럼

- START WITH와 CONNECT BY PRIOR

- ex) SELECT * FROM ~ PIVOT ~

 UNPIVOT

- WIDE 데이터를 LONG 데이터로 변경하는 문법

- STACK 컬럼, VALUE 컬럼, 값1값2 필요

- ex) SELECT * FROM ~ UNPIVOT ~

정규 표현식

- 문자열의 공통된 규칙을 보다 일반화 하여 표현하는 방법

- [a-z], ^, $ 등