[SQL 전문가] 과목II. 제2장 SQL 활용

📖제2장 SQL 활용

• 제1절 서브 쿼리
• 제2절 집합 연산자
• 제3절 그룹 함수
• 제4절 윈도우 함수
• 제5절 Top N 쿼리
• 제6절 계층형 질의와 셀프 조인
• 제7절 PIVOT 절과 UNPIVOT 절
• 제8절 정규 표현식
• 장 요약
• 연습문제

✨제1절 서브 쿼리(Subquery)

---

서브쿼리란?

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

1. 단일 행 서브 쿼리

서브 커리 종류	설명
Single Row 서브 쿼리 (단일 행 서브 쿼리)	서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리를 의미한다. 단일 행 서브 쿼리는 단일 행 비교 연산자와 함께 사용된다. 단일 행 비교 연산자에는 =, <, <=, >, >=, <> 등이 있다.
Multi Row 서브 쿼리 (다중 행 서브 쿼리)	서브 쿼리의 실행 결과가 여러 건인 서브 쿼리를 의미한다. 다중 행 서브 쿼리는 다중행 비교 연산자와 함께 사용된다. 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
Multi Column 서브 쿼리 (다중 칼럼 서브 쿼리)	서브 쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브 쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

2. 다중 행 서브 쿼리

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

3. 다중 칼럼 서브 쿼리

다중 칼럼 서브 쿼리는 서브 쿼리의 결과로 여러 개의 칼럼이 반환돼 메인 쿼리의 조건과 동시에 비교되는 것을 의미한다. SQL Server에서는 지워되지 않는 문법이다.

소속팀별 키가 가장 작은 사람들의 정보를 출력하는 문제를 갖고 다중 칼럼 서브 쿼리를 알아보도록 한다. 소속팀별 키가 가장 작은 사람들의 정보는 GROUP BY를 이용해 찾을 수 있으므로 다음과 같이 SQL문을 작성할 수 있다.

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. 연관 서브 쿼리

연관 서브 쿼리(Correlated Subquery)는 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리다.

선수 자신이 속한 팀의 평균키보다 작은 선수들의 정보를 출력하는 SQL 문을 연관 서브 쿼리를 이용하여 작성해 보면 다음과 같다.

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
                         WHERE X.TEAM_ID = A.TEAM_ID
                      GROUP BY X.TEAM_ID)
  AND B.TEAM_ID = A.TEAM_ID
ORDER BY 선수명;

 

EXISTS 서브 쿼리는 항상 연관 서브 쿼리로 사용된다. 또한 EXISTS 서브 쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.

다음은 EXISTS 서브 쿼리를 사용해 '20220501'부터 '20220502' 사이에 경기가 있는 경기장을 조회하는 SQL 문이다.

SELECT A.STADIUM_ID AS ID, A.STADIUM_NAME AS 경기장명
    FROM STADIUM A
  WHERE EXISTS (SELECT 1 
                   FROM SCHEDULE X
                 WHERE X.STADIUM_ID = A.STADIUM_ID
                   AND X.SCHE_DATE BETWEEN '20220501' AND '20220502');

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

종류	설명
SELECT 절 - 스칼라 서브 쿼리(Scallar Subquery)	한 행, 한 칼럼만을 반환하는 서브 쿼리 ** 단일 행 서브 쿼리이기 때문에 결과가 2건 이상 반환되면 SQL 문은 오류를 반환한다.
FROM 절 - 인라인 뷰(Inline View) (중요)	인라인 뷰를 사용하면 서브 쿼리의 결과를 마치 테이블처럼 사용할 수 있다. 인라인 뷰는 쿼리 내에서 즉시 처리된다. (게시판 페이징 할때 유용하다 자세히 알아두기!)
HAVING 절	집계함수와 함께 사용될 때 그룹핑 된 결과에 대해 부가적인 조건을 주기 위해 사용한다.

 

6. 뷰(View)

테이블은 실제로 데이터를 갖고 있는 반면, 뷰(View)는 실제 데이터를 갖고 있지 않다. 뷰는 단지 뷰 정의(View Definition)만을 갖고 있다. 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)해 질의를 수정한다.

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

