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

제 1절 서브쿼리 

1. 서브쿼리

• 하나의 SQL문 안의 SQL 문
• 단일행 또는 복수행 비교 연산자와 함께 사용 가능
• 서브쿼리에선 ORDER BY 사용 불가(메인쿼리의 맨 마지막 부분에만 위치 가능)
• 서브쿼리는 메인쿼리의 테이블의 칼럼 사용 가능(메인쿼리에선 서브쿼리의 칼럼 사용불가)
  
  
• 서브쿼리가 SQL 문에서 사용 가능한 곳
  • SELECT, FROM, WHERE, HAVING, ORDER BY절
  • INSERT 문의 VALUES절
  • UPDATE문의 SET절
  • DELECT문 사용 불가

2. 동작방식에 따른 분류

• Un-Correlated(비연관) 서브쿼리: 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브쿼리. 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용
• Correlated(연관) 서브쿼리: 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리. 일반적으로 메인쿼리가 먼저 수행되어 얽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용

3. 반환되는 데이터 형태에 따른 분류

• Single Row 서브쿼리(단일 행 서브쿼리): 서브쿼리 실행 결과가 항상 1건 이하인 서브쿼리. 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 이용
• Multi Row 서브쿼리(다중행 서브쿼리): 실행 결과가 여러 건인 서브쿼리. 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 이용
  • IN: 서브쿼리 결과에 값이 포함되는 지 확인
  • ALL: 서브쿼리 결과에 존재하는 모든 값을 만족하는 조건
  • ANY: 서브쿼리 결과에 존재하는 어느 하나의 값이라도 만족하는 조건
  • EXISTS: 서브쿼리 결과를 만족하는 값이 존재하는지 여부 확인 조건
  • SQL>> “선수들 중에 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력하라”

    SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 
FREOM TEAM 

    WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME=‘정현수’)
    ORDER BY TEAM_NAME;

4. 그 밖의 위치에서 사용하는 서브쿼리

• 스칼라 서브쿼리(Scalar Subquery) → SELECT 절에 서브쿼리 사용
  • 한 행, 한 칼럼만을 반환
  • 값 하나를 반환하는 서브쿼리, SELECT절에 사용하는 서브쿼리
  • 스칼라 서브쿼리 대신 JSON으로 동일한 결과 추출 가능
• 뷰
  • 가상의 테이블, FROM절에 사용하는 뷰는 인라인 뷰라고 함. 실제 데이터X
  • SQL이 실행될 때만 임시적으로 생성되는 동적 뷰 (일회성)
  • 일반 뷰가 정적 뷰, 인라인 뷰는 동적 뷰
  • 장점
    • 독립성: 테이블 구조 변경 자동 반영
    • 편리성: 쿼리를 단순하게 작성 가능
    • 보안성: 뷰를 생성할 때 칼럼을 제외할 수 있음

 

제 2절 집합 연산자

1. 집합 연산자

• 두 개 이상의 테이블에서 JOIN을 사용하지 않고, 연관된 데이터를 조회하는 방법
• 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만듬
• SELECT절의 컬럼 수가 동일해야 하고, 동일 위치 데이터 타입이 상호 호환 가능해야 한

2. 집합연산자 종류

• UNION: 여러 개의 SQL문의 결과에 대한 합집합. 모든 중복된 행은 하나의 행으로 만듬
• UNION ALL: 중복 행도 그대로 결과로 표시(단순히 결과만 합쳐놓은 것).
• INTERSECT: 여러 개의 SQL 문의 결과에 대한 교집합, 중복된 행은 하나의 행으로 만듬
• EXCEPT: 앞의 SQL 문의 결과에서 뒤의 SQL문 결과에 대한 차집합. 중복된 행은 하나의 행으로 만듬

 

제 3절 그룹 함수

1. 데이터분석 개요

• ANSI/ISO SQL 표준은 데이터 분석을 위해 다음 세 가지 함수를 정의함
• AGGREGATE FUNTION(집계), GROUP FUNCTION(그룹), WINDOW FUNCTION(원도우)
• NULL 빼고 집계, 결과값 없는 행 출력 안 함

2. ROLLUP 함수

• ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용
• Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성
• GROUP BY로 묶은 칼럼의 소계 계산, 계층 구조
• GROUP BY 칼럼 순서가 바뀌면 결과 값 바뀜
• GROUP BY의 확장된 형태
  • GROUP BY ROLLUP(A): 전체 합계, 칼럼 A 소계
  • GROUP BY ROLLUP(A, B): 전체 합계, 칼럼 A 소계, 칼럼 (A, B) 조합 소계
  • GROUP BY ROLLUP(A, B, C): 전체 합계, 칼럼 A 소계, 칼럼 (A, B) 조합 소계, (A, B, C) 조합 소계 
  • GROUP BY ROLLUP(A, (B, C): 전체 합계, 칼럼 A 소계, (A, (B, C)) 조합 소계
  • GROUP BY A, ROLLUP(B): A그룹별 집계, A그룹 내부에서 B칼럼별 집계
• SQL>> “부서명과 업무명을 기준으로 사원수와 급여 합을 집게한 일반적인 GROUP BY SQL 문장을 수행”
  SELECT DNAME, JOB, COUNT(*) “Total Empl”, SUM(SAL) “Total Sal”
  FROM EMP, DEPT
  WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME.JOB;

3. CUBE 함수

• 결합 가능한 모든 갑에 대한 다차원 집계, 순서 무관
  • GROUP BY CUBE(A): 전체 합계, 칼럼 A 소계
  • GROUP BY CUBE(B): 전체 합계, 칼럼 A 소계, 칼럼 B 소계, 칼럼 (A, B) 조합 소계

4. GROUPING SETS 함수

• 특정 항목에 대한 소계 계산, GROUP BY 칼럼 순서와 무관하게 개별적으로 처리
• 내가 보고 싶은 것만 소계를 생성
  • GROUP BY GROUPING SETS(A): 칼럼 A 소계
  • GROUP BY GROUPING SETS(A, B): 칼럼 A 소계, 칼럼 B 소계
  • GROUP BY GROUPING SETS((A, B)): 칼럼 (A, B) 소계

 

제 4절 원도우 함수

1. 윈도우 함수 (WINDOW FUNCTION)

• 여러 행 간의 관계 정의 함수, 중첩 불가

2. 원도우 함수 종류

• 순위 함수
  • RANK: 중복 순위 포함
  • DENSE_RANK: 중복 순위 무시 (중간 순위를 비우지 않음)
  • ROW_NUMBER: 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여
    SQL>> “사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력하라”

    SELECT JOB, ENAME, SAL, RANK( )

    OVER (ORDER BY SAL DESC) ALL_RANK, RANK( )

    OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;
• 윈도우 일반 집계 함수: SUM, MAX, MIN, AVG 등
• 행 순서 함수
  • FIRST_VALUE / LAST_VALUE 함수: 첫 값 / 끝 값
  • LAG / LEAD: 이전 값 / 이후 값
  • LEAD(E, A)는 E에서 A번째 행의 값을 호출하는 형태로도 쓰임 (A의 기본값은 1)
• 비율 함수
  • RATIO_TO_REPORT: 전체 SUM 값에 대한 행별 갈럼 값의 백분율을 소수점 반환
  • PERCENT_RANK: 제일 먼저 나오는 것 0, 제일 늦게 나오는 것 1, 행의 순서별 백분율
  • CUME_DIST: 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율
  • NTILE: 전체 건수를 ARGUMENT 값으로 N 등분한 결과

3. 문법

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

 

제 5절 TOP N 쿼리

1. TOP N 쿼리

• 페이징 처리를 효과적으로 수행하기 위해 사용
• 전체 결과에서 특정 N 개 추출
• 윈도우 함수를 사용하여 상위 N 개에 대한 값을 추출할 수 있으나 단일 쿼리로 표현이 불가
• 윈도우 함수의 결과를 서브쿼리로 사용하여 필터링해야 함
  
  
• ORDER BY: 데이터 정렬
• LIMIT: 정렬된 결과에서 상위 N개 행 선택
• FETCH: 결과 집합에서 상위 N개 행 선택
• TOP(n) WITH TIES: 값이 동일한 경우 함께 출력

2. Top N 쿼리 종류

• ROWNUM (Oracle)
  • Oracle에서 순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM 조건을 같이 사용하는 경우, 이 두 조건으로는 원하는 결과 얻을 수 없음
  • 정렬을 먼저 하고 나서 ROWNUM 을 수행
  • 출력된 데이터 기준으로 행 번호 부여
  • 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음 ( ‘=’ 연산 불가)
    • 출력되는 결과에 따라 바뀜. 실제로는 존재하지 않는 가짜 컬럼.
  • 첫번째 행이 증가한 이후 할당되므로 ‘>’ 연산 사용 불가 → 1을 포함해야 함 (> 0 은 가능, ≥ 1 가능)
    • 항상 < 조건이나 <= 조건으로 사용해야 한다.

• TOP ( )
  • SQL Server는 TOP 조건을 사용하게 되면 별도 처리 없이 관련 ORDER BY 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력 가능
  • TOP 절을 사용하여 결과 집합을 반환되는 행의 수를 제한
  • WITH TIES 옵션은 ORDER BY 절의 조건 기준으로 TOP N 의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+ 동일 정렬 순서 데이터를 추가 반환하도록 지정하는 옵션임.

• FETCH 절
  • 출력될 행의 수를 제한하는 절
  • 단일 쿼리로도 정렬 순서대로의 상위 N 개에 대한 값을 추출할 수 있음.
  • ORACLE 12 C 이상부터 제공, SQL server 지원
  • ORDER BY 절 뒤에 사용
  • ORDER BY 컬럼 OFFSET N {ROW | ROWS} 

    FETCH {FIRST | NEXT} N {ROW | ROWS} ONLY 
    • OFFSET : 건너뛸 행의 수 
    • FIRST : OFFSET 을 쓰지 않았을 때는 처음부터 N 행 출력
    • NEXT : OFFSET 을 사용할 경우 제외한 행 다음부터 N 행 출력

 

제 6절 계층형 질의와 셀프 조인

1. 계층형 질의

• 계층형 질의: 동일 테이블에 계층적으로 상/하위 데이터가 포함된 데이터
• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생 (조직, 사원, 메뉴 등)
  
  
• Oracle 계층형 질의

• START WITH: 데이터 전개 시작(데이터 전개: 데이터 찾아가기)
• CONNECT BY: 자식 데이터 지정
• PRIOR: CONNECT BY 절에서 사용 (SELECT, WHERE절에서도 사용 가능)
  • PRIOR 자식=부모: [부모→자식] 순방향 전개, 리프(최하위) = 1
  • PRIOR 부모=자식: [자식→부모] 역방향 전개, 루트(최상위)=1
• NOCYCLE: 이미 나타난 동일한 데이터가 다시 나타나는 것을 CYCLE이라고 하는데, 사이클이 발생한 데이터는 런타임 오류 발생 →
• NOCYCLE 추가 → 사이클 발생 이후 데이터 전개X
• ORDER SIBLINGS BY: 동일한 LEVEL의 노드(형제 노드) 사이에서 정렬 수행
• WHERE: 모든 데이터 전개를 수행하고 지정된 조건을 만족하는 데이터만 추출
  
  
• 계층형 질의에서 사용하는 가상 컬럼
  • LEVEL: 전개 과정에서 루트 데이터면 1, 그 하위 데이터면 0. 루프에서 리프로 내려갈 때마다 1씩 증가
  • CONNECT_BY_ISLEAF: 전개 과정에서 해당 데이터가 리프데이터면 1, 아님 0 
  • CONNECT_BY_ISCYCLE: 전개 과정에서 해당 데이터가 조상 데이터로서 존재하면(자식 데이터가 있으면) 1, 아님 0, CYCLE 기능 사용했을 때만 확인 가능
• 계층형 질의에서 사용하는 함수
  • SYS_CONNECT_BY_PATH: 루트 데이터로부터 현재 전개할 데이터까지의 경로 표시
사용법: SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
  • CONNET_BY_ROOT: 현재 전개할 데이터의 루트 데이터 표시(단항 연산자임)
사용법: CONNET_BY_ROOT 칼럼
    
    
• SQL Server 계층형 질의
  • CTF(Common Table Expression)로 재귀 호출해 상위에서 하위 방향 전개
  • “” 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행함.

2. 셀프 조인

• 동일 테이블 사이의 조인, 반드시 테이블 별칭(Alias)을 사용해야 함

 

제 7절 PIVOT절과 UNPIVOT절

• 칼럼을 기준으로 여러 개의 행으로 나뉜 데이터를 행과 열을 전환해 테이블을 재구성해 보기 편하도록 만드는 것

1. PIVOT 절

• 특정 행의 값을 새로운 열로 반환해 새로운 데이터를 요약하는데 사용
• 행을 열로 바꿈, 지정된 칼럼의 각 행 속성값들이 새로운 칼럼의 헤더가 되고 이에 맞게 전체 속성값 재배치

2. UNPIVOT 절

• PIVOT된 요약 데이터를 다시 원래의 행 형태로 변환, 이 과정에서 여러 열이 하나의 열로 합쳐지고 각 값은 별도의 행으로 표시
• 칼럼 헤더들이 한 칼럼의 각 행 속성값이 되고 이에 맞게 전체 속성값 재배치

3. 예시

• PIVOT 절

• sales_data 테이블에서 고객별로 Product A, Product B, Product C의 판매량을 집계
• 집계과정에서 SUM 함수 사용해 각 제품의 총 판매량 계산, 이를 각각의 열로 표시

• UNPIVOT 절

• [Product A], [Product B], [Product C] 열이 하나의 Product열로 통합, 각 값은 Quantity 열에 위치

 

제 8절 정규 표현식

1. 메타 문자

• 문자 그 자신이 가진 의미가 아니라 다른 의미로 사용되는 문자

2. 리터럴 문자

• 문자 자체가 가진 의미를 그대로 사용되는 문자, 정규표현식에서 패턴 매칭을 수행할 때 처리되는 취소 단위

3. Oracle과 SQL Server의 정규식 지원 차이

• Oracle

• SQL Server

• PATINDEX 함수: 찾고자 하는 문자열을 검색 후 위치를 반환. 정규식은 아니지만 유사한 패턴 문자열 지원
• LIKE 연산자: PATINDEX의 패턴 문자열 지원