[SQLD] 2과목 - 2. SQL 활용

 

[ 2과목 - 2. SQL 활용 ]

 * 서브쿼리
   - 하나의 쿼리 안에 존재하는 또다른 쿼리
   - 메인쿼리와 서브쿼리로 구성
   (1) 스칼라 서브쿼리 (Scalar Subquery)
     : 주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있음
     : 컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야 하며 그렇지 않은 경우 에러를 발생시킴
   (2) 인라인 뷰 (Inline View)
     : FROM 절 등 테이블명이 올 수 있는 위치에 사용 가능함
   (3) 중첩 서브쿼리 (Nested Subquery)
      1. Where 절과 Having 절에 사용할 수 있음
        - 비연관 서브쿼리 (Uncorrelated Subquery) : 서브쿼리 내에 메인쿼리의 컬럼이 존재하지 않음
        - 연관 서브쿼리 (Correlated Subquery) : 서브쿼리 내에 메인쿼리의 컬럼이 존재
      2. 중첩 서브쿼리는 반환하는 데이터 형태에 따라 다음과 나눠짐
        - 단일 행(Single Row) 서브쿼리
          : 서브쿼리가 1건 이하의 데이터를 반환
          : 단일 행 비교 연산자와 함께 사용
          : 항상 1건 이하의 결과만 반환함
          : =, <, >, <=, >=, <>
        - 다중 행(Multi Row) 서브쿼리
          : 서브쿼리가 여러 건의 데이터를 반환 
          : 다중 행 비교 연산자와 함께 사용
          : 2건 이상의 행을 반환
          : IN, ALL, ANY, SOME, EXISTS
        - 다중 컬럼(Multi Column) 서브쿼리
          : 서브쿼리가 여러 컬럼의 데이터를 반환
 * 뷰
   - 특정 SELECT문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트
   - 가상 테이블
   - 실제 데이터를 저장하지는 않고 해당 데이터를 조회해오는 SELECT 문만 가지고 있음

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

 * 집합 연산자
   - 각 쿼리의 결과 컬럼 집합을 가지고 연산을 하는 명령어

UNION ALL	각 쿼리의 결과 집합의 합집합
UNION	각 쿼리의 결과 집합의 합집합
INTERSECT	각 쿼리의 결과 집합의 교집합
MINUS / EXCEPT	앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합 중복된 행은 한 줄로 출력됨

 * 그룹 함수
   - 데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수
   (1) ROLLUP
     : 소그룹 간의 소계 및 총계를 계산하는 함수

