Oracle SQL - Group & Aggregation

Aggregation Function (집계함수)

  - 여러 행으로부터 하나의 결과값을 반환
  - 종류

• AVG
• COUNT
• COUNT(*) : 테이블 내의 행 수 (NULL도 카운트됨)
• COUNT(expr) : 테이블 내의 행 수 (NULL 제외)
• MAX
• MIN
• SUM
• STDDEV
• VARIANCE

count()

  - 함수에 입력되는 데이터의 총 건수를 구하는 함수
  - * 를 사용하면 null을 포함한 총 Row의 개수를 구하며, 필드를 명시할 경우 null 값을 제외한다.

SELECT COUNT(*), COUNT(commission_pct)
FROM employees;

SELECT COUNT(*) FROM employees
WHERE salary > 16000;

sum()

  - 입력된 데이터들의 합계 값을 구하는 함수

SELECT COUNT(*), SUM(salary)
FROM employees;

avg()

  - 입력된 데이터들의 평균 값을 구하는 함수
  - 주의 : null 값이 있는 경우 빼고 계산해야 함 = nvl 함수와의 조함

SELECT COUNT(*), SUM(salary), AVG(salary)
FROM employees;

SELECT COUNT(*), SUM(salary), AVG(VNL(salary,0))
FROM employees;

※ NULL 값을 포함시킬 것인지, 뺄 것인지에 따라 통계 결과가 달라진다. 어떤 값을 대상으로 통계 값을 잡을 것인지는 정책으로 결정

min() / max()

  - 입력된 값 중 가장 작은 값/큰 값을 구하는 함수
  - 여러 건의 데이터를 순서대로 정렬 후 값을 구하기 때문에 데이터가 많을 떄는 느리다 (사용에 유의)

SELECT COUNT(*), MAX(salary), MIN(salary)
FROM employees

 

일반적인 오류

부서의 평균 연봉을 구하고자 다음과 같은 Query를 실행 할 수 있다.

SELECT deptno, AVG(sal) FROM emp;          --  잘못된 코드

주의
  - 집계함수의 결과는 하나의 ROW
  - deptno는 하나의 ROW에 표현할 수 없음
  - 부서별과 같은 내용이 필요할 때는 GROUP BY 절 사용

SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno;

!! 부서별 급여에 부서명도 함께 출력하고자 한다.

SELECT deptno, dname, AVG(sal) FROM emp
GROUP BY deptno
ORDER BY deptno;                         -- 잘못된 코드

비록 부서 번호에 따라 부서명은 하나로 결정될 수 있지만, dname은 groupin에 참여하지 않았으므로

하나의 row로 aggregate 되었다고 볼 수 없음

 

주의
   - SELECT의 Col 목록에는 Group by에 참여한 필드나 aggregate 함수만 올 수 있다
   - Group by 이후에는 Group by에 참여한 필드나 aggregate 함수만 남아있는 셈
       - HAVING, ORDER BY도 마찬가지

 

 

HAVING 절

Aggregation 결과에 대해 다시 condition을 검사할 때
  - 일반적 오류
  - 평균 월급이 2000 이상인 부서는?

SELECT deptno, AVG(sal) FROM emp
WHERE AVG(sal) > 2000
GROUP BY deptno;              -- 잘못된 코드 // 평균을 구하는게 Group by 이후에 실행된다

 주의
  - WHERE 절은 Aggregation 이전, HAVING 절은 Aggregation 이후의 필터링
  - HAVING 절에는 GROUP BY에 참여한 컬럼이나 Aggregation 함수만 사용 가능

SELECT department_id, ROUND(Avg(salary)) FROM employees
GROUP BY department_id
	HAVING AVG(salary)>=7000
ORDER BY department_id;

단일 SQL문의 실행

Flow

단일 SQL 작성법

1. 최종 출력될 정보에 따라 원하는 컬럼을 SELECT 절에 추가
2. 원하는 정보를 가진 테이블들을 FROM 절에 추가
3. WHERE 절에 알맞은 JOIN 조건 추가
4. WHERE 절에 알맞은 검색 조건 추가
5. 필요에 따라 GROUP BY, HAVING 등을 통해 Grouping하고 Aggregate
6. 정렬 조건 ORDER BY에 추가

---

분석함수 ( Only Oracle)

ROLLUP 

SELECT deptno, job, sum(sal) FROM emp
GROUP BY ROLLUP(deptno, job);

GROUP BY 절과 함께 사용되며 그룹 지어진 결과에 대하여 좀더 상세한 정보를 변환하는 기능을 수행

1차 그룹(부서번호), 2차그룹(분류), Sub total 제공

CUBE

SELECT deptno, job, sum(sal) FROM emp
GROUP BY CUBE(deptno, job)

Cross-Tab에 대한 Summary를 추출하는데 사용
: ROLLUP에 의해 출력되는 Item Total 값과 Column Total 값을 나타낼 수 있음