SQLD(SQLP) 과목2 - 2장. SQL 활용

서브 쿼리

서브 쿼리란?

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 의미

▶ 서브 쿼리는 메인 쿼리의 칼럼을 모두 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 칼럼을 사용할 수 없음

▶ 메인 쿼리로 조직(1), 서브 쿼리로 사원(M) 테이블 사용 시 결과 집합은 조직(1) 레벨이 됨

[주의할 점]

서브 쿼리는 괄호로 감싸서 기술함
서브 쿼리는 단일 행(SINGLE ROW) 또는 복수 행(MULTIPLE ROW) 비교 연산자와 함께 사용 가능.
▶ 단일 행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하여야 함
▶ 복수 행 비교 연산자는 서브 쿼리의 결과 건수와 상관없음
중첩 서브 쿼리 및 스칼라 서브 쿼리에서는 ORDER BY를 사용하지 못함

[구분]

서브 쿼리는 동작하는 방식이나 반환하는 데이터의 형태에 따라 분류할 수 있음

동작하는 방식에 따른 서브 쿼리 분류

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

반환하는 데이터의 형태에 따라 분류

Single Row 단일 행 서브 쿼리	1. 서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리 2. 단일 행 비교 연산자와 함께 사용됨 3. 단일 행 비교 연산자: =, <, >, <=, >=, <>, etc..
Multi Row 다중 행 서브 쿼리	1. 서브 쿼리의 실행 결과가 여러 건인 서브 쿼리 2. 다중 행 비교 연산자와 함께 사용됨 3. 다중행 비교 연산자: IN, ALL, ANY, SOME, EXISTS
Multi Column 다중 칼럼 서브 쿼리	1. 서브 쿼리의 실행 결과로 여러 칼럼을 반환 2. 메인 쿼리의 조건 절에 여러 칼럼을 동시에 비교 가능 3. 서브 쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 함

Single Row(단일 행) 서브 쿼리

서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리

▶ 서브 쿼리의 결과가 2건 이상일 경우 Run Time Error 발생

[예제] 정남일 선수가 소속된 팀의 선수들에 대한 정보를 출력

구성: 서브 쿼리(정남일 선수가 소속된 팀을 알아냄) + 메인 쿼리(서브 쿼리의 결과를 이용해 해당 팀에 소속된 선수들의 정보 출력)

SELECT 
	PLAYER_NAME AS 선수명,
	POSITION AS 포지션,
	BACK_NO AS 백넘버
	FROM PLAYER 
	WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일')
    ORDER BY 1;

Multi Row(다중 행) 서브 쿼리

서브 쿼리의 실행 결과가 여러 건인 서브 쿼리

[다중 행 비교 연산자]

IN	서브 쿼리에 존재하는 임의의 값과 동일한 조건을 의미(Multiple OR)
비교 연산자 ALL	서브 쿼리에 존재하는 모든 값을 만족하는 조건 EX) 비교 연산자에 '>'을 사용했다면 ▶서브 쿼리에 존재하는 모든 값들보다 커야 하므로 서브 쿼리의 최댓값보다 큰 모든 건이 조건을 만족
비교 연산자 ANY	서브 쿼리의 결과에 존재하는 어느 하나 값이라도 만족하는 조건 EX) 비교 연산자에 '>'을 사용했다면 ▶서브 쿼리에 존재하는 값들 중 어떤 값이라도 만족하면 되므로 서브 쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족함(SOME은 ANY와 동일)
EXISTS	서브 쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건 ▶ 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않음 ▶ EXITSTS 서브 쿼리는 항상 연관 서브 쿼리로 사용

[예제] 정현수 선수가 소속된 팀의 정보 출력

** 주의할 점: '정현수'라는 이름을 가진 선수가 2명임 **

SELECT REGION_NAME AS 연고지명, TEAM_NAME AS 팀명, E_TEAM_NAME AS 영문팀명 
	FROM TEAM 
	WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_ID = '정현수') 
    ORDER BY TEAM_NAME

Multi Column(다중 칼럼) 서브 쿼리

서브 쿼리의 실행 결과로 여러 칼럼을 반환되어 메인 쿼리의 조건과 동시에 비교되는 것

▶SQL Server에서는 지원하지 않음

[예제] 소속팀 별 키가 가장 작은 사람들의 정보 출력

SELECT TEAM_ID AS 팀코드, PLAYER_NAME AS 선수명, POSITION AS 포지션, BACK_NO AS 백넘버, HEIGHT AS 키
	FROM PLAYER
	WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)
    ORDER BY TEAM_ID, PLAYER_NAME;

Correlated (연관) 서브 쿼리

서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리

[예제] 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보 출력

