[SQL][SQLD][SQLP] 다중행 함수, 그룹 함수, 집계함수

다중행 함수

• 다중행 함수는 여러 행(로우)를 입력으로 하여 처리하는 함수이며, 단일 값을 반환한다.
• 다중행 함수에는 그룹함수, 집계함수, 윈도우 함수 등이 있다.

 

그룹 함수

• 여러 로우의 데이터를 가지고 한 번에 처리하여 결과 값 1건을 반환하는 함수이다.
• 1건으로 만들기 때문에 복수행 함수 또는 집계 함수라고도 표현한다.
• 그룹 함수는 일반적으로 NULL 값을 제외하여 처리한다.
• 그룹 함수는 WHERE절에 사용할 수 없다.

 

기본 함수

COUNT(*)

• NULL 값을 포함한 행의 수를 출력한다.

COUNT(표현식)

• NULL 값을 제외한 행의 수를 출력한다.

SUM(표현식)

• 표현식의 NULL 값을 제외한 합계를 출력한다.

AVG(표현식)

• 표현식의 NULL 값을 제외한 평균을 출력한다.
• NULL값을 0으로 하여 전체 평균을 구하고 싶다면 NVL 함수를 사용한다. 예시는 다음과 같다.
• AVG(NVL(score, 0))

MAX(표현식) / MIN(표현식)

• 표현식의 최대값, 최솟값을 출력한다.
• 문자, 날짜 타입도 사용 가능하다.
• 날짜의 최대값은 현재랑 가장 가까운 날이다.

STDDEV(표현식) / VARIAN(표현식)

• 표현식의 표준편차, 분산을 출력한다.

 

GROUP BY

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY 칼럼

• GROUP BY는 SELECT에 그룹 함수(= 복수행 함수 = 집계 함수)을 사용할 때, 특정 조건으로 그룹을 만들어 그룹별로 함수를 사용하여 결과를 출력한다.
• GROUP BY을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• GROUP BY는 SELECT와 달리 ALIAS를 사용할 수 없다.

 

HAVING

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼] HAVING 그룹조건식

• 그룹 함수는 WHERE절에 사용할 수 없다. WHERE절 대신 HAVING 절을 사용한다.
• HAVING은 일반적으로 GROUP BY 뒤에 표현하지만, 앞에 표현해도 된다.
• GROUP BY가 없어도 HAVING은 사용할 수 있다.

 

집계함수 : ROLLUP, CUBE, GROUPING SETS

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY ROLLUP(칼럼1, 칼럼2)

-- ROLLUP을 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1, 칼럼2)
UNION ALL
GROUP BY (칼럼1)
UNIN ALL
모든 집합 그룹 결과


SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY CUBE(칼럼1, 칼럼2)

-- CUBE를 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1, 칼럼2)
UNION ALL
GROUP BY (칼럼1)
UNION ALL
GROUP BY (칼럼2)
UNIN ALL
모든 집합 그룹 결과


SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY GROUPING SETS(칼럼1, 칼럼2)

-- GROUPING SETS을 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1)
UNION ALL
GROUP BY (칼럼2)

• ROLLUP은 주어진 그룹핑의 소계를 표현한다.
• CUBE는 소계 + 총계를 표현한다. 즉 ROLLUP 결과에 총계가 추가된다.
• 예제는 아래와 같다.

SELECT 부서, 직급, COUNT(*) FROM 직원 GROUP BY CUBE(부서, 직급)

부서	직급	COUNT(*)	설명
		30	전체 총계
	사원	15	직급별 총계
	대리	10
	과장	5
001		5	소계
	사원	4
	과장	1
002		10	소계
	사원	6
	대리	2
	과장	2
003		15	소계
	사원	5
	대리	8
	과장	2

 

GROUPING

GROUPING은 해당 로우 데이터가 ROLLUP, CUBE 또는 GROUPING SETS을 이용할 때 만들어진 로우인지, 아니면 일반 로우의 데이터인지를 판별할 수 있는 함수이다.

즉, 해당 컬럼의 데이터가 Null인 경우에는 특수하게 생성된 로우이므로 1을 반환하고, 해당 컬럼의 데이터가 Null이 아닌 경우에는 기존 로우의 데이터를 이용한 것이므로 0을 반환한다.

SELECT CASE WHEN GROUPING(부서) = 0 THEN 부서 ELSE '합계' END AS 부서
, 직급, COUNT(*) FROM 직원 GROUP BY CUBE(부서, 직급)

부서	직급	COUNT(*)	설명
합계		30	전체 총계
합계	사원	15	직급별 총계
합계	대리	10
합계	과장	5
001		5	소계
합계	사원	4
합계	과장	1
002		10	소계
합계	사원	6
합계	대리	2
합계	과장	2
003		15	소계
합계	사원	5
합계	대리	8
합계	과장	2