그룹 함수, 윈도우 함수

그룹 함수

데이터 분석 개요

ANSI/ISO SQL 표준은 데이터 분석을 위해 세 가지 함수를 정의하고 있다.

 

1. AGGREGATE FUNCTION

GROUP AGGREGATE FUCTION이라고도 부르며 COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들이 포함되어 있다.

 

2. GROUP FUNCTION

결산 개념의 업무를 가지는 원가 판매 시스템의 경우는 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요한 업무 중 하나이다. 이런 보고서를 작성하기 위해서 사용되는 함수

 

3. WINDOW FUNCTION

분석 함수나 순위 함수로 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능이다.

 

 

ROLLUP

Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다. 인수 순서에 주의해야 한다.

 

GROUPING : 집계 표시면 1, 아니면 0

 

CUBE : 결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하 심함, 2^N

CUBE(A, B) = GROUPING SETS(A, B, (A, B ), ())

GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있다. 다양한 소계 집합 생성 가능

 

 

윈도우 함수

행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수

 

순위 관련 함수

- RANK : 동일한 값에 대해서는 동일한 순위를 부여 (1, 2, 2, 4)

- DENSE_RANK : 동일한 순위를 하나의 등수로 간주 (1, 2, 2, 3)

- ROW_NUMBER : 동일한 값이라도 고유한 순위 부여 (1, 2, 3, 4)

 

집계 관련 함수

- SUM : 파티션 별 윈도우의 합을 구할 수 있다.

- MAX, MIN : 파티션별 윈도우의 최대, 최소 값을 구할 수 있다.

Ex) 같은 매니저를 두고 있는 사원들 중 최대 값

- AVG : 원하는 조건에 맞는 데이터에 대한 통계 값

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ex) 같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균

- COUNT : 조건에 맞는 데이터에 대한 통계 값

Ex) 본인의 급여 보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수

 

행 순서 관련 함수 - SQL server 지원 X

- FIRST_VALUE : 파티션별 윈도우의 처음 값

- LAST_VALUE : 파티션별 윈도우의 마지막 값

- LAG : 파티션 별 윈도우에서 이전 몇 번째 행의 값

- LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값

 

비율 관련 함수

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

- PERCENT_RANK : 파티션별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구한다.

- CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다.

- NTILE : 파티션별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다.

 

---

참고 사이트

요약 정리본 : https://yurimac.tistory.com/40

SQL) SQLD 이론 요약 정리 PDF

DATE ON-AIR: https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=5&mod=list 

SQL – DATA ON-AIR