ROLLUP(A)	A로 그룹핑 총합계
ROLLUP(A, B)	A, B로 그룹핑 A로 그룹핑 총합계
ROLLUP(A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A로 그룹핑 총합계

   (2) CUBE
     : 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수
     : 조합할 수 있는 모든 그룹에 대한 소계를 집계함

CUBE(A)	A로 그룹핑 총합계
CUBE(A, B)	A, B로 그룹핑 A로 그룹핑 B로 그룹핑 총합계
CUBE(A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A, C로 그룹핑 B, C로 그룹핑 A로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

   (3) GROUPING SETS
     : 특정 항목에 대한 소계를 계산하는 함수

GROUPING SETS(A, B)	A로 그룹핑 B로 그룹핑
GROUPING SETS(A, B, ())	A로 그룹핑 B로 그룹핑 총합계
GROUPING SETS(A, ROLLUP(B))	A로 그룹핑 B로 그룹핑 총합계
GROUPING SETS(A, ROLLUP(B, C))	A로 그룹핑 B, C로 그룹핑 B로 그룹핑 총합계
GROUPING SETS(A, B, ROLLUP(C))	A로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

   (4) GROUPING
     : ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며, 소계를 나타내는 Row를 구분할 수 있게 해줌
     : GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트를 출력할 수 있음

 * 윈도우 함수
   - 행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수
   - 분석 함수(ANALYTIC FUNCTION)이나 순위 함수(RANK FUNCTION)로도 알려짐
   - 기존 집계 함수, 새로이 WINDOW 함수 전용 존재
   - 서브쿼리 사용 가능, 중첩(NEST)해서 사용 불가

 * 윈도우 함수 종류
   1. 그룹 내 순위(RANK) 관련 함수
     - RANK, DENSE_RANK, ROW_NUMBER
        ① RANK - 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 반복하고 다음 순위를 건너뜀
         ② DENSE_RANK - 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매김
         ③ ROW_NUMBER - 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여함
     - ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS에서 지원

   2. 그룹 내 집계(AGGREGATE) 관련 함수
     - SUM ,MAX, MIN, AVG, COUNT
         ① SUM - 데이터의 합계를 구하는 함수로 인자값으로는 숫자형만 올 수 있음
                   SUM하는 컬럼을 OVER절에서 ORDER BY절에 명시해주면 RANGE UNBOUNDED PRECEDING 구문 없어도 누적합이 집계됨
         ② MAX - 데이터의 최댓값을 구하는 함수
         ③ MIN - 데이터의 최솟값을 구하는 함수 
         ⑤ COUNT - 데이터의 건수를 구하는 함수
     - ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS에서 지원
     - SQL Server의 경우 집계 함수는 뒤에서 설명할 OVER 절의 내의 ORDER BY 지원 하지않음

   3. 그룹 내 행 순서 관련 함수
     - FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수
         ① FIRST_VALUE - 파티션 별 가장 선두에 위치한 데이터를 구하는 함수
         ② LAST_VALUE - 파티션 별 가장 끝에 위치한 데이터를 구하는 함수
         ③ LAG - 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수로 LAG함수의 두 번째 인자값을 생략하면
                  default값인 1을 인자값으로 사용
         ④ LEAD - 파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수로 LEAD함수의 두 번째 인자값을 생략하면 default값인 1을 인자값으로 사용
     - ORACLE에서만 지원

   4. 그룹 내 비율 관련 함수
     - CUME_DIST, PERCENT_RANK - ANSI/ISO SQL 표준과 Oracle DBMS에서 지원
     - NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원
     - RATIO_TO_REPORT - Oracle에서만 지원
         ① RATIO_TO_REPORT - 파티션 별 합계에서 차지하는 비율을 구하는 함수
         ② PERCENT_RANK - 해당 파티션의 맨 위 행을 0, 맨 아래 행을 1로 놓고 현재 행이 위치하는 백분위 
                               순위값을 구하는 함수
         ③ CUME_DIST - 해당 파티션에서의 누적 백분율을 구하는 함수로 결괏값은 0보다 크고 1보다 작거나 
                          같은 값을 가짐
         ④ NTILE - 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 순번을 구하는 함수
                     순번을 구하고도 순번을 할당할 행이 남아 있을경우 맨 앞의 그룹부터 하나씩 더 채워짐 

   5. 선형 분석을 포함한 분석 관련 함수 – 생략

 * Top-N 쿼리
   - ROWNUM : 슈도 컬럼으로 존재하지 않는 컬럼
                 : 엑셀의 자동 순번처럼 ROWNUM도 순번이 1씩 증가하기 때문에 ROWNUM 조건식에 <나 <=만 사용 가능

 * 셀프 조인
   - 나 자신과의 조인하므로 혼동을 막기 위해 FROM 절에 테이블명마다 별칭을 표기해야 함

 * 계층 쿼리
   - 테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있음
   - ORDER SIBLINGS BY절을 써서 같은 레벨에서 정렬함
   - LEVEL : 현재의 DEPTH를 반환하며 루트 노드는 1이 된다.
   - SYS_CONNECT_BY_PATH(컬럼, 구분자) : 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수
   - START WITH : 경로가 시작되는 루트 노드를 생성해주는 절
   - CONNECT BY : 루트로부터 자식 노드를 생성해주느 절으로 조건에 만족하는 데이터가 없을 때까지 노드를 생성
   - CONNECT_BY_ROOT 컬럼 : 루트 노드의 주어진 컬럼값을 반환
   - CONNECT_BY_ITSELF : 가장 하위 노드인 경우 1을 반환하고 그 외에는 0을 반환
   - PRIOR : 바로 앞에 있는 부모 노드의 값을 반환한다. 즉 하위 데이터를 지정

 * PIVOT절과 UNPIVOT절
   - PIVOT : 행을 열로 회전, UNPIVOT : 열을 행으로 회전시킴
   - PIVOT절 구문
     (1) Aggregate_function : 집계할 컬럼 지정
     (2) FOR절 : PIVOT할 컬럼을 지정
     (3) IN절 : PIVOT할 컬럼 값을 지정

   - UNPIVOT절 구문
     (1) UNPIVOT column: UNPIVOT된 값이 들어갈 컬럼을 지정
     (2) FOR절 : UNPIVOT된 값을 설명할 값이 들어갈 컬럼을 지정
     (3) IN절 : UNPIVOT할 컬럼 + 설명할 값의 리터걸 값을 지정
     (4) INCLUDE NULLS : UNPIVOT된 열의 값이 널인 행도 결과에 포함
   - PIVOT, UNPIVOT을 사용할 수 없는 경우 CASE 표현식 사용

 * 정규표현식

  - POSIX 연산

연산자	영문	설명
.	dot	모든 문자와 일치(newline 제외)
|	or	대체 문자를 구분
\	backslash	다음 문자를 일반 문자로 취급
^	carrot	문자열의 시작
$	dollar	문자열의 끝
?		0회 또는 1회 일치
*		0회 또는 그 이상의 횟수로 일치
+		1회 또는 그 이상의 횟수로 일치
{m}		m회 일치
{m,}		최고 m회 일치
{,m}		최대 m회 일치
{m,n}		최소 m회, 최대 n회 일치
\n		n번째 서브 표현식과 일치, n은 1에서 9 사이의 정수
[char…]		문자 리스트 중 한 문자와 일치
[^char…]		문자 리스트에 포함되지 않은 한 문자와 일치

   - PERL 정규표현식 연산자

연산자	설명
\d	숫자
\D	숫자가 아닌 모든 문자
\w	숫자와 영문자(underbar 포함)
\W	숫자와 영문자가 아닌 모든 문자(underbar 제외)
\s	공백 문자
\S	공백 문자가 아닌 문자
??	0회 또는 1회 일치
*?	0회 또는 그 이상의 횟수로 일치
+?	1회 또는 그 이상의 횟수로 일치
{m}?	m회 일치
{m,}?	최소 m회 일치
{,m}?	최대 m회 일치
{m,n}?	최소 m회, 최대 n회 일치

 * 정규표현식 조건과 함수
   1. REGEXP_LIKE 조건
     - REGEXP_LIKE 조건은 source_char가 패턴과 일치하면 true를 일치하지 않으면 false를 반환

REGEXP_LIKE(source_char, pattern [, match_param])

     - source_char는 검색문자열을 지정
     - pattern은 검색 패턴을 지정
     - match_param은 일치 옵션을 지정

   2. REGEXP_REPLACE 함수
     - source_char에서 일치한 패턴을 replace_string으로 변경한 문자 값을 반환

REGEXP_REPLACE(source_char, pattern[, replace_string[, position [, occurrence [, match_param]]]])

     - replace_string은 변경 문자열을 지정
     - position은 검색 시작 위치를 지정(default값 : 1)
     - occurrence는 패턴 일치 횟수를 지정 (default값 : 1)

   3. REGEXP_SUBSTR 함수
     - REGEXP_SUBSTR 함수는 source_char에서 일치한 pattern을 반환

REGEXP_SUBSTR(source_char, pattern, [, position[, occurrence [, match_param [, subexpr]]]])

     - subexpr은 서브표현식을 지정 (0은 전체패턴, 1이상은 서브표현식, 기본값은 0)

   4. REGEXP_INSTR 함수
     - REGEXP_INSTR 함수는 source_char에서 일치한 pattern의 시작 위치를 정수로 반환

REGEXP_INSTR(source_char, pattern [,position[, occurrence[, return_opt [, match_param [,subexpr]]]]])

     - return_opt은 반환 옵션을 지정(0은 시작위치, 1은 다음위치, 기본값은 0)

   5. REGEXP_COUNT 함수
     - REGEXP-COUNT 함수는 source_char에서 일치한 pattern의 횟수를 반환

REGEXP_COUNT(source_char, pattern [, position [, match_param]])