SELECT B.TEAM_NAME AS 팀명, A.PLAYER_NAME AS 선수명, A.POSITION AS 포지션, A.BACK_NO AS 백넘버, A.HEIGHT AS 키
	FROM PLAYER A, TEAM B
	WHERE A.HEIGHT < (SELECT AVG(X.HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = A.TEAM_ID GROUP BY X.TEAM_ID)
	AND B.TEAM_ID = A.TEAM_ID
	ORDER BY 선수명;

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

1. SELECT 절에 서브 쿼리 사용 - 스칼라 서브 쿼리(Scalar Subquery)

스칼라 서브 쿼리란 한 행, 한 칼럼만을 반환하는 서브 쿼리를 의미

[예시 코드]

SELECT A.PLAYER_NAME AS 선수명, A.HEIGHT AS 키, ROUND((SELECT AVG(X.HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = A.TEAM_ID), 3) AS "팀 평균 키"
	FROM PLAYER A;

2. FROM 절에서 서브 쿼리 사용 - 인라인 뷰(Inline View)

FROM절에서 사용되는 서브 쿼리를 의미

▶ 데이터 베이스에 SELECT문을 객체로서 저장해 테이블처럼 사용하는 View와 다르게 인라인 뷰는 쿼리 내에서 즉시 처리됨

[예시 코드]

SELECT A.PLAYER_NAME, A.BACK_NO, B.TEAM_NAME
	FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO FROM PLAYER WHERE POSITION = 'MF') A, TEAM B
	WHERE B.TEAM_ID = A.TEAM_ID
	ORDER BY 1;

3. HAVING 절에서 서브 쿼리 사용

집계 함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해 사용

[예제] 삼성 블루윙즈 선수들의 평균 키보다 평균 키가 작은 팀들의 정보 출력

- 내 코드

SELECT B.TEAM_ID, B.TEAM_NAME, ROUND(AVG(HEIGHT), 2) AS 평균키
    FROM PLAYER A, TEAM B
    WHERE A.TEAM_ID = B.TEAM_ID
    GROUP BY B.TEAM_ID, B.TEAM_NAME
    HAVING ROUND(AVG(A.HEIGHT), 2) < (SELECT ROUND(AVG(HEIGHT), 2) FROM PLAYER WHERE TEAM_ID = 'K02')
    ORDER BY TEAM_ID;

- 교재 코드

SELECT B.TEAM_ID, B.TEAM_NAME, ROUND(AVG(HEIGHT), 2) AS 평균키
    FROM PLAYER A, TEAM B
    WHERE A.TEAM_ID = B.TEAM_ID
    GROUP BY B.TEAM_ID, B.TEAM_NAME
    HAVING ROUND(AVG(A.HEIGHT), 2) < (SELECT ROUND(AVG(X.HEIGHT), 2) 
    									FROM PLAYER X 
    									WHERE X.TEAM_ID IN (SELECT TEAM_ID FROM TEAM WHERE TEAM_NAME = '삼성블루윙즈'));

뷰(View)

테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않음. 오직 뷰 정의(View Definition)만을 가지고 있음

▶ 쿼리에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성하여 질의를 수행함

[장점]

독립성	테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
편리성	▶ 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성 가능 ▶ 해당 형태의 SQL 문을 자주 사용할 때 뷰 이용시 편리
보안성	직원의 급여 정보와 같이 숨기고 싶은 정보 존재 시, 뷰 생성 시에 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있음

[뷰 생성 예시]

CREATE VIEW V_PLAYER_TEAM AS
	SELECT A.PLAYER_NAME, A.POSITION, A.BACK_NO, B.TEAM_ID, B.TEAM_NAME
	FROM PLAYER A, TEAM B
	WHERE B.TEAM_ID = A.TEAM_ID;

다음은 위에서 생성한 뷰 V_PLAYER_TEAM을 이용하여 생성한 뷰임

CREATE VIEW V_PLAYER_TEAM_FILTER AS 
	SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME
	FROM V_PLAYER_TEAM
	WHERE POSITION IN ('GK', 'MF');

다음과 같이 이미 존재하는 뷰를 참조해서 새로운 뷰를 만들 수도 있음

[뷰 삭제 예시]

DROP VIEW V_PLAYER_TEAM;

뷰 V_PLAYER_TEAM를 DROP 한 후 V_PLAYER_FILTER를 SELECT문을 이용해 조회하려고 하면 오류가 남.

집합 연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법으로 집합 연산자(Set Operator)를 사용하는 방식이 있음

[JOIN VS SET OPERATOR]

JOIN	SET OPERATOR
조인 조건을 사용해 여러 테이블의 행과 행을 서로 연결	여러 개의 결과 집합 간의 연산을 통해 결합

[집합 연산자 사용 조건]

SELECT 절의 칼럼 수가 동일
SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 동일

[집합 연산자의 종류]

UNION	▶개별 SQL 문의 결과에 합집합 연산 수행 ▶단, 결과에서 모든 중복된 행은 하나의 행으로 합침
UNION ALL	▶개별 SQL 문의 결과에 합집합 연산 수행 ▶중복된 행도 그대로 표시
INTERSECT	▶개별 SQL 문의 결과에 대해 교집합 연산 수행 ▶단, 결과에서 모든 중복된 행은 하나의 행으로 합침
EXCEPT	▶개별 SQL 문의 결과에 대해 차집합 연산 수행 ▶단, 결과에서 모든 중복된 행은 하나의 행으로 합침 ▶Oracle: MINUS 연산자 / SQL Server: EXCEPT 연산자

[집합 연산자 사용 SQL문]

SELECT 칼럼명1, 칼럼명2, ...
	FROM 테이블명1
[WHERE 조건절]
[GROUP BY 칼럼이나 표현식]
[HAVING 그룹 조건식]
집합 연산자
    SELECT 칼럼명1, 칼럼명2, ...
            FROM 테이블명2
    [WHERE 조건절]
    [GROUP BY 칼럼이나 표현식]
    [HAVING 그룹 조건식]
    [ORDER BY 칼럼이나 표현식 [ASC/DESC]]

그룹 함수

데이터 분석을 위한 함수

ANSI/ISO 표준은 데이터 분석을 위해서 다음의 세 가지 함수를 정의하고 있음

AGGREGATE FUNCTION
▶ GROUP AGGREGATE FUNCTION이라고도 부름
▶ COUNT, SUM, AVG, MAX, MIN 등 각종 집계 함수들을 포함함
GROUP FUNCTION
▶ ROLLUP함수: 소그룹 간의 소계 계산 / GROUP BY의 확장된 형태로 사용하기 쉬움 / 병렬 수행 가능 / 계층적 분류를 포함한 데이터의 집계에 적합
▶ CUBE 함수: GROUP BY 항목 간 다차원적인 소계 계산 / 결합 가능한 모든 값에 대해 다차원적인 집계 생성 / 시스템에 부하 多
▶ GROUPING SETS 함수: 특정 항목에 대한 소계 계산 / 원하는 부분의 소계만 손쉽게 추출 가능
WINDOW FUNCTION

ROLL UP 함수

[일반적인 GROUP BY 절 사용]

SELECT B.DNAME, A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY B.DNAME, A.JOB;

[GROUP BY 절 + ORDER BY 절 사용]

SELECT B.DNAME, A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY B.DNAME, A.JOB
    ORDER BY B.DNAME, A.JOB;

[ROLLUP 함수 사용]

SELECT B.DNAME, A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY ROLLUP (B.DNAME, A.JOB);

▶ 계층적 분류 수행

L1 - GROUP BY 수행 시 생성되는 표준 집계(9건)
L2 - DNAME 별 모든 JOB의 SUBTOTAL(3건)
L3 - GRAND TOTAL(마지막 행, 1건)

** 주의할 점: 계층 간 집계에 대해서는 LEVEL 별 순서(L1 → L2 → L3)를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않음 → 정렬을 위해서는 ORDER BY 절을 사용해야 함 **

[ROLLUP 함수 + ORDER BY 절 사용]

SELECT B.DNAME, A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY ROLLUP(B.DNAME, A.JOB)
    ORDER BY B.DNAME, A.JOB;

[GROUPING 함수 사용]

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수 지원을 위해 GROUPING 함수가 추가됨

(소계인 경우) ROLLUP, CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시됨
(총계인 경우) 그 외의 결과에는 GROUPING(EXPR) = 0이 표시됨
GROUPING 함수와 CASE/DECODE를 이용해 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있음

SELECT B.DNAME, GROUPING(B.DNAME) AS DNAME_GRP, A.JOB, GROUPING(A.JOB) AS JOB_GRP, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY ROLLUP(B.DNAME, A.JOB)
	ORDER BY B.DNAME, A.JOB;

[GROUPING 함수 + CASE 사용]

SELECT CASE GROUPING (B.DNAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DNAME END AS DNAME,
    CASE GROUPING(A.JOB) WHEN 1 THEN 'ALL JOBS' ELSE A.JOB END AS JOB,
    COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY ROLLUP(B.DNAME, A.JOB)
	ORDER BY B.DNAME, A.JOB;

SELECT 
    DECODE(GROUPING(B.DNAME), 1, 'ALL DEPARTMENTS', B.DNAME) AS DNAME,
    DECODE(GROUPING(A.JOB), 1, 'ALL JOBS', A.JOB) AS JOB,
    COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY ROLLUP(B.DNAME, A.JOB)
	ORDER BY B.DNAME, A.JOB;

[ROLLUP 함수 일부 사용]

SELECT 
    DECODE(GROUPING(B.DNAME), 1, 'ALL DEPARTMENTS', B.DNAME) AS DNAME,
    DECODE(GROUPING(A.JOB), 1, 'ALL JOBS', A.JOB) AS JOB,
    COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY B.DNAME, ROLLUP(A.JOB)
	ORDER BY B.DNAME, A.JOB;

[ROLLUP 함수 결합 칼럼 사용]

SELECT B.DNAME, A.JOB, A.MGR, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE B.DEPTNO = A.DEPTNO
	GROUP BY ROLLUP (B.DNAME, (A.JOB, A.MGR))
	ORDER BY B.DNAME, A.JOB, A.MGR;

CUBE 함수

결합 가능한 모든 값에 대해 다차원 집계를 생성

[CUBE 함수 사용]

SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN 'ALL Departments' ELSE B.DNAME END AS NAME,
    CASE GROUPING(A.JOB) WHEN 1 THEN 'ALL Jobs' ELSE A.JOB END AS JOB,
    COUNT(*) AS EMP_CNT, SUM(A.SAL) AS  SAL_SUM
	FROM EMP A, DEPT B
	WHERE B.DEPTNO = A.DEPTNO
	GROUP BY CUBE (B.DNAME, A.JOB)
	ORDER BY B.DNAME, A.JOB;

내가 보려고 정리하는 ROLLUP VS CUBE 차이점

ROLLUP(DNAME, JOB): (부서, 직업) → (부서 소계) → (총계)
CUBE(DNAME, JOB): (부서, 직업) → (부서 소계) → (직업 소계) → (총계)

[UNION ALL 사용 SQL]

CUBE SQL과 결과 데이터는 같으나 행들의 정렬은 다를 수 있음

SELECT DNAME, JOB, COUNT(*) AS EMP_CNT, SUM(SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY DNAME, JOB
UNION ALL
SELECT DNAME, 'ALL Jobs' AS JOB, COUNT(*) AS EMP_CNT, SUM(SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY DNAME
UNION ALL
SELECT 'ALL Departments' AS DNAME, JOB, COUNT(*) AS EMP_CNT, SUM(SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY JOB
UNION ALL
SELECT 'ALL DEPARTMENTS' AS DNAME, 'ALL JOBS' AS JOB, COUNT(*) AS EMP_CNT, SUM(SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE B.DEPTNO = A.DEPTNO;

GROUPING SETS 함수

▶ GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있음

▶ 표시된 인수 간에는 계층 구조인 ROLLUP과 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같음

[일반 그룹 함수를 이용한 부서별, JOB별 인원수와 급여 합]

SELECT DNAME, 'All Jobs' AS JOB, COUNT(*) AS EMP_CNT, SUM(SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY DNAME
UNION ALL
SELECT 'All Departments' AS DNAME, JOB, COUNT(*) AS EMP_CNT, SUM(SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY JOB;

[GROUPING SETS 사용 SQL]

SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN 'All Departments' ELSE B.DNAME END AS DNAME, 
    CASE GROUPING(A.JOB) WHEN 1 THEN 'All Jobs' ELSE A.JOB END AS JOB,
    COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP A, DEPT B
	WHERE A.DEPTNO = B.DEPTNO
	GROUP BY GROUPING SETS(B.DNAME, A.JOB)
	ORDER BY B.DNAME, A.JOB;

윈도우 함수

행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수

** 중첩해서 사용 불가 BUT 서브 쿼리에서는 사용 가능 **

함수 종류

크게 다섯 개의 그룹으로 분류할 수 있지만 벤더 별로 지원하는 함수에는 차이가 있음

그룹 내 순위 관련 함수(RANK)
RANK, DENSE_RANK, ROW_NUMBER
** ANSI/ISO SQL 표준, Oracle, SQL Server 등 대부분의 DBMS에서 지원 **
그룹 내 집계 관련 함수(AGGREGATE)
SUM, MAX, MIN, AVG, COUNT
** ANSI/ISO SQL 표준, Oracle, SQL Server 등 대부분의 DBMS에서 지원 **
** SQL Server에서는 Over 절의 ORDER BY 지원 X **
그룹 내 행 순서 관련 함수
FIRST_VALUE, LAST_VALUE, LAG, LEAD
** Oracle에서만 지원 **
그룹 내 비율 관련 함수
CUME_DIST, PERCENT_RANK, NTLE, RATIO_TO_REPORT
** CUME_DIST, PERCENT_RANK: ANSI/ISO 표준, Oracle에서 지원 **
** NTLE: ANSI/ISO SQL 표준에는 없지만 Oracle, SQL Server에서 지원 **
** RATIO_TO_REPORT: Oracle에서만 지원 **
선형 분석을 포함한 통계 분석 관련 함수
CORR, COVAL_POP, OVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

SYNTAX

윈도우 함수에는 OVER문구가 키워드로 필수 포함됨

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절]) 
FROM 테이블 명;

WINDOW_FUNCTION
ARGUMENTS(인수)
PARTITION BY 절: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음
ORDER BY 절
WINDOWING 절: 대상이 되는 행 기준의 범위를 강력하게 지정 가능 ** SQL Server에서는 미지원 **

그룹 내 순위 함수

1. RANK 함수

ORDER BY를 포함한 QUERY문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수

[예제] 사원 데이터에서 JOB 별로 급여가 높은 순서 출력

SELECT JOB, ENAME, SAL,
RANK () OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_RK
FROM EMP;

EX) 1 → 2 → 2 → 4 →...

2. DENSE_RANK 함수

RANK와 유사하지만 동일한 순위를 하나의 건수로 취급함

[예제] 사원 데이터에서 JOB 별로 급여가 높은 순서 출력

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) AS RK,
	DENSE_RANK() OVER (ORDER BY SAL DESC) AS DR
FROM EMP;

EX) 1 → 2 → 2 → 3 →...

3. ROW_NUMBER 함수

동일한 값에는 동일한 순위를 부여하는 RANK 함수나 DENSE_RANK 함수와 다르게 동일한 값이라도 고유한 순위를 부여함

[예제] 사원 데이터에서 JOB 별로 급여가 높은 순서 출력

SELECT JOB, ENAME, SAL,
	RANK () OVER (ORDER BY SAL DESC) AS SAL_RK,
	ROW_NUMBER () OVER (ORDER BY SAL DESC) AS SAL_RK2
FROM EMP;

EX) EX) 1 → 2(동일 값) → 3(동일 값) → 4 →...

일반 집계 함수

1. SUM 함수

파티션 별 윈도우의 합 구할 수 있음

[예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구한다. PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화 한다.

SELECT MGR, ENAME, SAL,
	SUM(SAL) OVER (PARTITION BY MGR) AS SAL_SUM
FROM EMP;

2. MAX 함수

파티션 별 윈도우의 최댓값 구할 수 있음

[예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최댓값을 함께 구한다.

SELECT MGR, ENAME, SAL,
	MAX(SAL) OVER (PARTITION BY MGR) AS MAX_SAL
FROM EMP;

3. MIN 함수

파티션 별 윈도우의 최솟값 구할 수 있음

[예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최솟값을 함께 구한다.

SELECT MGR, ENAME, HIREDATE, SAL,
MIN (SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) AS MIN_SAL
FROM EMP;

4. AVG 함수

AVG 함수와 파티션 별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통곗값을 구할 수 있음

[예제] EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING은 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다(ROWS는 현재 행의 앞뒤 건수를 말함)

SELECT MGR, ENAME, SAL,
ROUND(AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS AVG_SAL
FROM EMP;

5. COUNT 함수

COUNT 함수와 파티션 별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통곗값을 구할 수 있음

[예제] 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력하라. RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING은 현재 행의 급여 값을 기준으로 급여가 -50에서 +150의 범위 안에 포함된 모든 행이 대상이 된다(RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임).

SELECT ENAME, SAL,
COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS EMP_CNT
FROM EMP;

그룹 내 행 순서 함수

1. FIRST_VALUE 함수

파티션 별 윈도우에서 가장 먼저 나온 값을 구함

** SQL Server에서는 지원하지 않음 **

[예제] 부서별 직원들은 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다. RANGE UNBOUNDED PRECEDING은 현재 행을 기준으로 파티션 내 첫 번째 행까지의 범위를 지정한다.

SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS ENAME_FV
FROM EMP;

** 공동 등수를 인정하지 않고 처음 나온 행만 처리함. 공동 등수가 있을 때 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나 OVER() 내의 ORDER BY 절에 칼럼을 추가해야 함 **

2. LAST_VALUE 함수

파티션 별 윈도우에서 가장 나중에 나온 값을 구함

** SQL Server에서는 지원하지 않음 **

[예제] 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING은 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

SELECT DEPTNO, ENAME, SAL,
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ENAME_LV
FROM EMP;

3. LAG 함수

파티션 별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음

** SQL Server에서는 지원하지 않음 **

[예제] 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력

SELECT ENAME, HIREDATE, SAL,
LAG(SAL) OVER (ORDER BY HIREDATE ASC) AS LAG_SAL 
FROM EMP
WHERE JOB = 'SALESMAN';

** LAG(SAL, 2, 0): 2번째 앞의 행의 값을 가져오되 값이 없다면(NULL) 0으로 대체한다 **

ARGUMENT2: 몇 번째 앞의 행을 가져올지 결정(DEFAULT 1)
ARGUMENT3: N번째 앞의 행의 값이 NULL이라면 ARGUMENT3의 값으로 대체

4. LEAD 함수

파티션 별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음

** SQL Server에서는 지원하지 않음 **

[예제] 직원들을 입사 일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사 일자를 함께 출력한다

SELECT ENAME, HIREDATE, SAL,
LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE ASC) AS LEAD_HIREDATE
FROM EMP
WHERE JOB = 'SALESMAN';

** LAG와 동일하게 3개의 ARGUMENTS까지 사용 가능 **

그룹 내 비율 함수

1. RATIO_TO_REORT 함수

파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있음

결과 값은 > 0 && <= 1의 범위를 가짐
개별 RATIO의 합을 구하면 1이 됨
SQL Server에서는 지원하지 않음

[예제] JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다

SELECT ENAME, SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (),2) AS SAL_PR
FROM EMP
WHERE JOB = 'SALESMAN';

2. PERCENT_RANK 함수

파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 해 값이 아닌 행의 순서 별 백분율을 구함

결과 값은 >= 0 & <= 1의 범위를 가짐
SQL Server에서는 지원하지 않음

[예제] 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력한다.

SELECT DEPTNO, ENAME, SAL, 
PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS PR
FROM EMP;

3. CUME_DIST 함수

파티션 별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함

결과 값은 > 0 & <= 1의 범위를 가짐
SQL Server에서는 지원하지 않음

[예제] 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력

SELECT DEPTNO, ENAME, SAL,
CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CD
FROM EMP;

4. NTILE 함수

파티션 별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있음

[예제] 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.

SELECT ENAME, SAL,
NTILE(4) OVER (ORDER BY SAL DESC) AS NT
FROM EMP;

Top N 쿼리

ROWNUM 슈도 칼럼 (Oracle)

ROWNUM이란?

Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column으로서 SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호

한 건의 행만 가져오고 싶은 경우

SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= ;

SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < 2;

두 건 이상의 N 행을 가져오고 싶은 경우

SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N;

SELECT PLAYER_NAME FROM PLAYER WHERE ROW_NUM < N + 1;

[예제] 사원 테이블에서 급여가 높은 3명만 내림차순으로 출력하고자 한다.

SELECT ENAME, SAL
FROM (SELECT ENAME, SAL 
		FROM EMP
		ORDER BY SAL DESC)
WHERE ROWNUM <= 3;

** 잘못된 코드 **

SELECT ENAME, SAL
FROM EMP
WHERE ROWNUM < 4
ORDER BY SAL DESC;

무작위로 추출된 3개의 행을 급여를 기준으로 내림차순으로 정렬한 것

TOP 절 (SQL Server)

SYNTAX

TOP (Expression) [PERCENT] [WITH TIES]

Expression: 반환할 행 수를 지정하는 숫자
PERCENT: 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타냄
WITH TIES: ORDER BY 절이 지정된 경우에만 사용할 수 있으며 TOP N(PERCENT)의 마지막 행과 값이 있는 경우 추가 행이 출력되도록 지정 가능

한 건의 행만 가져오고 싶은 경우

SELECT TOP(1) PLAYER_NAME FROM PLAYER;

두 건 이상의 N행을 가져오고 싶은 경우

SELECT TOP(N) PLAYER_NAME FROM PLAYER;

[예제] 사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하고자 한다.

SELECT TOP(2) ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

[예제] 사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 같이 출력한다. TOP(2) WITH TIES 옵션은 동일 수치의 데이터를 추가로 더 추출하는 것으로, SCOTT와 FORD의 급여가 공동 2위임으로 TOP(2) WITH TIES의 실행 결과는 3건의 데이터가 출력된다.

SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

ROW LIMITING 절

ANSI/ISO 표준 SQL 절

SYNTAX

[OFFSET offset {ROW | ROWS}]
[FETCH {FIRST | NEXT} [{rowcount | percent PERCENT}] {ROW | ROWS} {ONLY | WITH TIES}]

OFFSET offset: 건너뛸 행의 개수 지정
FETCH: 반환할 행의 개수나 백분율 지정
ONLY: 지정된 행의 개수나 백분율만큼 행을 반환
WITH TIES: 마지막 행에 대한 동순위를 포함해서 반환

[예제 1]

SELECT EMPNO, SAL 
FROM EMP 
ORDER BY SAL, EMPNO
FETCH FIRST 5 ROWS ONLY;

[예제 2] OFFSET만 기술한 경우

건너뛴 행 이후의 전체 행이 반환됨

SELECT EMPNO, SAL 
FROM EMP 
ORDER BY SAL, EMPNO, EMPNO OFFSET 5 ROWS;

[예제 3] 모두 사용

SELECT EMPNO, SAL 
FROM EMP 
ORDER BY SAL, EMPNO
OFFSET 2 ROWS FETCH FIRST 5 ROWS WITH TIES;

계층형 질의와 셀프 조인

계층형 데이터란?

계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말함.

EX) 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계, 즉 계층형 데이터가 존재한다.

▶ 엔터티를 순환 관계 데이터 모델로 설계할 경우 계층형 데이터가 발생

▶ 계층형 데이터가 테이블에 존재하는 경우 데이터 조회를 위해 계층형 질의를 사용

SELF JOIN 셀프 조인

동일 테이블 사이의 조인을 의미

SYNTAX

SELECT WORKER.EMPNO AS 사원번호, WORKER.ENAME AS 사원명, MANAGER.ENAME AS 관리자명
FROM EMP WORKER, EMP MANAGER
WHERE MANAGER.EMPNO = WORKER.MGR;

계층형 질의

계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원함.

1. Oracle 계층형 질의

SELECT ...
	FROM 테이블
	WHERE condition
	AND condition
START WITH condition
	AND condition
CONNECT BY [NOCYCLE] condition
	AND condition
[ORDER SIBLINGS BY column, column, ...]

START WITH 절: 계층 구조 전개의 시작 위치 지정(ROOT DATA 지정)
CONNECT BY 절: 다음에 전개될 자식 데이터 지정, 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 함
PRIOR: CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정함
(FK) = PRIOR (PK): 순방향 전개, 부모 → 자식
(PK) = PRIOR (FK): 역방향 전개, 자식 → 부모
NOCYCLE: 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 사이클이 발생했다고 함. 이때 런타임 오류가 발생하는데, NOCYCLE을 추가하면 CYCLE 발생 이후의 데이터를 전개하지 않고 오류를 발생시키지 않음.
ORDER BY SIBLINGS BY: 형제 노드(동일 LEVEL) 사이에서 정렬 수행
WHERE: 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출

가상 칼럼(Pseudo Column)

Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼을 사용함

LEVEL	▶ 루트 데이터면 1, 하위 데이터면 2 ▶ 리프(Leaf) 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	▶ 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	▶ 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0 ▶ 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말함 ▶ CYCLE 옵션을 사용했을 때만 사용 가능

계층형 질의에서 사용되는 함수

Oracle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해 다음과 같은 함수를 제공함

SYS_CONNECT_BY_PATH	▶ 루트 데이터로부터 현재 전개할 데이터까지의 경로를 표시 ▶ 사용법: SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
CONNECT_BY_ROOT	▶ 현재 전개할 데이터의 루트 데이터를 표시 ▶ 단항 연산자 ▶ 사용법: CONNECT_BY_ROOT 칼럼

[예제 1]

** LPAD: 띄어쓰기를 위해 사용 **

SELECT LEVEL AS LV, LPAD(' ', (LEVEL - 1) * 2) || EMPNO AS EMPNO, MGR,
	CONNECT_BY_ISLEAF AS ISLEAF
	FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;

[예제 2] SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT 사용 예시

SELECT 
    CONNECT_BY_ROOT(EMPNO) AS ROOT_EMPNO,
	SYS_CONNECT_BY_PATH(EMPNO, ', ') AS PATH,
	EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;

2. SQL Server 계층형 질의

SQL Server 2005 버전부터 지원

WITH EMPLOYEES_ANCHOR AS (
	SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL
		FROM EMPLOYEES
		WHERE REPORTSTO IS NULL
	UNION ALL
	SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1
		FROM EMPLOYEES_ANCHOR A, EMPLOYEES R
		WHERE R.REPORTSTO = A.EMPLOYEEID)

SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO
FROM EMPLOYEES_ANCHOR

위의 WITH 절의 CTE 쿼리를 보면 UNION ALL 연산자로 쿼리 두 개를 결합함.
그중 위에 있는 쿼리를 '앵커 멤버(Anchor Member)'라고 하고, 아래에 있는 쿼리를 '재귀 멤버(Recursive Member)'라고 함.

CTE 식을 앵커 멤버와 재귀 멤버로 분할함
앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 생성함
Ti는 입력으로 사용하고 Ti + 1은 출력으로 사용해 재귀멤버를 실행함
빈 집합이 반환될 때까지 3단계를 반복함
결과 집합을 반환함. 이것은 T0에서 Tn까지의 UNION ALL임

WITH T_EMP_ANCHOR AS (
	SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL
		FROM T_EMP
		WHERE MANAGERID IS NULL
	UNION ALL
	SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1
		FROM T_EMP_ANCHORE A, T_EMP R
		WHERE R.MANAGERID = A.EMPLOYEEID)
SELECT LEVEL, REPLICATE (' ', LEVEL * 2) + EMPLOYEEID AS EMPLOYEEID, MANAGERID
FROM T_EMP_ANCHOR

WITH T_EMP_ANCHORE AS (
	SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL, CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT
    	FROM T_EMP
        WHERE MANAGERID IS NULL
    UNION ALL
    SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1, CONVERT(VARCHAR(1000), A.SORT + ',' + R.EMPLOYEEID) AS SORT
    	FROM T_EMP_ANCHORE A, T_EMP R
        WHERE R.MANAGERID = A.EMPLOYEEID)

SELECT LEVEL, REPLICATE(' ', LEVEL * 2) + EMPLOYEEID AS EMPLOYEEID, MANAGERID, SORT
FROM T_EMP_ANCHOR
ORDER BY SORT

PIVOT 절과 UNPIVOT 절

PIVOT = 회전시키다!

PIVOT 절

행 → 열로 전환

PIVOT [XML]
		(aggregate_function(expr) [[AS] alias]
    [, aggregate_function(expr) [[AS] alias] ...
    	FOR {column | (column [, column] ...)}
        IN ({{{expr | (expr [, expr] ...)} [[AS] alias]} ...
        	| subquery
            | ANY [, ANY] ...
            })
        )

aggregate_function은 집계할 열을 지정함
FOR절은 PIVOT 할 열을 지정함
IN 절은 PIVOT할 열 값을 지정함

[예제]

STEP1. 기본적인 PIVOT을 수행해 보자

SELECT * 
	FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM(SAL) FOR DEPTNO IN(10, 20, 30))
ORDER BY 1;

STEP2. 별칭 지정해 보자

SELECT * 
	FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM(SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))
ORDER BY 1, 2;

집계함수와 IN 절에 지정한 별칭에 따라 다음과 같은 규칙으로 열 명이 부여됨

	10	10 AS D10
SUM(SAL)	10	D10
SUM(SAL) AS SUM	10_SAL	D10_SAL

STEP3. 다수의 집계함수를 사용해 보자

SELECT *
	FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM(SAL) AS SUM, COUNT(*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20))
