CH3 SQL 활용(2)

윈도우 함수

- over 키워드와 함께 사용된다.

- 윈도우 함수 처리로 인해 결과 건수는 줄어들지 않는다. 

순위 함수

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

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

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

 

집계 함수 

SUM 

- 데이터의 합계를 구하는 함수이다. 

- SUM 하는 컬럼은 OVER 절에서 OREDER BY 절에 명시해주게 되면 RANGE UNBOUNDED PRECEDING

구문이 없어도 누적합이 집계된다. 

- RANGE 옵션은 동일한 데이터가 있을 경우 모두 합한 값을 출력한다. (230P)

 

MAX

데이터의 최댓값을 구하는 함수이다.

 

MIN

데이터의 최솟값을 구하는 함수이다. 

(OVER 절에 OREDER BY 구문있으면 R U P 와 같은 방식으로 전부다 출려되는듯?)

 

AVG

데이터의 평균값을 구하는 함수이다.

 

윈도우 함수 사용 옵션

UNBOUNDED PRECEDING - 위쪽 끝 행 

UNBOUNDED FOLLOWING - 아래 쪽 끝 행

CURRENT ROW - 현재 행 

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

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

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

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

 

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 

처음부터 현재 행까지, RANGE UNBOUNDED PRECEDING랑 같은 의미 

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW

G현재 행이 가지고 있는 값보다 10만큼 적은 행부터 현재 행까지, RANGE 10 PRECEDING랑 같은 의미

ROWS BETWEEN CURRENT ROW AND FOLLOWING

현재 행부터 끝까지 

ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING

현재 행부터 아래로 5만큼 이동한 행까지 

(240P) 보기

 

COUNT

데이터의 건수를 구하는 함수이다. 

 

 

 

행 순서 함수- SQL Server(MSSQL) 지원 X

 

FIRST_VALUE

파티션 별 가장 선두에 위치한 데이터를 구하는 함수이다. 

 

LAST_VALUE

- 파티션 별 가장 끝에 위치한 데이터를 구하는 함수이다. 

- RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 를 명시해야지 

동일한 값이 뜬다.(246P)

 

LAG

- 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수이다. 

- LAG(SCORE,3) SCORE 에서 앞에 3 만큼 이동해서 그거를 출력

- 두번째 인자값을 생략할시 1이 default 값이다. 

 

LEAD

파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수이다. LAG의 반대다.

 

 

비율 함수- SQL Server(MSSQL) 지원 X(NTILE 제외)

RATIO_TO_REPORT

파티션 별 합계에서 차지하는 비율을 구하는 함수이다. 

0<=X<=1

 

PERCENT_RANK

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

- RANK 순위에서 -1, 총 COUNT -1 해서 나누면 된다. 

0<=X<=1

 

CUME_DIST

- 해당 파티션에서의 누적 백분율을 구하는 함수이다. 결과값은 0보다 크고 1보다 작거나 같은 값을 가진다. 

- 현재 행까지의 누적 건수에서 전체 건수로 나눈 값과 동일하다. 

0<X<=1

 

NTILE

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

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

 

Top - N 쿼리

ROWNUM

- 이것은 슈도 컬럼이다. 즉 가짜이다. 이것은 보통 순번 즉 자동번호를 매길때 사용한다. 

- WHERE ROWNUM =5 와 같이 건너뛰기 조건은 성립 불가능이다. 왜냐하면 순번이 1씩 증가하기 때문이다.

- < 조건이나 <= 조건을 사용한다.

- ORDER BY 절은 WHERE절보다 뒤에!! 서브쿼리 써서 

 

윈도우 함수의 순위 함수

 

셀프 조인 

셀프조인은 말 그대로 나 자신과의 조인이다. FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에

혼란을 막기 위해 ALIAS를 반드시 표기해주어야 한다.

 

계층 쿼리

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

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

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

START WITH: 경로가 시작되는 루트 노드는 생성해주는 절이다. 

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

PRIOR: 바로 앞에 있는 부모 노드의 값을 반환한다, PRIOR 자식 = 부모, CONNECT BY 절 이외에 다른곳에 사용가능(SELECT, WHERE)! 

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

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

ORDER SIBLLINGS BY절을 사용하면 형제들끼리 정렬된다. 즉 같은 LEVEL 끼리 정렬된다.