[SQLD] 2-2 SQL 활용

제1절 표준 조인

ANSI/ISO 표준 SQL 기능

- STANDARD JOIN 기능 추가( CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)

- SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

- ROLLUP, CUBE, GROUPING SETS등의 새로운 리포팅 기능

- WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

 

8가지 관계형 대수 > 각각 4개의 일반 집합 연산자와 순수 관계 연산자로 나눌 수 있음

 

일반 집합 연산자

- UNION 연산은 UNION 기능

> 수학적인 합집합을 제공하기 위해 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템에 부하를 주는 정렬 작업이 발생

> UNION ALL 기능의 경우 특별한 요구 사항이 없는 경우 공통집합을 중복해서 보여줘 정렬 작업이 일어나지 않음

> UNION과 UNION ALL의 출력이 같다면 응답 속도 향상이나 자원 효율화 측면에서 데이터 정렬 작업이 일어나지 않는 UNION ALL 권고

- INTERSECTION 연산은 INTERSECT 기능

> 수학의 교집합으로써 두 집합의 공통집합을 추출

- DIFFERENCE 연산은 EXCEPT(ORACLE은 MINUS) 기능

> 수학의 차집합으로 첫 번째 집합에서 두 번째 집합과의 공통집합을 제외한 부분

> 대다수의 벤더는 EXCEPT, 오라클은 MINUS 용어 사용

- PRODUCT 연산은 CROSS(ANSI/ISO 표준) JOIN 기능으로 구현

>  곱집합으로 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터 조합을 말함

> 양쪽 집합의 M*N건의 데이터 조합이 발생하며 CARTESIAN PRODUCT라고도 표현

 

순수 관계 연산자

- SELECT 연산은 WHERE 절

- PROJECT 연산은 SELECT절

- (NATURAL) JOIN 연산은 다양한 JOIN 기능

> INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절

- DIVIDE 연산은 현재 사용x

 

관계형 데이터베이스

- 요구사항 분석  ->  개념적 데이터 모델링 ->  논리적 데이터 모델링 -> 물리적 데이터 모델링

> 이 단계에서 엔티티 확정 및 정규화 과정, 다대다 관계를 분해하는 절차를 거침

- 정규화 과정

> 데이터 정합성과 데이터 저장 공간의 절약을 위해 엔티티를 최대한 분리하는 작업

> 일반적으로 3차 정규형이나 보이스코드 정규형까지 진행

> 하나의 주제에 관련 있는 엔티티가 여러 개로 나누어지고 테이블이 여러 개 생기는데 이때 join이 연결하는 역할

 

FROM절 JOIN 형태(ANSI/ISO SQL)

- INNER JOIN/ NATURAL JOIN/ USING 조건절/ ON 조건절/ CROSS JOIN./OUTER JOIN

- 기존 WHERE 절의 검색 조건과 테이블 간의 JOIN 조건을 구분없이 사용하던 방식을 그대로 사용 가능

- 추가된 선택 기능으로 테이블 간에 JOIN 조건을 FROM 절에서 명시적으로 정의 가능

- INNER JOIN > WHERE절에서부터 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환

> DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없음

- NATURAL JOIN > INNER JOIN의 하위개념

> NATURAL JOIN은 두 테이블 간의 동일한 이름을 같는 모든 칼럼에 대해 EQUI JOIN을 수행

> NATURAL INNER JOIN이라고도 표시 가능하나 NATURAL JOIN과 같음

 

ON 조건절

- 과거 WHERE 절에서 JOIN 조건과 데이터 검증 조건이 같이 사용되어 용도가 불분명한 경우 발생

- WHERE 절의 JOIN 조건은 FROM절의 ON조건절로 분리하여 표시

- 명시적으로 JOIN 조건을 구분할 수 있고 NATURAL JOIN이나 USING 조건절처럼 칼럼명이 같아야 된다는 제약없이 칼럼명이 상호 다르더라도 JOIN 조건으로 사용할 수 있음

- FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어지는 단점

- SQL SERVER > ON 조건절만 지원하고 NATURAL JOIN과 USING 조건절을 지원X

 

INNER JOIN

- OUTER JOIN과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환

- 그동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시임으로 USING 조건절이나 ON 조건절을 필수적으로 사용

- 차이가 나더라도 2개의 칼럼으로 표시

 

NATURAL JOIN

- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 수행

- 추가로 USING 조건절, ON 조건절, WHERE절에서 JOIN 조건을 정의X

- SQL Server에서는 지원하지 않는 기능

- JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 하머 ALIAS나 테이블 명과 같은 접두가 불가

- JOIN이 되는 테이블의 도메인과 칼럼명 등이 동일해야 한다는 제약조건

- 모델링 상의 부주의로 인해 동일한 칼럼명이더라도 다른 용도의 데이터를 저장하는 경우도 있기에 주의하여 사용

- 같은 이름의 칼럼을 하나로 처리

 

USING 조건절

- NATURAL JOIN에서는 모든 일치되는 칼럼에 대해 JOIN이 이루어지나 FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있음

- SQL Server에서는 지원하지 않는 기능

- SELECT에서 * 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력

- USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리

 

ON 조건절

- JOIN  서술부(ON 조건절)과 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬움

- 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점

- NATURAL JOIN의 JOIN 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건이지만, 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나 JOIN 칼럼을 명시하기 위해 ON 조건절을 사용

- ON 조건절에 사용된 괄호는 옵션사항.

- ON 조건절을 사용한 JOIN의 경우 ALAIS나 테이블 명과 같은 접두사를 사용하여  SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 함.

- WHERE 절의 JOIN 조건과 같은 기능을 하면서도, 명시적으로 JOIN의 조건을 구분할 수 있으므로 가장 많이 사용될 것으로 예상

- FROM 절에 테이블이 많이 사용될 경우 가독성이 떨어짐.

 

CROSS JOIN

- 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말함

- 2개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로 양쪽 집합의 M*N 건의 데이터 조합 발생

- 필요한 경우가 많지 않으나 튜닝이나 리포트를 작성하기 위해 고의적으로 사용하는 경우가 있을 수 있음

- 데이터 웨어하우스의 개별 차원을 사실 칼럼과 JOIN하기 전에 모든 DIMENSION의 CROSS PRODUCT를 먼저 구할 때 유용하게 사용

 

OUTER JOIN

- 내부 JOIN과 대비되며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용

- 명시적인 OUTER JOIN을 사용할 것을 권장

- JOIN 조건을 FROM 절에서 정의하겠다는 표시로 USING 조건절이나 ON 조건절을 피수적으로 사용

- LEFT/RIGHT OUTER JOIN의 경우 기준이 되는 테이블이 조인 수행시 무조건 드라이빙 테이블이 됨

 

LEFT OUTER JOIN

- 조인 수행시 먼저 표시된 자측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어옴

- A와 B가 있을 때 A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우 B테이블에서 가져오는 칼럼들은 NULL값으로 채움

- LEFT JOIN으로 OUTER 키워드를 생략해서 사용

 

RIGHT OUTER JOIN

- 조인 수행시 우측 테이블이 기준이 되어 결과를 생성

- A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들을 NULL값으로 채움

- RIGHT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있음

 

FULL OUTER JOIN

- 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성

- RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과

- 중복되는 데이터는 삭제

- OUTER 키워드를 생략해서 사용 가능

 

INNER JOIN(B-B, C-C)

LEFT OUTER JOIN(B-B C-C D-NULL E-NULL)

RIGHT OUTER JOIN(NULL-A B-B C-C)

FULL OUTER JOIN(NULL-A B-B C-C D-NULL E-NULL)

CROSS JOIN(B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C)

제2절 집합 연산자

집합 연산자

- 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법

- 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용( 2개 이상의 질의 결과를 하나의 결과로 만들어줌)

