DB#CH05. 그룹 쿼리와 집합 연산자 이해

1. 기본 집계 함수

2. GROUP BY 절과 HAVING 절

3. ROLLUP 절과 CUBE 절

4. 집합 연산자

 

1. 기본 집계 함수

- COUNT(expr)

- SUM(expr)

- AVG(expr)

- MIN(expr) & MAX(expr)

- VARINACE(expr), STDDEV(expr)

 

2. GROUP BY 절과 HAVING 절

- SELECT 리스트에 있는 컬럼명이나 표현식 중 집계 함수를 제외하고는 모두 GROUP BY절에 명시해야한다.

- HAVING절은 GROUP BY절 다음에 위치. GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 수행함. 반드시 GROUP BY 뒤에 있어야함.

3. ROLLUP 절과 CUBE 절

- ROLLUP(expr1, expr2, ...)

 

4. 집합 연산자

- UNION : 합집합

- UNION ALL : 중복된 항목도 모두 조회되는 합집합

- MINUS : 차집합

  * 집합 연산자의 제한 사항

     > 집합 연산자로 연결되는 각 SELECT문의 SELECT 리스트의 개수와 데이터 타입은 일치해야 함.

     > 집합 연산자로 SELECT문을 연결할 때 ORDER BY절은 맨 마지막 문장에서만 사용가능.

     > CLOB (4G CHAR) 타입 컬럼에 대해서는 집합 연산자를 사용할 수 없다.

- GROUPING SETS절

 

>SQLGATE

SELECT COUNT(a.MANAGER_ID) -- NULL은 COUNT에서 제외됨
	FROM EMPLOYEES a
;

--DESC EMPLOYEES;

-- COUNT 하려는 컬럼에 NULL 유무를 확인해야함
SELECT COUNT(*) -- 전체 column을 따져본다 => 효과적 x
	FROM EMPLOYEES b
;

-- COUNT할 때 NULL이 없는 column을 선택하는 것이 좋다.
-- NULL 없는 column 선택 : NN, PK 칼럼

SELECT a.EMP_NAME, a.MANAGER_ID
		FROM EMPLOYEES a
 WHERE a.MANAGER_ID IS NULL -- NULL이 누군지 확인. 컬럼에 어떻게 부합해서 쓰이는지 파악해야함
;

DESC EMPLOYEES;

SELECT a.EMP_NAME, a.DEPARTMENT_ID
		FROM EMPLOYEES a
 WHERE a.DEPARTMENT_ID IS NULL
;

SELECT *
		FROM EMPLOYEES a
 WHERE a.DEPARTMENT_ID IS NULL
;

DESC EMPLOYEES;

SELECT COUNT(DISTINCT a.DEPARTMENT_ID)
  FROM EMPLOYEES a
;
DESC DEPARTMENTs;
-- 부서테이블 : departments
SELECT COUNT(a.DEPARTMENT_ID)
  FROM DEPARTMENTS a
;

SELECT DISTINCT a.DEPARTMENT_ID
  FROM EMPLOYEES a
 ORDER BY 1
;

/*
=================================
기간, 지역, 대출 잔액 데이터
1. 기간 : PERIOD => 7개
201111 201112 201210 201211 201212 201310 201311
2. 지역 : REGION => 17개, 시도까지
3. 구분 : GUNUN => 2개
기타대출, 주택담보대출
4. 대출 잔액 : LOAN_JAN_AMT, 단위 => 십억 (1000, 조)
전체 ROW : 238
===================================
*/
-- SELECT는 GROUP BY에서 만들어진 테이블에서 임시테이블을 생성할 뿐임
-- GROUP BY의 핵심! 임시 테이블!!!
-- 문제 : 2013년 지역별 가계대출 총 잔액
SELECT a.REGION
     , SUM(a.LOAN_JAN_AMT) AS tot_jan
  FROM KOR_LOAN_STATUS a
 WHERE a.PERIOD LIKE '2013%'
 GROUP BY a.REGION
 ORDER BY a.REGION
;

-- 문제 : 2013년 11월 지역별 총 잔액
SELECT a.PERIOD
      , a.REGION
     , SUM(a.LOAN_JAN_AMT) AS tot_jan
  FROM KOR_LOAN_STATUS a
 WHERE a.PERIOD = '201311'
 GROUP BY a.REGION, a.PERIOD
;

-- 문제 : 201311, 지역별 총 잔액, 100조 이상
-- 갯수로 확인이 안 된다. 값이 문제기 때문.
-- 2013년 경기도를 SUM해서 testing
SELECT a.REGION, SUM(a.LOAN_JAN_AMT) AS tot_jan
  FROM KOR_LOAN_STATUS a
 WHERE a.PERIOD = '201311'
 GROUP BY a.REGION
 HAVING SUM(a.LOAN_JAN_AMT) > 100000 -- a.LOAN_JAN_AMT는 HAVING에서 사용X, ORDER BY에서 사용O
 ORDER BY tot_jan
;

-- 문제: 2013년 기간별, 구분별 대출 총 잔액
-- 2013년 전체데이터 필터링 -> where
-- 기간, 구분 => group by
-- 기간 정렬
SELECT a.PERIOD
			,a.GUBUN
			,SUM(a.LOAN_JAN_AMT) AS tot_jan
		FROM KOR_LOAN_STATUS a
	WHERE a.PERIOD LIKE '2013%'
	GROUP BY ROLLUP(a.PERIOD, a.GUBUN)
	ORDER BY a.PERIOD ASC
;

SELECT a.PERIOD
			,a.GUBUN
			,SUM(a.LOAN_JAN_AMT) AS tot_jan
		FROM KOR_LOAN_STATUS a
	WHERE a.PERIOD LIKE '2013%'
	GROUP BY a.PERIOD, a.GUBUN
	ORDER BY a.PERIOD ASC