ORDER BY 1;

STEP4. 다수의 집계함수와 다수의 열을 사용해 보자

SELECT *
	FROM (SELECT TO_CHAR (HIREDATE, 'YYYY') AS YYYY, JOB, DEPTNO, SAL FROM EMP)
	PIVOT (SUM(SAL) AS SAL, COUNT(*) AS CNT FOR (DEPTNO, JOB) IN ((10, 'ANALYST') AS D10A, (10, 'CLERK') AS D10C, (20, 'ANALYST') AS D20A, (20, 'CLERK') AS D20C))
ORDER BY 1;

STEP5. PIVOT 절을 사용할 수 없는 경우 집계함수와 CASE 표현식으로 PIVOT을 수행해 보자

SELECT 
    JOB,
    SUM (CASE DEPTNO WHEN 10 THEN SAL END) AS D10_SAL,
    SUM (CASE DEPTNO WHEN 20 THEN SAL END) AS D20_SAL,
    SUM (CASE DEPTNO WHEN 30 THEN SAL END) AS D30_SAL
	FROM EMP
GROUP BY JOB
ORDER BY JOB;

UNPIVOT 절

열 → 행으로 전환

UNPIVOT [{INCLUDE | EXCLUDE} NULLS]
		( {column | (column [, col] ...)}
        FOR {column | (column [, col] ...)}
        IN ({column | (column [, col] ...)} [AS {literal | (literal [, literal] ...)}]
        [, {column | (column [, col] ...)} [AS {literal | (literal [, literal] ...)}] ...
        )
    )

UNPIVOT column절은 UNPIVOT 된 값이 들어갈 열을 지정
FOR 절은 UNPIVOT된 값을 설명할 값이 들어갈 열을 지정
IN 절은 UNPIVOT 할 열과 설명할 값의 리터럴 값 지정

[예제]

STEP1. 다음과 같이 테이블을 생성하자

CREATE TABLE T1 AS
SELECT JOB, D10_SAL, D20_SAL, D10_CNT, D20_CNT
	FROM (SELECT JOB, DEPTNO, SAL FROM EMP WHERE JOB IN ('ANALYST', 'CLERK'))
PIVOT (SUM(SAL) AS SAL, COUNT(*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20));

생성된 테이블은 다음과 같음.

STEP2. UNPIVOT절을 이용해 D10_SAL, D20_SAL 열을 행으로 전환하자

SELECT JOB, DEPTNO, SAL
	FROM T1
UNPIVOT (SAL FOR DEPTNO IN (D10_SAL, D20_SAL))
ORDER BY 1, 2;

STEP3. IN절에 별칭을 지정하자

IN절에 별칭을 지정하면 FOR절에 지정한 열의 값을 변경할 수 있음

SELECT JOB, DEPTNO, SAL
	FROM T1
UNPIVOT (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))
ORDER BY 1, 2;

