3. SQL 기본 및 활용

NULL

NULL 연산

• NULL을 연산한 값은 NULL이다

  NULL 비교

• NULL을 다른 값과 비교하면 알 수 없음이 반환된다.

  NULL 조회

• IS NULL : NULL인 값을 조회
• IS NOT NULL : NULL이 아닌 값을 조회

  NULL 함수

• NVL(A, 0) : A 값이 NULL이면 0을 반환
• NVL2(A, 1, 0) : A 값이 NULL이 아니면 1, NULL이면 0을 반환
• NULLIF(A, B) : A, B 값이 같으면 NULL, 다르면 A 반환
• COALESCE(A, B, C, ...) : NULL이 아닌 최초의 값을 반환

제약 조건

제약 조건(CONSTRAINT)의 변경

• 추가 기본형태
  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약이름 제약조건;
• 수정 기본형태
  • ALTER TABLE 테이블명 MODIFY 컬럼조건;
• 삭제 기본형태
  • ALTER TABLE 테이블명 DROP CONSTRAINT 제약이름;

그룹 함수

ROLLUP, GROUPING, GROUPING SETS, CUBE

• ROLLUP()
  • 인자로 제공한 COLUMN의 각각 그룹의 소계와 그룹 전체의 합계를 만들어준다.
  • 2개 인자도 가능
• GROUPING()
  • SELECT에 사용하는 함수로, 해당 행의 데이터가 소계 및 합계 데이터이면 1을 반환하고 아니면 0을 반환한다.
• GROUPING SETS()
  • GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 때 사용한다.(개별적 처리)
  • 인자로 제공하는 COLUMND에 대해서 개별적으로 소계를 내고, 합계는 기본으로 제공하지 않는다.
• CUBE()
  • 인자로 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.

그룹 함수와 UNION

• group by grouping sets(a, b, c)

    ==
    group by a union all  
    group by b union all  
    group by c

• group by rollup(a, b, c)

    ==
    group by (a,b,c) union all
    group by (a,b) union all
    group by (a) union all
    group by ()
    ==
    group by grouping sets((a,b,c), (a,b), (a), ())

• group by cube(a, b, c)

    group by (a,b,c) union all
    group by (a,b) union all
    group by (b,c) union all
    group by (c,a) union all
    group by (a) union all
    group by (b) union all
    group by (c) union all
    group by () union all

계층형 조회

계층형 조회(CONNECT BY)

• Oracle 에서 지원
• 트리 형태의 구조를 위에서 아래로, 혹은 아래에서 위로 조회(부장, 차장, 과장, 대리, ...)

계층형 조회 키워드

• CONNECT BY
  • 질의를 수행하는 키워드
  • PRIOR 과 함께 사용하여 JOIN 조건으로 사용
• START WITH
  • 시작 조건
  • 계층형 조회에서 가장 먼저 시작되는 부분
  • 보통 START WITH COL2 IS NULL 을 사용하여 ROOT 부터 순회하도록한다. (COL2 값이 NULL인 COL1이 시작점인 순방향 전개)
  • 역방향 전개를 위해서는 leaf node의 값을 알고 START WITH에 제공해야한다.
• PRIOR
  • 바로 직전에 출력된 행을 의미
  • PRIOR A: 이전에 나온 A의 값
• PRIOR 자식 = 부모
  • 순방향 전개
  • 부모에서 자식방향으로 조회
• PRIOR 부모 = 자식
  • 역방향 전개
  • 자식에서 부모방향으로 조회
• NOCYCLE
  • CYCLE 발생지점까지만 전개시킴
• ORDER SIBLINS BY COl_NAME
  • 동일한 LEVEL(형제 노드)간 정렬 수행

계층형 쿼리문의 내장 함수

• LEVEL
• CONNECT_BY_ROOT
• CONNECT_BY_ISLEAF
• SYS_CONNECT_BY_PATH
• NOCYCLE
• CONNECT_BY_ISCYCLE

윈도우 함수

윈도우 함수

• 행과 행 간의 관계를 정의하기 위한 함수
• 순위, 집계 함수, 행 위치 등을 조작할 수 있다.
  • 순위 : rank
  • 집계 함수 : 합계(sum), 평균(avg), 수량(count), 최대(max), 최소(min)
  • 행 위치 : FIRST_VALUE(파티션에서 가장 처음 값, MIN), LAST_VALUE(가장 나중값, MAX), LAG(이전 행), LEAD(특정 위치의 행, 기본값 1)
• SELECT 절에서 사용

윈도우 함수 구조

SELECT WINDOW_FUNCTION(ARGUMENTS)
    OVER (PARTITION BY COL
        ORDER BY WINDOWING절)
FROM TEST_TABLE

• ARGUMENTS(인수) : 윈도우 함수에 따라서 0~N개의 인수를 설정한다.
• PARTITION BY COL_NAME: 기준을 두고 전체 집합을 소그룹으로 나눈다.(윈도우 함수 사용 범위를 지정)
• ORDER BY : 정렬
• WINDOWING : 행 기준의 범위를 정한다.
  • ROWS : 물리적 결과의 행 수
  • RANGE : 논리적 값에 의한 범위

WINDOWING

• ROWS : 조회한 행(row) 하나하나를 대상으로 연산
• RANGE : 조회한 행(row)의 값이 같은 경우, 동일한 컬럼값을 갖는 row를 묶어서 연산
• BETWEEN ~ AND : 윈도우의 시작과 끝의 위치를 지정
• UNBOUNDED PRECEDING : 윈도우의 시작 위치사 첫 번째 행임을 의미
• UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 행임을 의미
• CURRENT ROW : 윈도우의 시작 위치가 현재 행임을 의미

순위 함수; RANK

• RANK() : 중복값에 중복 등수를 매기고 다음 등수는 건너뜀(1, 1, 3, 4)
• DENSE_RANK() : 중복값에 중복 등수를 매기고 다음 등수로 건너뛰지 않음(1, 1, 2, 3)
• ROW_NUMBER() : 중복값이 있어서 고유 등수 부여(1, 2, 3, 4)

테이블 파티션

Partition 기능

• 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장
• CRUD 성능 향상
• 파티션 별로 독립적으로 관리 가능(백업, 복구, 전용 인덱스 생성)

Partition 종류

• Range Partition : 특정 범위를 기준으로 분할
• List Partition : 특정 값을 기준으로 분할
• Hash Partition : 해시 함수 사용하여 분할
• Composite Partition : 여러 파티션 기법을 복합 사용

Partition Index

• Global Index : 여러 파티션이 하나의 인덱스 사용
• Local Index : 파티션 별로 인덱스 사용
• Prefixed Index : 파티션 키와 인덱스 키가 동일
• Non Prefixed Index : 파티션 키와 인덱스 키가 다름