[SQLD] 4장. SQL 활용과 고급 기능

서브쿼리(Subqueries)

서브쿼리란, 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문입니다. 반드시 괄호로 묶어서 표현해야 합니다.

• 서브쿼리를 사용할 수 있는 절
  • SELECT
  • FROM
  • WHERE
  • HAVING
  • ORDER BY
  • 기타 DML

서브쿼리 주의사항
GROUP BY 절은 사용할 수 없습니다.
특별한 경우(TOP-N 분석 등)을 제외하고 서브쿼리 절에 ORDER BY 절을 사용할 수 없습니다.

서브쿼리의 종류

• 동작하는 방식에 따른 종류
  • UN-CORRELATED(비연관) 서브쿼리
    • 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태입니다.
    • 메인쿼리에 서브쿼리가 실행된 결과 값을 제공하기 위해 사용합니다.
  • CORRELATED(연관) 서브쿼리
    • 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태입니다.
    • 메인쿼리가 먼저 수행된 후 서브쿼리 조건이 맞는지 확인하기 위해 사용합니다.
• 위치에 따른 종류
  • 스칼라 서브쿼리
    • SELECT에 사용하는 서브쿼리입니다.
    • 서브쿼리 결과를 마치 하나의 컬럼처럼 사용하기 위해 사용합니다.
    • 조인의 대체 연산으로 사용할 수 있습니다.
    • 스칼라 서브쿼리를 사용한 조인 처리 시 OUTER JOIN이 기본입니다.
  • 인라인 뷰
    • FROM절에 사용하는 서브쿼리입니다.
    • 서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용합니다.
    • 쿼리 안에 뷰의 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용합니다.
    • 테이블명이 존재하지 않기 때문에 테이블 별칭이 필요합니다.
    • 서브쿼리 결과를 메인 쿼리의 어느 절에나 사용할 수 있습니다.
    • 인라인뷰의 결과와 메인쿼리 테이블과 조인할 목적으로 주로 사용합니다.
    • 모든 연산자를 사용할 수 있습니다.
• WHERE절 서브쿼리
  • 가장 일반적인 서브쿼리입니다.
  • 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용합니다.
  • 리턴 데이터의 형태에 따라 단일행, 다중행, 다중컬럼, 상호연관 서브쿼리로 구분이 가능합니다.
• WHERE절 서브쿼리의 종류
  • 단일행 서브쿼리
    • 서브쿼리 결과가 1개의 행이 리턴되는 형태입니다.
    • 연산자 종류에는 =, <, >, <=, >= 이 있습니다.
  • 다중행 서브쿼리
    • 서브쿼리 결과가 여러 행이 리턴되는 형태입니다.
    • =, >, <와 같은 비교 연산자 사용이 불가능합니다.
    • 서브쿼리 결과를 하나의 요약하거나 다중행 서브쿼리 연산자를 사용합니다.
• 다중컬럼 서브쿼리
  • 서브쿼리 결과가 여러 컬럼이 리턴되는 형태입니다.
  • 메인쿼리와의 비교 컬럼이 2개 이상입니다.
  • 대소비교 전달이 불가능합니다.
• 상호연산 서브쿼리
  • 메인쿼리와 서브쿼리의 비교를 수행하는 형태입니다.
  • 비교할 집단이나 조건은 서브쿼리에 명시합니다.
  • 상호연산 서브쿼리의 연산 순서
    1. 메인쿼리 테이블 READ
    2. 메인쿼리 WHERE절 확인
    3. 서브쿼리 테이블 READ
    4. 서브쿼리 WHERE절 확인
    5. 메인쿼리의 컬럼 값을 서브쿼리의 컬럼과 비교해 WHERE절을 완성
    6. WHERE절에 성립하는 행의 그룹연산 결과를 확인
    7. 위 결과를 메인쿼리에 전달하여 해당 조건을 만족하는 행만 추출

집합 연산자(Set Operators)

SELECT문 결과를 하나의 집합으로 간주하여 그 집합에 대해 합/교/차집합을 연산합니다. 두 집합의 컬럼이 동일하게 타입 및 순서 구성되어야 합니다. 전체 집합의 데이터 타입과 컬럼명은 첫 번째 집합에 의해 결정됩니다.

• 집합 연산자 사용 시 주의사항
  • 두 집합의 컬럼 수가 일치해야 합니다.
  • 두 집합의 컬럼 순서가 일치해야 합니다.
  • 두 집합의 각 컬럼의 데이터 타입이 일치해야 합니다.
  • 각 컬럼의 사이즈는 달라도 됩니다.
  • 개별 쿼리에 ORDER BY를 전달할 수 없으며 집합 연산자 전체 결과에 ORDER BY를 전달할 수 있습니다.
  • GROUP BY는 개별 쿼리에도 전달할 수 있습니다.