- 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나로 합치고자 할 때와 동일 테이블에서 서로 다른 질의를 수행하여 하나로 합치고자 할 때 사용할 수 있음..

- 튜닝 관점에서 실행계획을 분리하고자 하는 목적으로도 사용가능

- SELECT 절의 칼럼 수가 동일하고 SELECCT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호호환 가능해야 함( 반드시 동일한 데이터 타입일 필요는 없음) > 그렇지 않으면 데이터베이스가 오류를 반환

- 개별 SQL문의 결과 집합에 대해 합집합, 교집합, 차집합으로 집합간의 관계를 가지고 작업

- 여러 개의 SELECT문을 연결./ ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬 처리로 가장 마지막 줄에 한번만 기술

제3절 계층형 질의와 셀프 조인

계층형 데이터

- 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의를 사용

- 계층형 데이터 > 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

- 엔티티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생

 

Oracle 계층형 질의

- start with절 루트 데이터를 지정

- connect by절 다음에 전개될 자식 데이터를 지정하는 구문( 자식 데이터는 주어진 조건에 만족해야함)

- PRIOR 자식 = 부모 형태를 사용하면 부모데이터에서 자식 데이터 방향으로 전개하는 순방향 전개

- PRIOR 부모 = 자식 형태를 사용하면 자식 데이터에서 부모 데이터 방향으로 전개하는 역방향 전개

- PRIOR이 자식 = PRIOR 부모형태로도 등장가능

- PRIOR은 WHERE절뿐만 아니라 다른 곳에서도 사용 가능 

- 데이터를 전재하면서 이미 나타났던 동일한 데이터가 전개중에 다시 나타난다면 CYCLE이 형성되며 런타임 오류

- NOCYCLE 추가시 사이클이 발생한 이후의 데이터는 전개X

- ORDER SIBLINGS BY- 형제 노드 사이에서만 정렬 수행

- WHERE - 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터 추출

- 계층형 질의를 사용할 때 가상 칼럼 제공

 

SQL Server 계층형 질의

- SQL Server 2005 버전부터 하나의 질의로 원하는 결과를 얻을 수 있음

- 위에 있는 쿼리를 앵커 멤커, 아래에 있는 쿼리를 재귀 멤버라고 함

- 재귀적 쿼리 처리 과정

> CTE 식을 앵커 멤버와 재귀 멤버로 분할

> 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합을 만든다

> Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행

> 빈 집합이 반환될 때까지 3단계 반복

> 결과 집합을 반환

 

셀프 조인

- 동일 테이블 사이의 조인( FROM 절에 동일한 테이블이 두 번 이상 일어남)

- 반드시 테이블 ALIAS를 사용해야함, 칼럼에도 테이블 ALIAS를 이용하여 어느 테이블 칼럼인지 식별 필요

제4절 서브쿼리

- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말함

- 알려지지 않은 기준을 이용한 검색을 위해 사용

- 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음

- 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브 쿼리를 사용해야함

- 서브쿼리 레빌과 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성

 

주의점

1) 서브쿼리를 괄호로 감싸서 사용

2) 서브쿼리는 단일 행(서브 쿼리의 결과가 반드시 1건 이하) 또는 복수 행 비교 연산자(서브쿼리의 결과 건수와 상관없음)와 사용 가능

4) 서브쿼리에서는 ORDER BY를 사용하지 못함( 메인 쿼리의 마지막 문장에 위치)

 

SQL문에서 사용 가능한 곳

- 논리적 실행 순서는 항상 메인쿼리에서 읽혀진 데이터에 대해 서브쿼리에서 해당 조건이 만족하는지 확인

 

단일 행 서브쿼리

- 서브 쿼리의 결과 건수가 반드시 1건 이하여야함 > 넘어가면 런타임 오류 발생

다중 행 서브쿼리

- 서브쿼리 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용

다중 칼럼 서브쿼리

- 서브 쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교

연관 서브쿼리

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

- EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용되며 아무리 조건을 만족하는 건이 여러 건이라 하더라도 조건을 만족하는 1건만 찾으면 추가적인 검색 진행X

 

