2장. SQL 활용

1절. 서브 쿼리

 

- 조인과 서브 쿼리

    조인 : 집합 간의 곱 관계

    서브쿼리 : 항상 메인쿼리 레벨의 집합

 

- 주의사항

    1. 괄호로 감싸 기술

    2. 비교연산자와 사용 가능

    3. ORDER BY 사용 불가

 

- 분류

    1) 동작 방식

비연관 서브쿼리: 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않음

연관 서브쿼리: 서브쿼리가 메인쿼리 칼럼을 가지고 있음

 

    2) 결과 데이터 형태

단일 행 서브 쿼리: 서브쿼리 실행 결과 1건 이하

다중 행 서브 쿼리: 서브쿼리 실행 결과 여러 개

다중 칼럼 서브 쿼리(Oracle): 여러 칼럼 반환

 

1. 단일 행 서브 쿼리

- 단일 행 비교 연산자 사용: =, <, <=, >, >=, <>

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

 

2. 다중 행 서브 쿼리

- 다중 행 비교 연산자 사용: IN, ALL, ANY, SOME

* EXISTS : 1건만 찾으면 더 이상 검색 X, 연관 서브쿼리로만 사용

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

 

3. 다중 칼럼 서브 쿼리

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;

 

4. 연관 서브 쿼리

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 선수명;

 

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

- 스칼라 서브 쿼리

한 행, 한 칼럼만을 반환하는 서브쿼리

 

1) SELECT 절

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 절 : 인라인 뷰

SELECT B.TEAM_NAME AS 팀명, A.PLAYER_NAME AS 선수명, A.BACK_NO AS 백ㄴ머버
FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO
	  FROM PLAYER
      WHERE POSITION = 'MF') A
WHERE B.TEAM_ID = A.TEAM_ID
ORDER BY 선수명;

* TOP-N 쿼리: 인라인 뷰 정렬 뒤 일부 데이터를 추출

 

3) HAVING 절

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

 

6. 뷰

- 단지 뷰 정의(View Definition)만을 가지고 있음 (내부적으로 질의를 재작성)

- 장점

1. 독립성

2. 편리성

3. 보안성

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;

 

2절. 집합 연산자

- 조건

1. SELECT 절의 칼럼 수가 동일

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

 

• UNION
• UNION ALL: 중복도 모두 표시
• INTERSECT
• EXCEPT

 

3절. 그룹 함수

 

2. ROLLUP 함수

 

1) N + 1 레벨 Subtotal 생성

2) 계층 구조

• L1: 표준 집계
• L2: Subtotal
• L3: Grand Total

SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN '모든 부서' ELSE B.DNAME END AS 부서,
       CASE GROUPING(A.JOB) WHEN 1 THEN '모든 직무' ELSE A.JOB END AS 직무,
		 COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_CUM
FROM EMP A, DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY ROLLUP(A.JOB, B.DNAME)
ORDER BY B.DNAME DESC, A.JOB DESC;

 

3. CUBE 함수

1) 2^N개 Subtotal 생성

2) 평등 구조

SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN '모든 부서' ELSE B.DNAME END AS 부서,
       CASE GROUPING(A.JOB) WHEN 1 THEN '모든 직무' ELSE A.JOB END AS 직무,
		 COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_CUM
FROM EMP A, DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY CUBE(A.JOB, B.DNAME)
ORDER BY B.DNAME DESC, A.JOB DESC;

 

4. GROUPING SETS 함수

1) 괄호로 묶은 집합별 집계

2) 평등 구조

SELECT CASE GROUPING(B.DNAME) WHEN 1 THEN '모든 부서' ELSE B.DNAME END AS 부서,
       CASE GROUPING(A.JOB) WHEN 1 THEN '모든 직무' ELSE A.JOB END AS 직무,
		 COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_CUM
FROM EMP A, DEPT B
WHERE B.DEPTNO = A.DEPTNO
GROUP BY GROUPING SETS(A.JOB, B.DNAME)
ORDER BY B.DNAME DESC, A.JOB DESC;

 

 