뷰는 다음과 같이 CREATE VIEW 문으로 생성할 수 있다.

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');

V_PLAYER_TEAM_FILTER 뷰는 이미 앞에서 생성했던 V_PLAYER_TEAM 뷰를 기반으로 해서 생성된 뷰다.

V_PLAYER_TEAM_FILTER 뷰는 선수 포지션이 골키퍼(GK), 미드필더(MF)인 선수만을 추출하고자 하는 뷰다(뷰를 포함하는 뷰를 잘못 생성하는 경우 성능상의 문제를 유발할 수 있으므로, 뷰와 SQL의 수행원리를 잘 이해하고 사용해야한다.)

-- 사용법

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME
  FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '황%';


-- 뷰 제거하기

DROP VIEW V_PLAYER_TEAM;

 

✨제2절 집합 연산자

---

집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	(합집합)여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
UNION ALL	(합집합)여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다. 일반적으로 여러 질의 결과가 상호 배타적인(Exclusive)일때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다. (결과의 정렬 순서에는 차이가 있을 수 있음)
INTERSECT	(교집합)여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
EXCEPT	(차집합)앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다. (일부 데이터베이스는 MINUS를 사용함)

집합 연산자를 사용해 만들어지는 SQL 문의 형태는 아래와 같다.

SELECT 칼럼명1, 칼럼명2, ...
	FROM 테이블명1
[ WHERE 조건식 ]
[ GROUP BY 칼럼 or 표현식 ]
[ HAVING 그룹조건식 ]
집합연산자 -- ( UNION/UNION ALL/INTERSECT/MINUS or EXCEPT )
	SELECT 칼럼명1, 칼럼명2, ...
    	FROM 테이블명2
    [ WHERE 조건식 ]
    [ GROUP BY 칼럼 or 표현식 ]
    [ HAVING 그룹조건식 ]
    [ ORDER BY 칼럼 or 표현식 [ ASC or DESC ] ];

 

✨제3절 그룹 함수

---

1. 데이터 분석 개요

ANSI/ISO SQL 표준 함수	함수 종류
AGGREGATE FUNCTION	COUNT, SUM, AVG,MAX,MIN 외 각종 집계 함수들
GROUP FUNCTION	ROLLUP, CUBE, GROUPING SETS
WINDOW FUNCTION	RANK(순위) function, AGGREGATE(집계) function, 행 순서 관련 함수

2. ROLLUP 함수

소그룹 간의 소계를 계산하는 ROLLUP 함수

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

 

3. CUBE 함수

GROUP BY 항목 간 다차원적인 소계를 계산할 수 있는 CUBE 함수

CUBE는 결합 가능한 모든 값에 대해 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻는 장점이 있는 반면,

시스템에 부하를 많이 주는 단점이 있다.

-- 작성법 예제
SELECT B.DNAME,A.JOB, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
	FROM EMP B, DEPT A
  WHERE B.DEPTNO = A.DEPTNO
GROUP BY CUBE (B.DNAME, A.JOB);

 

4. GROUPING SETS 함수

원하는 부분의 소계만 손쉽게 추출하여 계산할 수 있는 GROUPING SETS 함수

-- 작성법 예제 Oracle

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 B.DEPTNO = A.DEPTNO
GROUP BY GROUPING SETS (B.DNAME, A.JOB)
ORDER BY B.DNAME, A.JOB;

 

✨제4절 윈도우 함수

---

1. 윈도우 함수 개요

윈도우 함수 종류		설명
순위(RANK) 관련 함수	RANK	- 특정항목 및 파티션에 대해서 순위를 계산한다. - 동일한 순위는 동일한 값이 부여된다.
	DENSE_RANK	- 동일한 순위를 하나의 건수로 계산한다.
	ROW_NUMBER	- 동일한 순위에 대해서 고유의 순위를 부여한다.
집계(AGGREGATE) 관련 함수	SUM	- 합계를 계산한다.
	AVG	- 평균을 계산한다.
	COUNT	- 행 수를 계산한다.
	MAX, MIN	- 최대값과 최소값을 계산한다.