STEP4. 값이 NULL인 행도 결과에 포함시키자

INCLUDE NULLS 키워드를 기술하면 UNPIVOT 된 열의 값이 널인 행도 결과에 포함됨

SELECT JOB, DEPTNO, SAL
	FROM T1
UNPIVOT INCLUDE NULLS (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))
ORDER BY 1, 2;

STEP5. FOR 절에 다수의 열, IN 절에 다수의 별칭을 지정해 보자

SELECT *
	FROM T1
UNPIVOT ((SAL, CNT) FOR DEPTNO IN ((D10_SAL, D10_CNT) AS 10, (D20_SAL, D20_CNT) AS 20))
ORDER BY 1, 2;

SELECT *
FROM T1
UNPIVOT ((SAL, CNT) FOR (DEPTNO, DNAME) IN ((D10_SAL, D10_CNT) AS (10, 'ACCONTING') , (D20_SAL, D20_CNT) AS (20, 'RESEARCH')))
ORDER BY 1, 2;

STEP6. UNPIVOT 절을 사용할 수 없는 경우 카티션 곱을 사용해 UNPIVOT을 수행해 보자

SELECT 
	A.JOB,
	CASE B.LV WHEN 1 THEN 10 WHEN 2 THEN 20 END AS DEPTNO,
	CASE B.LV WHEN 1 THEN A.D10_SAL WHEN 2 THEN A.D20_SAL END AS SAL,
	CASE B.LV WHEN 1 THEN A.D10_CNT WHEN 2 THEN A.D20_CNT END AS CNT