4절. 윈도우 함수

 

1. 윈도우 함수 개요

• 윈도우 함수 = 분석 함수 = 순위 함수
• 행과 행간의 관계를 쉽게 정의
• 중첩해서 사용 불가, 서브쿼리에서 사용 가능

- 종류

1. 순위 함수: RANK, DENSE_RANK, ROW_NUMBER

2. 집계 함수: SUM, MAX, MIN, AVG, COUNT

3. 순서 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD

4. 비율 함수: RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE

5. 통계 함수

 

- 용법

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

* ROWS: 물리적인 결과 행의 수, RANGE: 논리적인 값에 의한 범위

 

2. 그룹 내 순위 함수

1) RANK

- 특정 칼럼에 대한 순위를 구하는 함수 (공동 2위 다음은 4위)

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

 

2) DENSE_RANK

- 동일한 순위를 하나의 건수로 취급 (공동 2위 다음은 3위)

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

 

3) ROW_NUMER 함수

- 동일한 값이라도 고유한 순위를 부여 (공동 2위는 없음)

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

 

3. 일반 집계함수

1) SUM

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

- 누적합계

SELECT MGR, ENAME, SAL,
       SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS SAL_NUM
FROM EMP;

 * RANGE UNBOUNDED PRECEDING: 파티션 내 첫번째 행까지 범위 지정

 

2) MAX

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

 

3) MIN

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

 

4) AVG

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

* ROWS BETWEEN 1 PRECEDING AND 1 FOLLWING: 앞의 한 행, 현재 행, 뒤의 한 행을 범위로 지정

 

5) COUNT

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

* ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING: 급여 -50 ~ +150

 

4. 그룹 내 행 순서 함수

* SQL Server는 지원하지 않음

1) FIRST_VALUE

- 등수 관계없이 처음 나온 행을 반환

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

 

2) LAST_VALUE

- 등수 관계없이 나중에 나온 행을 반환

 

3) LAG

- 이전 몇 번째 행을 가져옴

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

* LAG(SAL, 2, 0): SAL 칼럼에서 두 행 앞의 값을 가져오고, NULL일 경우 0 반환

 

4) LEAD

- 이후 몇 번째 행을 가져옴

 

5. 그룹 내 비율 함수

* SQL Server는 지원하지 않음

1) RATIO_TO_REPORT

- 파티션 내 행별 칼럼 값의 백분율

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

 

2) PERCENT_RANK

- 파티션 내 행의 순서별 백분율

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

 

3) CUME_DIST

- 현재 행 이하의 건에 대한 누적백분율

* 동일한 값에 대해 뒤 행의 함수 결과 값을 따름

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

 

4) NTILE

- 전체 건수를 N등분 (분위 수를 구함)

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

 

 

5절. Top N 쿼리

1. ROWNUM 슈도 칼럼 (Oracle)

- WHERE 절에서 행의 개수를 제한

- Top N 쿼리 시 인라인 뷰에서 먼저 데이터를 정렬해야 함

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

 

2. TOP 절 (SQL Server)

TOP (expression) [PERCENT] [WITH TIES]

Ex)

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

 

3. ROW LIMITING 절 (ANSI 표준)

- Oracle 12.1, SQL Server 2012 이상

- ORDER BY 절 다음에 기술, ORDER BY 절과 함께 수행

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

Ex1)

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

Ex2)

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

 

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

1. 개요

- 계층형 데이터: 동일 테이블에 계층적으로 상위, 하위 데이터가 포함된 데이터 → 셀프 조인, 계층형 질의로 조회

 

2. 셀프 조인

- 특징

• FROM 절에 동일 테이블이 두 번 이상
• 반드시 ALIAS 사용
• 깊은 레벨의 노드를 조회하기 위해선 셀프 조인을 반복

Ex1) 순방향 전개: 루트 → 리프

