SQL 활용 3. 다중 행 함수

SUBQUERY01.txt

0.00MB

SUBQUERY02.txt

0.00MB

DEPT

EMP

---

목차

• 집계함수 (COUNT, MAX, MIN, SUM, AVG)
• 그룹별 쿼리
  • GROUP BY
  • HAVING
  • ORDER BY
  • 그룹함수 (ROLLUP, CUBE, GROUPING SET)
• Top N Query (ROWID / ROWNUM)
  • 순위함수(RANK, DENSE_RANK, ROW_NUMBER)
• 계층형 함수 (START WITH ~ CONNECT BY)

추가 (집합연산)

• UNION : 합집합 (중복 제거)
• UNION ALL : 합집합 (중복 허용)
• INTERSECT : 교집합
• MINUS : 차집합

 

---

집계함수(COUNT, MAX, MIN, SUM, AVG)

 

Q1) EMP 테이블의 ROW(TUPLE) 갯수를 구해보자.

SELECT COUNT(*) FROM EMP;

 

Q2) 커미션이 부과되는 직원의 수를 구해보자.

SELECT COUNT(COMM), COUNT(NVL(COMM, 0)) FROM EMP;

모든 연산에서 NULL 은 제외된다!!

 

Q3) 월급의 최대값, 최소값, 총합, 평균을 구해보자.

SELECT MAX(SAL), MIN(SAL), SUM(SAL), AVG(SAL) FROM EMP;

 

---

그룹별 쿼리

SELECT 컬럼
FROM 테이블명
WHERE 컬럼의 조건
HAVING 그룹조건
GROUP BY 컬럼
ORDER BY 컬럼;

GROUP BY

Q1) 부서별 월급 합계를 구하자.

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

Q1-1) 직원명을 추가하여 (ERROR 인 경우)

SELECT DEPTNO, SUM(SAL), ENAME --ERROR
FROM EMP
GROUP BY DEPTNO;

ENAME 은 한 GROUP 안에서 여러개의 튜플이 나올 수 있기 때문에 ERROR 발생!!

Q1-1) 직원명을 추가하여 (직원명을 GROUP BY 절에 추가)

SELECT DEPTNO, SUM(SAL), ENAME
FROM EMP
GROUP BY DEPTNO, ENAME; --DEPTNO 에서 1차적으로 묶고 이후에 ENAME 으로 2차적으로 묶는다

 

Q2) 직업별 월급 평균을 구하자.

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB;

Q2-1) 30번 부서의 직업별 월급 평균을 구하자 (WHERE 조건으로 가능)

SELECT JOB, AVG(SAL)
FROM EMP
WHERE DEPTNO = 30
GROUP BY JOB;

 

HAVING

• 그룹함수에 조건문을 줄 때는 HAVING 을 이용!

Q1) 30번 부서의 직업별 월급 평균 중 평균이 1500이상인 직업들의 이름과 평균 월급을 출력하자

SELECT JOB, AVG(SAL)
FROM EMP
WHERE DEPTNO = 30 
HAVING AVG(SAL) >= 1500
GROUP BY JOB;

 

Q2) 부서별 월급 합계를 구하자. 단, 월급의 합계가 7000이상인 부서만 출력하자.

SELECT DEPTNO, SUM(SAL)
FROM EMP 
HAVING SUM(SAL) >= 7000
GROUP BY DEPTNO;

 

ORDER BY

• 쿼리 결과를 정렬하기 위해 사용!
• ASC(DEFAULT) = 오름차순  /  DESC = 내림차순

Q1) 사원 테이블에서 월급 순으로 이름과 월급을 출력하자

-- 단 오름차순

SELECT ENAME, SAL
FROM EMP
ORDER BY SAL;	--(ASC) 오름 차순

-- 단 내림차순

SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC;	--내림 차순

 

그룹함수 (ROLLUP, CUBE, GROUPING SET)

ROLLUP 

• 순차적으로 중간합계 출력
• 순서가 바뀌면 결과도 바뀜

ROLLUP 출력 순서

SELECT A, B, COUNT(*)
FROM EMP
GROUP BY ROLLUP(A, B);

A, B, COUNT(*)

UNION ALL

A, NULL, COUNT(*)

UNION ALL

NULL, NULL, COUNT(*)

 

Q1) 직업, 부서 별 봉급의 합을 출력하자.

SELECT JOB, DEPTNO, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(JOB, DEPTNO);

 