순서 관련 함수	FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수를 사용해서 같은 결과를 구할 수 있다.
	LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구한다.
	LAG	- 이전 행을 가지고 온다.
	LEAD	- 윈도우에서 특정 위치의 행을 가지고 온다. - 기본값은 1이다.
비율 관련 함수	CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. - 누적 분포상에 위치를 0~1사이의 값을 가진다.
	PERCENT_RANK	- 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다.
	NTILE	파티션 별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회한다.
	RATIO_TO_REPORT	파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회한다.
통계 분석 관련 함수	CORR, COVAR_POP, COVAR_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

◇ 윈도우 함수 구조

-- 윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.

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

 

✨제5절 Top N 쿼리

---

1. ROWNUM 슈도 칼럼

Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column으로서 SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호다. 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다. (예 : 게시글)

-- 한 건의 행만 가져오고 싶을 때 아래와 같이 사용할 수 있다.

SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= 1;
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < 2;

-- 두 건 이상의 N 행을 가져오고 싶을 때는 출력되는 행의 한계를 지정해서 사용할 수 있다.

SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N;
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < N+1;

 

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

-- [예제] Oracle

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


-- [출력 화면]

ENAME			SAL
-----			----
KING			5000
SCOTT			3000
FORD			3000

 

2. TOP 절

SQL Server는 TOP절을 사용해 결과 집합으로 출력되는 행의 수를 제한할 수 있다.

TOP (Expression) [PERCENT] [WITH TIES]

TOP 절의 표현식	설명
Expression	반환할 행 수를 지정하는 숫자이다.
PERCENT	쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타낸다.
WITH TIES	ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.(동일 수치의 데이터를 추가로 더 추출한다.)

 

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

-- [예제] SQL Server

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

-- [출력 화면]

ENAME		SAL
-----		----
KING		5000
SCOTT		3000

[예제] 사원 테이블에서 급여가 높은 2명을 내림차순으로 출력, 같은 급여를 받는 사원이 있으면 같이 출력하시오.

( TOP(2) WITH TIES 옵션은 동일 수치의 데이터를 추가로 더 추출하는 것으로, SCOTT와 FORD의 급여가 공동 2위이므로 출력 결과는 아래와 같다. )

-- [예제] SQL Server

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

-- [출력 화면]

ENAME 		SAL
-----		----
KING		5000
SCOTT		3000
FORD		3000

 

3. ROW LIMITING 절

Oracle은 12.1 버전, SQL Server는 2012 버전부터 ROW LIMITING 절로 Top N 쿼리를 작성할 수 있다.

ROW LIMITING 절은 ANSI 표준 SQL 문법이다.

 

아래는 ROW LIMITING 절의 구문이다. ROW LIMITING 절은 ORDER BY 절 다음에 기술하며, ORDER BY 절과 함께 수행된다. ROW와 ROWS는 구분하지 않아도 된다.

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

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

[예제] 아래는 ROW LIMITING 절을 사용한 Top N 쿼리다.

-- [예제]

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

-- [출력 화면]

EMPNO		SAL
-----		----
7369		800
7900		950
7876		1100
7654		1250
7521		1250

5 행이 선택됐습니다.

 

[예제] 아래와 같이 OFFSET만 기술하면 건너뛴 행 이후의 전체 행이 반환된다.

-- [예제]

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

-- [출력 화면]

EMPNO		SAL
-----		----
7934		1300
7844		1500
7499		1600

...

9 행이 선택됐습니다.

 

✨제6절 계층형 질의와 셀프 조인(중요)

---

1. 개요

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다.

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

 

계층형 데이터

위의 그림은 사원에 대한 순환 관계 데이터 모델을 표현한 것이다.

(2)계층형 구조에서 A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E가 있다.

계층형 구조를 데이터로 표현한 것이 (3)샘플 데이터다.

순환 관계 데이터 모델은 셀프 조인이나 계층형 질의로 조회할 수 있다.

2. 셀프 조인 ( 중요 ) 💡

셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하므로 식별을 위해 반드시 테이블 별칭(Alias)을 사용해야 한다. 그리고 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해줘야 한다. 이외 사항은 조인과 동일하다.

-- 셀프 조인에 대한 기본적인 사용법은 다음과 같다.