FROM T1 A, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B
ORDER BY 1, 2;

정규 표현식

문자열의 규칙을 표현하는 검색 패턴

→ 주로 문자열 검색과 치환에 사용

기본 문법

POSIX 연산자

1. 기본 연산자

연산자	영문	설명
.	dot	모든 문자와 일치 (newline 제외)
\|	or	대체 문자를 구분
\	backslash	다음 문자를 일반 문자로 취급

2. 앵커(anchor) 연산자

검색 패턴의 시작과 끝 지정

연산자	영문	설명
^	carrot	문자열의 시작
$	dollar	문자열의 끝

4. 수량사(qualifier)

선행 표현식의 일치 횟수 지정 → greedy 방식으로 동작(패턴을 최대로 일치시킴)

연산자	설명
?	0회 또는 1회 일치
*	0회 또는 그 이상의 횟수로 일치
+	1회 또는 그 이상의 횟수로 일치
{m}	m회 일치
{m,}	최소 m회 일치
{,m}	최대 m회 일치
{m, n}	최소 m회, 최대 n회 일치

5. 서브 표현식

연산자	설명
(expr)	괄호 안의 표현식을 하나의 단위로 취급

6. 역참조 (back reference)

일치한 서브 표현식을 다시 참조 가능

연산자	설명
\n	n번째 서브 표현식과 일치, n은 1에서 9사이의 정수