SELECT C.EMPNO, C.ENAME, C.MGR
FROM EMP A, EMP B, EMP C
WHERE A.ENAME = 'JONES'
AND B.MGR = A.EMPNO
AND C.MGR = B.EMPNO;

Ex2) 역방향 전개: 리프 → 루트

SELECT C.EMPNO, C.ENAME, C.MGR
FROM EMP A, EMP B, EMP C
WHERE A.ENAME = 'SMITH'
AND B.EMPNO = A.MGR
AND C.EMPNO = B.MGR;

 

3. 계층형 질의

1) Oracle

SELECT ...
FROM 테이블
WHERE 조건
START WITH 조건 
CONNECT BY [NOCYCLE] 칼럼 = PRIOR 칼럼
[ORDER SIBLINGS BY 칼럼, ...]

START WITH: 루트 데이터 지정

CONNECT BY: 자식 데이터 지정

PRIOR: 순방향 전개 - (FK) = PRIOR(PK)

            역방향 전개 - (PK) = PRIOR(FK)

NOCYCLE: 사이클 발생 이후 데이터 전개 X

ORDER SIBLINGS BY: 형제 노드 사이 정렬 수행

 

- 계층형 질의 가상 칼럼

1) LEVEL: 루트 데이터면 1, 리프 데이터까지 1씩 증가

2) CONNECT_BY_ISLEAF: 리프 데이터이면 1, 아니면 0

3) CONNECT_BY_ISCYCLE: 조상이면 1, 아니면 0

 

- 계층형 질의 함수

1) SYS_CONNECT_BY_PATH: 루트부터 현재까지 경로 표시

2) CONNECT_BY_ROOT: 현재 데이터의 루트 표시

 

Ex)

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

 

2) SQL Server

- CTE(Common Table Expression)를 재귀 호출

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

앵커 멤버: 위의 쿼리

재귀 멤버: 아래 쿼리

 

7절. PIVOT 절과 UNPIVOT 절

2. PIVOT 절

- 행을 열로 회전

- CASE, GROUP BY 절 사용하여 수행 가능

PIVOT (aggregate_function(칼럼1)
       FOR 칼럼2
       IN 칼럼값)

-> 칼럼 2의 칼럼값을 기준으로 칼럼1 피벗테이블 생성

Ex) 칼럼명: D10_SAL, D20_SAL, D30_SAL

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;

 

3. UNPIVOT 절

- 열을 행으로 전환

- 행 복제 사용하여 수행 가능

UNPIVOT [{INCLUDE | EXCLUDE} NULLS]
        (칼럼1 FOR 칼럼2 IN 칼럼값3)

-> 칼럼3으로부터 도출된 칼럼2를 기준으로 칼럼1을 언피벗

Ex)

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;

 

8절. 정규표현식

 

2. 기본 문법

1) POSIX

- 수량사: 패턴을 최대로 일치시키는 탐욕적인 방식

- 역참조(back reference): \n; n번째 서브 표현식과 일치

- 문자 리스트

[:digit:]	숫자
[:lower:]	소문자
[:upper:]	대문자
[:alpha:]	영문자
[:alnum:]	영문자와 숫자
[:xdigit:]	16진수
[:punct:]	구두점 기호
[:blank:]	공백 문자
[:space:]	공간 문자 (space, enter, tab)

 

2) PERL

- 수량사: 패턴을 최소로 일치시키는 비탐욕적인 방식 (그냥 뒤에 물음표 하나만 더 붙음)

??	0회 또는 1회 일치
*?	0회 또는 그 이상 일치
+?	1회 또는 그 이상 일치
{m}?	m회 일치
{m,}?	최소 m회 일치
{,m}?	최대 m회 일치
{m, n}?	최소 m회, 최대 n회 일치

- 문자 리스트

\d	숫자
\D	숫자가 아닌 모든 문자
\w	숫자와 영문자
\W	숫자와 영문자가 아닌 모든 문자
\s	공백 문자
\S	공백 문자가 아닌 모든 문자

 