;

SELECT a.PERIOD
			,a.GUBUN
			,SUM(a.LOAN_JAN_AMT) AS tot_jan
		FROM KOR_LOAN_STATUS a
	WHERE a.PERIOD LIKE '2013%'
	GROUP BY a.PERIOD, a.GUBUN
	ORDER BY a.GUBUN
;

SELECT a.PERIOD
			,a.GUBUN
			,SUM(a.LOAN_JAN_AMT) AS tot_jan
		FROM KOR_LOAN_STATUS a
	WHERE a.PERIOD LIKE '2013%'
	GROUP BY a.PERIOD, a.GUBUN
	ORDER BY tot_jan DESC
;

SELECT a.PERIOD
     , a.GUBUN
     , SUM(a.LOAN_JAN_AMT) AS tot_jan
		FROM KOR_LOAN_STATUS a
	WHERE a.PERIOD LIKE '2013%'
	GROUP BY a.PERIOD, ROLLUP(a.GUBUN)
;

SELECT a.PERIOD
			,a.GUBUN
			,SUM(a.LOAN_JAN_AMT) AS tot_jan
		FROM KOR_LOAN_STATUS a
 	WHERE a.PERIOD LIKE '2013%'
 	GROUP BY ROLLUP(a.PERIOD), a.GUBUN
;

CREATE TABLE exp_goods_asia(
  country VARCHAR2(10)
  ,seq number,
  goods VARCHAR2(80));

INSERT INTO exp_goods_asia VALUES ('한국', 1, '원유제외 석유류');
INSERT INTO exp_goods_asia VALUES ('한국', 2, '자동차');
INSERT INTO exp_goods_asia VALUES ('한국', 3, '전자집적회로');
INSERT INTO exp_goods_asia VALUES ('한국', 4, '선박');
INSERT INTO exp_goods_asia VALUES ('한국', 5, 'LCD');
INSERT INTO exp_goods_asia VALUES ('한국', 6, '자동차 부품');
INSERT INTO exp_goods_asia VALUES ('한국', 7, '휴대전화');
INSERT INTO exp_goods_asia VALUES ('한국', 8, '환식탄화수소');
INSERT INTO exp_goods_asia VALUES ('한국', 9, '무선송신기 디스플레이 부속품');
INSERT INTO exp_goods_asia VALUES ('한국', 10, '철 또는 비합금강');

INSERT INTO exp_goods_asia VALUES('일본',1,'자동차');
INSERT INTO exp_goods_asia VALUES('일본',2,'자동차 부품');
INSERT INTO exp_goods_asia VALUES('일본',3,'전자집적회로');
INSERT INTO exp_goods_asia VALUES('일본',4,'선박');
INSERT INTO exp_goods_asia VALUES('일본',5,'반도체웨이퍼');
INSERT INTO exp_goods_asia VALUES('일본',6,'화물차');
INSERT INTO exp_goods_asia VALUES('일본',7,'원유제외 석유류');
INSERT INTO exp_goods_asia VALUES('일본',8,'건설기계');
INSERT INTO exp_goods_asia VALUES('일본',9,'다이오드, 드랜지스터');
INSERT INTO exp_goods_asia VALUES('일본',10,'기계류');

COMMIT;

/**********************************
* 한국과 일본의 주요 10대 수출품 데이터
* 경쟁 제품, 우위 제품
***********************************/
-- 한국 주요 수출품
SELECT a.SEQ, a.GOODS
		FROM exp_goods_asia a
	WHERE a.COUNTRY = '한국'
	ORDER BY a.SEQ
;

--일본
SELECT a.SEQ, a.GOODS
 FROM exp_goods_asia a
 WHERE a.COUNTRY = '일본'
 ORDER BY a.SEQ
;

--오타 수정
UPDATE exp_goods_asia
	 SET goods = '다이오드, 트랜지스터'
 WHERE country = '일본'
   AND seq = 9;

UPDATE exp_goods_asia
	 SET goods = '자동차부품'
 WHERE country = '일본'
 	 AND seq = 2;

-- 두 나라의 주요 수출품은 무엇인가? => 16개
SELECT goods
	FROM exp_goods_asia
	WHERE country = '한국'
UNION -- 두개의 집합을 합칠 때 사용
SELECT goods
	FROM exp_goods_asia
	WHERE country = '일본'
; -- 4개의 품목이 겹침

SELECT goods
	FROM exp_goods_asia
	WHERE country = '한국'
UNION ALL -- 차집합(겹치는 부분)까지 모두 나타냄 => 사용할 이유가 없음
SELECT goods
	FROM exp_goods_asia
	WHERE country = '일본'
;

-- 겹치는 상품은 무엇인가? => 5개
--result: 선박 원유제외 석유류 자동차 자동차부품 전자집적회로
SELECT goods
	FROM exp_goods_asia
	WHERE COUNTRY = '한국'
INTERSECT
SELECT goods
	FROM exp_goods_asia
	WHERE COUNTRY = '일본'

-- 한국이 우위인 상품은 무엇인가? => 5개
-- LCD 무선송신기 디스플레이부속품 철또는비합금강 환식탄화수소 휴대전화
SELECT goods
	FROM exp_goods_asia
	WHERE COUNTRY = '한국'
MINUS
SELECT goods
	FROM exp_goods_asia
	WHERE COUNTRY = '일본'
;

-- 일본이 우위인 상품은 무엇인가? => 5개
-- 선박 원유제외석유류 자동차 자동차부품 전자집적회로
SELECT goods
	FROM exp_goods_asia
	WHERE COUNTRY = '일본'
INTERSECT
SELECT goods
	FROM exp_goods_asia
	WHERE COUNTRY = '한국'