SELECT A.칼럼, B.칼럼, ...
	FROM EMP A, EMP B
WHERE A.칼럼 = B.칼럼;

-- [예시]

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

3. 계층형 질의

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

 

Oracle 계층형 질의 구문

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

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

Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.

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

다음은 emp 테이블을 계층형 질의 구문을 이용해서 조회한 것이다. PK인 empno 앞쪽에 PRIOR 연산자를 기술했다. 여기서는 결과 데이터를 들여쓰기하기 위해 LPAD 함수를 사용했다.

-- [예제]

SELECT LEVEL
      ,LPAD ('', 4 * (LEVEL - 1)) || 사원 AS 사원, 직속상사
      ,CONNECT_BY_ISLEAF AS ISLEAF
      FROM 사원
START WITH 직속상사 IS NULL
CONNECT BY 직속상사 = PRIOR 사원;

CONNECT BY 절은 각 행이 어떻게 연결되는지를 Oracle에게 알려주는 역할을 한다. 현재 행과 다른 행은 PRIOR 키워드를 통해 구별된다.CONNECT BY PRIOR 사원 = 직속상사; 이뜻은 "방금 전 행의 사원 값이 현재 행의 직속상사 값인 행을 모두 찾아라" 라는 뜻이다.쉽게 풀이하자면, 방금전에 살펴본 사원이 현재 사원의 상사가 되는 방식으로 출력하라는 것이다. ** CONNECT BY 사원 = PRIOR 직속상사; (== CONNECT BY PRIOR 직속상사 = 사원;) 와 같이 PRIOR 키워드를 "=" 기호를 사이에 두고 반대편으로 옮길 경우, 결과는 Tree를 거슬러 내려가는 것(순방향 전개)이 아니라, 거슬러 올라가는 방식(역방향 전개)으로 리턴된다.

 

순방향

위 그림은 LEVEL 칼럼이 사용된 순방향 계층형 질의에 대한 논리적인 실행 모습이다. 그림을 보면 순방향 전개 시, A는 루트 데이터이기 때문에 레벨이 1이다. A의 하위 데이터인 B, C는 레벨 2 👉🏻 C의 하위 데이터인 D, E는 레벨 3 👉🏻 리프 데이터는 B, D, E 가 된다.

역방향 전개

역방향 계층형 전개 시에는 D가 루트 데이터로 레벨 1이 되며, 상위 레벨의 데이터가 역순의 레벨을 갖는다.

계층형 질의에서 사용되는 함수	설명
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다. 사용법 : SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다. 사용법 : CONNECT_BY_ROOT 칼럼

 

✨제7절 PIVOT 절과 UNPIVOT 절

---

1. 개요

PIVOT은 회전시킨다는 의미를 갖고 있다. PIVOT 절은 행을 열로 회전시키고, UNPIVOT 절은 열을 행으로 회전시킨다.

 

2. PIVOT 절

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]...
             })
         )

PIVOT 절 ( 행 -> 열 )
aggregate_function	집계할 열을 지정
FOR	PIVOT 할 열을 지정한다.
IN	PIVOT 할 열 값을 지정한다.

PIVOT 절은 집계함수와 FOR절에 지정되지 않은 열을 기준으로 집계되기 때문에 인라인 뷰를 통해 사용할 열을 지정해야 한다.

[예제 1]

-- [예제 1] PIVOT 절을 사용한 쿼리

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

-- [출력 화면]

JOB        10     20     30
----       ---    ---    ---
ANALYST          6000		
CLERK      1300  1900     950
MANAGER    2450  2975    2850
PRESIDENT  5000	
SALESMAN                 5600

5 행이 선택됐습니다.

다음 쿼리는 인라인 뷰에 yyyy표현식을 추가한 것이다. 행 그룹에 yyyy 표현식이 추가된 것을 확인할 수 있다.

[예제 2]

-- [예제 2]

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;

-- [출력 화면]

YYYY    JOB       10     20      30
----    ----      ---    ---    ---
1980    CLERK            800
1981    ANALYST         3000
1981    CLERK                    950
1981    MANAGER   2450  2975    2850
...

9행이 선택됐습니다.

