[SQL기본및활용] 2-2. SQL활용 (1)

---

목차

01. 서브쿼리(Subquery) 02. 뷰(View) 03. 집합 연산자 04. 그룹 함수

---

01. 서브쿼리(Subquery)

• 하나의 쿼리 안에 존재하는 또 다른 쿼리 / 위치에 따라 다음과 같이 나눔
  • SELECT절 : 스칼라 서브쿼리(scala)
  • FROM절 : 인라인 뷰(inline)
  • WHERE절, HAVING절 : 중첩 서브쿼리(nested)
• 스칼라 서브쿼리 : 주로 SELECT절에 위치 / 컬럼 대신 사용되기에 반드시 하나의 값만 반환해야 함(안 그러면 에러나요~~)

SELECT M.PRODUCT_CODE,
	(SELECT S.PRODUCT_NAME
    FROM PRODUCT S
    WHERE S.PRODUCT_CODE = M.PRODUCT_CODE) AS PRODUCT_NAME,
    M.MEMBER_ID, M.CONTENT
FROM PRODUCT_REVIEW M;

• 인라인 뷰 : FROM절 등 테이블명이 올 수 있는 위치에 사용 가능

SELECT M.PRODUCT_CODE, S.PRODUCT_NAME, S.PRICE, M.MEMBER_ID, M.CONTENT
FROM PRODUCT_REVIEW M,
	(SELECT PRODUCT_CODE, PRODUCT_NAME, PRICE
    FROM PRODUCT) S
WHERE M.PRODUCT_CODE = S.PRODUCT_CODE;

• 중첩 서브쿼리
  • WHERE, HAVING절에 사용 가능
    • 비연관 서브쿼리 : 메인쿼리와 관계 X... 서브쿼리 내에 메인 쿼리의 컬럼 존재 X
    • 연관 서브쿼리 : 메인쿼리와 관계 O... 서브쿼리 내에 메인 쿼리의 컬럼 존재
  • 반환 데이터 형태에 따른 분류
    • 단일행 서브쿼리 : 서브쿼리가 1건 이하의 데이터를 반환 / 단일행 비교 연산자와 함께 사용
    • 다중행 서브쿼리 : 서브쿼리가 여러 개의 데이터 반환 / 다중행 비교 연산자와 함께 사용(IN, ALL, ANY, SOME, EXISTS
    • 다중 컬럼 서브쿼리 : 서브쿼리가 여러 컬럼의 데이터 반환

---

02. 뷰(View)

• 특정 SELECT문에 이름을 붙여 재사용이 가능하도록 저장해놓은 오브젝트 / SQL에서 테이블처럼 사용 가능 / 가상테이블... ~> 실제 데이터 저장 않고 해당 데이터를 조회해오는 SELELCT문만 가지고 있음
  • 특징) 보안성 / 독립성 / 편리성

CREATE OR REPLACE VIEW DEPT_MEMBER AS
	SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.FIRST_NAME, B.LAST_NAME
    FROM DEPARTMENTS A
    LEFT OUTER JOIN EMPLOYEES B
    ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;
    
-- 삭제
DROP VIEW DEPT_MEMBER;

---

03. 집합 연산자

• UNION ALL / UNION : UNION 사용 시 DB 내부적으로 중복행 제거하는 과정 거쳐야 하므로 성능상 불리할 수 있음
  • UNION ALL : 쿼리 결과 집합의 합집합... 중복된 행도 그대로 출력
  • UNION : 쿼리 결과 집합의 합집합... 중복된 행은 한 줄로 출력

SELECT * FROM RUNNING_MAN
UNION ALL
SELECT * FROM INFINITE_CHALLENGE;

SELECT * FROM RUNNING_MAN
UNION
SELECT * FROM INFINITE_CHALLENGE;

• INTERSECT : 쿼리1, 2 결과에서 공통된 부분만 중복 제거해 출력

SELECT * FROM RUNNING_MAN
INTERSECT
SELECT * FROM INFINITE_CHALLENGE;

• MINUS / EXCEPT : 쿼리1의 결과에서 쿼리2의 결과 제거 후 출력 (순수 쿼리 1)

SELECT * FROM RUNNING_MAN
MINUS
SELECT * FROM INFINITE_CHALLENGE;

---

04. 그룹 함수

• GROUP BY해서 나타낼 수 있는 데이터를 구하는 함수
  • 집계함수) COUNT, SUM, AVG, MAX, MIN 등
  • 소계(총계)함수) ROLLUP, CUBE, GROUPING SETS 등
