[Oracle/오라클] 그룹함수 - COUNT, SUM, AVG, MAX, MIN, ROWS , + OVER, group by, having

MIN, MAX

select min(ename), max(ename), min(hiredate), max(hiredate) from emp;

 

 

예제 테이블

SELECT * FROM TB_GRADE;

SELECT

     CLASS_CD,

     MAX(KOR) KOR,

     MAX(ENG) ENG,

     MAX(MAT) MAT

FROM TB_GRADE

GROUP BY CLASS_CD;

 

SELECT

     CLASS_CD,

     STUDENT_NO,

     STUDENT_NM,

     KOR,

     SUM(KOR) OVER(PARTITION BY CLASS_CD) AS KOR_SUM,

     MAX(KOR) OVER(PARTITION BY CLASS_CD) AS KOR_MAX

FROM

     TB_GRADE

ORDER BY

     CLASS_CD, STUDENT_NO;

 

 

1) GROUP BY 절로 CLASS_CD그룹을 만들고 최대값 구하기 

2) MAX(KOR) OVER(PARTITION BY CLASS_CD)

해당레코드의 행에서만 CLASS_CD 필드 그룹을 적용

MAX, MIN, SUM, AVG, COUNT 사용법 동일 

 

AVG

avg의 경우 null을 제외한 값으로 평균

avg(NVL(WEIGHT,0)) 널을 포함하여 평균

select ROUND(avg(WEIGHT), 2) as AVG1, ROUND(avg(NVL(WEIGHT,0)),2) as AVG2 from PLAYER;

기본 테이블 조회 EMP

SELECT deptno, COUNT(*), TRUNC(AVG(sal), 1), MIN(sal), MAX(sal), SUM(sal)

FROM emp GROUP BY deptno;

 

SELECT TEAM_ID, COUNT(*) FROM PLAYER WHERE POSITION ='DF' GROUP BY TEAM_ID ORDER BY COUNT(*) DESC;

SELECT JOB, DEPTNO, COUNT(*), AVG(SAL), SUM(SAL) FROM EMP GROUP BY JOB, DEPTNO;

GROUP 을 HAVING 절로 조건 적용

select

     TEAM_ID,

     ROUND(avg(HEIGHT), 2)

from

     PLAYER

-- WHERE AVG(height) > 180 에서는 그룹연산/함수를 사용할수 X

group by

     TEAM_ID

having avg(HEIGHT) > 180 

order by TEAM_ID;

---

집계함수에 OVER() 추가로 ROW별 집계구하기 

SUM

SELECT ENAME, DEPTNO, SAL, 
SUM(SAL) OVER() "TOTAL_SUM",
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) "DEPT_SUM"
FROM EMP;

OVER(), OVER(PARITION BY) 사용으로 ROW마다 SUM값 출력가능

예제) 

적립횟수 와 적립금 합계 구하기  

SELECT * FROM TB_POINT;

SELECT CUSTOMER_CD,
COUNT(SEQ_NO) AS 적립수,
SUM(POINT) AS 적립금
FROM TB_POINT
GROUP BY CUSTOMER_CD;

AVG, MAX

SELECT ENAME, JOB, SAL, 
ROUND(AVG(SAL) OVER(PARTITION BY JOB), 1) "JOB_AVG",
MAX(SAL) OVER(PARTITION BY JOB) "JOB_MAX"
FROM EMP;

 

ROWS 옵션 - ROW의 계산 범위를 지정

SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM,

- ROWS를 사용하기위해서는 앞단 ORDER BY가 필수

- 앞 1ROW 부터 뒤 1ROW까지의 3ROW 합계 구하기 
SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) SUM2

- 누적 합계

SELECT ENAME, DEPTNO, SAL,
SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM,
SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) SUM2
FROM EMP;