오라클 MAX, 그룹함수 AVG, GROUP BY, 서브쿼리, ROWNUM, UNION ALL, DECODE, CASE

환경
윈도우10 64비트
오라클 18c 익스프레스

 

스캇 예제
예제를 확인하시려면

https://ondolroom.tistory.com/163

 

 

MAX

SELECT MAX(SAL), ENAME
FROM EMP;

MAX는 한줄 ENAME는 여러줄 이기 때문에 오류가 난다 

 

 

GROUP BY

SELECT MAX(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;

 

 

-- GROUP BY에 나눌 컬럼을 적고 SELECT에 그룹핑할 컬럼을 적어라
-- 문법은 행의 개수를 맟추는 것
SELECT MAX(SAL), JOB
FROM EMP
GROUP BY JOB;

 

 

-- 그룹을 2가지 이상하면 많은 것을 기준으로 그룹
SELECT MAX(SAL), DEPTNO, ENAME
FROM EMP
GROUP BY DEPTNO, ENAME;

DEPTNO를 그룹으로 만들었으나 ENAME가 양이 더 많기때문에 중복으로 나타남

 

 

-- 부서별 월급의 총합을 구하고 부서를 오름차순으로 정렬 
SELECT SUM(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

 

 

-- 직업별 직원수를 구하라
SELECT COUNT(*), JOB
FROM EMP
GROUP BY JOB;

 

 

-- 직업별 연봉의 평균(+소수점 탈락)
SELECT FLOOR(SUM(SAL)/COUNT(JOB)), JOB
FROM EMP
GROUP BY JOB
HAVING JOB != 'MANAGER';

 

 

HAVING을 쓰는 이유!

--그룹함수(AVG 등)은 무조건 GROUP BY 다음에 써야한다, 그래서 HAVING을 사용
SELECT DEPTNO, AVG(NVL(SAL,0))
FROM EMP
GROUP BY DEPTNO 
HAVING AVG(NVL(SAL,0)) > 2000;

 

서브쿼리

-- 서브쿼리 (셀렉트 문안에 또 셀렉트가 들어가는 것)
SELECT  -- 스칼라 서브쿼리
FROM  -- 인라인뷰 서브쿼리
WHERE   -- ? 서브쿼리

 

인라인뷰 서브쿼리

일단 테이블에서 원하는 자료만 추출한다

SELECT ENAME "EN"
FROM EMP;

해당코드를 FROM( 이곳 )에 넣는다

 

SELECT *
FROM
(
SELECT ENAME "EN"
FROM EMP
)
WHERE EN = 'SMITH';

 

 

ROWNUM

--ROWNUM은 하드디스크에서 가져온 순서대로 번호를 매김

SELECT ROWNUM
FROM GOGAK
WHERE ROWNUM > 0 AND ROWNUM < 9;

 

 

SELECT MAX(POINT)
FROM GOGAK
WHERE ROWNUM > 0 AND ROWNUM < 9;

 

UNION ALL

SELECT MAX(POINT)
FROM GOGAK
WHERE ROWNUM > 0 AND ROWNUM < 9

UNION ALL --합치기

--1번부터 불러오지 않으면 NULL이뜬다
SELECT MAX(POINT)
FROM 
( -- 서브쿼리
  SELECT GNO, POINT, ROWNUM "NO"
  FROM GOGAK
)
WHERE NO > 8 AND NO < 21;

 

위코드를 DECODE나 CASE를 사용해서 만들어본다

DECODE

-- DECODE로 구분하기, DECODE는 부등호를 쓸수 없다 
SELECT GNO, GNAME, POINT, DECODE(NO, 1, 1, 2) "NO" 
FROM 
( 
  SELECT GNO, GNAME, POINT, CEIL(ROWNUM /9) "NO" 
  FROM GOGAK 
);

CASE

-- CASE로 구분하기
SELECT GNO, GNAME, CASE WHEN NO < 9 THEN 1 ELSE 2 END "NO"
FROM
(
  SELECT GNO, GNAME, POINT, ROWNUM "NO"
  FROM GOGAK
);

 

위 코드들이 서브쿼리의 인수가 된다

SELECT NO, MAX(POINT)
FROM
(
  SELECT GNO, GNAME, POINT, DECODE(NO, 1, 1, 2) "NO"
  FROM
  (
    SELECT GNO, GNAME, POINT, CEIL(ROWNUM /9) "NO"
    FROM GOGAK
  )
)
GROUP BY NO;

 

또는

-- 세로연산의 핵심은 그룹을 만드는 것

SELECT MAX(POINT), G
FROM
(
    SELECT point, CASE WHEN rownum < 9 THEN 1 ELSE 2 END "G"
    FROM GOGAK
)
GROUP BY G;

 

 

-- 5명씩 끊어서 POINT의 합을 구하시오
SELECT SUM(POINT)
FROM
(
    SELECT POINT, CEIL(ROWNUM/5) "FIVE"
    FROM GOGAK
)
GROUP BY FIVE;

 

 

-- 같은 나이의 POINT의 합을 구하시오
SELECT SUM(POINT)
FROM
(
    SELECT POINT, SUBSTR(JUMIN, 1, 2) "NAI"
    FROM GOGAK
)
GROUP BY NAI;

 

 

-- 순위매기기
SELECT ENAME, SAL, ROWNUM
FROM
(
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC
);