• 합집합 : 두 집합의 총합을 출력합니다.

UNION

• 중복된 데이터를 한 번만 출력합니다.
• 중복된 데이터를 제거하기 위해 내부적으로 정렬을 수행합니다.
• 중복된 데이터가 없을 경우에는 UNION ALL을 사용하는 것이 불필요한 정렬을 줄일 수 있습니다.

UNION ALL

• 중복된 데이터도 모두 출력합니다.
• 교집합
  • 두 집합에서 공통으로 있는 행을 출력합니다.
• 차집합
  • 두 집합에서 한 쪽 집합에만 존재하는 행을 출력합니다.
  • A-B와 B-A는 다르기 때문에 집합의 순서가 중요합니다.

그룹 함수(Group Functions)

숫자 함수 중 여러 값을 전달하여 하나의 요약값을 출력하는 다중행 함수입니다. GROUP BY절에 의해서 그룹별 연산결과를 리턴합니다. 반드시 하나의 컬럼만 전달해야 하고, NULL은 무시하고 연산합니다.

• COUNT
  • 행의 수를 세는 함수입니다.
  • 대상 컬럼은 * 또는 단 하나의 컬럼만 전달 가능합니다.
  • 문자/숫자/날짜/컬럼 모두 전달이 가능합니다.
  • 행의 수를 세는 경우에는 NOT NULL 컬럼을 세는 것이 좋습니다.
• SUM
  • 총 합을 출력하는 함수입니다.
  • 숫자 컬럼만 전달이 가능합니다.
• MIN/MAX
  • 최소/최대를 출력하는 함수입니다.
  • 날짜/숫자/문자 모두 전달이 가능하며, 오름차순 순서대로 최소와 최대를 출력합니다.
• VARIANCE/STDDEV
  • 분산과 표준편차를 출력하는 함수입니다.

OVER절을 사용하여 윈도우 함수로 사용할 수 있습니다. 반드시 연산할 대상을 그룹함수의 입력값으로 전달해야 합니다.

• SUM OVER()
• AVG OVER()
• MIN/MAX OVER()

GROUP BY FUNCTION

GROUP BY 절에 사용하는 함수로, 여러 GROUP BY 결과를 동시에 출력(합집합)하는 기능입니다.

• GROUP SETS(A, B)
  • A별, B별 그룹 연산 결과를 출력합니다.
  • 나열 순서는 중요하지 않습니다.
  • 기본 출력에 전체 총계는 출력되지 않습니다.
  • NULL 혹은 ()을 사용하여 전체 총계를 출력할 수는 있습니다.
• ROLL UP(A, B)
  • A별, (A,B)별, 전체 그룹 연산 결과를 출력합니다.
  • 나열 대상의 순서가 중요합니다.
  • 기본적으로 전체 총계가 출력됩니다.
  • UNION ALL로 대체할 수 있습니다.
• CUBE(A, B)
  • A별, B별, (A,B)별, 전체 그룹 연산 결과를 출력합니다.
  • 나열 대상의 순서가 중요하지 않습니다.
  • 기본 출력에 전체 총계가 출력됩니다.
  • GROUPING SETS로 대체할 수 있습니다.

윈도우 함수(Window Functions)

서로 다른 행의 비교나 연산을 위한 함수로 GROUP BY를 쓰지 않고 그룹 연산을 할 수 있습니다.

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

• PARTITION BY : 출력할 총 데이터 수 변화 없이 그룹연산을 수행할 GROUP BY 컬럼입니다.
• ORDER BY : RANK의 경우 필수이며 누적값 출력 시에 사용합니다.
• ROWS | RANGE BETWEEN A AND B : 연산 범위를 설정하며 ORDER BY 절 사용이 필수입니다.
• ORDER BY 절을 PARTITION BY 전에 사용할 수 없습니다.
• ROWS vs RANGE
  • ROWS : 값이 같더라도 각 행씩 연산합니다.
  • RANGE : 같은 값의 경우 하나의 RANGE로 묶어 동시에 연산합니다.
• BETWEEN A AND B
  • 시작점 정의(A)
    • CURRENT ROW : 현재 행부터
    • UNBOUNDED PRECEDING : 처음부터
    • N PRECEDING : N 이전부터
  • 마지막 시점 정의(B)
    • CURRENT ROW : 현재 행까지
    • UNBOUNDED FOLLOWING : 마지막까지
    • N FOLLOWING : N 이후까지

순위 관련 함수

