[SQLD] 정리 2 ( JOIN, 계층형질의, GROUP, WINDOW FUNCTION )

일반집합 연산자

1. UNION 연산은 UNION 기능으로，

2 INTERSECTION 연산은 INTERSECT 기능으로，(교집합)

3. DIFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능(차집합)

4 PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다(곱)

순수관계 연산자

5 SELECT 연산은 WHERE 절로 구현되었디.

6. PROJECT 연산은 SELECT 절로 구헌되 었다

7. (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현되었다.

8 DIVIDE 연산은 현재 사용되지 않는다

<JOIN>

EQUI JOIN = INNER JOIN

INNER JOIN

* JOIN 조건에서 동일한 값이 있는 행만 반환

* DEFAULT 옵션이므로 생략 가능 ( JOIN만 표기 가능 )

* CROSS JOIN, OUTER JOIN과 같이 못씀.

* 그동안 WHERE절에서 사용하던 JOIN 조건을 FROM에서 정의

따라서 USING이나 ON 필수

*별도의 칼럼순서를 정하지 않았다면 별개의 칼럼으로 출력

NATURAL JOIN

* INNER JOIN의 하위 개념

* 동일한 이름을 갖는 모든 칼럼에 대해 EQUI JOIN을 수행

* NATURAL INNER JOIN이라고도 표시

* JOIN 조건이 숨어있음 ( USING, ON, WHERE절에서 JOIN조건 쓸 수 없음 )

* SQL Server에서 지원 안함

* NATURAL JOIN에 사용된 열은 E.EMPLOYEE_ID 처럼 ALIAS, 접두사 쓸수 없음

*별도의 칼럼순서를 정하지 않았다면 JOIN의 기준이 되는 칼럼들이 먼저 출력

ON 조건절

* 상호명이 달라도 사용가능 ( NATURAL JOIN, USING 조건절 과 다름)

* 앞으로 가장많이 사용될 것이라 예상

* FROM절에 테이블이 많을경우 가독성이 떨어짐

USING 조건절

* ALIAS나 접두사를 쓸수 없음

INNER JOIN은 동일한 값이 있는 행만 나온다는 것~

CROSS JOIN

* 나올 수 있는 모든 경우의 수를 출력

* WHERE 사용할 수 있지만 INNER와 같은 결과가 되므로 권고하지 않음

모두 4번씩 출력..

OUTER JOIN

* JOIN조건에서 동일한 값이 없는 행도 반환할 때 사용

* JOIN 조건을 FROM절에서 정의하겠다는 표시이므로 USING이나 ON조건절을 필수적으로 사용해야 함.

LEFT OUTER JOIN

* LEFT JOIN으로 OUTER생략 가능

RIGHT OUTER JOIN

* RIGHT JOIN 으로 OUTER생략 가능

* A RIGHT OUTER JOIN B 는 B테이블을 기준으로 가져오고 A테이블에 없는 값은 NULL로 표기

FULL OUTER JOIN

* A B 테이블 모두 기준이 됨

* UNION과 같은 결과. 즉, 중복데이터는 삭제됨

(LEFT OUTER JOIN (UNION) RIGHT OUTER JOIN)

* FULL JOIN으로  OUTER 생략 가능

집합연산자

* SELECT 절의 칼럼 수가 동일해야 함

* SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 함

UNION

* 공통 교집합의 중복을 없앰

* ORDER BY 는 맨밑에 한번만 기입

* WHERE IN, OR 절로 변형하여 사용할 수 있다.

집합 연산자 결과를 표시할때 처음에 사용된 HEADING이 적용됨

UNION ALL : 공통집합을 중복해서 그대로 보여줌

INTERSECT : 여러 SQL문의 결과에 대한 교집합

EXCEPT : 앞의 SQL 결과에서 뒤의 SQL결과를 뺀 값, SQL Server에서 사용 ( 일부 데이터베이스는 MINUS를 사용 )

Oracle - 계층형 질의

* START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.

* CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.

* PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 데이터에서 부모데이터 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터 방향으로 전개하는 역방향 전개를 한다.

* NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

* ORDER SIBLINGS BY : 형제 노드 사이에서 정렬을 수행한다.

* WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 ( 필터링 )

**아래쿼리

* 순방향 전개 [PRIOR 자식 = 부모]

* EMPLOYEE 가 MANAGER가 된다. 위에서 아래로

*역방향 전개 [PRIOR MANAGER_ID=EMPLOYEE_ID]

* MANAGER가 EMPLOYEE가 된다. 아래에서 위로

* SYS_CONNECT_BY_PATH ( EMPLOYEE_ID, '/' ) 추가한 구문

CONNECT_BY_ROOT EMPLOYEE_ID

SQL Server 계층형 질의 - CTE (Common Table Expression)

* SQL Server 계층형 질의

* 재귀 공통 테이블 식

* 자기 테이블을 계속 재귀 호출하며 계층 구조를 전개

* Ti 는 입력으로 Ti+1은 출력으로 사용

* 결과집합은 T0에서 Tn까지의 UNION ALL이다.

서브쿼리

* 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.

* 서브쿼리 레벨(행 개수)과는 상관없이 항상 메인쿼리 레벨로 출력

* 단일행 복수행 비교 연산자 모두 사용 가능

* ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 함

* 많이 사용하면 중복되는 것이 많아 컴퓨터 힘들어짐

SCALAR SUBQUERY

NESTED SUBQUERY

서브쿼리 사용 가능한 곳

* SELECT

* FROM

* WHERE

* HAVING

* ORDER BY

* INSERT문의 VALUES

* UPDATE문의 SET

다중행 서브쿼리

IN, ALL, ANY, EXISTS

연관 서브쿼리

서브쿼리 내에 메인 쿼리 칼럼이 사용된 서브쿼리

인라인 뷰

* FROM절에서 사용되는 서브쿼리

* SQL이 실행될 때만 임시적으로 생성되는 동적인 뷰

* 칼럼 자유롭게 참조 가능

뷰 VIEW

장점

* 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.

* 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.

* 보안성 : 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

이미 생성된 뷰를 참조해서 뷰를 만들 수 있다.

뷰생성

CREATE VIEW < > AS

SELECT

FROM

WHERE

뷰삭제

DROP VIEW < >

그룹함수

AGGREGATE FUNCTION ( 집계함수 )

COUNT, SUM, AVG, MAX, MIN

GROUP FUNCTION

ROLLUP : 병렬로 수행가능, 시간 및 지역처럼 계층적 분류를 포함하고있는 데이터 집계에 적합

CUBE : 다차원적인 집계 생성, 시스템에 부하를 주는 단점

GROUPING SETS : 원하는 부분의 소계

WINDOW FUNCTION

분석ANALYTIC, 순위RANK

데이터 웨어하우스에서 발전

ROLLUP

Grouping Columns의 Subtotal을 생성

*그냥 GROUP BY

* ROLLUP 썼을 때

* ROLLUP함수 일부사용

CUBE

GROUPING COLUMN 수가 N이라면 2의 N승 개의 Sub Total level 생성

ROLLUP과 달리 평등한 관계 이므로, 인수 순서가 바뀌는 경우 행간의 정렬순서도 바뀔수 있다

GROUPING SETS

* 인수들에 대한 개별집계 가능

* 평등관계 이므로 (DEPT, JOB)과 (JOB, DEPT) 결과 같음

WINDOW함수

* 중첩사용 불가

* 서브쿼리엔 사용 가능

* SQL Server는 OVER절 내의 ORDER BY, WINDOWING 구문 지원안함

* RATIO_TO_REPORT는 ORACLE에서만 지원 가능

* OVER문구가 필수

순위

RANK 1 2 2 4

DENSE_RANK 1 2 2 3 : 동일 순위를 하나의 건수로 취급

ROW_NUMBER 1 2 3 4 : 동일한 값도 고유 순위 부여

이 부분은 데이터베이 스 별로 틀린 결과가 나올 수 있으므로， 만일 동일 값에 대한 순서까지 관리하고 싶으면

ROW_NUMBER( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을 이용해 추가적인 정렬기준을 정의해야 한다.

SUM

RANGE UNBOUNDED PRECEDING : 현재 행부터 파티션 내의 첫번째 행까지

RANGE 1750~1850

ROWS 앞뒤로 5줄

AVG

FIRST_VALUE

파티션별 가장먼저 나온 함수

MIN활용해서 같은 결과 낼수 있음

SQL Server 지원 안함

LAST_VALUE

파티션별 가장나중에 나온 함수

MAX활용해서 같은 결과 낼수 있음

SQL Server 지원 안함

LAG

이전행 데이터 가져오기

SQL Server 지원 안함

LAG(SAL,2,0) : SAL 2전행 데이터 가져오기, 데이터 없으면 0으로 표시

LEAD

이후행 데이터 가져오기

SQL Server 지원 안함

LEAD(SAL,2,0) : SAL 2이후행 데이터 가져오기, 데이터 없으면 0으로 표시

RATIO_TO_REPORT

파티션내 전체 SUM값에 대한 비율 0<X<=1 로 표시

데이터 다 더하면 1

RATIO_TO_REPORT(SAL) OVER ()

SQL Server X

PERCENT_RANK

값이 아닌 행의 순서별 백분율

제일 빠른 데이터 0

제일 마지막 데이터 1

데이터 N개 있을시 1/(N-1) 비율로 나눔

0 0.5 1

0 0 0.5 0.75 1

같은 데이터 있을 시 RANK처럼 처리됨

SQL Server X

CUME_DIST

현재 행보다 작거나 같은 건수에 대한 누적 백분율

0<X<=1 범위

N행 출력시 1/N 비율로 나눔

0.3333 0.6667 1

SQL Server X

NTILE

전체 행을 파티션별로 N등분 한 것

12행 출력시

111/222/333/444

14행 출력시

1111/2222/333/444