[SQLD] 2.2.1~3 SQL 활용

서브쿼리

---

• 하나의 SQL 문에 포함돼 있는 또 다른 SQL 문
• 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브 쿼리의 칼럼을 사용할 수 없다.
• 중첩, 스칼라 서브 쿼리에서는 ORDER BY를 사용하지 못한다.
• 조인이 아닌 서브 쿼리를 사용해 결과집합을 원하는 레벨로 생성할 수 있다. p.280
• 단일 행 서브쿼리 : 실행결과가 1건
• 다중 행 서브쿼리 : 실행 결과가 여러건, 다중행 비교 연산자 사용(IN, ALL, ANY ... )
• 다중 컬럼 서브쿼리 : 여러 컬럼 반환

 

비연관 서브쿼리 (Un-Correlated)

• 서브 쿼리가 메인 쿼리 컬럼을 갖지 않는 형태의 서브쿼리
• 메인 쿼리에 값을 제공하기 위한 목적으로 사용한다.

 

연관 서브쿼리 (Correlated)

• 서브쿼리가 메인 쿼리 칼럼을 갖고 있는 형태의 서브 쿼리
• 일반적으로 메인쿼리가 먼저 수행돼 읽혀진 데이터를, 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용

EXISTS

SELECT COL_NM
FROM TBL A
WHERE EXISTS(
	SELECT 1
    FROM TBL B
    WHERE B.COL1 = A.COL1
      AND B.COL2 = 'HELLO'
)

• 외부 쿼리의 모든 행을 돌면서 EXISTS의 결과값이 TRUE인 행들을 반환해준다.

 

뷰 (View)

• 테이블은 실제로 데이터를 가지고 있지만, 뷰는 실제 데이터를 갖고있지 않다.
• 뷰 장점
  • 독립성 : 테이블 구조가 변경돼도, 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
  • 편리성 : 복잡한 질의를 뷰로 생성함으로써, 관련 질의를 단순하게 작성할 수 있다. 또 비슷한 SQL문을 자주 사용할때 뷰를 이용하면 편리하다.
  • 보안성 : 숨기고 싶은 정보가 존재할 때, 뷰를 생성할 때 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

-- VIEW 생성
CREATE VIEW VIEW_NAME AS

	... QUERY ...
    
    EX. SELECT COL_NAME FROM TBL
    
    
    
-- VIEW 삭제
DROP VIEW VIEW_NAME

• 뷰는 테이블 뿐 아니라, 이미 존재하는 뷰를 참조해서 생성할 수 있다.

 

+++ MSSQL

• VIEW MSSQL에서 CTE (Common Table Expression) 과 비슷해 보이지만, VIEW는 DB에 영구적으로 저장된다.
• CTE는 일시적으로 사용하는 결과집합으로, 쿼리 실행이 끝나면 사라진다.

 

집합 연산자

---

• 두 개 이상의 테이블에서 조인을 사용하지 않고, 연관된 데이터를 조회하는 방법
  1. 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합칠 때
  2. 동일 테이블에서 서로 다른 질의를 수행해 하나의 결과로 합칠 때
  3. 튜닝 관점에서 실행 계획을 분리하고자 하라는 목적
• 종류
  • UNION : 합집합 연산, 중복된 행은 하나의 행으로 만든다.
  • UNION ALL : 합집합 연산, 중복된 행도 그대로 표시한다.
  • INTERSECT : 개별 SQL 문의 결과에 교집합 연산을 수행, 중복된 행은 하나의 행으로 만든다.
  • EXCEPT : 개별 SQL문의 결과에 차집합 연산을 수행, 중복된 행은 하나의 행으로 만든다. (ORACLE은 MINUS)

... SELECT SQL1 ...
    
집합연산자
    
... SELECT SQL2 ...

ORDER BY ...

• 집합 연산자의 결과를 표시할 때, SQL1에서 사용된 ALIAS가 적용된다.

 

그룹합수

---

ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음의 세 가지 함수를 정의한다.

• AGGREGATE FUNCTION
  • GROUP FUNCTION의 일부라고 분류할 수 있다.
  • COUNT, SUM 등 각종 집계함수가 포함돼있다.
• GROUP FUNCTION
  • 집계함수를 제외하고,
  • ROLL UP : GROUP BY의 확장된 형태로 병렬로 수행할 수 있다. 계층적 분류를 포함한 데이터 집계에 적합
  • CUBE : 다차원적 집계 생성 가능해 ROLL UP 보다 다양한 데이터를 얻지만, 시스템에 부하를 많이 줄 수 있다.
  • GROUPING SETS : 원하는 부분의 소계만 손쉽게 추출 가능한다.
