hyerimir_archive

테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있음

- LEVEL : 현재 DEPTH를 반환한다, 루트 노드는 1이 된다

- SYS_CONNECT_BY_PATH(컬럼, 구분자) : 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수

- START WITH : 경로가 시작되는 루트 노드를 생성해주는 절

- CONNECT BY : 루트로부터 자식 노드를 생성해주는 절, 조건에 만족하는 데이터가 없을 때까지 노드를 생성

- PRIOR : 바로 앞에 있는 부모 노드의 값을 반환

- CONNECT_BY_ROOT 컬럼 : 루트 노드의 주어진 컬럼 값을 반환

- CONNECT_BY_ISLEAF : 가장 하위 노드인 경우 1을 반환하고 그 외에는 0을 반환

ORDER SIBLINGS BY 절을 사용하면 같은 레벨들끼리 정렬을 수행하기 때문에 계층 구조를 유지할 수 있음

- ROWNUM : 슈도 컬럼, 실제로 존재하지 않는 가짜 컬럼

건너뛰기 조건은 성립될 수 없어서 ROWNUM은 항상 < 조건이나 <= 조건으로 사용해야 한다

Top-N 쿼리를 작성할 때 ORDER BY 절이 WHERE 절보다 나중에 수행되기 때문에 ROWNUM으로 순서를 지정할 때에는 ORDER BY 절 바깥에서 해야함

셀프 조인 Self Join

; 말 그대로 나 자신과의 조인이며, 혼란을 막기 위해 ALIAS를 반드시 표기해주어야 한다

OVER 키워드와 함께 사용되며 역할에 따라 다음과 같이 나뉨

- 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER

- 집계 함수 : SUM, MAX, MIN, AVG, COUNT

- 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD

- 비율 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

순위함수

- RANK : 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다

- DENSE_RANK : 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다

- ROW_NUMBER : 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다

집계함수

- SUM

- MAX

- MIN

- AVG

- COUNT

윈도우 함수 사용 옵션

; WINDOWING 절을 이용하여 집계하려는 데이터의 범위를 지정할 수 있음

범위

- UNBOUNDED PRECENDING : 위쪽 끝 행

- UNBOUNDED FOLLOWING : 아래쪽 끝 행

- CURRENT ROW : 현재 행

- n PRECENDING : 현재 행에서 위로 n만큼 이동

- n FOLLOWING : 현재 행에서 아래로 n만큼 이동

기준

- ROWS : 행 자체가 기준이 된다

- RANGE : 행이 가지고 있는 데이터 값이 기준이 된다

ex)

- RANGE BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW : 처음부터 현재 행까지

- RANGE BETWEEN 10 PRECENDING AND CURRENT ROW : 현재 행이 가지고 있는 값보다 10만큼 적은 행부터 현재 행까지

- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재 행부터 끝까지

- ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING : 현재 행부터 아래로 5만큼 이동한 행까지

행 순서 함수

- FIRST_VALUE : 파티션 별 가장 선두에 위치한 데이터

- LAST_VALUE : 파티션 별 가장 끝에 위치한 데이터

- LAG : 파티션 별로 특정 수만큼 앞선 데이터

- LEAD : 파티션 별로 특정 수만큼 뒤에 있는 데이터

비율 함수

- RATIO_TO_REPORT : 파티션 별 합계에서 차지하는 비율

- PERCENT_RANK : 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값

; (RANK - 1) / (COUNT -1) 값과 같음

- CUME_DIST : 해당 파티션에서 누적 백분율을 구하는 함수

- NTILE : 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수

; 할당할 행이 남았을 경우 맨 앞의 그룹부터 하나씩 더 채워진다

집합 연산자

- UNION ALL : 중복된 행도 그대로 출력

- UNION : 결과를 합친 후 중복 제거

- INTERSECT

- MINUS/EXCEPT

그룹 함수

; 테이블을 GROUP BY 하여 나타낼 수 있는 데이터를 구하는 함수

역할에 따라 집계함수와 소계(총계) 함수로 나눌 수 있음

- 집계 함수 : COUNT, SUM, AVG, MAX, MIN 등

- 소계(총계) 함수 : ROLLUP, CUBE, GROUPING SETS 등

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

- ROLLUP(A) : A로 그룹핑, 총합계

- ROLLUP(A,B) : A,B로 그룹핑, A로 그룹핑, 총합계

- ROLLUP(A,B,C) : A,B,C,로 그룹핑, A,B로 그룹핑, A로 그룹핑, 총합계

CUBE : 조합할 수 있는 모든 그룹에 대한 소계를 집계

- CUBE(A) : A로 그룹핑, 총합계

- CUBE(A,B) : A,B로 그룹핑, A로 그룹핑, B로 그룹핑, 총합계

- CUBE(A,B,C) : A,B,C로 그룹핑, A,B로 그룹핑, A,C로 그룹핑, B,C로 그룹핑, A로 그룹핑, B로 그룹핑, C로 그룹핑, 총합계

