SQLD_2과목_2장 SQL 활용_4절 서브쿼리, 5절 그룹 함수, 6절 윈도우 함수

2장 SQL 활용

 

4절 서브쿼리

서브쿼리: 하나의 SQL문 안의 SQL문
종류
  ⦁ 동작 방식에 따른 분류
    - 비연관 서브쿼리: 메인쿼리 칼럼을 가지고 있지 않는 서브쿼리, 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용함
       ⦁ Access Subquery: 제공자 역할
       ⦁ Filter Subquery: 확인자 역할
       ⦁ Early Filter Subquery: 데이터 필터링 역할
   - 연관 서브쿼리(Associative Subquery): 메인쿼리의 결과를 조건이 맞는지 확인하기 위한 목적으로 주로 사용함

 ⦁ 반환 데이터 형태에 따른 분류
   ⦁ 단일 행 서브쿼리: 실행 결과가 1건 이하인 서브쿼리, 단일 행 비교 연산자와 함께 사용
   ⦁ 다중 행 서브쿼리: 실행 결과가 여러 건인 서브쿼리, 다중 행 비교 연산자와 함께 사용
      ※ 다중 행 비교 연산자
          ⦁ IN : 서브쿼리의 결과 중 하나의 값이라도 동일하다는 조건
          ⦁ ANY : 서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건
          ⦁ ALL : 서브쿼리의 모든 결과값을 만족한다는 조건
          ⦁ EXISTS : 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건, ‘WHERE EXISTS (SELECT ~)’

                            (항상 연관 서브쿼리로 사용)
    ⦁ 다중 칼럼 서브쿼리: 실행 결과로 여러 칼럼 반환, 주로 메인쿼리의 조건과 비교하기 위해 사용

                                       (비교하고자 하는 칼럼의 개수와 위치가 동일해야 함)

스칼라 서브쿼리: 값 하나를 반환하는 서브쿼리, SELECT절에 사용하는 서브쿼리

뷰: 가상의 테이블, FROM절에 사용하는 뷰는 인라인 뷰(Inline View)라고 함
  ⦁ 장점
    ⦁ 독립성: 테이블 구조 변경 자동 반영
    ⦁ 편리성: 쿼리를 단순하게 작성할 수 있음, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용할 수 있음
    ⦁ 보안성: 뷰를 생성할 때 칼럼을 제외할 수 있음

 

WITH: 서브쿼리를 이용하여 뷰로 사용할 수 있는 구문
SQL>> WITH 뷰명 AS (SELECT ~)

---

5절 그룹 함수

ANSI/ISO 표준 데이터 분석 함수: 집계 함수, 그룹 함수, 윈도우 함수
그룹 함수(Group Function): 합계 계산 함수, NULL을 빼고 집계함 (~ 집계 함수), 결과값 없는 행은 출력 안함
  ⦁ ROLLUP : GROUP BY로 묶인 칼럼의 소계 계산, 계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 바뀜
  ⦁ CUBE : 조합 가능한 모든 값에 대해 다차원 집계
  ⦁ GROUPING SETS : 특정 항목에 대한 소계 계산, GROUP BY의 칼럼 순서와 무관하게 개별적으로 처리함

표현식	출력값
GROUP BY ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 소계 / 총합계
GROUP BY CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
GROUP BY GROUPING SETS (E1,E2)	E1별 소계 / E2별 소계

※ ‘GROUP BY CUBE (E1,E2)’와 ‘GROUP BY GROUPING SETS (E1,E2,(E1,E2),())’는 동일한 결과 출력

GROUPING : 그룹 함수에서 생성되는 합계를 구분해주는 함수, 소계나 합계가 계산되면 1 아니면 0 반환

---

6절 윈도우 함수

 

윈도우 함수(Window Function): 여러 행 간의 관계 정의 함수, 중첩 불가

  순위 함수
  ⦁ RANK : 중복 순위 포함
  ⦁ DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음)
  ⦁ ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여

  일반집계 함수: SUM, MAX, MIN, AVG, COUNT

  행 순서 함수
  - FIRST_VALUE, LAST_VALUE : 첫 값, 끝 값
  - LAG, LEAD : 이전 행, 이후 행 (Oracle) 랙릿
    ※ ‘LEAD(E,A)’는 E에서 A번째 행의 값을 호출하는 형태로도 쓰임 (A의 기본값은 1)

  비율 관련 함수
  ⦁ PERCENT_RANK() : 백분율 순서
  ⦁ CUME_DIST() : 현재 행 이하 값을 포함한 누적 백분율
  ⦁ NTILE(A) : 전체 데이터 A등분
  ⦁ RATIO_TO_REPORT : 총합계에 대한 값의 백분율

 

윈도우 함수 문법

SELECT 윈도우함수(A) OVER (PARTITION BY 칼럼 ORDER BY 칼럼 윈도잉절) 
FROM 테이블명;

 

  ⦁ PARTITION BY : 그룹핑 기준
  ⦁ ORDER BY : 순위 지정 기준
  ⦁ 윈도잉절 : 함수의 대상이 되는 행 범위 지정
    - BETWEEN A AND B : 구간 지정
      ⦁ N PRECEDING, N FOLLOWING : N번째 앞 행, N번째 뒤 행
      ⦁ UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행
      ⦁ CURRENT ROW : 현재 행
    - ROWS, RANGE : 행 지정, 값의 범위 지정