그 밖에 위치에서 사용하는 서브쿼리

- SELECT 절에서 사용하는 서브쿼리 > 스칼라 서브쿼리

> 한 행, 한 칼럼만 반환하는 서브쿼리/ 칼럼을 쓸 수 있는 대부분의 곳에서 사용 가능

- FROM 절에서 사용되는 서브쿼리 > 인라인 뷰

> 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용 가능

> 테이블 명이 올 수 있는 곳에 사용 / 조인 방식을 사용하기에 인라인 뷰의 칼럼은 SQL문을 자유롭게 참조 가능

> ORDER BY절 사용 가능/ TOP-N쿼리나 ROWNUM 사용 가능

- HAVING 절에서 사용되는 서브쿼리

- UPDATE문의 SET 절에서 사용하기

- INSERT문의 VALUES절에서 사용

 

뷰

- 실제 데이터를 가지고 있지 않음

- 단지 뷰 정의만을 가지고 있음/ 뷰가 사용시 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성하여 질의를 수행

- 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기에 가상테이블이라 함

 

제5절 그룹 함수

ANSI/ISO SQL 표준 > AGGREGATE FUNCTION, GROUP FUNCTION, WINDOW FUNCTION

 

AGGREGATE FUNCTION > COUNT, SUM, AVG, MAX, MIN외 각종 집계 함수들이 포함

GROUP FUNCTION > GROUPING, CASE, ROLLUP, GROUP BY, CUBE, GROUPING SETS 함수

- ROLLUP은 GROUP BY의 확장된 형태로 사용하기 쉬우며 병렬로 수행이 가능. 계층적 분류를 포함하고 있는 데이터의 집계에 적합

- CUBE는 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻을 수 있으나 시스템에 부하를 많이 줌

- GROUPING SETS는 원하는 부분의 소계만 손쉽게 추출할 수 있는 장점

-  ROLLUP, CUBE, GROUPING SETS 결과에 대한 정렬이 필요한 경우 ORDER BY절에 정렬 칼럼을 명시해야함

WINDOW FUNCTION

- 분석함수나 순위 함수로도 알려져 있으며 데이터웨어하우스에서 발전된 기능

 

ROLLUP함수

- 지정된 GROUPING COLUMNS의 LIST는 Subtotal을 생성하기 위해 사용되며 grouping columns 수가 N이면 N+1 level의 subtotal이 생성

- ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의

 

CUBE 함수

- 결합 가능한 모든 값에 대해 다차원 집계를 생성

- 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행해야함

-  Grand Total은 양쪽의 Query에서 모두 생성되므로 한 번의 Query에서는 제거되어야만 함으로 ROLLUP에 비해 연산 대상이 많음

- Grouping Columns이 가질 수 있는 모든 경우에 대해  Subtotal을 생성해야 하는 경우에만 사용

- 표시된 인수들에 대한 계층별 집계를 구할 수 있으며 이때 인수들 간은 평등한 관계이므로 인수의 순서가 바뀌는 경우 데이터의 결과는 같음

- 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되어야함.

 

GROUPING SETS 함수

- GROUP BY SQL 문장을 여러번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있음

- GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며 이때 평등한 관계로 인수의 순서가 바뀌어도 결과가 같음

- 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬칼럼이 표시되어야함

제6절 윈도우 함수

- INLINE VIEW를 이용해 복잡한 SQL문을 작성해야 했던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만들어짐

- 분석 함수나 순위 함수로 알려져있으며 데이터웨어하우스에서 발전된 기능

- 집계 함수도 있고 WINDOW 함수 전용으로 만들어진 기능도 있음

- 다른 함수와 달리 중첩해서 사용하지 못하나 서브쿼리에서는 사용 가능

 

WINDOW FUNCTION 종류

1. 그룹 내 순위 관련 함수 

RANK, DENSE_RANK, ROW_NUMBER > ANSI/ISO SQL 표준과 Oracle, SQL Server등 대부분의 DBMS에서 지원

