SQL 활용 - 서브쿼리, 뷰, 집합연산자, 그룹함수

서브쿼리

• 하나의 쿼리 안에 존재하는 또 다른 쿼리

• SELECT절에 있는 서브쿼리 : 스칼라 서브 쿼리 Scalar Subquery

• FROM절에 있는 서브쿼리 : 인라인 뷰 Inline View

• WHERE절, HAVING절에 있는 서브쿼리 : 중첩 서브쿼리 Nested Subquery

  
  

  1. 스칼라 서브쿼리 (Scalar Subquery)

• 컬럼대신 오는 서브쿼리.

• 반드시 하나의 값만 반환되어야함. 안그러면 오류 발생.

• 서브쿼리를 이용해 해당 테이블에 없는 컬럼을 추가로 반환할 수 있음.

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

  
  

  2. 인라인 뷰 (Inline View)

• FROM절 등 테이블명이 올 수 있는 위치에 사용.

• 하나의 테이블에서 필요한 컬럼만 골라서 새 테이블처럼 사용할 수 있는 방법.

  SELECT M.PRODUCT_CODE,
  		S.PRODUCT_NAME,
         S.PRIDE,
         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; 

  
  

  3. 중첩 서브쿼리 (Nested Subquery)

• WHERE절, HAVING절에 사용.

• 중첩 서브쿼리는 메인 쿼리와의 관계에 따라 비연관 서브 쿼리, 연관 서브쿼리로 나눌 수 있음.

1) 비연관 서브 쿼리

• 서브 쿼리 내에 메인 쿼리의 컬럼이 존재하지 않음.

SELECT NAME, JOB, BIRTHDAY, AGENCY_CODE
  FROM ENTERTAINER
 WHERE AGENCY_CODE = (SELECT AGNCY_CODE
 						FROM AGENCY
                       WHERE AGENCY_NAME = 'SM엔터테인먼트');

2) 연관 서브 쿼리

• 서브 쿼리 내에 메인 쿼리의 컬럼 존재

SELECT ORDER_NO,
	   DRINK_CODE,
       ORDER_CNT
  FROM CAFE_ORDER A
 WHERE ORDER_CNT = (SELECT MAX(ORDER_CNT) 
 					  FROM CAFE_ORDER B
 					 WHERE B.DRINK_CODE = A.DRINK_CODE);

• 중첩 서브쿼리는 반환하는 데이터 형태에 따라 단일 행, 다중 행, 다중 컬럼 서브 쿼리로 나눌 수 있음.

  1) 단일 행 서브쿼리 : 항상 1건 이하의 결과만 반환

• 단일 행 비교 연산자와 함께 사용 ( =, <, >, <=, >=, <> )
  
  

  2) 다중 행 서브쿼리 : 2건 이상의 행을 반환

• 다중 행 비교 연산자와 함께 사용 ( IN, ALLM ANY, SOME, EXISTS )
  
  

  3) 다중 컬럼 서브쿼리 : 서브 쿼리가 여러 컬럼의 데이터를 반환.

  SELECT * 
   FROM EMPLOYEES
  WHERE (JOB_ID, SALARY) IN (SELECT JOB_ID, MAX_SALARY
                               FROM JOBS
                              WHERE MAX_SALARY = 10000);

  
  
  

뷰 (VIEW)

• 특정 SELECT문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트.
• 가상의 테이블이므로 실제 데이터를 저장을 하지 않고, 해당 데이터를 조회해오는 SELECT문만 가지고 있음.

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;

// 만든 DEPT_MEMBER VIEW를 이용하여 아래와 같이 활용가능.

SELECT DEPARTENT_NAME, COUNT(*)
  FROM DEPT_MEMBER
 GROUP BY DEPARTMENT_NAME
 ORDER BY COUNT(*) DESC;
 
 
 // VIEW 삭제
 DROP VIEW DEPT_MEMBER;

집합 연산자

• UNION ALL : 합집합. 중복 포함.
• UNION : 합집합. 중복 제거.
• INTERSECT : 교집합. 중복 제거.
• MINUS/EXCEPT : 앞에 있는 쿼리의 결과에서 뒤에 있는 쿼리의 결과집합을 뺀 차집합. 중복 제거.

그룹 함수

• 소계(총계) 함수 : ROLLUP, CUBE, GROUPING SETS 등

1. ROLLUP

• ROLLUP(A) : A로 그룹핑 + 총합계.
• ROLLUP(A, B) : A, B로 그룹핑 + A로 그룹핑 + 총합계.
• ROLLUP(A, B, C) : A,B,C로 그룹핑 + A, B로 그룹핑 + A로 그룹핑 + 총합계.

SELECT ORDER_DI, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP (ORDER_DT, ORDER_ITEM, REG_NAME)
 ORDER BY ORDER_DT;
 
 //위는 날짜별(ORDER_DT), 주문음료별(ORDER_ITEM), 판매사원별(REG_NAME)로 그룹핑 + 날짜별, 주문음료별로 그룹핑 + 날짜별로 그룹핑 + 총합계를 구하는 쿼리.
 
 // ROLLUP 내용에 괄호를 어떻게 처리하느냐에 따라도 결과가 달라짐.
SELECT ORDER_DI, ORDER_ITEM, REG_NAME, COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP ((ORDER_DT, ORDER_ITEM), REG_NAME)
 ORDER BY ORDER_DT;
 
//위와 같이 괄호를 추가하면 날짜별로 그룹핑한 ROW가 빠져서 나옴.

2. CUBE

• 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수.
• GROUP BY가 일방향으로 그룹핑하며 소계 구한다면,
• 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로 그룹핑 + 총합계.

3. GROUPING SETS

• 특정항목에 대한 소계를 계산하는 함수.
• 인자값으로 ROLLUP, CUBE 사용 가능.
• 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로 그룹핑 + 총합계.

4. GROUPING

• GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS등과 함께 사용되며 소계를 나타내는 ROW를 구분할 수 있게 해줌. 그룹핑ㄷ의 기준이 되는 컬럼을 제외하고 모두 NULL값으로 표현이 되었으나 GROUPING함수를 이용하면 원하는 위치에 원하는 텍스트를 출력할 수 있음.

SELECT ORDER_DT,
	   GROUPING(ORDER_DT),
	   COUNT(*)
  FROM STARBUCKS_ORDER
 GROUP BY ROLLUP(ORDER_DT)
 ORDER BY ORDER_DT;