7. 문자 리스트 (character list)

문자를 대괄호로 묶은 표현식

문자 리스트에서 하이픈(-)은 범위 연산자로 동작함

연산자	설명
[char ...]	문자 리스트 중 한 문자와 일치
[^char ...]	문자 리스트에 포함되지 않은 한 문자와 일치

연산자	설명	동일
[:digit:]	숫자	[0-9]
[:lower:]	소문자	[a-z]
[:upper:]	대문자	[A-Z]
[:alpha:]	영문자	[a-zA-Z]
[:alnum:]	영문자와숫자	[0-9a-zA-Z]
[:xdigit:]	16진수	[0-9a-fA-F]
[:punct:]	구두점 기호	[^[:alnum:][:cntrl:]]
[:blank:]	공백 문자
[:space:]	공간 문자

[예제 1] dot 연산자 활용 쿼리

참고: REGEXP_SUBSTR 함수는 문자열에서 일치하는 패턴을 반환하는 함수

해설: a.b 는 첫 번째 문자가 a이고 세 번째 문자가 b이고 가운데 문자가 1개만 있는 문자열에 대한 패턴임

SELECT REGEXP_SUBSTR ('aab', 'a.b') AS C1,
REGEXP_SUBSTR ('abb', 'a.b') AS C2,
REGEXP_SUBSTR ('acb', 'a.b') AS C3,
REGEXP_SUBSTR ('adc', 'a.b') AS C4
FROM DUAL;

[예제 2] or 연산자 활용 쿼리

해설:
일치하는 만큼만 출력
EX) 'abc'이고 기준 문자열이 'ab|cd'라면 ab까지만 출력

기술 순서에 따라 비교

EX) 'aa'이고 기준 문자열이 'a|aa'라면 a가 출력됨

SELECT REGEXP_SUBSTR('a', 'a|b') AS C1,
    REGEXP_SUBSTR('b', 'a|b') AS C2,
	REGEXP_SUBSTR('c', 'a|b') AS C3,
	REGEXP_SUBSTR('ab', 'ab|cd') AS C4,
	REGEXP_SUBSTR('cd', 'ab|cd') AS C5,
	REGEXP_SUBSTR('bc', 'ab|cd') AS C6,
	REGEXP_SUBSTR('aa', 'a|aa') AS C7,
	REGEXP_SUBSTR('aa', 'aa|a') AS C8