2. 그룹 내 집계 관련 함수

SUM, MAX, MIN, AVG, COUNT > 대부분의 DBMS에서 지원하나 SQL Server의 경우 집계 함수는 OVER 절 내의 ORDER BY 구문 지원X

3. 그룹 내 행 순서 관련 함수 > FIRST_VALUE, LAST_VALUE, LAG, LEAD > Oracle에서만 지원되는 함수

- FIRST_VALUE, LAST_VALUE함수 MAX, MIN 함수와 비숫한 결과를 얻을 수 있으며 LAG, LEAD함수 DW에서 유용하게 사용되는 기능

4. 그룹 내 비율 관련 함수 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

- ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며 NTILE 함수는 ANSI/ISO SQL 표준에는 없으나 ORACLE, SQL SERVER에서 지원, RATIO_TO_REPORT 함수는 ORACLE에서만 지원되는 함수

5. 선형 분석을 포함한 통계 분석 관련 함수가 있는데, 통계에 특화된 기능

- WINDOW 함수에는 OVER 문구가 키워드로 필수 포함

ARGUMENTS(인수) > 0개 이상의 인수 지정 가능

PARTITION BY 절 > 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음

ORDER BY절 > 어떤 항목에 대해 순위를 지정할 지 ORDER BY절을 기술

WINDOWING 절 > 함수의 대상이 되는 행 기준의 범위를 강력하게 지정 가능

> ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를 나타냄, SQL Server에서는 지원x

 

RANK 함수

- ORDER BY를 포함한 QUERY 문에서 특정 항목에 대한 순위를 구하는 함수

- 특정 범위 내에서 순위를 구할 수 있고 전체 데이터에 대한 순위를 구할 수 있음

- 동일한 값에 대해서는 동일한 순위를 부여

 

DENSE_RANK 함수

- RANK와 흡사하나 동일한 순위를 하나의 건수로 취급

 

ROW_NUMBER 함수

- 동일한 값이라도 고유한 순위부여

 

일반 집계 함수

SUM 함수

- 파티션별 윈도우의 합을 구할 수 있음

 

MAX 함수

- 파티션별 윈도우의 최대값을 구할 수 있음

 

MIN 함수

- 파티션별 윈도우의 최소값을 구할 수 있음

 

AVG 함수

- AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있음

 

COUNT 함수

- 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있음

 

그룹 내 행 순서 함수

FIRST_VALUE 함수

- 파티션별 윈도우에서 가장 먼저 나온 값을 구함

- SQL Server에서는 지원하지 않는 함수(MIN)

 

LAST_VALUE 함수

- 파티션별 윈도우에서 가장 나중에 나온 값을 구함

- SQL Server에서는 지원하지 않는 함수(MAX)

 

LAG 함수

- 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음

- SQL Server에서는 지원하지 않는 함수

 

LEAD 함수

- 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음

- SQL Server에서는 지원하지 않는 함수

 

그룹 내 비율 함수

RATIO_TO_REPORT 함수

- 파티션 내 전체 SUM값에 대한 행렬 칼럼 값의 백분율을 소수점으로 구할 수 있음

- 0< <=1의 범위를 가짐/ 개별 RATIO의 합을 구하면 1이 됨

- SQL Server에서는 지원하지 않는 함수

 

PERCENT_RANK 함수

- 파티션별 원도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구함

- 0< <=1의 범위를 가짐

- SQL Server에서는 지원하지 않는 함수

 

CUME_DIST 함수

- 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 가은 건수에 대한 누적백분율을 구함

- 0< <=1의 범위를 가짐

- SQL Server에서는 지원하지 않는 함수

 

NTILE 함수

- 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구함

제7절 DCL

- 유저를 생성하고 권한을 제어할 수 있는 DCL

 

유저와 권한

- 부서 혹은 회사간 데이터 공유를 위해 DB를 오픈해야 하는 경우가 발생