• POLLUP : 소그룹 간의 소계, 총계 계산 함수
  • ROLLUP(A) : A로 그룹핑 / 총합계
  • ROLLUP(A, B) : A, B로 그룹핑 / A로 그룹핑 / 총합계
  • ROLLUP(A, B, C) : A, B, C로 그룹핑 / A, B로 그룹핑 / A로 그룹핑 / 총합계

-- 날짜별로 그룹핑 + 총합계가 나옴
SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ROLLUP(ORDER_DT)
ORDER BY ORDER_DT;

/* 날짜, 주문음료, 판매사원별로 그룹핑 + 날짜별,
주문음료별로 그룹핑 + 날짜별로 그룹핑 + 총합계가 나옴 */
SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM, REG_NAME)
ORDER BY ORDER_DT;

-- ROLLUP 괄호 안에 괄호를 또 넣을 수도 있다 - ~!!

• CUBE : 소그룹 간의 소계, 총계를 다차원적으로 계산할 수 있는 함수... 조합할 수 있는 모든 그룹에 대한 소계를 집계함 
  • CUBE(A) : A로 그룹핑 / 총합계
  • CUBE(A, B) : A,B로 그룹핑 / A로 그룹핑 / B로 그룹핑 / 총합계
  • CUBE(A, B, C) : A, B, C / A, B / A, C / B, C / A / B / C 순으로 그룹핑 / 총합계

-- 날짜별로 그룹핑 + 총합계
SELECT ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY CUBE(ORDER_DT)
ORDER_BY ORDER_DT;
-- 요기까진 롤업 함수와 결과가 같다!

-- 날짜별, 주문음료별로 그룹핑 + 날짜별로 그룹핑 + 주문음료별로 그룹핑 + 총합계
SELECT ORDER_DT, ORDR_ITEM, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY CUBE(ORDER_DT, ORDER_ITEM)
ORDER_BY ORDER_DT;
-- 결과적으로 아래 쿼리들과 같은 의미
SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT, ORDER_ITEM
UNION ALL
SELECT ORDER_DT, NULL, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT
UNION ALL
SELECT NULL, ORDER_ITEM, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ORDER_ITEM
UNION ALL
SELECT NULL, NULL, COUNT(*)
FROM STARBUKS_ORDER
ORDER BY 1, 2;

• GROUPING SETS : 특정 항목에 대한 소계 계산 / 인자값으로 롤업이나 큐브 사용 가능
  • GROUPING SETS(A, B) : A로 그룹핑 / B로 그룹핑
  • GROUPING SETS(A, B, ()) : A로 그룹핑 / B로 그룹핑 / 총합계
  • GROUPING SETS(A, ROLLUP(B)) : A로 그룹핑 / B로 그룹핑 / 총합계
  • GROUPING SETS(A, ROLLUP(B, C)) : A로 그룹핑 / B, C로 그룹핑 / B로 그룹핑 / 총합계
  • GROUPING SETS(A, B, ROLLUP(C)) : A로 그룹핑 / B로 그룹핑 / C로 그룹핑 / 총합계

-- 날짜별로 그룹핑 + 주문음료별로 그룹핑
SELECT ORDER_DT, ORDER_ITEM, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY GROUPING SETS(ORDER_DT, ORDER_ITEM)
ORDER BY ORDER_DT;
-- 여기에 총계 더하려면 인자값에 ()나 롤업 적용하면 됨!

• GROUPING : ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이고 소계를 나타내는 로를 구분할 수 있게 해줌... 앞선 함수들은 그룹핑의 기준이 되는 컬럼을 제외하고는 모두 널값으로 출력하지만, GROUPING함수 이용하면 원하는 위치에 원하는 텍스트 출력 가능!

-- CASE문을 이용해 원하는 텍스트 출력
SELECT CASE GROUPING(ORDER_DT) WHEN 1 THEN 'TOTAL' ELSE ORDER_DT
    END AS ORDER_DT, COUNT(*)
FROM STARBUKS_ORDER
GROUP BY ROLLUP(ORDER_DT)
ORDER BY ORDER_DT;

-- 오라클의 경우 DECODE문으로 CASE문 대체 가능
SELECT DECODE(GROUPING(ORDER_DT), 1, 'TOTAL', ORDER_DT) AS ORDER_DT, COUNT(*)
FROM STARBUCKS_ORDER
GROUP BY ROLLUP(ORDER_DT)
ORDER BY ORDER_DT;

---