GROUPING SETS : 특정 항목에 대한 소계를 계산, 인자값으로 ROLLUP이나 CUBE를 사용할 수도 있음

- GROUPING SETS(A,B) : A로 그룹핑, B로 그룹핑

- GROUPING SETS(A,B,()) : A로 그룹핑, B로 그룹핑, 총합계

- GROUPING SETS(A, ROLLUP(B)) : A로 그룹핑, B로 그룹핑, 총합계

- GROUPING SETS(A, ROLLUP(B,C)) : A로 그룹핑, B,C로 그룹핑, B로 그룹핑, 총합계

- GROUPING SETS(A, B, ROLLUP(C)) : A로 그룹핑, B로 그룹핑, C로 그룹핑, 총합계

ROLLUP 함수는 인수의 순서에 따라 결과가 달라지며 CUBE함수와 GROUPING SETS 함수는 인수의 순서가 바뀌어도 같은 결과를 출력한다

GROUPING : ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 ROW를 구분할 수 있게 해준다

소계가 계산된 Row에서는 GROUPING 함수의 결과값이 1이 되고 나머지 Row에서는 0이 됨

뷰 View : 특정 SELECT문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트, 가상 테이블

뷰에 대한 특징

- 보안성 : 보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안을 유지할 수 있음

- 독립성 : 테이블 스키마가 변경되었을 경우 어플리케이션은 변경하지 않고 관련 뷰만 수정한다

- 편리성 : 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용할 수 있다

사용자는 내부적으로 뷰를 생성하는 SQL을 볼 수 없으므로 투명성을 가진다고 할 수 없음

서브쿼리

; 하나의 쿼리 안에 존재하는 또 다른 쿼리

위치에 따라 나눌 수 있음

- SELECT 절 : 스칼라 서브쿼리; 반드시 하나의 값만을 반환해야 함

- FROM 절 : 인라인 뷰

- WHERE 절, HAVING 절 : 중첩 서브쿼리

중첩 서브쿼리는 메인 쿼리와의 관계에 따라 다음과 같이 나눌 수 있음

- 비연관 서브쿼리 : 메인 쿼리와 관계를 맺고 있지 않음; 서브쿼리 내에 메인 쿼리의 컬럼이 존재하지 않음

- 연관 서브쿼리 : 메인 쿼리와 관계를 맺고 있음; 서브쿼리 내에 메인 쿼리의 컬럼 존재

중첩 서브쿼리는 반환하는 데이터 형태에 따라 다음과 같이 나눌 수 있음

- 단일 행 서브쿼리 : 서브쿼리가 1건 이하의 데이터를 반환, 단일 행 비교 연산자와 함께 사용

- 다중 행 서브쿼리 : 서브쿼리가 여러 건의 데이터를 반환, 다중 행 비교 연산자와 함께 사용

- 다중 컬럼 서브쿼리 : 서브쿼리가 여러 컬럼의 데이터를 반환

NULL값을 가장 큰 값으로 간주하여 정렬할 것인지 가장 작은 값으로 간주하여 정렬할 것인지는 DBMS마다 다르다

문장의 논리적인 실행 순서

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

DISTINCT

; 해당 컬럼의 중복 데이터를 제거하여 출력

NULL에 대한 조건은 IS NULL 혹은 IS NOT NULL로 기술해야 함

LIKE 조건에 앞뒤로 '%'가 붙으면 그 문자가 포함된 컬럼을 가진 행을 출력하게 됨

'_' 혹은 '%'가 포함된 문자를 검색하고자 할 때 ESCAPE를 지정해서 쿼리를 작성할 수 있음, ESCAPE '#'로 지정해주면 '#%' 혹은 '#_'가 문자 그대로 인식될 수 있음

CONCAT(문자열1, 문자열2); 문자열1과 문자열2를 연결해주는 함수

CHAR 데이터 타입은 고정 길이를 가지고 있는 문자열 데이터이다

ALIAS를 별도로 지정해주지 않으면 컬럼명이 대문자로 출력된다

ORDER BY 절에는 컬럼명을 명시해줄 수도 있고 SELECT 절에 기술된 컬럼의 순서를 숫자로 명시해줄 수도 있음

HAVING 절은 주로 GROUP BY 절 뒤에 오면서 집계 데이터에 대한 조건을 부여하지만 테이블 전체가 한 개의 그룹이 되는 경우 HAVING만 단독으로 사용할 수 있음

INNER JOIN

; 조건에 충족하는 데이터만 출력, ON절을 사용하여 작성

OUTER JOIN

- LEFT OUTER JOIN

- RIGHT OUTER JOIN

- FULL OUTER JOIN

NATURAL JOIN

; A 테이블과 B 테이블에서 같은 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN, SQL에서는 지원하지 않음

Oracle에서는 USING 조건절을 이용, 별도의 ALIAS나 테이블명을 붙이지 않아야 한다

CROSS JOIN

; Cartesian Product, 조합할 수 있는 모든 경우 출력