[SQLD] 2과목 (2) SQL 기본 및 활용 - 서브 쿼리, 집합 연산자, 그룹 함수, 윈도우 함수, Top N 쿼리, 계층형 질의와 셀프 조인, PIVOT 절과 UNPIVOT절, 정규 표현식

9. 서브 쿼리 

- 서브쿼리 

- 서브쿼리 사용 가능한 위치: GROUP BY절 사용 불가 

- 서브쿼리 종류 

    1) 동작하는 방식에 따라: UN-CORRELATED 서브쿼리, CORRELATED 서브쿼리 

    2) 위치에 따라: 스칼라 서브쿼리(SELECT절), 인라인뷰(FROM절), WHERE절 서브쿼리  

- WHERE절 서브쿼리 종류: 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리. 상호연관 서브쿼리 

    - ALL과 ANY 비교 

    - EXISTS / NOT EXISTS 연산자 

- 인라인뷰: 쿼리 안의 뷰 

- 스칼라 서브쿼리 

 

10. 집합 연산자 

- 집합 연산자

- 합집합: UNION(UNION ALL)

- 교집합: INTERSECT

- 차집합: MINUS 

 

11.그룹 함수 

- 그룹함수

- COUNT: NULL은 세지 않는다, count(*)은 항상 모든 행의 수 출력

- SUM

- AVG: NULL을 제외한 대상의 평균

- MIN/MAX

- VARIANCE / STDDEV

- GROUP BY FUNCTION 

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

    2) ROLLUP(A,B) : (A,B) -> A -> 전체 행  

    3) CUBE(A,B): (A,B) -> A -> B -> 전체 행

 

12. 윈도우 함수

- WINDOW FUNCTION 

    1) PARTITION BY절

    2) ORDER BY절

    3) ROWSlRANGE BETWEEN A AND B

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

    1) SUM OVER()

    2) AVG OVER()

    3) MIN/MAX OVER()

    4) COUNT

- 윈도우 함수 연산 대상 및 범위 : ROWSlRANGE BETWEEN A AND B

    1) 연산대상 

    2) 범위 : A(시작점) - CURRENT ROW, UNBOUNDED PRECEDING, N PRECEDING, B(마지막 지점) - CURRENT ROW, UNBOUNDED FOLLOWING, N FOLLOWING

- 순위 관련 함수

     1) RANK(순위): RANK WITHIN GROUP, RANK() OVER(), DENSE_RANK, ROW_NUMBER

- 행 간의 값 참조함수

    - LAG(이전값), LEAD(다음값)

    - FIRST_VALUE, LAST_VALUE(범위 지정 키워드 필요) 

- 비율함수

    1) NTILE: 정해진 수의 그룹으로 나누기 위한 함수 

    2) CUME_DIST(): 해당 값보다 작거나 같은 값의 비율 

    3) PERCENT RANK(): 주어진 값이 데이터 집합 내에서 상대적으로 어디에 위치하는지를 비율로 나타낸 함수 
    4) RATIO_TO_REPORT() : 각 값이 전체 합에서 차지하는 비율

 

13. Top N 쿼리  

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

- TOP N 행 추출 방법

    1) ROWNUM

    2) RANK

    3) FETCH: 출력될 행의 수를 제한 

    4) TOP N (SQL Server) 

 

14. 계층형 질의와 셀프 조인 

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

- 계층형 질의 조건

    1) CONNECT BY절 조건: START WITH 절 데이터 항상 출력

    2) WHERE절 조건: START WITH 절 데이터가 조건에 맞지 않는 경우 생략 

- 계층형 질의 가상 컬럼: LEVEL, CONNECT_BY_ISLEAF

- 계층형 질의 가상 함수 

    1) CONNECT_BY_ROOT: 루트노트의 컬럼값

    2) SYS_CONNECT_BY_PATH: 이어지는 경로

    3) ORDER SIBILINGS BY: 같은 레벨일 경우 정렬

    4) CONNECT_BY_ISCYCLE: 순환 발생 

 

15. PIVOT절과 UNPIVOT절

- 데이터의 구조: LONG DATA(tidy data), WIDE DATA(Cross table) 

- 데이터 구조 변경

    1) PIVOT: LONG -> WIDE = 교차표 만드는 기능

    2) UNPIVOT: WIDE -> LONG

 

16. 정규 표현식 

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

- REGEXP_REPLACE: 정규식 표현을 사용할 문자열 치환 가능 

- REGEXP_SUBSTR: 정규식 표현식을 사용한 문자열 추출 

- REGEXP_INSTR: 특정 패턴의 시작 위치 반환 

- REGEXP_LIKE: 특정패턴 갖는 경우 반환

- REGEXP_COUNT: 특정패턴의 횟수 반환