3. 정규 표현식 조건과 함수

1) REGEXP_LIKE: 일치하면 True, 아니면 False

REGEXP_LIKE(source_char, pattern [, match_param])

 

- match_param

• i: 대소문자 무시
• c: 대소문자 구분
• n: .를 개행 문자와 일치
• m: 다중 행 모드
• x: 검색 패턴의 공백 문자 무시

 

2) REGEXP_REPLACE: 변경 문자값 반환

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

- position: 검색 시작 위치

- occurrence: 패턴 일치 횟수

 

3) REGEXP_SUBSTR: 일치한 패턴 반환

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

- subexpr: 0은 전체 패턴, 1 이상은 서브 표현식 반환

 

4) REGEXP_INSTR: 일치한 패턴의 시작 위치를 정수로 반환

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

 

5) REGEXP_COUNT: 일치한 패턴의 횟수

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

---

추가

1. 연관 서브쿼리는 상대적으로 속도가 떨어진다. -> 조건이 잘 맞는지 검토가 필요

2. PIVOT 절은 집계함수와 FOR절에 지정되지 않은 열을 기준으로 집계

Ex) sal 값은 어떤 열로 집계?

yyyy, job

SELECT *
FROM (SELECT TO_CHAR(hiredate, 'YYYY') AS yyyy, job, deptno, sal from emp)
PIVOT (SUM(sal) FOR deptno IN (10, 20, 30))
ORDER BY 1, 2;

 

3. UNION ALL: 중복 포함

4. ROW LIMITING: Oracle 12.1, SQL Server 2012 이상 지원

5. PIVOT = GROUP BY + CASE

   UNPIVOT = 행 복제

---

EXISTS: 서브 쿼리의 결과가 1건이라도 존재하면 True 아니면 False

LPAD(칼럼, a, b): 칼럼값의 길이가 a가 될 때까지 b로 채움

REGEXP(문자열, 패턴, 시작위치, 일치 횟수)

 

집합 연산자

1. MINUS = DISTINCT + NOT EXISTS

    INTERSECT = DISTINCT + EXISTS

2. ORDER BY는 오직 한 번만!

 

그룹 함수

1. ROLLUP(C1, C2) = (C1, C2) → (C1) → ()

    CUBE(C1, C2) = () → (C2) → (C1) → (C1, C2)

2. 조합 열은 하나의 단위로 처리

3. GROUPING: 표현식이 행 그룹에 포함되면 0, 그렇지 않으면 1 반환

 

윈도우 함수

기본 범위는 RANGE UNBOUNDED PRECEDING

SUM, COUNT 등에 ORDER BY를 기술하면 누적 통계를 구함

 

Top N 쿼리

TOP(N) WITH TIES = RANK

- ROW LIMITING: FETCH & OFFSET

 

계층형 질의

1. START WITH = CTE 상단 쿼리 = 시작 노드

    CONNECT BY = CTE 하단 쿼리 = 반복 조건

2. CTE: WITH ~ UNION ALL 

3. WHERE절보다 실행순서가 앞선다!

 

 

PIVOT과 UNPIVOT

PIVOT SUM(칼럼) FOR A IN (a, b, c): A를 a, b, c로 pivot

UNPIVOT (A FOR FOR B IN (a, b, c)): a, b, c를 A로 unpivot, B칼럼에는 별칭 없을 시 칼럼명

D10_SAL, D20_SAL, D30_SAL

 

Q. 다음 중 아래 SQL의 결과를 고르시오.

<T1 테이블>

C1

1

2

3

<T2 테이블>

C1

1

NULL

SELECT COUNT(*) AS CNT FROM T1 WHERE C1 NOT IN (SELECT C1 FROM T2);

C1 NOT IN (1, NULL)

= NOT (C1 = 1 OR C1 = NULL)

= C1 <> 1 AND C1 <> NULL

NULL의 연산은 항상 NULL이므로 조회되는 행은 0개