Part1, 서브쿼리
📍서브쿼리
- 메인 쿼리의 반댓말
- 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말함
- 알려지지 않은 기준을 이용한 검색에 사용
- 반드시 괄호로 묶어서 전달하는 또다른 셀렉트 문
- 사용 가능한 곳
- GROUP BY 절을 제외한 모든 절
- SELECT, FROM, WHERE, HAVING, ORDER BY, 기타 DML(INSERT-VALUES, UPDATE-SET절에 사용가능)
- 단일행 비교 연산자 : =, >, <, <>
- 다중행 비교 연산자 : IN, ALL, ANY, SOME
📍서브쿼리 종류
1. 동작하는 방식에 따라
- UN-CORRELATED : 비연관 서브쿼리
- 서브 쿼리가 메인 쿼리 컬럼을 갖고 있지 않은 형태의 서브쿼리
- 메인쿼리에 서브쿼리가 실행된 결과 값을 제공하기 위한 목적으로 사용
- CORRELATED : 연관 서브쿼리
- 서브쿼리가 메인 쿼리 컬럼을 가지고 있는 형태의 서브쿼리
- 일반적으로 메인 쿼리가 먼저 수행된 후에 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용
2. 위치에 따라
- ① 스칼라 서브쿼리
- 한행, 한 컬럼만을 반환하는 서브쿼리
- SELECT에 사용하는 서브쿼리
- 서브쿼리 결과를 마치 메인쿼리의 하나의 컬럼처럼 사용하기 위해 주로 사용
- ② 인라인 뷰
- FROM 절에 사용하는 서브쿼리
- ORDER BY 사용 가능
- 서브쿼리 결과를 "테이블"처럼 사용하기 위해 주로 사용
뷰 : 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. (가상 테이블이라고도 함)
실행 시점에 sql 재작성 하여 수행됨
===============
뷰 사용 장점
① 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 플그램은 변경하지 않아도 된다.
② 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
③ 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용
- ③ WHERE 절 서브쿼리
- 가장 일반적인 서브쿼리
- 비교상수자리에 값을 전달하기 위한 목적으로 주로 사용 (상수항의 대체)
- 리턴 데이터의 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리, 상호연관 서브쿼리로 구분
📍WHERE 절 서브쿼리 종류
1) 단일행 서브쿼리
- 서브쿼리 결과가 1개의 행이 리턴되는 형태
- 단일행 서브쿼리 연산자 종류
- ex) 전체 직원의 평균급여보다 높은 값을 받는 능력있는 직원을 추출하기
2) 다중행 서브쿼리
- 서브쿼리 결과가 여러개의 행이 리턴되는 형태
- =, >, < 와 같은 비교 연산자 사용불가 (여러 값이랑 동시에 같다? 그것보다 동시에 크다? 비교할 수 없는 연산자들)
- 서브쿼리 결과를 하나로 요약하거나 다중행 서브쿼리 연산자를 사용
❉ ~~ > ALL(200, 300) : 200보다도 커야되고, 300보다도 커야된다~ ➡️ 최댓값인 300보다 커야된다! 라는 뜻
❉ ~~ ><ALL(200, 300) : 200보다도 작아야되고, 300보다도 작아야된다~ ➡️ 최솟값인 200보다 작아야 된다! 라는 뜻
-----------------------------------------------------------------------
❉ ~~ > ANY(200, 300) : 200보다 커도되고, 300보다 커도된다~ ➡️ 최솟값인 200보다 크면된다! 라는 뜻
❉ ~~ < ANY(200, 300) : 200보다 작아도되고, 300보다 작아도된다~ ➡️ 최댓값인 300보다 작으면된다! 라는 뜻
3) 다중컬럼 서브쿼리
- 서브쿼리 결과가 여러 컬럼이 리턴되는 형태
- 메인 쿼리와의 비교 컬럼이 2개 이상
- 단점 : 2개를 묶어서 하다보니까 대소 비교 전달 불가 (두 값을 동시에 묶어 대소비교 할 수 없음)
- ex) 부서별 최대 급여자 확인
4) 상호연관 서브쿼리
- 메인쿼리와 서브쿼리의 비교를 수행하는 형태
- 비교할 집단이나 조건은 서브쿼리에 명시 (메인 쿼리절에는 서브쿼리 컬럼이 정의되지 않았기 때문에 에러 발생)
- ex) 그룹별 평균 급여를 구한 후에 그 그룹의 평균 급여보다 높은 급여를 받는 사람 정보
📍인라인뷰 Inline View
- 쿼리 안의 뷰의 형태로 테이블처럼 FROM절에서 조회할 데이터를 정의하기 위해 사용
- 서브쿼리 결과를 메인쿼리의 어떤 절에서도 모두 사용할 수 있음
- 모든 연산자 사용가능
- ex) 부서별로 최대 급여자를 추출하는데, 최대 급여랑 함께 출력
❉ 여기서 주의할 점, 인라인 뷰에서의 출력결과 MAX(SAL)은 컬럼 별칭 AS MAX_SAL 을 통해 메인 쿼리에 전달!
- ex) 부서별로 해당 부서의 평균급여보다 높은 급여자를 출력하되, 평균 급여와 함께 출력
📍스칼라 서브쿼리
- SELECT절에 사용하는 쿼리로, 마치 하나의 컬럼처럼 표현하기 위해 사용 (단 하나의 출력대상만 표현 가능)
- 각 행마다 스칼라 서브쿼리 결과가 하나여야 함 (=단일행 서브쿼리)
- 조인의 대체연산
- 단일행이어야하므로 (각 직원당 하나의 부서이름이 필요하므로 WHERE 절에 = 식 중요★)
📍서브쿼리 주의사항
- 특별한 경우 (TOP-N 분석 등)을 제외하고는 서브 쿼리절에 ORDER BY 절을 사용 불가
- 단일 행 서브쿼리와 다중행 서브쿼리에 따라 연산자의 선택이 중요
Part2, 집합연산자
📍집합 연산자
두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때
SELECT 절의 컬럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 컬럼의 데이터 타입이 상호호환일 때 사용 가능
- SELECT문 결과를 하나의 집합으로 간주하고, 그 집합에 대한 합집합/교집합/차집합 연산
- SELECT문과 SELECT 문 사이에 집합 연산자 정의
- 두 집합의 컬럼 구성이 동일해야함 (컬럼명은 달라도 되지만, 각 컬럼의 데이터타입과 순서 일치 필요)
- 두 집합의 컬럼 수 일치
- 두 집합의 컬럼 순서 일치
- 두 집합의 컬럼의 데이터 타입 일치 (숫자NUMBER↔️문자VARCHAR2)
- 각 컬럼의 사이즈는 달라도 됨
- 전체 집합의 데이터 타입과 컬럼명은 첫번째 집합에 의해 결정됨
📍합집합 - UNION, UNION ALL
- 두 집합의 총 합(전체) 출력
- 1) UNION : 중복된 데이터는 한번만 출력, 합집합 정렬
- 중복된 데이터를 제거하기 위해 내부적으로 정렬을 수행하기 때문에
- 웬만해서 중복된 데이터가 없을 경우에는 불필요한 정렬을 방지하기 위해 UNION ALL을 쓰길 권장
- 2) UNION ALL : 중복되더라도 ALL 모두 출력하고 싶어
📍교집합 - INTERSECT
- 두 집합의 교집합(공통으로 있는 행) 출력
- 중복 한개로
📍차집합 - MINUS, EXCEPT
- 두 집합의 차집합 (한쪽 집합만 존재하는 행) 출력
- a-b, b-a는 다르므로 집합의 순서 주의
- 중복 한개로
Part3, 그룹함수
📍그룹함수 - COUNT, SUM, AVG, MIN/MAX, VARIANCE/STDDEV
- 숫자 함수 중 여러값을 전달하여 하나의 요약값을 출력하는 다중행 함수
- 수학/통계 함수들 (기술 통계 함수)
- GROUP BY 에 의해 그룹별 연산처리
- NULL은 무시하고 연산된다.
📍COUNT
- 행의 수를 세는 함수
- COUNT(*), COUNT(EMPNO) → NULL값이 없는 컬럼이나 PK를 세면, 행의 전체수를 리턴받을 수 있음
📍AVG
- 그냥 AVG(COMM)하면 null값 제외하고 4명에 대한 평균 리턴
- 합 / 카운트 하면 전체 14명에 대한 평균으로 리턴
- 그러면 AVG(NVL(COMM,0)) 하면 널값을 다 0으로 바꾸고 14명에 대한 평균 받을 수 있음
📍MIN/MAX
- 문자, 숫자, 날짜 모두 가능 (오름차순 순서대로 최소, 최대 출력)
📍GROUP BY FUNCTION ★
- 여러 그룹바이 결과를 동시에 출력*(합집합) 하는 기능
- 그룹바이로 요약된 정보에, 전체 총계를 같이 표현할 수 있는 함수
| 종류 | 설명 | 치환 |
| GROUPING SETS(A,B) | ① A 하나 ② B 하나 |
각각 하나씩 하고 union all 한 결과와 동일 |
| ROLLUP(A,B) | ① DEPTNO를 먼저썻으니까 그거별로 정리된 값 쓰고, ② 그 다음에 그 2개를 묶어서 한번에 그룹바이 해주고 ③ 전체 총계 |
먼저 2개별로 그룹핑, 먼저 나열된 DEPTNO별로 그룹핑, 마지막에 전체 총계한 후에 UNION ALL |
| CUBE(A,B) | ① A단독그룹핑 ② B단독그룹핑 ③ A-B 두개 묶어서 ④ 전체 그룹 연산결과 출력 |
GROUPING SETS(DEPTNO, JOB, (DEPTNO, JOB), ()) ➡️ 나열한 대로 각각 단독, 단독, 집합, 총계 |
1. GROUPING SETS(A,B) = 각각 하나씩 하고 union all 한 결과와 동일
- 나열된 컬럼 마다의 그룹바이 연산을 다 보여줌! (= A별, B별 그룹 연산 결과 출력)
- 둘다보여주니까 나열 순서 중요하지 않음
- 기본출력에 전체 총계는 출력되지 않음
+) 전체 결과의 총계 보고 싶을 때 : GROUPING SETS(DEPTNO, JOB, () );
2. ROLLUP(A,B) = 먼저 2개별로 그룹핑, 먼저 나열된 DEPTNO별로 그룹핑, 마지막에 전체 총계한 후에 UNION ALL
- 나열 순서가 중요함
- 첫번째 나열된걸 먼저 단독 그룹핑으로 묶어주고, 그 다음에 그 두개를 묶어서 그룹바이 해줌
- 그룹핑 값이 null이면 1로 출력됨 (기본값) → CASE WHEN으로 1이면, '월별합계'라고 써라 값 지정 가능
- 기본적으로 전체 총계가 출력됨
-
Subtotal을 생성하기 위해 사용
3. CUBE(A,B) = GROUPING SETS, UNION ALL 로 표현 가능
- A단독그룹핑, B단독그룹핑, A-B 두개 묶어서 전체 그룹 연산결과 출력
- 전체 총계 출력됨
-
결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하 심함. 2^N
- 순서 상관없음
+) GROUPING SETS(DEPTNO, JOB, (DEPTNO, JOB), ()) ➡️ 나열한 대로 각각 단독, 단독, 집합, 총계
Part4, 윈도우 함수
📍WINDOW FUNCTION
- 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수
- 원칙적으로는 하나의 행을 읽으면 다른 행과 비교가 어렵지만,, 그걸 가능하게 해주는게 window function
- OVER 절을 사용해서 윈도우 함수로 사용 가능
- GROUP BY 를 쓰지 않고 전체 데이터를 출력하면서 그룹 연산 가능
- LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK
- 윈도우함수 뒤에 내가 sum하고 싶은 대상을 쓰고,
- PARTITION BY : 그룹연산할 group by 컬럼 (반드시 연산할 대상을 그룹함수의 입력값으로 전달)
- ORDER BY 어떤 순서로 (rank, 누적합 같은 경우-sum, avg, min, max, count 등)
- ROWS : 연산범위 설정 - 누적합 구할 때 범위 설정할 수 있으니까
- 범위 설정할 때 order by 필수
1. SUM OVER() : 전체 총합, 그룹별 총합 출력 가능
- ex) 각 직원정보와 함께 급여 총합 출력
📍WINDOW 함수의 연산범위 : 집계연산시 행의 범위 설정 가능 ★
- ROWS, RANGE 중 하나 선택 (디폴트는 range)
- ROWS : 값이 같더라도 한 행씩 연산 ➡️ 대신, rows를 쓰면 반드시 뒤에 범위를 적어줘야 함 (어디부터 어디까지 연산)
- RANGE (디폴트) : 정렬을 하고, 순서대로 누적합을 계산하는데 앞뒤 같은 값이 나오면 하나로 묶어서 동시 연산
- BETWEEN A AND B
- A 시작점 정의
- CURRENT ROW : 현재행부터
- UNBOUNDED PRECENDING : 처음부터 (디폴트값)
- N PRECENDING : N 이전부터
- B 마지막 시점 정의
- CURRENT ROW : 현재행까지 (디폴트값-처음부터 현재행까지 다 더해!)
- UNBOUNDED FOLLOWING : 마지막까지
- N FOLLOWING : 5개 이후 라인 까지!
- A 시작점 정의
❉ 디폴트 : 처음부터 현재행까지 차례대로 누적합!, range → 값이 같은건 하나로 생각하고 누적합에서 한번에 두개 다 더함
❉ ROWS 범위 설정시 BETWEEN ➡️ 값이 같더라도 한번에 계산안하고 하나씩 누적합
❉ UNBOUNDED PRECEDING AND CURRENT ROW : 처음부터 현재행까지
❉ UNBOUNDED PRECEDING AND 1 FOLLOWING : 누적합 계산시 처음부터 그 다음행까지 연산
📍순위 관련 함수
| 순위 관련 함수 | 개념 | 예시 |
| RANK | 동일한 값에 대해서는 동일한 순위를 부여 | 1,2,2,4,5 |
| DENSE_RANK | 동일한 순위를 하나의 등수로 간주 | 1,2,2,3,4,5 |
| ROW_NUMBER | 동일한 값이라도 고유한 순위 부여 | 1,2,3,4,5 |
- 1) RANK (순위)
- ① RANK WITHIN GROUP : 특정 값에 대한 순위 하나만 확인하고 싶을 때 (윈도우함수 아니고 일반함수)
❉ 급여가 3000정도면 전체 순위가 얼마일까? :
이런식으로 하나만 궁금할때 RANK(3000) WITHIN GROUP (ORDER BY SAL) → 으음~ 2등정도 군~!
- ② RANK() OVER() : 전체 중, 특정 그룹 내에 값의 순위 확인, 순위 구할 대상을 order by절에 여러개 나열 가능, 그룹 내 순서 구할 시 PARTITION BY 절 사용
❉ 각 직원의 급여의 전체 순위 (큰 순서대로) : 전체니까 굳이 partition by 사용 x
동순위 나오면, 2등으로 똑같이 처리하고, 그 다음순위는 3등 아니고 4등으로!!
❉ 각 직원의 급여의 부서별 순위 (큰 순서대로) : 조직별로 순위가 궁금한거니까 partition by 사용 !
- ③ DENSE_RANK : 누적 순위, 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
ex) 1등이 5명이더라도 그 다음 순위가 2등 - ④ ROW_NUMBER : 그냥 일련의 연속된 행 번호, 동일한 순위를 인정하지 않고 단순히 순서대로 나열한 대로의 순서값 리턴
❉ RANK() OVER(), DENSE_RANK(), ROW_NUMBER() 비교 시험 출제 ↑ ★
RANK() OVER() : 동순위 개수만큼 이후 숫자 +됨, 2등 2명 다음은 4등
DENSE_RANK() : 2등 다음은 그냥 3등
ROW_NUMBER() : 얜 그냥 1,2,3,4,5,6,7,8,9,~
📍LAG(파티션별 윈도우에서 이전 몇번째 행의 값), LEAD(이후 몇번째 행의 값)
- 행 순서대로 각각 이전 값(LAG), 이후 값 (LEAD) 가져오기
- ORDER BY 절 필수
- LAG(가져올 컬럼, 몇번째 값을 가져올까
- sql server 지원 X
❉ 바로 이전 입사자의 급여 ➡️ 우선 고용된 날짜순으로 나열하고 나서, 이전꺼 SAL을 가져와라
❉ 이전/이후값 가져올때 값이 같더라도 항상 행의 순서대로 이전,이후 하나를 가져오기 때문에
ORDER BY 기준을 2개로 두면서 충분히 전달하고 값을 가져오면 됨
📍FIRST_VALUE(파티션별 윈도우의 처음 값), LAST_VALUE(파티션별 윈도우의 마지막 값)
- 정렬 순서대로 정해진 범위에서의 첫번째값, 마지막 값 출력
- 순서, 정렬, 범위 정의 등에 따라 최솟값과 최댓값 리턴 가능해짐
- FIRST_VALUE = 1300첫행과 2450 중에 첫번째 값, → 1300 이런식으로 첫행과 현재행까지의 최솟값/최댓값 구할 수 있음
- LAST_VALUE = 처음부터 본인행까지 가장 큰 마지막 값 5000, 1300이랑 2450하면 2450.
- LAST VALUE에서 내림차순으로 DESC 처음부터 마지막까지 정렬시 최솟값 리턴
📍NTILE (파티션별 전체건수를 인수값으로 N등분한 결과)
- 행을 특정 컬럼 순서에 따라서 정해진 수[NTILE(N)]의 그룹으로 나누기 위한 함수
- 그룹 번호가 리턴됨
- ❉ EMP라는 테이블을 2개로 나누겠다. SAL 순서대로, 14명이니까 7명씩 분리
📍비율 관련 함수
- 1) RATIO_TO_REPORT >0, <=1
- 파티션내 전체 sum에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다
- 각 값의 비율을 리턴 (전체 중의 비율 또는 특정 그룹 내 비율)
- ORDER BY 사용 불가! (그 값 하나에 대한 비율이니까, 굳이?)
- 2) CUME_DIST : 누적비율 >0, <=1
- 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.
- 각 값의 누적비율을 리턴 (전체 중의 비율 또는 특정 그룹 내 비율)
- 누적비율은 항상 ORDER BY가 붙음! 필수적으로 (누적비율을 구하는 순서를 정할 수 있음)
- 3) PERCENT_RANK : 분위수 출력 0>=, <=1
- 전체 카운트 중 상대적 위치 출력 (0~1 범위 내)
- 파티션 별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구한다.
- ORDER BY 필수
❉ RATIO_TO_REPORT, CUM E_DIST 비교
- 부서내에서 각 값의 비율
- 누적합
- SAL 만 오더바이 기준으로 잡을때는 3000값이 같아보이니까 두개 다 1로 잡은 건데,
- 결국 오더바이에 SAL, ENAME 두개 다 기준으로 잡을때는 각각 값이 달라지니까 서로 다른 값으로 해석됨 → 각각 누적값 구함
❉PERCENT_RANK 예제 : 최솟값을 0으로 최댓값을 1로 만들음
Part5, TOP N QUERY
📍TOP N QUERY
- 페이징 처리를 효과적으로 수행하기 위해 사용
- 한페이지를 눌렀을 때 특정 N개의 행을 추출한 목적으로 사용하는 기법
- 성적 상위자 3명
- TOP-N 행 추출 방법 : 1️⃣ ROWNUM, 2️⃣ RANK, 3️⃣ FETCH
1️⃣ ROWNUM
- 출력된 데이터 기준으로 행 번호 부여
- 절대적인 행 번호가 아닌 계속 출력된 결과에 따라 바뀌는 행번호
- 또다른 순서가 그때그때 반영되니까 이퀄로 특정행을 지칭하기 어려움
- '>' 연산 사용불가
- ex) EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력
- 아래 쿼리에서는 ROWNUM 이 있는 WHERE절 먼저 → 그 다음 ORDER BY가 나오기 때문에 그냥 아무 5개나 리턴 된거임
- 먼저 정렬을 싸악~하고 그 서브쿼리를 가지고 와서 5개만 추출 (rownum이 결정되기 전, 먼저 데이터 정렬 순서를 바꿔놓음)
- ex) EMP 테이블에서 급여가 높은 순서대로 4~6번째 해당하는 직원 정보 출력
- 아래 쿼리에서는 시작값이 정의가 안되었고, 1아니고 4먼저 추출하려고 해서 출력 불가능
- ① 인라인 뷰에서 각 행마다 행번호를 직접 부여
- 일단은 순서대로 나열하고, 거기에 RONUM을 1부터 쪼ㅑ르륵 부여하고 그 다음에 4~6번째만 추출
2️⃣ RANK
- ② 윈도우함수의 RANK 사용
- RANK를 사용해서 1부터 쪼ㅑ르르륵 순위를 부여하고, 그 다음에 4등~6등 불러오기
3️⃣ FETCH절
- 출력될 행의 수를 제한하는 줄
- ORACLE 12C 이상부터 제공, sql-Server 사용 가능
- ORDER BY 절 뒤에 사용 (내부 파싱 순서도 ORDER BY 뒤)
- OFFSET : 건너뛸 행의 수 → 1명, 1등은 스킵하고 나머지 3명만 리턴하고 싶을 때
- N : 출력할 행의 수
- FETCH : 결과적으로 추출하려고 하는 출력할 행의 수를 전달하는 구문
- FIRST : OFFSET을 쓰지 않았을 때 처음부터 N행 출력 명령
- NEXT : OFFSET을 사용했으면, 그 제외한 행 다음부터 N행 추출
- ROW | ROWS : 행의 수에 따라, 하나일 경우 단수, 여러값이면 복수 (특별히 구분 X)
- ex) EMP 테이블에서 SAL 순서대로 상위 5명만 리턴해보자!
- SAL 큰 순서대로 오더바이 하고, fetch로 5개만 추출해보기
- ex) EMP 테이블에서 급여가 높은 순서대로 4~6번째 해당하는 직원 정보 출력
- 1,2,3, → 3개를 건너 뛰고 그 다음에 있는 2줄 4등과 5등을 출력해줘라
Part6, 계층형 질의
📍계층형 질의
- 테이블의 여러개 각 행끼리 관계를 가질 때, 그 연결고리를 파악을 하고 행과 행 사이의 계층 depth를 표현하는 기법
- DEPT2에서의 부서별 상하관계
- PRIOR의 위치에 따라 연결하는 데이터가 달라짐
- START WITH : 데이터를 출력할 시작점을 지정하는 조건, 계층 구조 전개의 시작 위치 지정
- CONNECT BY : 행을 이어나갈 조건, 다음에 전개될 자식 데이터 지정
- PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정한다.
- [PRIOR 자식 = 부모 형태] 를 사용하면 계층 구조에서 부모➡️자식 데이터 방향으로 전개하는 순방향 전개
- ex) 각 부서의 레벨을 출력, 최상위 부서(사장실)가 1레벨
- 사장실은 가장 상위부서여서 PDEPT(상위부서번호 컬럼)이 NULL값임 → 이걸 활용해서 PDEPT가 NULL인 사장실 행부터 시작~!
- PRIOR는 먼저 결정되는 정보 앞, DCODE가 먼저 결정되고 PDEPT 결정되니까. (DECODE가 먼저다! 라는걸 알려줌)
- 근데? DCODE = PRIOR PEDEPT 이런식으로 잘못 적으면 사장실이 보이긴 하지만, 반대방향으로 가서, 사장님 위에는 뭐가 없으니까 아무것도 출력되지 않는 것
- 연결조건 START WITH, CONNECT BY 뒤에는 더 많은 추가 조건이 붙을 수 있다!!!!
- 여기서의 조건은 WHERE절의 조건이 아니라 부모행과 자식행을 연결하기 위한, 잇기 위한 조건임!!!! [AREA = '서울지사']
- 사장실 하위지사는 서울, 포항지사로 여러 가지가 있었는데, 그걸 이을때 서울만 잇겠다는 조건
- 부서의 상하관계 반영해서 레벨의 수만큼 들여쓰기
- 가장 최상위 = 뿌리, 루트 노드 // 단말, 끝나는 지점들 = 리프노드
- 영업 1팀 기준으로 상위 부서들,
- 영업 1팀의 상위부서는, 상위부서코드 PDEPT가 1007인~
- PRIOR이 PDEPT 앞에 붙음
📍계층형 질의 가상 칼럼
- 1) LEVEL : 각각의 행의 depth 를 표현하는 (시작점부터 1)
- 2) CONNECT_BY_ISLEAF : 해당 데이터가 리프데이터(최하위 노드)면 1, 그렇지 않으면 0
📍계층형 질의 가상 함수
- 1) CONNECT_BY_ROOT 컬럼명 : 이 컬럼에 대한 루트노드 값이 뭐냐, 각각의 행의 depth 를 표현하는 (시작점부터 1)
- 현재 전개할 데이터의 루트 데이터를 표현한다, 단항 연산자
- CONNECT_BY_ROOT DNAME : 루트노드의 dname 값이 뭐야? 이쪽 뿌리는 자연과학부가 조상이야, 이쪽 뿌리는 컴공이 조상이야.. 알려줌
- 2) SYS_CONNECT_BY_PATH(컬럼, 구분자) : 연결고리를 이어서 경로 출력하고 싶어짐, 이어질 때 구분자 표시
- SYS_CONNECT_BY_PATH(DNAME, '-') : 연결되는 dname의 관계를 하이퍼로 표현해라,
- 자연 과학부는 레벨 1이니까 -자연과학부, -자연과학부-수학과, 자연과학부-통계학과 • • •
- 3) ORDER SIBLINGS BY 컬럼 : 같은 LEVEL 일 경우 정렬 수행, 형제 노드간의 정렬 수행
- ODRDER SIBLINGS BY DNAME : 만약에 둘다 레벨이 1로 같으면, 뭘 먼저 나타내고 싶다!
Part6, PIVOT 과 UNPIVOT (데이터의 구조를 변경하는 기능)
📍데이터의 구조
1) LONG DATA (Tidy data)
- 아래로 추가, 길~게 생겼다, 깔끔하게 생겼다
- 속성별로 하나의 컬럼으로 구성되는, 값들이 여러 행으로 쌓이는 구조
- 제 1 정규화를 거치면 그 다른 값을을 가진 하나의 JOB이라는 컬럼을 만들게 됨
- 다른 테이블과의 조인연산이 가능한 구조
- RDBMS의 테이블 설계 방식
2) WIDE DATA (Cross data)
- 옆으로 펼쳤다~
- 행과 컬럼에 유의미한 정보 전달을 목적으로 작성하는 교차표
- 하나의 속성값이 컬럼을 옆으로 펼쳐 여러 컬럼으로 분리되어 표현
- RDBMS 에서 WIDE 형식으로 테이블 설계 시 값이 추가될 때마다 컬럼이 추가돼야 하므로 비효율적! (구조변경 잦)
- 다른 테이블과 조인연산이 불가능함
- 주로 데이터를 요약할 목적으로 사용
📍데이터의 구조 변경
1) PIVOT : LONG ➡️WIDE
- 부서번호 형태를 오른쪽으로 펼쳤다.
2) UNPIVOT : WIDE➡️LONG
- 옆으로 펼쳐져 있던 월 정보를 중복되더라도 쌓아서, 구매건수라는 새로운 칼럼명 추가해서 값 추가
📍PIVOT
- 교차표를 만드는 기능 (롱 데이터를 와이드 하게 옆으로 펼친다.)
- 구매건수라는 데이터를 옆으로 펼치니까
month 로 고정할컬럼(stack 컬럼), 옆으로 펼칠 컬럼(unstack 컬럼), 안에 채울 값!(value 컬럼) - FROM 절에 STACK, UNSTACK, VALUE 컬럼명만 정의, 필요시 서브쿼리 사용해서 필요컬럼 제한★
- FROM 절에 선언된 컬럼 중 피봇 절에서 선언한 value컬럼, unstack 컬럼을 제외한 모든 컬럼은 stack 컬럼이 됨
- PIVOT 절에 VALUE, UNSTACK 컬럼명 정의
- PIVOT 절 IN 연산자에 UNSTACK 컬럼 값을 정의
- 테이블에서 아래와 같이 JOB별 DEPTNO 별 도수(count) 출력
- FROM (PIVOT 절에서 언급한 deptno, empno, job 이 3개 컬럼 외에는 프롬절에 있으면 안됨!)
- PIVOT (어떤 컬럼을 카운트 해서 값으로 채워넣겠다, FOR 가로줄에 분류할 언스택컬럼명 IN (넣고싶은 컬럼명 10,20,30)
- 만약에 FROM절에 3개 다 안넣고, JOB을 제외한 2개만 넣는다면,
직업별로 분리하지 못하고 한줄로 표현하게 됨
- 만약에 FROM 절에 서브쿼리로 3개로 제한하지 않고, 모든 테이블을 한번에 다 넣는다면,
수없이 많은 컬럼들이 다 스택컬럼이 되고, 더 구체적인 도수가 나옴
- 이미 한번 만들어진 와이드 데이터를 한번 더 피봇하기
- 성별로 고정(stack 컬럼), 연도컬럼(unstack 컬럼), 구매량의 총합!(value 컬럼)
- PIVOT (구매량 총합 FOR 연도 IN (2008, 2009))
➡️ 성별은 따로 지정안하고, 그냥 프롬절에만 포함해서 잘 쓰면, 알아서 스택으로 고정됨
📍UNPIVOT
- WIDE 데이터를 롱 데이터로 변경하는 문법
- UNPIVOT 때는 굳이 프롬절을 제한할 필요는 없음 !
- STACK 컬럼 : 이미 펼쳐져 있는 여러 컬럼을 하나의 컬럼으로 새롭게 만들 이름, 컬럼명
- VALUE : 값이 채울 때 새롭게 만들 컬럼명
- 값1, 값2 : 실제 unstack 되어있는 컬럼이름들, 값1, 값2 자리에 명시해서 쌓겠다.
- 컬럼명으로서의 "숫자"는 쌍따옴표로 전달필요하고
- 문자는 컬럼명은 대문자라서 따옴표 붙으면 안됨
Part8, 정규 표현식
📍정규 표현식
- 문자열의 공통된 규칙을 보다 일반화 하여 표현하는 방법
- 정규표현식 사용가능한 문자 함수 제공 (regexp_replace, regexp_substr, regexp_instr,,,)
- ex) 숫자를 포함하는, 숫자로 시작하는 4자리.., 두번째 자리가 A인 5글자
- 데이터 포맷과 관련한 것
- 소문자 d : Digit 지칭 → 숫자를 표현하는 방법, 숫자 하나
- 대문자 D : 반대집합, 숫자가 아닌 것
- s : 공백, S : 공백이 아닌 것
- w : 숫자, 문자(영어, 한글), _ W : 단어가 아닌 것 (언더바를 제외한 대부분의 특수 기호)
- ^ 캐럿, 시작되는 것
- ^a : a로 시작하는 것
- 반드시 맨앞에 있어야 함
- $ : 달러는 맨뒤에 적어야 함
- a$ : a 로 끝나는
- • : 엔터를 제외한 모든
- .+ : 여러개가 나열된다.는 뜻
- 원 기호 \ : 지금 규칙으로 나와ㅇ있는걸 뒤에 있는 기호의 의미를 제거하는 것
- [대괄호] : 대괄호 하나당 한 글자를 의미
- [ab] : 한글자인데, a 또는 b
- [^ab] : 뒤에있는걸 제외하겠다. a와 b를 제외한 모든 문자
- [A-Z] : -은 범위를 의미 → 대문자
- [A-z] : 대소문자 구분하지 않고 모든 영문자
- [가-힣] : 한글 표현
- 반복횟수
- a+ : a가 여러번 반복된다.
- a* : 있거나 없거나, 0회 이상 반복
- a? : 있거나 없거나, 0회에서 1회 반복
- a{n} : n으로 횟수 지정 가능, a 가 n 회 반복됨
- a{n1, n2} : a가 n1 반복에서 n2반복 까지 허용
- a{n,} : 끝이 지정안되고 , 로 끝난 경우는 계~속 반복된다.는 뜻. n회 이상 반복
- 0 그룹지정
- \1 : 그룹번호, 1번 그룹에 대한 표현
tel) 은 무조건 들어가고, 숫자는 다 바뀔 수 있음 한글자에 [0-9] 0에서 9까지 숫자나 하이픈을 넣을 수 있다는 의미에서 [0-9-]
+ 는 위글자가 반복된다
) 이게 있고 없고 일 때는 \) 괄호가 있거나 없거나.
📍1. REGEXP_REPLACE
- 정규식 표현을 사용한 문자열 치환 가능
- 대상, 찾을 문자열, [바꿀문자열], [검색위치], [발견횟수], [옵션]
- 대상에서 찾을 문자열을 찾아서 바꿀 문자열을 바꿔라, 바꿀문자열이 생략되면 그냥 삭제처리할수도
- 검색위치 : 어디서부터 검색할거냐, 생략시 1로 처리됨 (처음부터 검색하겠다는 뜻)
- 발견횟수 : 몇번째 발견되는걸 치환할거냐, 생략시 0으로 처리 (모든것들을 다 치환하거나 삭제해줌)
- 옵션 :
- c : 대소를 구분해서 검색할거냐
- i : 대소를 구분하지 않고 검색할거냐
- m : 패턴을 다중라인으로 선언 가능 (허용할거냐)
- '\d' 숫자 한 자 → ' '지워버려라. 발견횟수 생략했으니까 모든 숫자를 삭제해라
- [[:digit:]] 역시 숫자를 의미
- '\w' : 언더바 제외한 모든 특수기호를 '' 로 replace해라. 즉, 모든 ID에 있는 모든 특수기호 삭제해라
- 언더바까지 포함한 특수기호를 삭제하고 싶다면 \w|_ ("또는 언더바" 라는 뜻)
- [[:punct:]] 언더바를 포함한 특수기호 지칭 - 구둣점 문자 [][!#$%&’()*+,./:;<=>?@\^_`{|}~-]
- 대괄호 하나가 한글자니까 [a-z][0-9]이건 두글자. 영어소문자+숫자로 연이은 단어를 지워라.
- 이와중에 kong-12는 하이픈이 끼어있어서 삭제가 안됨
- [A-z0-9] 이거는 한글자, 영문이거나 숫자면 다 지워버려 → 하이픈 빼고 다 삭제됨, 이래서 한글자를 주의해야함
- 삭제할건데, 처음부터 스캔해서 0 모든걸 삭제해라. i는 대소를 구분하지 않고
- [a-zA-Z][0-9] = [a-z][0-9], '', 1, 0, 'i'
- 영문자 한글자, 하이픈, 숫자 이걸 한번에 지우고싶다면?
- [A-z]-[0-9]
- 근데 언더바도 같이 OR 기준으로 주고싶다면?, 두가지 조건 넣을 때 소괄호 안에
- [A-z](-|_)[0-9]
- 괄호 안에 있는 그람, 밀리리터, 무게 데이터 한번에 지우고싶다면? → 근데 안에 들어가는 내용이 다양해.
- . 쩜은 엔터를 제외한 모든 무엇이 와도 상관없음
- '\(.+\)' : 플러스를 붙여서 1회이상으로 어떤 글자든 몇글자 이상으로 괄호안에 있는 글자를 삭제하자
- 이름 마스킹 처리할때, 이름 세글자에서 두번째에서 발견된 곳에 마스킹
- '[[:alpha:]], '' ➡️ 처음부터 발견되면 다 지워버림
- '[[:alpha:]], '' , 1, 1 ➡️ 첫번째 발견된거 하나를 지우겠다.
- 대상, 찾을 문자열, [바꿀문자열], [검색위치], [발견횟수], [옵션]
- '[[:alpha:]], 'x' , 1, 2 ➡️ 처음부터 검색하되, 두번째 발견된 곳에 x로 replace 하겠다.
📍2. REGEXP_SUBSTR
- 정규식 표현을 사용한 문자열 추출
- 추출은 전체 패턴은 정규표현식으로 나타내고, 내가 뽑고싶은 추출그룹을 지정해주면 됨!
- REGEXP_STR(대상, 패턴, [검색위치], [발견횟수], [옵션], [추출그룹])
- 검색위치 생략하면 1
- 몇번째 발견될걸 추출해줄까, 생략되면 1
- 추출그룹은 서브패턴을 추출 시 그 중 추출할 서브패턴 번호
- 지역번호가 다 다르고, 숫자들도 자릿수가 다 다를때 어떻게 지역번호만 추출해올 수 있을까?
- 우선 d 숫자, 괄호는 두가지 의미가있는데, 진짜 문자열 '('이랑 수식상 묶기위해 필요한 괄호(이건 그냥 괄호 사용)일수도 있음
- 진짜 문자열 괄호에는 괄호에 \ 붙임 \)
- \d 숫자+ 여러개반복에 오른쪽 괄호 추가하려면 \) 가 붙음
- 괄호를 통해서 그룹이 3개가 되는데
- 자릿수를 채우기 위해 옵션값 null로 채워서
- 이 TEL이라는 대상에서 다음과 같은 조건의 패턴을 찾아라, 처음부터 검색하고, 하나의 발견된 순간, 옵션은 null, 이고 추출그룹은 첫번째 그룹을 뽑아라!! ➡️ 괄호 이전에 지역번호가 추출됨
- 이메일에서 아이디만 추출하기
- 이메일 아이디에는 영문자, 숫자, 하이픈과 같은 특수기호 모두 사용가능 ➡️ .+로 처리
- 그다음은 @ + 영문자 + .영문자(com, net과 같은)
- 뽑고싶은 아이디"쪽만 괄호로 묶어주고, 뒤에는 그냥 아무거나 와라싶은 마음으로 @ 다음 .+ 처리
- 처음부터 검색해서, 첫번째로 발견되는 이메일 주소만 가져와, 옵션은 null, 첫번째 서브패턴(그룹)을 가지고 와라!
📍3. REGEXP_INSTR
- 정규식 표현을 사용한 문자열의 위치를 추출 (특정패턴의 시작위치를 반환)
- INSTR은 옵션전달 불가
- REGEXP_INSTR(원본, 찾을문자열, 시작위치, 발견횟수)
- 시작위치 생략시 1, 처음부터 값 확인
- 발견횟수 생략시 처음 발견된 문자열 위치 리턴
- ID에서 숫자하나 한글자만 가져와 '\d', 시작위치는 처음부터 스캔하고, 두번째 발견된! 숫자의 자리의 위치
- \d+ : 플러스가 붙으면 한글자가 아니라 한 뭉치, 두번째 뭉치를 도출해야함
- 숫자, 문자열, 공백도 있는 경우
- [^ ]+ : ^는 제외한, 그 뒤에는 공백이 있으니까 공백을 제외한 것들의 반복은, 공백을 제거한 숫자든 문자든 어떤 단어!
- 처음부터 검색하되, 두번째 단어를 가져와! → ORACLE 인데
- 그게 몇번째 글자에서 시작되냐. 5번째!
📍4. REGEXP_LIKE
- 주어진 문자열에서 특정 패턴을 갖는 경우 반환 (WHERE절에서만 사용 가능)
- REGEXP_LIKE(원본, 찾을 문자열, [옵션])
- ID 에서 숫자로 끝나는 교수명만 추출해랏!
📍5. REGEXP_COUNT
- 주어진 문자열에서 특정 패턴의 횟수를 반환
- REGEXP_COUNT(원본, 찾을문자열, 시작위치, [옵션])
- 한글자씩, +를 붙이면 숫자덩어리 ➡️ 각각 카운트값이 다름!
'📝 자격증 > SQLD : SQL 개발자' 카테고리의 다른 글
| [SQLD] 자격증 합격 🎉 (0) | 2024.07.16 |
|---|---|
| [2과목] SQL 관리구문 - DML, TCL, DDL, DCL (0) | 2024.05.24 |
| [2과목] SQL 기본 - 관계형 DB 개요, SELECT 문, 함수, WHERE절, GROUP BY/HAVING절, ORDER BY 절, 조인, 표준 조인 (0) | 2024.05.17 |
| [1과목] 데이터 모델과 SQL - 정규화, 관계와 조인의 이해, 모델이 표현하는 트랜잭션의 이해, Null 속성의 이해, 본질 식별자 vs 인조 식별자 (2) | 2024.05.15 |
| [1과목] 데이터 모델링의 이해 - 데이터 모델의 이해, 엔터티, 속성, 관계, 식별자 (0) | 2024.05.11 |