FROM DUAL;

[예제 3] backslash 연산자 활용 쿼리

SELECT REGEXP_SUBSTR('a|b', 'a|b') AS C1,
    REGEXP_SUBSTR('a|b', 'a\|b') AS C2
FROM DUAL;

[예제 4] carrot 연산자와 dollar 연산자 활용 쿼리

참고: CHR(10) 표현식은 개행(line feed) 문자를 반환

해설: C1은 'ab\ncd'를 의미. 그중 첫 번째 '^.'(시작 문자)을 찾아야 하므로 a가 출력되지만 C2는 문자열이 1개이므로 두 번째 시작 문자는 존재하지 않아 NULL이 출력됨

SELECT REGEXP_SUBSTR('ab' || CHR(10) || 'cd', '^.', 1, 1) AS C1,
    REGEXP_SUBSTR('ab' || CHR(10) || 'cd', '^.', 1, 2) AS C2,
    REGEXP_SUBSTR('ab' || CHR(10) || 'cd', '.$', 1, 1) AS C3,
    REGEXP_SUBSTR('ab' || CHR(10) || 'cd', '.$', 1, 2) AS C4
FROM DUAL;

[예제 5]?, *, + 연산자 활용 쿼리

SELECT REGEXP_SUBSTR('ab', 'ab?c') AS C1, -- a(b가 0또는 1회)c
    REGEXP_SUBSTR('abc', 'ab?c') AS C2, 
    REGEXP_SUBSTR('abbc', 'ab?c') AS C3,
    REGEXP_SUBSTR('ac', 'ab*c') AS C4, -- a(b가 0회 이상)c
    REGEXP_SUBSTR('abc', 'ab*c') AS C5,
    REGEXP_SUBSTR('abbc', 'ab*c') AS C6,
    REGEXP_SUBSTR('ac', 'ab+c') AS C7, -- a(b가 1회 이상)c
    REGEXP_SUBSTR('abc', 'ab+c') AS C8,
    REGEXP_SUBSTR('abbc', 'ab+c') AS C9
FROM DUAL;

[예제 6] {m}, {m,}, {m, n} 연산자 활용 쿼리

SELECT REGEXP_SUBSTR('ab', 'a{2}') AS C1,
    REGEXP_SUBSTR('aab', 'a{2}') AS C2,
    REGEXP_SUBSTR('aab', 'a{3,}') AS C3,
    REGEXP_SUBSTR('aaab', 'a{3,}') AS C4,
    REGEXP_SUBSTR('aaab', 'a{4,5}') AS C5,
    REGEXP_SUBSTR('aaaab', 'a{4,5}') AS C6
FROM DUAL;

[예제 7] 서브 표현식 활용 쿼리

SELECT REGEXP_SUBSTR('ababc', '(ab)+c') AS C1, --ab가 1회 이상 반복
    REGEXP_SUBSTR('ababc', 'ab+c') AS C2, --b가 1회이상 반복
    REGEXP_SUBSTR('abd', 'a(b|c)d') AS C3, --abd or acd
    REGEXP_SUBSTR('abd', 'ab|cd') AS C4 --ab or cd
FROM DUAL;

[예제 8] 역참조 활용 쿼리

SELECT REGEXP_SUBSTR('abxab', '(ab|cd)x\n') AS C1, --abxba or cdxcd
    REGEXP_SUBSTR('cdxcd', '(ab|cd)x\1') AS C2, 
    REGEXP_SUBSTR('abxef', '(ab|cd)x\1') AS C3, 
    REGEXP_SUBSTR('ababab', '(.*)\1+') AS C4, --( )문자열이 1회 이상 반복
    REGEXP_SUBSTR('abcabc', '(.*)\1+') AS C5,
    REGEXP_SUBSTR('abcabd', '(.*)\1+') AS C6
FROM DUAL;

[예제 9] 문자 리스트 활용 쿼리

SELECT REGEXP_SUBSTR('ac', '[ab]c') AS C1,
    REGEXP_SUBSTR('bc', '[ab]c') AS C2, 
    REGEXP_SUBSTR('cc', '[ab]c') AS C3, 
    REGEXP_SUBSTR('ac', '[^ab]c') AS C4,
    REGEXP_SUBSTR('bc', '[^ab]c') AS C5,
    REGEXP_SUBSTR('cc', '[^ab]c') AS C6
FROM DUAL;

[예제 10] 문자 리스트 범위 연산자 활용 쿼리

SELECT REGEXP_SUBSTR('1a', '[0-9][a-z]') AS C1, 
    REGEXP_SUBSTR('9z', '[0-9][a-z]') AS C2, 
    REGEXP_SUBSTR('aA', '[^0-9][^a-z]') AS C3, 
    REGEXP_SUBSTR('Aa', '[^0-9][^a-z]') AS C4
FROM DUAL;

[예제 11] POSIX 문자 클래스 활용 쿼리. c7 열은 [:punct:] 클래스를 사용해 특수 문자를 검색.

SELECT REGEXP_SUBSTR('gF1', '[[:digit:]]') AS C1, 
    REGEXP_SUBSTR('gF1', '[[:alpha:]]') AS C2, 
    REGEXP_SUBSTR('gF1', '[[:lower:]]') AS C3, 
    REGEXP_SUBSTR('gF1', '[[:upper:]]') AS C4, 
    REGEXP_SUBSTR('gF1', '[[:alnum:]]') AS C5, 
    REGEXP_SUBSTR('gF1', '[[:xdigit:]]') AS C6, 
    REGEXP_SUBSTR('gF1', '[[:punct:]]') AS C7
FROM DUAL;

PERL 정규 표현식 연산자

1. POSIX의 문자 클래스와 유사하게 동작하는 연산자

연산자	설명	동일
\d	숫자	[[:digit:]]
\D	숫자가 아닌 모든 문자	[^[:digit:]]
\w	숫자와 영문자(underbar 포함)	[[:alnum:]_]
\W	숫자와 영문자가 아닌 모든 문자(underbar 제외)	[^[:alnum:]_]
\s	공백 문자	[[:space:]]
\S	공백 문자가 아닌 모든 문자	[^[:space:]]

2. 수량사와 유사하게 동작하는 연산자

비탐욕적 방식으로 동작(패턴을 최소로 일치시킴)

연산자	설명
??	0회 또는 1회 일치
*?	0회 또는 그 이상의 횟수로 일치
+?	1회 또는 그 이상의 횟수로 일치
{m}?	m회 일치
{m,}?	최소 m회 일치
{,m}?	최대 m회 일치
{m,n}?	최소 m회, 최대 n회 일치

[예제 1] 문자 클래스 유사 동작 연산자 활용

SELECT 
    REGEXP_SUBSTR('(650) 555-0100', '^\(\d{3}\) \d{3}-\d{4}$') AS C1,
	REGEXP_SUBSTR('650 555-0100', '^\(\d{3}\) \d{3}-\d{4}$') AS C2,
	REGEXP_SUBSTR('b2b', '\w\d\D') AS C3,
	REGEXP_SUBSTR('b2_', '\w\d\D') AS C4,
	REGEXP_SUBSTR('b22', '\w\d\D') AS C5