• RANK
  • RANK WITHIN GROUP
    
    • 특정값에 대한 순위를 확인하는 일반 함수입니다.
  • RANK() OVER()
    • 전체 혹은 특정 그룹 중 값의 순위를 확인합니다.
    • ORDER BY 절을 필수로 사용해야 합니다.
    • 순위를 구할 대상을 ORDER BY절에 명시합니다.
    • 그룹 내에서 순위를 구할 때에는 PARTITION BY절을 사용합니다.
  • DENSE_RANK
    • 누적 순위를 말합니다.
    • 값이 같을 때 동일한 순서를 부여한 후 다음 순위가 바로 이어집니다.
  • ROW_NUMBER
    • 연속된 행 번호를 나타냅니다.
    • 동일한 순위를 인정하지 않고 단순히 순서대로 나열한 대로 순서 값을 반환합니다.
  • LAG/LEAD
    • 행 순서대로 이전 값(LAG), 이후 값(LEAD)을 가져옵니다.
    • ORDER BY절을 필수로 사용해야 합니다.
  • FIRST_VALUE, LAST_VALUE
    • 정렬 순서대로 정해진 범위에서 처음 값과 마지막 값을 출력합니다.
    • 순서와 범위에 따라서 최솟값과 최댓값은 반환합니다.
    • PARTITION BY, ORDER BY절 생략이 가능합니다.
  • NTILE
    • 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수입니다.
    • 그룹 번호가 반환되며, ORDER BY를 필수로 사용해야 합니다.
    • PARTITION BY를 사용하여 특정 그룹을 원하는 수만큼 분리할 수 있습니다.
    • 총 행의 수가 명확하게 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리됩니다.

비율 관련 함수

• RATIO_TO_REPORT
  • 값 값의 비율을 리턴합니다.
  • ORDER BY절 사용이 불가능합니다.

RATIO_TO_REPORT (대상) OVER ([PARTITION BY 컬럼])

• CUME_DIST
  • 각 행의 수에 대한 누적 비율을 리턴합니다.
  • 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위수로 계산하여 출력합니다.
  • ORDER BY를 사용하여 누적비율을 구하는 순서를 정할 수 있습니다.
  • ORDER BY 사용이 필수입니다.

CUME_DIST (대상) OVER ([PARTITION BY 컬럼] ORDER BY 컬럼 ASC | DESC)

• PERCENT_RANK
  • 분위수를 출력합니다.
  • 전체 COUNT 중에서 상대적 위치를 출력합니다.
  • ORDER BY 사용이 필수입니다.

PERCENT_RANK (대상) OVER ([PARTITION BY 컬럼] ORDER BY 컬럼 ASC | DESC)

TOP N QUERY

페이징 처리를 효과적으로 수행하기 위해 사용합니다. 전체 결과에서 특정 N개를 추출합니다.

• ROWNUM
  • 출력된 데이터를 기준으로 행 번호를 부여합니다.
  • 가상의 번호이기 때문에 특정 행을 지정하거나 연산할 수 없습니다.
  • = 연산과 > 연산이 불가능합니다.
  • 상위 N명의 결과를 출력하고 싶다면 인라인 뷰에서 각 행마다 순위를 직접 부여한 이후 ROWNUM을 사용해야 합니다.
  • 혹은 윈도우 함수의 RANK를 사용할 수 있습니다.
• FETCH
  • 출력된 행의 수를 제한하는 절입니다.
  • ORDER BY 절 뒤에 사용합니다.
  • ORACLE 12C부터 제공하며, SQL Server는 사용이 가능합니다.
  • OFFSET : 건너뛸 행의 수
  • N : 출력할 행의 수
  • FETCH : 출력할 행의 수를 전달하는 구문
  • FIRST : OFFSET을 사용하지 않았을 때 처음부터 N행 출력을 명령
  • NEXT : OFFSET을 사용했을 때 제외한 행 다음부터 N행 출력을 명령
  • ROW | ROWS : 행의 수에 따라 하나일 경우 단수, 여러 값이면 복수
• TOP N
  • SQL Server에서만 사용합니다.
  • 서브쿼리 사용 없이 하나의 쿼리로 정렬된 순서대로 상위 N개 추출이 가능합니다.
  • WITH TIES를 사용하여 동순위까지 함께 출력할 수 있습니다.

계층형 질의(Hierarchical Queries)

하나의 테이블 내 각 행끼리 관계를 가질 때 연결고리를 통해 행과 행 사이의 계층을 표현하는 기법입니다. PRIORT의 위치에 따라 연결하는 데이터가 다릅니다.

SELECT 컬럼명
FROM 테이블명
START WITH 시작조건
CONNECT BY [NOCYCLE] PRIOR 연결조건;

• START WITH : 데이터를 출력할 시작을 지정하는 조건
• CONNECT BY PRIOT : 행을 이어나갈 조건
• NOCYCLE : 순환 방지 
• 계층형 질의 가상 컬럼
  • LEVEL : 각 depth를 표현 (시작점부터 1)
  • CONNECT_BY_ITLEAF : LEAF NODE 여부