-> 이때 새로운 유저를 생성하고 생성한 유저에게 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만 부여

Oracle

- 유저를 통해 데이터베이스에 접속하는 형태

- 아이디/비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여

 

SQL Server

- 인스턴스에 접속하기 위해 로그인이라는 것을 생성하며 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성후 로그인과 유저를 매핑

- 특정 유저는 특정 데이터베이스 내의 특정 스키마에 대해 권한 부여

 

유저 생성과 시스템 권한 부여

- 일반적으로 ROLE을 이용하여 간편하고 쉽게 권한을 부여

- CREATE USER > 유저 생성 

> ORACLE의 경우 DBA 권한을 가지고 있는 SYSTEM 유저로 접속하여 유저 생성 권한을 다른 유저에게 부여

> SQL Server는 유저를 생성하기 전 먼저 로그인을 생성( 로그인 생성 권한을 가진 로그인은 sa)

> SQL Server에서 유저는 DB마다 존재하므로 유저를 생성하기 위해 생성하고자 하는 유저가 속할 DB로 이동을 한 후 처리

 

- 생성되더라도 유저가 로그인을 하려면 CREATE SESSION 권한을 부여받아야함

- 테이블을 생성하려면 CREATE TABLE 권한을 부여받아야함

 

 

오브텍트 권한

- 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미

ORACLE - 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야함

> 다른 유저가 소유한 객체에 접근하기 위해 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근

SQL SERVER - 유저는 단지 스키마에 대한 권한을 가짐( 오브젝트는 스키마가 소유)

> 객체 앞에 소유한 유저의 이름을 붙이는 것이 아니고 객체가 속한 스키마 아름을 붙여서 접근

 

ROLE을 이용한 부여

 - DB에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 부여

- 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며 ROLE은 유저에게 직접 부여할 수 있고, 다른 ROLE에 포함하여 유저에게 부여될 수 있음.

ORACLE

-  제공하는 ROLE중 가장 많이 사용하는 ROLE

> CONNECT(CREATE SESSION과 같은 로그인 권한)와 RESOURCE(CREATE TABLE과 같은 오브젝트 생성 권한)임

DROP USER

- 유저를 삭제하는 명령어

- CASCADE 옵션을 주면 해당 유저가 생성한 오브젝트를 먼저 삭제한 후 유저를 삭제

 

SQL SERVER

- ROLE을 생성하여 사용하기 보다는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식 사용

- ROLE을 자주 사용하지 않는 대신 서버수준 역할 및 데이터베이스 수준 역할을 이용해 로그인 및 사용자 권한 제어

- 인스턴스 수준을 요구하는 로그인엥는 서버 수준 역할, 데이터베이스 수준을 요구하는 사이용자에게는 데이터베이스 수준 역할 부여

제8절 절차형 SQL

- SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성

 

PL/SQL(ORACLE)

- Block 구조로 Block내에는 DML 문장과 QUERY 문장, 절차형 언어 등을 사용할 수 있으며 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어

- 저장모듈 > PL/SQL문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에 공유할 수 있도록 만든 이롲의 SQL 컴퓨턴트 프로그램/ 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

> Procedure, User Defined Function, Trigger

- Block 구조로 각 기능별로 모듈화가 가능

- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환

- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그래밍이 가능하다록 함

- DBMS 정의에러나, 사용자 정의 에러를 정의하여 사용 가능

- ORACLE과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음

- PL/SQL은 응용 프로그램의 성능을 향상

- 여러 SQL문장을 블록으로 묶고 한 번에 BLOCK 전부를 서버로 보내기에 통신량 감소 효과

PL/SQL 기본 문법(Stored Procedure)

생성된 프로시저 삭제하는 명령어

- CREATE 명령어로 프로시저 생성 가능 > DB 내에 저장

- 필요할 때마다 호출하여 실행

- [OR REPLACE] - 데이터베이스 내에 같은 이름의 프로시저가 있을 경우 기존의 프로시저를 무시하고 덮어쓰기

