MLP) [Oracle] SQL 함수 - 그룹 함수

그룹 함수

단일행 함수와 달리 그룹 함수는 여러 행 또는 테이블 전체에 함수가 적용되어 하나의 결과를 가져오는 함수를 의미한다.

 

 

그룹 함수의 종류

1. AVG(DISTINCT | ALL | N): null값을 제외한 n개 행의 평균값
2. COUNT(DISTINCT | ALL | expr | *) : null값을 포함한 행의 개수
3. MAX(DISTINCT |  ALL | expr) : 최대값
4. MIN (DISTINCT |  ALL | expr): 최솟값
5. STDDEV(DISTINCT| ALL| n) : null값을 제외한 n의 표준편차
6. SUM(DISTINCT | ALL | n) :  null값을 제외한 n의 합계
7. VARIANCE(): null값을 제외한 n의 분산

• DISTINCT는 중복되지 않는 값만 반환한다. 그러나 ALL은 모든 값을 반환한다.
• expr이 있는 인수들의 자료형태는 CHAR, VARCHAR2, NUMBER, DATE형이 될 수 있다.
• COUNT(*)를 제외한 모든 그룹 함수들은 null값을 무시한다. null값을 하나의 값으로 치환하기 위해서는 NVL함수를 사용한다.
• 모든 자료형에 대해 MAX와 MIN을 사용할 수 있다. 그러나 AVG, SUM, VARIANCE, STDDEV는 NUMBER만 사용 가능하다. 

 

COUNT

• COUNT (컬럼명): null값을 무시하고 카운트를 센다.
• COUNT ( * ): null값도 포함하여 카운트를 센다.

EMP 테이블

SELECT COUNT(MGR) "관리자가 있는 사원수", COUNT(COMM) "보너스를 받는 사원수" FROM EMP;
SELECT COUNT(DISTINCT MGR) "관리자수" FROM EMP;
SELECT COUNT(EMPNO) FROM EMP;
SELECT COUNT(*) FROM EMP;

---

 

TEST 테이블

SELECT COUNT(A) FROM TEST; -- 0
SELECT COUNT(*) FROM TEST; -- 3

 

 

[실습]

--emp테이블에서 모든 SALESMAN에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하세요.
SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM emp
WHERE job = 'SALESMAN';

--emp테이블에 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수,
--보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하세요.
SELECT COUNT(*), COUNT(comm), AVG(comm), COUNT(DISTINCT deptno)
FROM emp;

 

 

---

GROUP BY

어순: SF - WGHO
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

• 특정 컬럼이나 값 등을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용하는 구문. 그룹 함수와 함께 사용한다.
• 상품 테이블에서 각 상품군별로 몇 개의 상품이 있는지 검사하거나, 직업군별로 최대, 최소값을 구할 때 사용됨
• GROUP BY 절에 명시된 컬럼값이 같을 때 그룹을 만들고 이 그룹으로부터 집합함수를 통해 다양한 결과를 얻는다.
• GROUP BY 절에서는 그룹함수와 GROUP BY에서 사용한 컬럼만 SELECT할 수 있음에 유의하자.

 

MEMBER 테이블

SELECT JOB, COUNT(*)
FROM MEMBER
GROUP BY JOB
ORDER BY COUNT(*) DESC;

 

[실습]

--고객 테이블에서 직업의 종류, 각 직업에 속한 최대 마일리지 정보를 보여주세요.
SELECT job, MAX(mileage)
FROM member
GROUP BY job
ORDER BY 2 DESC;

--상품 테이블에서 각 상품 카테고리별로 총 몇 개의 상품이 있는지 보여주세요.
SELECT category_fk, COUNT(*)
FROM products
GROUP BY category_fk
ORDER BY 1;

--상품 테이블에서 각 공급업체 코드별(EP_CODE_FK)로 공급한 상품의 평균입고가를 보여주세요.
SELECT ep_code_fk, TO_CHAR(AVG(input_price), 'L9,999,999') AS 평균입고가
FROM products
GROUP BY ep_code_fk;

--사원 테이블에서 입사한 년도별로 사원 수를 보여주세요.
SELECT TO_CHAR(hiredate, 'YY') AS year, COUNT(*)
FROM emp
GROUP BY TO_CHAR(hiredate, 'YY')
ORDER BY year;

--사원 테이블에서 해당년도 각 월별로 입사한 사원수를 보여주세요.
SELECT TO_CHAR(hiredate, 'YYYY-MM') AS month, COUNT(*) AS 사원수
FROM emp
GROUP BY TO_CHAR(hiredate, 'YYYY-MM')
ORDER BY 1;

--사원 테이블에서 업무별 최대 연봉, 최소 연봉을 출력하세요.
SELECT job, MAX(sal*12+NVL(comm,0)) "최대 연봉", MIN(sal*12+NVL(comm,0)) "최소 연봉"
FROM emp
GROUP BY job
ORDER BY 2;

 

 

---

HAVING

주로 GROUP BY와 함께 사용하는 구문으로, GROUP BY의 결과에 조건을 주어 제한할 때 사용한다.

--고객 테이블에서 직업의 종류와 각 직업에 속한 사람의 수가 2명 이상인 직업군의 정보를 보여주시오.
SELECT * FROM MEMBER ORDER BY JOB;
SELECT JOB, COUNT(*)
FROM MEMBER
GROUP BY JOB
HAVING COUNT(*)>1;

--고객 테이블에서 직업의 종류와 각 직업에 속한 최대 마일리지 정보를 보여주세요.
--단, 직업군의 최대 마일리지가 0인 경우는 제외시킵시다.
SELECT JOB, MAX(MILEAGE) AS "최대 마일리지"
FROM MEMBER
GROUP BY JOB
HAVING MAX(MILEAGE)<>0
ORDER BY 2;