DB - 다중 행 함수(Multi-Row Function)

DataBase

다중 행 함수(Multi-Row Finction)

 - 여러 행의 그룹에 대해 적용되는 함수

 - 다중 행 함수의 종류

    그룹 함수(Group Function)

          집계 함수(Aggregate Function) : COUNT, SUM, AVG, MAX/MIN 등

          고급 집계 함수 : ROLLUP, CUBE, GROUPING SETS, PIVOT, UNPIVOT

    윈도우 함수(Window Function)

 

집계 함수

 - 여러 행의 그룹에 대한 연산을 통해 하나의 결과를 반환함

 - SELECT, HAVING, ORDER BY 절에 사용 가능

      WHERE 절에는 사용 불가

 - GROUP BY 절을 통해 그룹핑 기준 명시

 

 집계 함수

 - NULL을 제외하고 계산

     입력행 전체가 NULL인 경우만 결과값이 NULL임

 

- (DISTINCT | ALL) - Default는 All

    DISTINCT : 같은 값을 갖는 여러 데이터를 한 건으로 간주

집계 함수	사용 목적
COUNT(*)	NULL을 제외한 전체 행의 수 출력
COUNT(표현식)	표현식의 값이 NULL인 행을 제외한 행의 수 출력
SUM([DISTINCT | ALL] 표현식)	표현식의 값이 NULL인 행을 제외한 합계 출력
AVG([DISTINCT | ALL] 표현식)	표현식의 값이 NULL인 행을 제외한 평균 출력
MAX([DISTINCT | ALL] 표현식)	표현식의 최대값 출력 (문자, 날짜 타입도 사용가능)
MIN([DISTINCT | ALL] 표현식)	표현식의 최솟값 출력 (문자, 날짜 타입도 사용가능)
STDDEV([DISTINCT | ALL] 표현식)	표현식의 값이 NULL인 행을 제외한 표준편차 출력
VARIAN([DISTINCT | ALL] 표현식)	표현식의 값이 NULL인 행을 제외한 분산 출력

 

GROUP BY

 - 집계 함수는 일반적으로 GROUP BY절을 사용하여 그룹별 연산 수행

    소그룹별 집계식 GROUP BY 사용

    테이블 전체가 하나의 그룹인 경우 GROUP BY절 생략 가능

 

GROUP BY ~ HAVING ~

 - WHERE절 집계 조건 사용 => ERROR

    WHERE절이 GROUP BY 절보다 먼저 수행되기 때문

SELECT		POSITION, ROUND(AVG(HEIGHT),2) 평균키	-- 4
FORM		PLAYER					-- 1
WHERE		AVG(HEIGHT)				-- 2  ----> ERROR
GROUP BY	POSITION				-- 3

 

 - 집계 조건은 HAVING 절에서 정의

SELECT		POSITION, ROUND(AVG(HEIGHT),2) 평균키	-- 4
FORM		PLAYER					-- 1
GROUP BY	POSITION				-- 2
HAVING		AVG(HEIGHT) >= 180;			-- 3

 

 

SELECT 문의 구조 및 실행 순서

SELECT		POSITION, ROUND(AVG(HEIGHT),2) 평균키
FROM		PLAYER
WHERE		HEIGHT IS NOT NULL
GROUP BY	POSITION
HAVING		AVG(HEIGHT) > 190
ORDER BY 	AVG(HEIGHT) DESC;

 

 

실제 실행 순서

 - 1. FROM - 질의 대상 테이블 참조

 - 2. WHERE - 반환 대상이 아닌 데이터 제거

 - 3. GROUP BY - 행 그룹화

 - 4. HAVING - 반환 대상이 아닌 그룹 제거

 - 5. SELECT - 데이터 값 계산 및 출력

 - 6. ORDER BY - 출력 데이터 정렬

 

컬럼의 유효 범위

 - 관계형 데이터베이스는 데이터를 행 단위로 메모리에 복사함

     SELECT절에서 명시되지 않은 컬럼도 WHERE, ORDER BY 절에서 사용 가능

 

- GROUP BY가 사용되는 경우

       SELECT절에서 명시되지 않은 집계 컬럼을 HAVING, ORDER BY절에 사용 가능

 

- 인라인 뷰(Inline View)가 사용되는 경우

     새로운 테이블 구조가 생성된 것으로 이해해야 함

          인라인 뷰의 SELECT절에 명시되지 않은 컬럼은 메인 쿼리에서 사용 불가

 

고급 집계 함수

ROLLUP

 - 소그룹 별 소계 계산 추가(순서 중요)

     각 조합에 대한 집계

     각 그룹(소그룹)에 대한 집계

     전체 집계

 

ROLLUP - 앞 컬럼 기준으로 소그룹 집계

 

GROUPING 함수

고급 집계를 도와줌

GROUPING + CASE 가능

CUBE

 - 다차원 소계 계산 추가( 순서 무관)

 - 모든 조합의 집계 계산 => 시스템 부하가 큼

 

순서와 관계 없이 모든 조합의 집계를 계산하는 것이 CUBE

 

한 쪽 방향으로만 묶여 있다면 ROLLUP

 

GOUPING SETS

 - 여러 컬럼 각각에 대해 반복적으로 그룹화

PIVOT 함수

 - 데이터를 특정 커럼을 기준으로 회전

 - 집계 결과가 넓은 형식으로 변환되어 출력됨 (<= 특정 기준의 열이 여러 열로 나열됨)

UNPIVOT 함수

 - PIVOT된 데이터를 원래 행 기반 형식으로 변환

     PIVOT과 반대의 동작 수행

윈도우 함수(WINDOW FUCTION)

 - 기존 관계형 DB는 컬럼간 연산은 쉽지만 행간의 연산은 어려움

 - 행 간의 관계 정의를 위해 윈도우 함수 고안

     예) 각 직원이 속한 부서 내에서 급여 순위는?

 - 중첩(Nested) 사용 불가

 - 서브쿼리에서도 사용 가능

 - 종류

순위	RANK, DENSE_RANK, ROW,_NUMBER
집계	SUM, MAX, MIN, AVG, COUNT
행 순서	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율	RATIO_TO_REPORT, PERCENT_RANK, NTILE
통계	CORR, STDDEV, VARIANCE 등