SQL | 응용 SQL 집계함수, 그룹함수, 윈도함수

데이터 분석 함수

: 총합, 평균 등의 데이터 분석을 위해서는 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수

 

데이터 분석 함수 특징

-단일 행을 기반으로 산출하지 않고 복수행을 그룹별로 모아 놓고 그룹당 단일 계산 결과 반환

-GROUP BY 구문을 활용해 복수 행을 그룹핑

-SELECT, HAVING, ORDER BY등의 구문에 활용

 

다중 행 함수 

-집계 함수 (하나의 결과 값 반환)

-그룹 함수 (중간 합계 분석 데이터 산출)

-윈도 함수 (OLAP_

 

 

1. 집계 함수

 

집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

 

집계함수 구문

SELECT 컬럼1.., 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1...
[HAVING 조건식(집계함수 포함)]

집계함수-GROUP BY 구문

-WHERE구문을 활용하여 조건별 대상 ROW를 선택

-NULL값을 가지는 ROW는 제외한 후 산출

-SELECT에서 사용하는 것과 같은 ALIAS(별칭)사용 불가

-WHERE 구문 안에 포함되지 않음

-WHERE구문은 GROUP BY 보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 함

 

집계함수-HAVING 구문

-WHERE구문 내에는 사용할 수 없는 집계 함수으 ㅣ구문 적용하는데 사용

-GROUP BY 뒤에 기재, 한마디로 GROUP BY 및 집계 함수에 대한 WHERE구문

 

집계함수 종류

집계함수	설명
COUNT	행의 줄 수
SUM	합계
AVG	평균
MAX	최댓값
MIN	최솟값
STDDEV	표준편차
VARIAN	분산

 

2. 그룹 함수

 

그룹 함수 :  테이블 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력 함

 

그룹 함수 유형

-ROLL UP 함수 (중간 집계값 산출)

-CUBE 함수 (4차원 집계)

-GROUPING SETS 함수 (개별집계)

 

1) ROLLUP

-중간 집계 값을 산출하기 위한 그룹 함수

-지정 컬럼 수N보다 하나 더 큰 레벨만큼의 (N+1) 중간 집계 값이 생성

-ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀜

SELECT 컬럼1, 컬럼2..., 집계함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼...] ROLLUP 컬럼
[HAVING ...]
[ORDER BY...]

 

2) CUBE 

-결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수

-연산이 많아 시스템에 부담을 줌

SELECT 컬럼1, 컬럼2..., 집계함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼...] CUBE(컬럼명a,...)
[HAVING ...]
[ORDER BY ...]

 

3) GROUPING SETS

-집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며 앞의 두 함수와는 달리 컬럼 간 순서와 무관한 결과를 얻음

-GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있음

-ORDER BY를 사용하여 집계 대상 그룹과의 표시 수선를 조정하여 체계적으로 보여줄 수 있음

SELECT 컬럼명1, 컬럼명2,..,집계함수
FROM 테이블명
[WHERE ...]
GROUP BY[컬럼명1,...] GROUPING SETS(컬럼명1,...)
[HAVING ...]
[ORDER BY ...]

 

 

3. 윈도 함수

 

윈도 함수는 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 함수(=OLAP 함수)

*GROUP BY 절 이용하지 않음

*행 그룹 기반 집계값 계산

SELECT 함수명(파라미터)
OVER
([PARTITION BY 컬럼1,...]
[ORDER BY 컬럼A,...])
FROM 테이블명

윈도 함수 유형

순위함수	레코드 순위를 계산하는 함수 RANK, DENSE_RANK, ROW_NUMBER
행 순서 함수	레코드에서 가장 먼저 나오거나 뒤에 나오는 값, 이전/이후 값들을 출력하는 함수 FRIST_VALUE, LAST_VALUE, LAG, LEAD
그룹네 비율 함수	백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수 RATIO_TO_REPORT, PERCENT_RANK

 

1) 순위함수

레코드의 순의를 계산하는 함수

순위함수	설명
RANK	-특정 항목(컬럼)에 대한 순위를 구하는 함수 -공동 순위 반영(1위,1위,1위,4위)
DENSE_RANK	-공동 순위 무시(1위,1위,1위,2위)
ROW_NUMBER	-동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여(1위 3개: 1위,2위,3위,4위)

쿼리 예시

SELECT NAME, SALARY
    RANK() OVER(OERDER BY SALARYY DESC) A,
    DENSE_RANK() OVER(OERDER BY SALARYY DESC) B,
    ROW_NUMBER() OVER(OERDER BY SALARYY DESC) C
  FROM EMPLOYEE;

 

2) 행순서 함수

레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값, 이전/이후 값들을 출력하는 함수

행 순서 함수	설명
FIRST_VALUE	-파티션별 윈도에서 가장 먼저 나오는 값을 반환하는 함수 -집계 함수의 MIN과 동일한 결과
LAST_VALUE	-가장 늦게 나오는 값을 반환 -MAX와 동일한 결과 출력
LAG	-이전 로우의 값을 반환
LEAD	-이후 로우의 값을 반환

쿼리 예시

SELECT NAME. SALARY
    FISRT_VALUE(NAME) OVER(ORDER BY SALARY DESC) A,
    LAST_VALUE(NAME) OVER(ORDER BY SALARY DESC) B,
    LAD(NAME) OVER(ORDER BY SALARY DESC) C,
    LEAD(NAME) OVER(ORDER BY SALARY DESC) D;

 

3) 그룹 내 비율 함수

백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수

그룹 내 비율 함수	설명
RATIO_TO_REPORT	-주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율 반환 -결과값은 0~1의 범위를 가짐
PERCENT_RANK	-주어진 그룹에 대해 가장 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는 함수 -결과값은 0~1의 범위