다음 쿼리는 집계함수와 IN 절에 별칭을 지정했다. 별칭을 지정하면 결과 집합의 열 명이 변경된다.

[예제 3]

-- [예제 3]

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;

-- [출력 화면]

JOB        D10_SAL     D20_SAL     D30_SAL
----       -------     -------     -------
ANALYST                 6000		
CLERK       1300        1900           950
MANAGER     2450        2975          2850
PRESIDENT   5000	
SALESMAN                              5600

5 행이 선택됐습니다.

SELECT 절에 부여된 열 명을 지정하면 필요한 열만 조회할 수 있다.

[예제 4]

-- [예제 4]

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

-- [출력 화면]

JOB         D20_SAL
----        ------
ANALYST      6000
MANAGER      2975

2 행이 선택됐습니다.

 

3. UNPIVOT 절

UNPIVOT 절은  PIVOT 절과 반대로 동작한다. 열이 행으로 전환된다.  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]...)}]]...
           )
        )
        
-- 작성법 예시 

SELECT *
    FROM ( 피벗 대상 쿼리문 )
UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )

UNPIVOT 절 ( 열 -> 행 )
UNPIVOT column	UNPIVOT된 값이 들어갈 열을 지정한다.
FOR	UNPIVOT된 값을 설명할 값이 들어갈 열을 지정한다.
IN	UNPIVOT할 열과 설명할 값의 리터럴 값을 지정한다.

출처 : https://gent.tistory.com/382

 

✨제8절 정규 표현식

---

1. 개요

정규 표현식(regular expression)은 문자열의 규칙을 표현하는 검색 패턴으로 주로 문자열 검색과 치환에 사용된다.

2. 기본문법

연산자	설명
.	(dot) 모든 문자와 일치(newline 제외)
|	(or) 대체 문자를 구분
\	(backslash) 다음 문자를 일반 문자로 취급
^	(carrot) 문자열의 시작
$	(dollar) 문자열의 끝
수량사(quantifier) 패턴을 최대로 일치시키는 탐욕적(greedy) 방식으로 동작 PERL 정규 표현식 연산자는 수량사와 유사하게 동작, 패턴을 최소로 일치시키는 비탐욕적 (nongreedy) 방식으로 동작
? / ??	0회 또는 1회 일치
* / *?	0회 또는 그 이상의 횟수로 일치
+ / +?	1회 또는 그 이상의 횟수로 일치
{M} / {M}?	m회 일치
{m,} / {m,}?	최소 m회 일치
{,m} / {,m}?	최대 m회 일치
{m,n} / {m,n}?	최소 m회, 최대 n회 일치
(expr)	괄호 안의 표현식을 하나의 단위로 취급
\n	n번째 서브 표현식과 일치, n은 1에서 9 사이의 정수
[a-z]	a부터 z중 한 문자와 일치
[^a-z]	a부터 z에 포함되지 않는 문자

문자 리스트(character list)는 문자를 대괄호로 묶은 표현식이다. 

문자 리스트 중 한 문자만 일치하면 패턴이 일치한 것으로 처리된다. 문자 리스트에서 하이픈(-)은 범위 연산자로 동작한다.

연산자	설명	동일
[[:digit:]]	숫자	[0-9], \d
[^[:digit:]]	숫자가 아닌 모든 문자	\D
[[:lower:]]	소문자	[a-z]
[[:upper:]]	대문자	[A-Z]
[[:alpha:]]	영문자	[a-zA-Z]
[[:alnum:]]	숫자와 영문자	[0-9a-zA-Z]
[[:alnum:]_]	숫자와 영문자(underbar 포함)	\w
[^[:alnum:]_]	숫자와 영문자가 아닌 모든 문자(underbar 제외)	\W
[[:xdigit:]]	16진수	[0-9a-fA-F]
[[:punct:]]	구두점 기호	[^[:alnum:][:cntrl:]]
[[:blank:]]	공백 문자
[[:space:]]	공간 문자 (space, enter, tab)	\s
[^[:space:]]	공간 문자가 아닌 모든 문자	\S

 

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

