[SQLD 2-03] ★COUNT OVER★PRECEDING, FOLLOWING★TOP★ROW LIMITING★START WITH★CONNECT BY★정규표현

1. WINDOWS 함수  COUNT OVER

SELECT EMPNO , ENAME , SAL , COUNT(*) OVER (
    ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 100 FOLLOWING) AS C1 FROM EMP
WHERE DEPTNO = 20

COUNT OVER

==> 각 SAL 값의 범위를 50뺀값에서 100 더한 값을 범위로 지정한다.

 

==> 750~850

==> 1050 ~ 1200

==> 2925 ~ 3075

==> 2950 ~ 3100

 

2. SELECT 문의 처리순서

 

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

 

3. TOP 절

 

SELECT TOP (4) WITH TIES * FROM EMP ORDER BY SAL DESC;

==> WITH TIES

 

SELECT * FROM (SELECT EMPNO , ENAME , SAL , RANK() OVER (ORDER BY SAL DESC) AS RN FROM EMP)
WHERE RN <=4;

RANK

3. ROW LIMITING 절

1> ROW_NUMBER 

SELECT * FROM
             (SELECT EMPNO, ENAME , SAL , ROW_NUMBER() over (ORDER BY SAL DESC) AS RN FROM EMP)
WHERE RN <=3

ROW_NUMBER

2> OFFSET

SELECT EMPNO, ENAME, SAL FROM EMP
ORDER BY SAL DESC OFFSET 3 ROWS;

==> 3번째 행 제외하고 출력

 

3> FETCH

 

==> 반환할 행의 개수나 백분율 지정

 

SELECT EMPNO, ENAME, SAL FROM EMP
ORDER BY SAL DESC FETCH FIRST 3 ROWS ONLY ;

==> 위의 3개의 행 반환

 

4> WITH TIES

SELECT EMPNO, ENAME, SAL FROM EMP
ORDER BY SAL DESC FETCH NEXT 10 ROWS WITH TIES;

==> 상위 10개 행 반환

 

4. SELF JOIN

SELECT A.ENAME , B.EMPNO , B.ENAME FROM EMP A,  EMP B
WHERE A.ENAME = 'JONES'

SELF JOIN

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

SELF_JOIN

==> JONES 의 EMPNO와 MGR이 같은 것들 출력

 

5. 계층쿼리

SELECT EMPNO, ENAME,  MGR FROM EMP
START WITH ENAME = 'JONES'
CONNECT BY MGR = PRIOR EMPNO

START WITH 절 : 루트 노드를 생성하며 1번만 수행

 

CONNECT BY 절 : 루트 노드의 하위 노드를 생성하고 결과가 없을 때까지 반복 수행

START WITH , CONNECT BY

==> 이전의 쿼리의 EMPNO와 현재 MGR이 동일한거 출력하기

 

==> ENAME이 JONES인거를 루트 노드로 지정한다.

 

6. PIVOT TABLE , UNPIVOT

CREATE TABLE EMP (
  JOB varchar2(20) NOT NULL,
  DEPTNO VARCHAR2(10),
  SAL VARCHAR2(9));

INSERT INTO EMP (JOB, DEPTNO, SAL)
SELECT 'ANALYST', 10, 3000 FROM DUAL UNION ALL
SELECT 'CLERK', 10, 1000 FROM DUAL UNION ALL
SELECT 'CLERK', 20, 2000 FROM DUAL UNION ALL
SELECT 'CLERK', 20, 1500 FROM DUAL UNION ALL
SELECT 'ANALYST', 10, 4000 FROM DUAL UNION ALL
SELECT 'CLERK', 20, 1200 FROM DUAL UNION ALL
SELECT 'CLERK', 10, 800 FROM DUAL;

==> UNION ALL 활용하여 테이블 추가

EMP TABLE

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

PIVOT TABLE

7. 정규표현

SELECT REGEXP_SUBSTR('ABC' , 'A.+') AS C1,
       REGEXP_SUBSTR('ABC' , 'A.+?') AS C2
FROM DUAL

정규표현

마침표(.) 는 모든 문자

더하기(+)는  1회 또는 그 이상의 횟수

물음표(?)는 끝내기

 

SELECT REGEXP_SUBSTR('ABD' , 'AB|CD') AS C1,
       REGEXP_SUBSTR('ABD' , 'A(B|C)D') AS C2
FROM DUAL

정규표현식

AB or CD

A(B or C) D

 

2> 문자 리스트

 

SELECT REGEXP_SUBSTR('HTTP://WWW.ABC.COM/EFG' , '[^:/]+',1,2) AS C1 FROM DUAL

[char] : 문자 리스트 중 한 문자와 일치

[^char] : 문자 리스트에 포함되지 않은 한 문자와 일치

 

[^:/]+ : ':' 와 '/' 를 포함하지 않은 2 번째 문자 값을 반환한다.

 

SELECT REGEXP_REPLACE('1A2B3C4D' , '\D') AS C1 FROM DUAL

REPLACE

'\D' ==> 숫자가 아닌 패턴 ==> 숫자만 출력한다