• WINDOW FUNCTION
  • 데이터 웨어하우스에서 발전한 기능이다.
  • 분석함수
  • 순위함수

 

ROLL UP 함수

• ROLL UP에 지정된 Grouping Columns의 list는 subtotal(부분합계)을 생성하기 위해 사용된다.
• Grouping Columns의 수를 N이라고 했을 때, N+1 Level의 Subtotal이 생성된다.
• ROLL UP의 인수는 계층 구조다. 즉, 인수 순서가 바뀌면 수행결과도 바뀐다! 주의!
• p.309

SELECT A.COL_NM1, B.COL_NM2, COUNT(COST) AS COST
FROM TBL1 A 
LEFT JOIN TBL2 B
    ON A.COL_NM1 = B.COL_NM2
GROUP BY ROLLUP(A.COL_NM1, B.COL_NM2)

COL_NM1	COL_NM2	COST
A	ㄱ	123
A	ㄴ	123
A		246
B	ㄷ	345
B		345
		591

• 이 경우 실행결과 (2+1) Level의 SUBTOTAL이 생성된다.
  • L1 : GROUP BY 수행 시 생성되는 표준 집계
  • L2 : COL_NM1 별 모든 COST의 SUBTOTAL
  • L3 : 마지막행에 GRAND TOTAL (총합)
• ROLL UP은 LEVEL 별 순서로 정렬해준다.
• GROUP BY로 생성되는 표준 집계는 별도의 정렬을 지원하지 않는다.

 

GROUPING 함수

• ROLLUP, CUBE, GROUPING SETS등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가됐다.
• 위의 RULLUP 예시처럼 소계가 계산된 결과에는 GROUPING(COL_NM) 값이 1로 표시된다.
• 그 외의 결과는 0이  표시된다.

SELECT A.COL_NM1, B.COL_NM2, GROUPING(A.COL_NM) AS GRP, COUNT(COST) AS COST
FROM TBL1 A 
LEFT JOIN TBL2 B
    ON A.COL_NM1 = B.COL_NM2
GROUP BY ROLLUP(A.COL_NM1, B.COL_NM2)

COL_NM1	COL_NM2	GRP	COST
A	ㄱ	0	123
A	ㄴ	0	123
A		1	246
B	ㄷ	0	123
B		1	123
		1	591

• CASE, DECODE와 같은 함수와 함께 사용해 여러가지 방법으로 활용할 수 있다.

 

ROLL UP 함수 활용

• p.315
• p.316

 

CUBE 함수

• ROLL UP은 가능한 Subtotal만 생성하지만, CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성한다.
• CUBE를 사용할 때 내부적으론 Grouping Columns의 순서를 바꿔 또 한번의 쿼리를 추가 수행해야한다.
• Grand Total은 양쪽의 쿼리에서 모두 생성되므로, 한 번의 쿼리에서는 제거돼야만 하므로 ROLLUP에 비해 시스템 연산 대상이 많다.
• 시스템에 많은 부담을 주므로 사용에 주의해야 한다.
• CUBE는 표시된 인수들에 대한 계층별 집계를 구할 수 있고, 표시된 인수간에는 ROLL UP과 달리 평등한 관계로 인수의 순서가 바뀌는 경우, 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다.
• CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 컬럼이 표시돼야한다.

SELECT ...
FROM ...
GROUP BY CUBE()
ORDER BY ...

• GROUPING 컬럼 수가 N일 경우, 2^N LEVEL의 Subtotal을 생성한다.
• P.317
• CUBE 사용으로 수행속도, 자원 사용률을 개선하고 가독성을 높일 수 있다.

 

GROUPING SETS

• GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있다.
• ROLL UP과 달리 계층 구조가 아닌 평등한 관계로, 인수 순서가 바뀌어도 결과는 같다.
• 정렬이 필요한 경우는 ORDER BY 를 사용해야 한다.
• p.319

SELECT CASE GROUPING(A.COL_NAME1) WHEN 1 THEN 'ALL COLUMN1' ELSE A.COL_NAME1 END AS COL1
     , CASE GROUPING(B.COL_NAME2) WHEN 1 THEN 'ALL COLUMN2' ELSE B.COL_NAME2 END AS COL2
FROM TBL1 A, TBL2 B
WHERE B.COL1 = A.COL1
GROUP BY GR4OUPING SETS(B.COL_NAME2, A.COL_NAME1)
ORDER BY COL_NAME1, COL_NAME2