2장_SQL 활용

SQLD

SQL 활용

서브쿼리

• SQL문 안의 또 다른 SQL문
• GROUP BY 절 제외 모두 사용 가능

스칼라 서브쿼리(SELECT 절)

• 주로 서브쿼리를 하나의 컬럼으로 사용하기 위해

SELECT COL1,
      (SELECT COL2
      	 FROM TABLE2
        WHERE 조건)
  FROM TABLE1;

인라인 뷰(FROM 절)

• 주로 서브쿼리를 테이블처럼 사용하기 위해

SELECT COL1
  FROM (SELECT COL2
          FROM TABLE1)
 WHERE 조건;

WHERE 절 서브쿼리

• 가장 흔한 서브쿼리
• 값의 전달을 목적

SELECT COL1
  FROM TABLE1
 WHERE 조건연산자 (SELECT COL2
                  FROM TABLE2
                 WHERE 조건;)

 

집합 연산자

• 각 쿼리의 결과를 가지고 연산

합집합(UNION/UNION ALL)

• UNION : 중복된 데이터는 한번만 출력, 내부적 정렬 수행
• UNION ALL : 중복된 데이터도 모두 출력

교집합(INTERSECT)

• 두 집합의 교집합 출력

차집합(MINUS)

• 두 집합의 차집합 출력, 집합의 순서 주의

 

그룹함수

• 여러값을 하나의 요약값으로 출력, NULL은 무시하고 연산

집계함수

• CONUT() : 행의 수를 세어줌
• SUM() : 합계를 출력
• AVG() : 평균을 출력, NULL제외 주의
• MIN/MAX : 최대 최소 출력, 날짜 숫자 문자 모두 가능

소계함수

GROUPING SETS(A, B, ...)

• A, B 별 그룹 연산 결과 출력
• NULL or () 사용 총 합 출력 가능

ROLLUP(A, B)

• A별, (A, B)별, 전체 그룹 연산 결과 출력
• 만약, ROLLUP(A, B, C)면 -> (A, B, C) 그룹핑, (A, B) 그룹핑, A 그룹핑, 총 합계

CUBE(A, B)

• A별, B별, (A, B)별, 총합계
• 가능한 모든 그룹핑과 총 합계

 

윈도우 함수

순위 함수

• RANK : 순위를 매기면서 동일 순위가 존재하면 그 수만큼 건너뜀
• DENSE_RANK : 순위를 매기면서 동일 순위가 존재해도 순위를 건너뛰지 않음
• ROW_NUMBER : 순위를 매기면서 같은 값이라도 다른 순위를 부여

집계 함수

• SUM, MAX/MIN, AVG, COUNT

행 순서 함수

• FIRST_VALUE : 파티션 별 가장 앞에 위치한 데이터를 구함
• LAST_VALUE : 파티션 별 가장 뒤에 위치한 데이터를 구함
• LAG : 파티션 별 특정 수만큼 앞선 데이터를 구함, ORDER BY 필수
• LEAD : 파티션 별 특정 수만큼 뒤에 있는 데이터를 구함, ORDER BY 필수

비율함수

• RATIO_TO_REPORT : 파티션 별 합계에서 차지하는 비율을 구함
• PERCENT_RANK : 파티션의 맨위를 0, 맨 아래를 1로 놓고 현재 행의 백분위 순위값을 구함, ORDER BY 필수
• CUME_DIST : 파티션에서 누적 백분율을 구함, ORDER BY 필수
• NTILE : 주어진 수만큼 행들을 N등분 후 현재 행에 해당하는 등급을 구함, ORDER BY 필수

 

TOP N QUERY

• 전체 결과에서 특정 N개 추출
• ROWNUM : 출력 데이터 기준 행 번호 부여
• FETCH : 출력될 행의 수 제한 (ORACLE 12C 이상부터 가능), ORDER BY 뒤에 사용

 

계층형 질의

• 테이블 내에서 계층관계를 가질 때, 그 계층을 표현하는 것
• PRIOR의 위치에 따라 연결 데이터가 달라짐

SELECT T1.*, LEVEL
  FROM TABLE1 T1
 START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT;

 

PIVOT 과 UNPIVOT

• 데이터의 구조를 변경하는 기능
• LONG DATA : 행이 쌓이는 구조
• WIDE DATA : 속성이 쌓이는 구조, 데이터 요약 목적

PIVOT

• LONG -> WIDE

-------<PIVOT 형태>-------
SELECT *
  FROM TABLE1
 PIVOT(VALUECOL1 FOR UNSTACKCOL2 IN (V1, V2, V3));


-------<PIVOT 예시>-------
SELECT *
  FROM (SELECT EMPNO, JOB, DEPTNO FROM EMP)
 PIVOT (COUNT(EMPNO) FOR DEPTNO IN (10, 20, 30));

UNPIVOT

• WIDE -> LONG

-------<UNPIVOT 형태>-------
SELECT *
  FROM TABLE1
UNPIVOT (VALUECOL1 FOR STACKCOL2 IN (V1, V2, ...));


-------<UNPIVOT 예시>-------
SELECT *
  FROM STACK_TEST
UNPIVOT (CNT FOR 년도 IN ("2008", "2009"));

 

 

정규 표현식

• 문자열의 공통된 규칙을 보다 일반화 하여 표현하는 방법
• REGEXP_REPLACE : 정규식 표현을 사용한 문자열 치환 가능
• REGEXP_SUBSTR : 정규식 표현을 사용한 문자열 추출
• REGEXP_INSTR : 주어진 문자열에서 특정 패턴의 시작 위치를 반환
• REGEXP_LIKE : 주어진 문자열에서 특정 패턴을 갖는 경우 반환(WHERE절에 사용)
• REGEXP_COUNT : 주어진 문자열에서 특정 패턴의 횟수를 반환

 

참고

홍쌤의 데이터랩