• 계층형 질의 가상 함수
  • CONNECT_BY_ROOT 컬럼명 : 루트 노드에 해당하는 컬럼 값
  • SYS_CONNECT_BY_PATH (컬럼, 구분자) : 이어지는 경로 출력
  • ORDER SIBILINGS BY 컬럼 : 같은 LEVEL일 경우 정렬 수행
  • CONNECT_BY_ISCYCLE : 계층형 쿼리의 결과에서 순환이 발생했는지 여부

데이터 구조

• LONG DATA
  • 하나의 속성이 하나의 컬럼으로 정의되어 값들이 여러 행으로 쌓이는 구조입닌다.
  • RDBMS의 테이블 설계 방식입니다.
  • 다른 테이블과의 조인 연산이 가능한 구조입니다.
• WIDE DATA
  • 행과 컬럼에 유의미한 전달을 목적으로 작성하는 교차표입니다.
  • 하나의 속성값이 여러 컬럼으로 분리되어 표현합니다.
  • RDBMS에서는 값이 추가될 때마다 컬럼이 추가되어야 하기 때문에 비효율적입니다.
  • 다른 테이블과의 조인 연산이 불가능한 구조입니다.
  • 주로 데이터 요약을 목적으로 사용합니다.

데이터 구조 변경

• PIVOT
  • LONG에서 WIDE으로 변환합니다.
  • STACK, UNSTACK, VALUE 컬럼의 중요가 중요합니다.
  • FROM 절에서 STACK, UNSTACK, VALUE 컬럼명을 정의해야 합니다.
  • PIVOT 절에서 UNSTACK, VALUE 컬럼명을 정의합니다.
  • PIVOT 절의 IN 연산자에 UNSTACK 컬럼값을 정의합니다.

SELECT *
FROM 테이블명 | 서브쿼리
PIVOT (VALUE 컬럼명 FOR UNSTACK 컬럼명 IN (값1, 값2, 값3));

• UNPIVOT
  • WIDE에서 LONG으로 변환합니다.
  • STACK 컬럼 : 이미 UNSTACK 되어 있는 컬럼을 하나의 컬럼으로 STACK 시 새로 만들어지는 컬럼명입니다.
  • VALUE 컬럼 : 교차표에서 VALUE값을 하나의 컬럼으로 표현하고자 할 때 새로 만들어지는 컬럼명입니다.
  • 값1, 값2, ... : 실제 UNSTACK 되어 있는 컬럼명입니다.

SELECT *
FROM 테이블명 | 서브쿼리
UNPIVOT (VALUE 컬럼명 FOR STACK 컬럼명 IN (값1, 값2, 값3));

정규 표현식

문자열의 공통된 규칙을 보다 일반화하여 표현하는 방법입니다.

• 옵션
  • c : 대소를 구분하여 검색합니다.
  • i : 대소를 구분하지 않고 검색합니다.
  • m : 패턴을 다중라인으로 선언할 수 있습니다.

• REGEXP_REPLACE

// 정규표현식을 사용하여 문자열 치환이 가능
REGEXP_REPLACE(대상, 찾을문자열, [바꿀문자열], [검색위치], [발견횟수], [옵션])

• 특징
  • 바꿀 문자열 생략 시 문자열을 삭제합니다.
  • 검색 위치 생략 시 DEFAULT 값은 1입니다.
  • 발견 횟수 생략 시 0입니다.
• REGEXP_SUBSTR

// 정규표현식을 사용하여 문자열을 추출
REGEXP_SUBSTR(대상, 패턴, [검색위치], [발견횟수], [옵션], [추출그룹])

• 특징
  • 검색 위치 생략 시 DEFAULT 값은 1입니다.
  • 발견 횟수 생략 시에는 1 입니다.
  • 추출 그룹은 서브패턴 번호를 얘기합니다.
• REGEXP_INSTR

// 주어진 문자열에서 특정 패턴의 시작 위치를 반환합니다.
REGEXP_INSTR(원본, 찾을문자열, [시작위치], [발견횟수], [옵션])

• 특징
  • 시작 위치 생략 시 처음부터 확인합니다.
  • 발견 횟수 생략 시 처음 발견된 문자열의 위치를 반환합니다.
• REGEXP_LIKE

// 주어진 문자열에서 특정 패턴을 가지는 경우를 반환
REGEXP_LIKE(원본, 찾을문자열, [옵션])

• 특징
  • 주어진 문자열에서 특정 패턴을 가지는 경우를 반환합니다.
• REGEXP_COUNT

// 주어진 문자열에서 특정 패턴의 횟수를 반환
REGEXP_COUNT(원본, 찾을문자열, [옵션])

• 특징
  • 주어진 문자열에서 특정 패턴을 가지는 횟수를 반환합니다.