[Oracle] 함수 :: 집계 함수(Aggregation Function)

함수, Function

Oracle에서 함수라 하면 2종류가 있다.

 

1. 내장 함수(Built-in Function)
2. 사용자 정의 함수(User Function) → PL/SQL

 

✓ 메서드(Method): 클래스 안에서 정의한 함수
✓ 함수(Function): 어딘가에 소속되어 있지 않고, 독립적으로 존재

 

이 내장 함수에는 여러가지가 있는데 오늘은 그 중 매우 자주 쓰이는 집계함수에 대해 알아볼 예정이다!

 

집계 함수, Aggregation Function

: 아주 쉽다. But 아직은 안배운 뒤의 다른 여러 내용과 결합시 꽤 어렵다.

 

집계 함수에는 아래와 같이 총 5종류가 있다.

1. COUNT()

2. SUM()

3. AVG()

4. MAX()

5. MIN()

 

1. COUNT()

• COUNT(column명)
• 결과 테이블의 레코드 수를 반환한다.
• NUMBER형 컬럼에서만 사용 가능
• NULL 값은 카운트에서 제외된다.(★★★★★)
• COUNT(*): 모든 레코드의 수 반환 → 일부 컬럼에 NULL 무관(전체 컬럼이 NULL인 레코드는 없을 테니..)

 

COUNT() 사용 예시

SELECT count(*) FROM tblinsa WHERE tel IS NULL;
SELECT count(*) FROM tblinsa WHERE tel IS NOT null;
SELECT count(DISTINCT buseo) FROM tblinsa;
SELECT 
	count(CASE
		WHEN buseo = '기획부' THEN 1
	END) AS 기획부인원수,
	count(CASE
		WHEN buseo = '총무부' THEN 1
	END) AS 총무부인원수,
	count(CASE
		WHEN buseo = '개발부' THEN 1
	END) AS 개발부인원수,
	count(*) AS 총인원,
	count(CASE
		WHEN buseo NOT IN ('기획부', '총무부', '개발부') THEN 1
	END) AS 나머지부서인원수
FROM tblinsa;

 

2. SUM()

• SUM(column명)
• 해당 컬럼의 합을 구한다.
• NUMBER형 컬럼에서만 사용 가능
• ⚠️주의⚠️ SUM(*)은 불가하다.
  → 모든 컬럼의 값을 더하는 건 상식적으로 말이 되지 않는다. WHY? 보통 테이블에 숫자형도 있고 문자형도 있고 날짜형도 있기 때문이다. 만일 숫자형만 있는 테이블이더라도 보통 전체 컬럼의 합은 사용할 수 있는 유효한 결과값이 아니기 때문이다.

 

SUM() 사용 예시

SELECT
    SUM(basicpay) AS "지출 급여 합",
    SUM(sudang) AS "지출 수당 합",
    SUM(basicpay) + SUM(sudang) AS "총 지출",
    SUM(basicpay + sudang) AS "총 지출"
  FROM tblInsa;

-- 문자형의 컬럼은 SUM() 사용 불가 > ORA-01722: invalid number
SELECT SUM(gender) FROM tblComedian;

 

3. AVG()

• AVG(column명)
• 해당 컬럼의 평균값을 구한다.
• NUMBER형 컬럼에서만 사용 가능

 

AVG() 사용 예시

-- 평균 급여?
SELECT AVG(basicpay) FROM tblInsa;
SELECT SUM(basicpay) / COUNT(*) FROM tblInsa;

-- 평균 인구수?
SELECT AVG(population) FROM tblCountry; -- 15588.615384

SELECT SUM(population) / COUNT(population) FROM tblCountry; -- 15588.615384
SELECT SUM(population) / COUNT(*) FROM tblCountry; -- 14475.142857

-- > AVG() 연산 결과는 population 컬럼에서 NULL 행이 제외된 결과와 동일하다.

-- 회사에서 성과금을 지급하는데, 1팀의 공로로 지급한다고 가정하자.
-- 1. 균등 지급: 총지급액 / 모든 직원수 = SUM() / COUNT(*)
-- 2. 차등 지급: 총지급액 / 1팀 직원수 = SUM() / COUNT(1팀) = AVG()

-- 숫자형이 아닌 컬럼은 AVG() 사용 불가
SELECT AVG(name) FROM tblInsa; -- ORA-01722: invalid number
SELECT AVG(ibsadate) FROM tblInsa; -- ORA-00932: inconsistent datatypes: expected NUMBER got DATE

 

4. MAX()

• MAX(컬럼명) → 숫자형, 문자형, 날짜형 모두 적용 가능
  • 문자의 경우 가장 끝 값
  • 날짜의 경우 가장 최신 날짜의 값
• 최댓값 반환

 

5. MIN()

• MIN(컬럼명) → 숫자형, 문자형, 날짜형 모두 적용 가능
  • 문자의 경우 가장 앞의 값
  • 날짜의 경우 가장 과거 날짜의 값
• 최솟값 반환

 

MAX(), MIN() 사용 예시

-- 숫자형
SELECT MAX(sudang), MIN(sudang) FROM tblInsa;

-- 문자형(가나다순 정렬 시 가장 앞/끝에 있는 사람을 찾아라)
SELECT MAX(name), MIN(name) FROM tblInsa;

-- 날짜형(입사 날짜가 가장 큰 값(막내), 가장 작은 값(왕 고참)을 찾아라)
SELECT MAX(ibsadate), MIN(ibsadate) FROM tblInsa;

 

집계 함수 총 정리

SELECT
    COUNT(*) AS 직원수,
    SUM(basicpay) AS 총급여합,
    AVG(basicpay) AS 평균급여,
    MAX(basicpay) AS 최고급여,
    MIN(basicpay) AS 최저급여
  FROM tblInsa;

 

집계 함수 사용 시 주의 사항

1. 컬럼 리스트에서는 집계 함수와 일반 컬럼을 동시에 사용할 수 없다.

: 개인의 데이터 vs 집합의 데이터이기 때문에 동시에 사용 불가

⚠️ORA-00937: not a single-group group function

-- 요구사항) 직원들 이름과 총 직원수를 동시에 가져오시오. -> 불가능
SELECT COUNT(*), basicpay FROM tblinsa; -- ORA-00937: not a single-group group function

 

2. WHERE절에는 집계 함수를 사용할 수 없다.

: 1번과 같은 이유로 집합 데이터와 개인 데이터를 동시에 표현할 수 있는 방법이 없기 때문이다. → 집계 함수(집합) vs 컬럼(개인)

WHERE절은 개개인(레코드)의 데이터를 접근해서 조건을 검색하기 때문에 집합값 호출이 불가하다.

⚠️ ORA-00934: group function is not allowed here

-- 요구사항) 평균 급여보다 더 많이 받는 직원들?
SELECT AVG(basicpay) FROM tblInsa; -- 1556526.66666
SELECT * FROM tblInsa WHERE basicpay >= 1556526.66666;
SELECT * FROM tblInsa WHERE basicpay >= AVG(basicpay); -- ORA-00934: group function is not allowed here

 

*글 작성에 참고한 내용: 학원 쌤의 열정적인 수업