종류	설명
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다. icnmx 형식으로 다수의 옵션을 함께 지정할 수도 있다.)
REGEXP_REPLACE	source_char 에서 일치한 pattern을 replace_string으로 변경한 문자 값을 반환	[ 작성법 ] REGEXP_REPLACE (source_char, pattern [, replace_string [, position [, occurrence [, match_param]]]])
		replace_string	변경 문자열을 지정
		positoin	검색 시작 위치를 지정
		occurrence	패턴 일치 횟수를 지정
REGEXP_SUBSTR	source_char에서 일치한 pattern을 반환	[ 작성법 ] REGEXP_SUBSTR (source_char, pattern [, position [, occurrence [, match_param [, subexpr]]]])
		subexpr	서브 표현식을 지정한다 (0은 전체 패턴, 1이상은 서브 표현식, 기본값은 0)
REGEXP_INSTR	source_char에서 일치한 pattern의 시작 위치를 정수로 반환	[ 작성법 ] REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_opt [, match_param [, subexpr]]]]])
		return_opt	반환 옵션을 지정한다 (0은 시작 위치, 1은 다음 위치, 기본값은 0)
REGEXP_COUNT	source_char에서 일치한 pattern의 횟수를 반환	[ 작성법 ] REGEXO_COUNT (source_char, pattern [, position [, match_param]])

 

✨장 요약

---

제1절 서브쿼리

서브 쿼리란 하나의 메인 쿼리 안에 포함된 종속적인 SQL 문장을 말하는 것으로, 위치나 기능에 따라 NESTED SUBQUERY, INLINE VIEW, SCALAR SUBQUERY로 구분할 수 있다.

제2절 집합 연산자

집합 연산자는 결과 집합 간의 연산을 통해 결합하는 방식을 사용한다. UNION 연산자와 UNION ALL 연산자는 개별 SQL 문의 결과에 대해 합집합 연산을, INTERSECT 연산자는 교집합 연산을, EXCEPT/MINUS 연산자는 차집합을 수행한다. UNION ALL 연산자를 제외한 모든 집합 연산자는 최종 결과에서 중복된 행은 제외하고 출력한다.

제3절 그룹 함수

데이터 분석을 위한 GROUP FUNCTION으로는 소그룹 간의 소계를 계산하는 ROLLUP 함수, GROUP BY 항목들간의 다차원적인 소계를 계산할 수 있는 CUBE 함수, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수가 있다.

제4절 윈도우 함수

데이터 분석을 위한 WINDOW FUNCTION은 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수다. WINDOW FUNCTION을 이용한 순위(RANK) 관련 함수는 RANK, DENSE_RANK, ROW_NUMBER가 있으며, 그외 그룹 내 집계(AGGREGATE) 관련 함수, 그룹 내 비율 관련 함수 등이 있다.

제5절 Top N 쿼리

Top-N 쿼리는 말 그대로 상위(Top) N개의 행을 조회하는 쿼리다. Oracle은 ROWNUM 슈도 칼럼, SQL Server는 TOP 절을 사용할 수 있다. Oracle은 12.1버전, SQL Server는 2012 버전부터 ROW LIMITING 절로 Top N 쿼리를 작성할 수 있다.

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

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(HIERARCHICAL QUERY)를 사용한다. 셀프 조인(SELF JOIN)이란 동일 테이블 사이의 조인을 말하며, FROM 절에 동일 테이블이 두 번 이상 나타난다.

제7절 PIVOT 절과 UNPIVOT 절

PIVOT은 회전시킨다는 의미다. PIVOT 절은 행을 열로 회전시키고, UNPIVOT 절은 열을 행으로 회전시킨다. PIVOT절은 CASE 표현식과 GROUP BY 절, UNPIVOT 절은 행 복제를 사용해 수행할 수도 있다.

제8절 정규표현식

정규 표현식(regular expression)은 문자열의 규칙을 표현하는 검색 패턴으로 주로 문자열 검색과 치환에 사용된다. REGEXP_LIKE 조건과 REGEXP_REPLACE 함수, REGEXP_SUBSTR 함수, REGEXP_INSTR 함수, REGEXP_COUNT 함수를 사용할 수 있다.

 

출처 : SQL 전문가 가이드