다중행 함수, GROUP BY, HAVING

sum(대상) , count(대상), Max(대상). min(대상), avg(대상)

                데이터개수 세기

 

 

GROUP BY  그룹화할 열

<- 별칭 인식 X. 원본 이름, 식 그대로 써야함!!!

 

HAVING 출력그룹 제한 조건식

(GROUP BY 쓰고나서 조건 작성할 때)

 

 

 

(야매 이해법)WHERE과 차이점은 GROUP BY를 쓰는 경우, HAVING으로 조건 보통 잘 먹힘

 

WHERE: 원본에 있는 것만 인식

HAVING: 함수로 만들어진 새 열 인식

 

 

 

 

WHERE은 원본 테이블에 없는 함수로 새로 만들어진 열을 조건으로 인식하지 못한다.

 

HAVING은 함수로 만들어진 새로운 열 AVG(SAL)을 조건에 활용할 때 적절하다.

WHERE의 SAL은 원래 원본에 있는 열이니까 인식, HAVING의 AVG(SAL)은 새로 함수로 만든 열에 대해 각각 조건 부여 기능 수행.

 

 

 

 

GROUP BY 예제 (SQL은 직접 해봐야 실력이 성장!)

 

SELECT DEPTNO, 
TRUNC(AVG(SAL)) as AVG_SAL,  -- TRUNC 함수로 간단히 소수점 제외
MAX(SAL) as MAX_SAL, 
MIN(SAL) as MIN_SAL,
COUNT(*) as CNT             -- 숫자 세기
  FROM EMP
GROUP BY DEPTNO;     -- GROUP BY 안쓰면 에러

 

SELECT JOB, COUNT(*) from EMP
where COUNT(*) >= 3      -- 조건을 WHERE로 작성하면 오류가 난다.
GROUP BY JOB;            -- COUNT(*)는 원본 표에 없는 열이기 때문이다.


SELECT JOB, COUNT(*) as CNT from EMP      -- 함수로 생성된 열에 이름을 붙여도
where CNT >= 3                            -- where조건으로 오류가나며 출력이 되지 않는다.
GROUP BY JOB;

SELECT JOB, COUNT(*) from EMP
GROUP BY JOB
HAVING COUNT(*) >= 3;       -- WHERE을 지우고 HAVING을 뒤에 쓸 때, 정상 출력

- 원본 EMP에 없는 칼럼에 대한 조건은 HAVING을 쓰자.
- (야매) GROUP BY가 들어가고, WHERE로 조건을 쓸 때 오류가 난다면 HAVING을 써보자.

 

 

 

 

SELECT TO_CHAR(HIREDATE, 'YYYY') as HIRE_YEAR, -- TO_CHAR로 형태 연도 형태 변환을 한다!!!
DEPTNO,
COUNT(*)
from EMP
group by TO_CHAR(HIREDATE, 'YYYY'), DEPTNO;

-- GROUP BY 함수는 별칭 HIRE_YEAR 대신 원본을 써야 인식한다!
-- HIRE_YEAR과 DEPTNO를 같이 그룹화해야 자료 개수 차이로 인한 출력 오류가 없다.
-- DEPTNO가 없으면 DEPTNO는 HIRE_YEAR이 여러번 나와야해서 오류가 생김.

SELECT NVL2(COMM, 'O', 'X') as EXIST_COMM, COUNT(*) as CNT
from EMP
group by NVL2(COMM, 'O', 'X');    -- 따로 having절 없이 NVL2함수로 해결하자.
-- 문자열표시를 위해 작은 따옴표를 잊지 말자.

 

 

GROUP BY 흔한 오류

ora-00979: not a group by expression 

-> GROUP으로 묶는 칼럼에 속한 SELECT의 모든 칼럼들을 GROUP BY에 넣어야 한다.

 

 

 

 

 

 

 

 

 

 

 

 

 

ROLLUP(대상) 함수: 데이터를 말아서(roll) 하나의 값으로 만드는 것.

 

 

 

 

CUBE(대상)함수: ROLLUP 보다 좀 더 상세하게 더 출력

ROLLUP과 달리 JOB별 합계관련 5행이 더 출력된다.

 

 

 

 

 

 

 

본 문제 결과 화면은 빈 hire_year칸이 보여주듯, rollup함수사용을 의도한 문제이다. cube만큼은 자세하지 않으니 rollup을 쓰자.

SELECT DEPTNO, 
TO_CHAR(HIREDATE, 'YYYY') as HIRE_YEAR,
COUNT(*) as CNT,
MAX(SAL) as MAX_SAL, 
SUM(SAL) as SUM_SAL,
AVG(SAL) as AVG_SAL
from EMP
group by ROLLUP(DEPTNO, TO_CHAR(HIREDATE, 'YYYY'));

 

그러나 cube, pivot(행, 열 바꾸기) 함수 등 복잡한 내용들은 SQL보다는 BI 툴을 통해 구현한다.

EX) power BI(ms사)

         Data visualization tool