데이터 분석함수 (집계함수, 그룹함수, 윈도우함수)

* SELECT문 해석 순서

   - GROUP BY구문은 NULL 값이 존재하는 ROW는 제외하고 산출, WHERE절 먼저 수행 후 실행 (그룹별 집계)

      : 또한 GROUP BY 뒤에 오는 속성명은 반드시 SELECT문의 찾는 속성 중에 존재해야함.

      : 윈도우함수와 절대 함께 사용할 수 없음

  - HAVING 구문은 GROUP BY에 대한 조건식을 서술하기 위한 것

 

* 데이터 분석 함수란?

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

  : 복수행을 그룹별로 묶어 그룹당 단일 계산 결과를 반환하는 것

 

1. 집계함수(Aggragate Function)

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

   : NULL값은 제외하고 집계

집계함수 : (컬럼명)기입	설명
COUNT(*)	복수 행의 ROW 개수
SUM(*)	복수 행의 해당 컬럼간 합계
AVG(*)	복수 행의 해당 컬럼간 평균
MAX(*)	복수 행의 해당 컬럼간 최대값
MIN(*)	복수 행의 해당 컬럼간 최소값
STDDEV(*)	복수 행의 해당 컬럼간 표준편차
VARIAN(*)	복수 행의 해당 컬럼간 분산

 

2. 그룹함수(Group Function)

   : 그룹별 소계 및 중계 등 중간 합계 분석 데이터 산출

   : GROUP BY 절 필수 & GROUP BY 절 뒤에 위치 ⇨ 하나 이상의 컬럼을 기준으로 컬럼값에 따라 그룹화 하여 그룹별로 출력

   : SELECT 문 뒤에 나오는 컬럼명은 반드시 GROUP BY(집계대상아닌경우) 또는 그룹함수 뒤(집계대상)에 포함되어있어야 한다.

      ➡︎ 단순 컬럼명만! 집계함수 등은 미포함!

ROLLUP	컬럼 간 순서에 따라 다른 결과 출력	주어진 컬럼을 기준으로 그룹별 소계 구함
CUBE		주어진 컬럼을 기준으로 모든 조합의 그룹별 소계를 구함
GROUPING SETS	컬럼 간 순서와 무관한 결과 출력	주어진 컬럼들에 대한 다양한 소계 집합을 구하는 함수

-- 부서별-직위별 그룹핑 > 소계는 부서별로 총계 후 전체합계
SELECT DEPT, JOB SUM(SALRARY) FROM DEPT_SAL
	GROUP BY ROLLUP(DEPT, JOB)

-- 모든 경우의 수의 조합에 대한 집계
-- 즉 : 부서멸, 직급별, 부서-직급별 모든 집계 산출
SELECT DEPT,JOB,SUM(SALARY) FROM DEPT_SAL
	FROM EMP
    GROUP BY CUBE(DEPT,JOB);

-- 컬럼별 개별집계 : 부서별로 직급별로 누적합계가 아닌 ~별. 
SELECT DEPT,JOB,SUM(SALARY)
	FROM DEPT_SAL
    GROUP BY GROUPING SETS(DEPT,JOB);

 

3. 윈도우함수(Window Function) 

   : 데이터베이스를 사용한 온라인 분석처리 용도 ➡︎ OLAP(Online Analytical Processing)함수

   : GROUP BY절 사용 불가 ➡︎ PARTITION BY로 구간 설정하여 사용

   : PARTITION BY는 GROUP BY와 같은 집약기능이 없기때문에 레코드가 줄어들지 않음. 그저 함수 적용 범위를 묶는 역할

   : 윈도우 함수는 반드시 OVER과 함께 사용

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

     1) 순위함수

         : 소괄호 안에 컬럼X, 순위를 정하려는 컬럼을 ORDER BY에 기입

-- RANK() : 동일 순위 존재시 후순위 넘어감
-- DENSE_RANK() : 동일 순위 존재시에도 후순위 넘어가지않음
-- ROW_NUMBER() : 연속번호부여 ⇨ 동일 순위 없음
SELECT NAME, SALARY, 
    RANK() OVER (ORDER BY SALARY DESC),
    DENSE_RANK() OVER(ORDER BY SALARY DESC),
    ROW_NUMBER() OVER(ORDER BY SALARY DESC)
FROM EMPLOYEE;

     2) 행순서함수

         : 출력 대상을 파라미터에 기입, 순위를 정하려는 컬럼은 ORDER BY에 기입

         : PARTITION BY 생략시 전체 컬럼 대상

-- FIRST_VALUE(출력대상) : 파티션 별 윈도우에서 가장 먼저 나오는 값을 찾음
-- LAST_VALUE(출력대상) : 파티션 별 윈도우에서 가장 나중에 나오는 값을 찾음
-- LAG(출력대상) : 파티션 별 윈도우에서 이전 로우값 반환
-- LEAD(출력대상) : 파티션 별 윈도우에서 다음 로우값 반환
SELECT NAME, SALARY,
    FIRST_VALUE(NAME) OVER(ORDER BY SALARY DESC),
    LAST_VALUE(NAME) OVER(ORDER BY SALARY DESC),
    LAG(NAME) OVER(ORDER BY SALARY DESC),
    LEAD(NAME) OVER(ORDER BY SALARY DESC),
FROM EMPLOYEE;

     3) 그룹내비율함수

-- RATIO_TO_REPORT : 주어진 그룹의 합에대해 로우의 상대적 비율 반환 ➡︎ 0~1 사이값 반환
-- PERCENT_RANK : 순서에 대한 백분율 반환 ➡︎ 0~1 사이값 반환 - 가장 먼저 나오는것 0