- ARGUMENT - 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 처리한 결과값을 운영체제로 리턴시킬 매개 변수 지정시 사용

-[MODE] 부분에 지정할 수 있는 매개변수 유형

> IN > 운영체제에서 프로시저로 전달될 변수

> OUT > 프로시저에서 처리된 결과가 운영체제로 전달

> INOUT > IN과 OUT 두 가지 기능을 동시에 수행

- / > DB에게 프로시저를 컴파일 하라는 의미

 

T-SQL(SQL Server)

- MS 사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 추가해 보안

- 변수 선언 기능 @@이라는 전역변수와 @이라는 지역변수가 있음

- 지역변수 > 연결된 시간 동안만 사용 / 전역변수 > SQL 서버에 내장된 값

- 데이터 유형을 제공

- 연산자/ 산술연산자/비교연산자/논리연산자 사용 가능

- 흐름 제어 기능 IF/ELSE, WHILE, CASE/THEN 사용이 가능

- 주석 기능 ( -- /**/)

 

T-SQL 기본 문법( Stored Procedure)

생성된 프로시저를 삭제하는 명령

- CREATE 명령어로 프로시저 생성 가능 > DB 내에 저장

- 필요할 때마다 호출하여 실행

- 프로시저의 변경이 필요한 경우 CREATE 구문을 ALTER 구문으로 변경

- @parameter는 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과 값을 리턴시킬 매개 변수를 지정할 때 사용

> 1) VARYING > 결과 집합이 출력 매개 변수로 사용되도록 지정( CURSOR 매개변수에만 적용)

> 2) DEFAULT > 기본 값이 지정되어 있으면 해당 매개 변수를 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행

> 3) OUT, OUTPUT > 프로시저에서 처리된 결과 값을 EXECUTE 문 호출시 반환

> 4) READONLY > 자주 사용x, 프로시저 본문 내에 매개변수를 업데이트하거나 수정할 수 없음을 나타냄

- 매개변수 유형이 사용자 정의 테이블 형식인 경우 지정

 

-WITH 부분 지정 옵션

> 1) RECOMPILE

 - 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일/ DB엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용

> 2) ENCRYPTION

- CREATE PROCEDURE문의 원문 텍스트가 알아보기 어려운 형식으로 변환

- 변조된 출력은 카탈로그 뷰 어디에도 직접 표시되지 않아 반드시 원문 백업이 필요

> 3) EXECUTE AS

- 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정

 

프로시저 생성과 활용

 주의해야하는 요소

1) PL/SQL 및 T-SQL에서는 다양한 변수가 있음. -> SCALAR 변수는 사용자의 임시 데이터르 하나만 저장할 수 있는 변수로 거의 모든 형태의 데이터 유형을 지정가능

2) PL/SQL에서 사용하는 SQL구문은 동일하게 사용 가능하나 SELECT문과는 다르다

- PL/SQL에서 사용하는 SELECT문은 반드시 결과값이 있어야 하며 그 결과가 1개여아함

- 조회 결과가 없거나 하나 이상인 경우 에러를 발생시키며 T-SQL에서는 에러가 발생하지 않음

3) T-SQL을 비롯하여 일반적으로 대입 연산자는 "="를 사용하지만 PL/SQL에서는 ":="를 사용

4) 에러처리를 담당하는  EXCEPTION에는 WHEN ~ THEN 절을 사용하여 에러의 종류별로 적절히 처리

- OTHERS를 이용하여 모든 에러를 처리가능하나 정확하게 에러를 처리하는 것이 필요

 

User Defined Function의 생성과 활용

- Procecure처럼 절차형 SQL을 로직과 함께 DB 내에 저장해 놓은 명령문의 집합을 의미

- Function이 Procedure과 다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야함

 

Trigger

- 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

- 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며

- 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 존재

-  ROLLBACK을 하면 Trigger로 입력된 정보까지 트랜잭션으로 인식하여 모두 취소됨
- 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용

 

프로시저와 트리거의 차이점