CUBE

• 모든 중간합계 출력

CUBE 출력 순서

SELECT A, B, COUNT(*)
FROM TMP
GROUP BY CUBE(A, B);

NULL, NULL, COUNT(*)

UNION ALL

NULL, B, COUNT(*)

UNION ALL

A, NULL, COUNT(*)

UNION ALL

A, B, COUNT(*)

 

Q1) 직업, 부서 별 봉급의 합을 출력하자.

SELECT JOB, DEPTNO, SUM(SAL)
FROM EMP
GROUP BY CUBE(JOB, DEPTNO);

GROUPING SET

• 원하는 결과를 출력하기 위한 셋팅

Q1) 직업, 부서 별 봉급의 합을 출력하자.

SELECT JOB, DEPTNO, SUM(SAL)
FROM EMP e
GROUP BY GROUPING SETS(ROLLUP(JOB, DEPTNO), DEPTNO);

---

Top N Query (ROWID / ROWNUM)

• 식별자로 사용 (수정할 수 없다)

DROP TABLE ROWTEST;
CREATE TABLE ROWTEST(	--TEST TABLE ROWTEST 생성
	NO NUMBER
);
SELECT * FROM ROWTEST;

INSERT INTO ROWTEST VALUES(111);
INSERT INTO ROWTEST VALUES(222);
INSERT INTO ROWTEST VALUES(333);

SELECT ROWNUM, ROWID, NO FROM ROWTEST;

• ROWNUM : 중간 튜플 삭제 시 ROWNUM 은 재생성
• ROWID : 내부적으로 관리를 위해 오라클이 만든 ID, 중간에서 삭제 시 ROWID 는 그대로 유지 

DELETE FROM ROWTEST 
WHERE NO = 222;

SELECT ROWNUM, ROWID, NO FROM ROWTEST;

 

Q1)

SELECT ROWNUM, ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

 

Q2)

SELECT ROWNUM, A.ENAME, A.SAL
FROM(SELECT ROWNUM, ENAME, SAL
	FROM EMP e
	ORDER BY SAL DESC) A
WHERE ROWNUM <= 3;

 

Q3)

SELECT ROWNUM, A.ENAME, A.SAL
FROM(SELECT ROWNUM, ENAME, SAL
	FROM EMP e
	ORDER BY SAL DESC) A
WHERE ROWNUM = 3; --한 튜플만 뽑으면 사실 ROWNUM 은 1로 바뀜

 

Q4)

SELECT B.R, B.ENAME, B.SAL
FROM (SELECT ROWNUM R, A.ENAME, A.SAL
	  FROM (SELECT ROWNUM, ENAME, SAL
		    FROM EMP e
		    ORDER BY SAL DESC) A) B
WHERE B.R = 3;

 

 

순위함수(RANK, DENSE_RANK, ROW_NUMBER) (ORACLE 함수)

 

• RANK : 동일한 값과 동일한 순위
• DENSE_RANK : 동일한 값과 동일한 순위(같은 순위는 하나의 건수로)
• ROW_NUMBER : 동일한 값이라도 고유한 순위

SELECT ENAME, SAL,
	RANK() OVER(ORDER BY SAL DESC) AS RANK,
	DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSERANK,
	ROW_NUMBER() OVER(ORDER BY SAL DESC) AS ROWNM
FROM EMP;

 

---

계층형 함수

SELECT 컬럼

FROM 테이블명

START WITH 계층구조 시작점

CONNECT BY 자식 데이터 조건

 

Q1) EMP 테이블의 계층구조를 출력해보자. (EMPNO 와 MGR 은 서로 계층구조이다.)

 

SELECT LPAD(' ',(LEVEL-1)*2,'-')||ENAME AS 이름, EMPNO, MGR, LEVEL
FROM EMP 
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;

 

---

집합연산

 

UNION : 합집합 (중복 제거)

Q1)

SELECT DEPTNO FROM DEPT
UNION
SELECT DEPTNO FROM EMP;

 

UNION ALL : 합집합 (중복 허용)

Q1)

SELECT DEPTNO FROM DEPT
UNION ALL
SELECT DEPTNO FROM EMP;

 

INTERSECT : 교집합

Q1)

SELECT DEPTNO FROM DEPT
INTERSECT
SELECT DEPTNO FROM EMP;

 

MINUS : 차집합

Q1)

SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;