FROM DUAL;

[예제 2] 문자 클래스 유사 동작 연산자 활용 2

SELECT 
    REGEXP_SUBSTR('jdoe@company.co.uk', '\w+@\w+(\.\w+)+') AS C1,
	REGEXP_SUBSTR('jdoe@company', '\w+@\w+(\.\w+)+') AS C2,
	REGEXP_SUBSTR('to: bill', '\w+\W\s\w+') AS C3,
	REGEXP_SUBSTR('to bill', '\w+\W\s\w+') AS C4
FROM DUAL;

[예제 3] 문자 클래스 유사 동작 연산자 활용 3

SELECT 
    REGEXP_SUBSTR('(a b )', '\(\w\s\w\s\)') AS C1,
	REGEXP_SUBSTR('(a b )', '\(\w\S\w\S\)') AS C2,
	REGEXP_SUBSTR('(a,b.)', '\(\w\s\w\s\)') AS C3,
	REGEXP_SUBSTR('(a,b.)', '\(\w\S\w\S\)') AS C4
FROM DUAL;

[예제 4] 수량사 유사 동작 연산자 활용 1

SELECT 
    REGEXP_SUBSTR('aaaa', 'a??aa') AS C1, -- non greedy 방식(최소로 찾음)
	REGEXP_SUBSTR('aaaa', 'a?aa') AS C2, -- greedy 방식(최대로 찾음)
	REGEXP_SUBSTR('xaxbxc', '\w*?x\w') AS C3,
	REGEXP_SUBSTR('xaxbxc', '\w*x\w') AS C4,
	REGEXP_SUBSTR('abxcxd', '\w+?x\w') AS C5,
	REGEXP_SUBSTR('abxcxd', '\w+x\w') AS C6
FROM DUAL;

[예제 5] 수량사 유사 동작 연산자 활용 2

SELECT 
    REGEXP_SUBSTR('aaaa', 'a{2}?') AS C1, -- non greedy 방식
	REGEXP_SUBSTR('aaaa', 'a{2}') AS C2, -- gredy 방식
	REGEXP_SUBSTR('aaaaa', 'a{2,}?') AS C3,
	REGEXP_SUBSTR('aaaaa', 'a{2,}') AS C4,
	REGEXP_SUBSTR('aaaaa', 'a{2,4}?') AS C5,
	REGEXP_SUBSTR('aaaaa', 'a{2,4}') AS C6
FROM DUAL;

정규 표현식 조건과 함수

REGEXP_LIKE 조건

source_char가 pattern과 일치하면 TRUE를, 일치하지 않으면 false를 반환함

REGEXP_LIKE(source_char, pattern [,match_param])

source_char: 검색 문자열 지정
pattern: 검색 패턴 지정
match_param: 일치 옵션 지정
- i (대소문자 무시)
- c (대소문자 구분)
- n (dot(.)을 개행 문자와 일치)
- m (다중 행 모드)
- x (검색 패턴의 공백 문자를 무시)
- 기본값: c / icmnx형식으로 다수의 옵션을 함께 지정할 수도 있음

[예제]

SELECT FIRST_NAME, LAST_NAME
	FROM HR.EMPLOYEES
WHERE REGEXP_LIKE(FIRST_NAME, '^Ste(v|ph)en$');

REGEXP_REPLACE 함수

source_char에서 일치한 pattern을 replace_string으로 변경한 문자 값을 반환

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

replace_string: 검색 문자열을 지정
position: 검색 시작 위치를 지정(기본 값은 1)
occurrence: 패턴 일치 횟수를 지정(기본 값은 1)

[에제]

SELECT 
    PHONE_NUMBER,
	REGEXP_REPLACE(PHONE_NUMBER, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})' , '(\1) \2-\3') AS C1
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID IN (144, 145);

REGEXP_SUBSTR 함수

source_char에서 일치한 pattern을 반환

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

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

SELECT REGEXP_SUBSTR('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') AS C1
FROM DUAL;

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) AS C1,
    REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) AS C2
FROM DUAL;

인수	설명
'1234567890'	입력 문자열
'(123)(4(56)(78))'	정규식 패턴
1	검색 시작 위치 (첫 번째 문자부터 검색)
1	첫 번째 매칭 결과 사용
'i'	대소문자 무시
1 or 4	캡쳐 그룹 인덱스(추출할 그룹 선택)

(123)             → 그룹 1
(4(56)(78))       → 그룹 2
   (56)          → 그룹 3
      (78)      → 그룹 4

REGEXP_INSTR 함수

source_char에서 일치한 pattern의 시작 위치를 정수로 반환

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

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

[예제]

SELECT 
    REGEXP_INSTR('123456789', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) AS C1,
	REGEXP_INSTR('123456789', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) AS C2,
	REGEXP_INSTR('123456789', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) AS C3
FROM DUAL;

REGEXP_COUNT 함수

source_char에서 일치한 pattern의 횟수 반환

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

[예제]

SELECT
	REGEXP_COUNT('123123123123123', '123', 1) AS C1,
	REGEXP_COUNT('123123123123', '123', 3) AS C2
FROM DUAL;

'자격증 공부 > SQLD 자격증' 카테고리의 다른 글

SQLD 합격 후기 / 공부 방법 (0)	2025.10.21
SQLD(SQLP) 과목1 - 2장. 데이터 모델의 이해 (1)	2025.03.07
SQLD(SQLP) 과목2 - 3장. 관리 구문 (0)	2025.03.05
SQLD(SQLP) 과목2 - 1장. SQL 기본 (1)	2025.02.28
SQLD(SQLP) 과목1 - 1장. 데이터 모델의 이해 (0)	2025.02.25

SQLD(SQLP) 과목2 - 2장. SQL 활용

서브 쿼리

서브 쿼리란?

Single Row(단일 행) 서브 쿼리

Multi Row(다중 행) 서브 쿼리

Multi Column(다중 칼럼) 서브 쿼리

Correlated (연관) 서브 쿼리

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

뷰(View)

집합 연산자

그룹 함수

데이터 분석을 위한 함수

ROLL UP 함수

CUBE 함수

GROUPING SETS 함수

윈도우 함수

함수 종류

SYNTAX

그룹 내 순위 함수

일반 집계 함수

그룹 내 행 순서 함수

그룹 내 비율 함수

Top N 쿼리

ROWNUM 슈도 칼럼 (Oracle)

TOP 절 (SQL Server)

ROW LIMITING 절

계층형 질의와 셀프 조인

계층형 데이터란?

SELF JOIN 셀프 조인

계층형 질의

PIVOT 절과 UNPIVOT 절

PIVOT 절

UNPIVOT 절

정규 표현식

기본 문법

POSIX 연산자

PERL 정규 표현식 연산자

정규 표현식 조건과 함수

REGEXP_LIKE 조건

REGEXP_REPLACE 함수

REGEXP_SUBSTR 함수

REGEXP_INSTR 함수

REGEXP_COUNT 함수

'자격증 공부 > SQLD 자격증' 카테고리의 다른 글

관련글

티스토리툴바