[SQLD: PART 2] SQL 활용

본 포스팅은 SQLD 자격증 취득을 위해 공부한 내용을 정리하기 위한 포스팅입니다.

1. 서브 쿼리

1. SELECT 절: 스칼라 서브쿼리
2. FROM 절: 인라인 뷰
3. WHERE, HAVING 절: 중첩 서브쿼리

2. 중첩 서브 쿼리

1. 비연관 서브쿼리

2. 연관 서브쿼리
(1) 단일 행 서브쿼리
(2) 다중 행 서브쿼리
(3) 다중 컬럼 서브쿼리

3. 뷰(VIEW)

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

4. 집합 연산자

1. UNION: 중복된 행 한 줄로 출력. 자동정렬.
2. UNION ALL
3. INTERSECT
4. MINUS / EXCEPT

5. 그룹 함수

1. 집계 함수

(1) COUNT
(2) SUM
(3) AVG
(4) MAX
(5) MIN
(6) VARIANCE
(7) STDDEV

2. 소계(총계) 함수

(1) ROLLUP(A, B): A, B / A / 총계
(2) CUBE(A, B): A, B / A / B / 총계
(3) GROUPING SETS(A, B): A / B ex) GROUPING SETS(A, B, ()) = A / B / 총계

3. GROUPING

소계를 타나내는 ROW를 구분할 수 있게 해준다.

6. 윈도우 함수

1. PARTITION 절: 출력할 총 데이터 수 변화없이 그룹연산을 수행할 GROUP BY 컬럼

2. ORDER BY 절: RANK의 경우 필수 / SUM, MIN, MAX, AVG 등은 누적값 출력시 사용

3. ROWS | RANGE BETWEEN A AND B: 연산범위 설정 / ORDER BY 절 필수

(1) ROWS: 값이 같더라도 한 행씩 계산
(2) RANGE: 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산(DEFAULT)

4. 문법

SELECT 윈도우함수([대상]) OVER([PARTITION BY 컬럼]
[ORDER BY 컬럼 ASC | DESC]
[ROWS | RANGE BETWEEN A AND B]);

7. BETWEEN A AND B

1. 시작점 정의

(1) CURRENT ROW
(2) UNBOUNDED PRECEDING -> DEFAULT
(3) N PRECEDING

2. 마지막 시점 정의

(1) CURRENT ROW -> DEFAULT
(2) UNBOUNDED FOLLOWING
(3) N FOLLOWING

8. 순위 함수

1. RANK ex) 1, 2, 2, 4, 5 ...
2. DENSE_RANK ex) 1, 2, 2, 3, 4 ...
3. ROW_NUMBER ex) 1, 2, 3, 4, 5 ...

9. 행 순서 함수

1. FIRST_VALUE
2. LAST_VALUE
3. LAG: 특정 수만큼 앞선 데이터
4. LEAD: 특정 수만큼 뒤에 있는 데이터

10. 비율 함수

1. RATIO_TO_REPORT: 파티션별 합계에서 차지하는 비율(0<=x<=1)
2. PERCENT_RANK: 백분위 순위 값(0<=x<=1)
3. CUME_DIST: 누적 백분율(0<x<=1)

11. TOP N 쿼리

ROWNUM: 슈도 컬럼, 항상 < or <= 조건으로 사용.

12. 셀프 조인

반드시 ALIAS 표기해야 한다.

13. 계층 쿼리

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

ex) PRIOR 자식 = 부모 -> 순방향 / 자식 = PRIOR 부모 -> 역방향

1. LEVEL: 루트 노드의 경우 1
2. SYS_CONNECT_BY_PATH(컬럼, 구분자)
3. START WITH
4. CONNECT BY
5. PRIOR: 바로 앞에 있는 부모 노드의 값을 반환
6. NOCYCLE
7. CONNECT_BY_ROOT 컬럼: 루트 노드의 주어진 컬럼 값 반환
8. CONNECT_BY_ISLEAF: 하위 노드인 경우 1, 그 외에는 0을 반환
9. ORDER SIBLINGS BY 컬럼: 같은 레벨인 경우 정렬