Part2 - Ch2. SQL 활용

01) 서브쿼리 (Subquery)

• 하나의 쿼리 안에 존재하는 또 다른 쿼리
• 위치에 따라 다음과 같이 나눌 수 있다.
  • SELECT 절 - 스칼라 서브쿼리
  • FROM 절 - 인라인 뷰
  • WHERE 절, HAVING 절 - 중첩 서브쿼리

메인쿼리의 컬럼이 포함된 서브쿼리를 연관 서브쿼리
메인쿼리의 컬럼이 포함되지 않은 서브쿼리를 비연관 서브쿼리

---

• 예제

SELECT A.FIRST_NAME, 
	   A.LAST_NAME,
       (SELECT B.DEPT_NAME
       	  FROM DEPT B
        WHERE B.DEPT_ID=A.DEPT_ID) AS DEPT_NAME
 FROM EMP A;

 

• 실행 순서

1. FROM EMP A; 외부 쿼리가 EMP A에서 첫 번째 행을 읽어옵니다.
2. 그 행의 A.DEPT_ID 값을 보고,
3. 내부 서브쿼리(SELECT B.DEPT_NAME …)를 실행하여
   1. DEPT 테이블에서 B.DEPT_ID = (그 직원의 A.DEPT_ID)인 행을 찾습니다.
   2. 일치하는 행이 있으면 DEPT_NAME 하나를 반환, 없으면 NULL.
4. 반환된 DEPT_NAME을 외부 쿼리 결과의 한 컬럼으로 합칩니다.
5. 같은 과정을 EMP의 다음 행마다 반복합니다.

• 서브쿼리 버전과 LEFT OUTER JOIN 버전이 동일한 결과가 나오는 이유
  • 둘 다 EMP 테이블의 모든 행을 기준으로 삼고, 각 행의 DEPT_ID에 대응하는 DEPT 테이블의 DEPT_NAME을 “있으면 붙이고, 없으면 NULL”로 채워 주는 방식이기 때문.

---

 

1. 스칼라 서브쿼리

• 주로 SELECT 절에 위치
• 컬럼이 올 수 있는 대부분 위치에 사용 가능
• 컬럼 대신 사용됨 → 반드시 하나의 값만을 반환. 아니면 ERROR

2. 인라인 뷰

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

3. 중첩 서브쿼리

• WHERE 절과 HAVING 절에 사용 가능
• 메인쿼리와의 관계에 따라
  • 비연관 서브쿼리; 메인쿼리와 관계를 맺고 있지 않음
  • 연관 서브쿼리; 메인쿼리와 관계를 맺고 있음

비연관 서브쿼리 : 서브쿼리 내에 메인쿼리의 컬럼이 존재 X
연관 서브쿼리 : 서브쿼리 내에 메인쿼리의 컬럼이 존재 O

 

• 중첩 서브쿼리는 반환하는 데이터 형태에 따라 다음과 같이 나뉨.

단일 행 서브쿼리	- 서브쿼리가 1건 이하의 데이터를 반환 - 단일 행 비교 연산자와 함께 사용 ex. =, <, >, <=, >=, <>
다중 행 서브쿼리	- 서브쿼리가 여러 건의 데이터 반환 - 다중 행 비교 연산자와 함께 사용 - '=' 조건과 함께 쓰일 수 없음. ex. IN, ALL, ANY, SOME, EXISTS
다중 컬럼 서브쿼리	- 서브쿼리가 여러 컬럼의 데이터를 반환

• 단일 행 서브쿼리 : 항상 1건 이하의 결과 반환
• 다중 행 서브쿼리 : 2건 이상의 행 반환
• 다중 컬럼 서브쿼리

02) 뷰(View)

• 특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트
• 주의) 뷰는 가상 테이블이라는 점.
  • 실제 데이터를 저장하지는 않고 해당 데이터를 조회해오는 SELECT 문만 갖고 있음.
• 특징
  • 보안성 - 보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안 유지
  • 독립성 - 테이블 스키마가 변경되었을 경우 애플리케이션은 변경하지 않고 관련 뷰만 수정
  • 편리성 - 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용할 수 있음.

03) 집합 연산자

• 집합 연산자는 각 쿼리의 결과 집합을 가지고 연산을 하는 명령어

UNION ALL	- 각 쿼리의 결과 집합의 합집합. - 중복된 행도 그대로 출력.
UNION	- 각 쿼리의 결과 집합의 합집합. - 중복된 행은 한 줄로 출력.
INTERSECT	- 각 쿼리의 결과 집합의 교집합. - 중복된 행은 한 줄로 출력.
MINUS/EXCEPT	- 앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합. - 중복된 행은 한 줄로 출력.

 
1. UNION ALL / UNION

• UNION ALL
  • QUERY1의 결과와 QUERY2의 결과를 그대로 합하는 것
  • 중복된 행도 그대로 출력
• UNION
  • QUERY1의 결과와 QUERY2의 결과를 합한 후 중복 제거하여 출력
• 각 쿼리의 결가 집합이 합집합에 중복된 행이 없을 때는 UNION ALL과 UNION 모두 같은 결과를 도출하지만,
• UNION을 사용할 때 데이터베이스 내부적으로 중복된 행을 제거하는 과정을 거쳐야하므로, 성능상 불리할 수 있음.

2. INTERSECT

• QUERY1의 결과와 QUERY2의 결과에서 공통된 부분만 중복을 제거하여 출력
• 헤더 값은 첫 번째 쿼리를 따라감.

3. MINUS / EXCEPT

• QUERY1의 결과에서 QUERY2의 결과를 제거하고 출력

04) 그룹 함수

• 데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수
• 역할에 따라
  • 집계 함수 - COUNT, SUM, AVG, MAX, MIN 등
  • 소계(총계) 함수 - 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로 그룹핑 -총합계

 

2. CUBE

• 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수
• GROUP BY가 일방향으로 그룹핑하며 소계를 구했다면, CUBE는 조합할 수 있는 모든 그룹에 대한 소계를 집계
• ROLLUP 함수에 비해 시스템에 많은 부하를 발생시키므로 주의해서 사용해야 함.

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로 그룹핑 - 총합계

• ROLLUP 함수는 인수의 순서에 따라 결과가 달라지며,
• CUBE 함수와 GROUPING SETS 함수는 인수의 순서가 바뀌어도 같은 결과를 출력한다.

4. GROUPING

• ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며, 소계를 나타내는 Row를 구분할 수 있게 해줌.
• 그동안 공부한 것은 소계를 나타내는 Row에서 그룹핑의 기준이 되는 컬럼을 제외하고는 모두 NULL 값으로 표현했지만,
• GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트를 출력할 수 있음.
• 소계가 계산된 행에서 결과값 1을 출력하는 함수 (↔GROUPING SETS는 인수들에 대한 개별 집계를 구하는 함수)

GROUP BY
하나 이상의 컬럼을 기준으로 집계(aggregation)를 수행하는 기본 구문이고,

GROUPING SETS
“하나의 쿼리 안에서 여러 개의 그룹핑 조합(소계·총계 등)을 동시에 수행”할 수 있게 해 주는 GROUP BY의 확장 기능.

05) 윈도우 함수

• OVER 키워드와 함께 사용
• 역할에 따라
  • 순위 함수 - RANK, DENSE_RANK, ROW_NUMBER
  • 집계 함수 - SUM, MAX, MIN, AVG, COUNT
  • 행 순서 함수 - FIRST_VALUE, LAST_VALUE, LAG, LEAD
  • 비율 함수 - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

1. 순위 함수

• RANK
  • 1, 2, 2, 4, 5, 5, 7 ...
  • 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뜀.
• DENSE_RANK
  • 1, 2, 2, 3, 4, 4, 5 ...
  • 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매김.
  • DENSE가 사전적 의미로 '밀집한'이라는 뜻을 가지고 있으므로 '순위가 밀집되어 있다' 라고 기억
• ROW_NUMBER (↔ ROWNUM)
  • 1, 2, 3, 4, 5, 6, 7 ...  
  •  순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여

2. 집계 함수

• SUM
  • 데이터의 합계 구하는 함수
  • 인자값 : 숫자형만 가능
  • SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주면 RANGE UNBOUNDED PRECEDING 구문이 없어도 누적합이 집계
  • RANGE 옵션은 동일한 데이터가 있을 경우 모두 합한 값 출력

SUM(SALARY) OVER(ORDER BY SALARY) AS SUM1
윈도우 함수를 이용해서 각 행마다 누적 합계를 계산한 뒤, 그 결과를 SUM1이라는 컬럼으로 보여주겠다는 의미

SUM(SALARY)
일반적인 집계 함수처럼 전체 합계를 계산하는 것이 아니라,

OVER(ORDER BY SALARY)
“급여(SALARY)가 작은 순서부터 지금 행까지” 누적해서 합계를 구하라는 창(Window) 정의
윈도우 함수에서 OVER 절로 “어느 범위(Window)” 에서 계산할지를 지정
ORDER BY SALARY는 “급여 오름차순으로”라는 정렬 기준이자 누적 경계

• 둘 다 “동일한 SALARY 값(피어)을 포함해서 값 기준으로 누적합” 을 계산

SUM(SALARY) OVER(ORDER BY SALARY RANGE UNBOUNDED PRECEDING) AS SUM2
윈도우 함수를 쓸 때, 프레임(frame) 정의를 명시적으로 RANGE 방식으로 지정한 형태

OVER(ORDER BY SALARY …)
윈도우를 “SALARY 오름차순”으로 정렬

RANGE UNBOUNDED PRECEDING
프레임을 “첫 번째(가장 작은) SALARY 행부터 ‘현재 행의 SALARY 값’까지의 값 범위(RANGE)”로 설정

SUM(SALARY)
그 프레임에 포함된 모든 행의 SALARY 합계를 계산 AS SUM2 결과 컬럼 이름을 SUM2로.

핵심 차이: ROWS vs RANGE

1. ROWS UNBOUNDED PRECEDING
물리적(행) 순서 기준으로 “처음 행부터 현재 행”까지 누적

2. RANGE UNBOUNDED PRECEDING
값(이 예에서는 SALARY) 기준으로 “가장 작은 값부터 현재 행의 SALARY 값까지”인 모든 동일값(피어, peers) 포함

 

•  MAX
  • 데이터의 최댓값을 구하는 함수
• MIN
  • 데이터의 최솟값을 구하는 함수
• AVG
  • 데이터의 평균값 구하는 함수

범위	의미
UNBOUNDED PRECEDING	위쪽 끝 행
UNBOUNDED FOLLOWING	아래쪽 끝 행
CURRENT ROW	현재 행
n PRECEDING	현재 행에서 위로 n만큼 이동
n FOLLOWING	현재 행에서 아래로 n만큼 이동

기준	의미
ROWS	행 자체가 기준
RANGE	행이 가지고 있는 데이터 값이 기준

 

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 처음부터 현재 행까지
  • RANGE UNBOUNDED PRECEDING과 같은 의미
• RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
  • 현재 행이 가지고 있는 값보다 10만큼 적은 행부터 현재 행까지
  • RANGE 10 PRECEDING과 같은 의미
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • 현재 행부터 끝까지
• ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
  • 현재 행부터 아래로 5만큼 이동한 행까지

---

• COUNT
  • 데이터의 건수를 구하는 함수

3. 행 순서 함수

• FIRST_VALUE
  • 파티션별 가장 선두에 위치한 데이터를 구하는 함수
  • SQL Server(MSSQL)에서는 지원X
• LAST_VALUE
  • 파티션별 가장 끝에 위치한 데이터를 구하는 함수
  • SQL Server(MSSQL)에서는 지원X
• LAG
  • 파티션별로 특정 수만큼 앞선 데이터를 구하는 함수
  • SQL Server(MSSQL)에서는 지원X
• LEAD
  • 파티션별 특정 수만큼 뒤에 있는 데이터를 구하는 함수
  • SQL Server(MSSQL)에서는 지원X

4. 비율 함수

• RATIO_TO_REPORT
  • 파티션별 합계에서 차지하는 비율 구하는 함수
  • SQL Server(MSSQL)에서는 지원X
• PERCENT_RANK
  • 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
  • SQL Server(MSSQL)에서는 지원X
• CUME_DIST
  • 해당 파티션에서의 누적 백분율을 구하는 함수
  • 결과값을 0보다 크고 1보다 작거나 같은 값
  • SQL Server(MSSQL)에서는 지원X
• NTILE
  • 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수
  • 할당할 행이 남았을 경우 맨 앞의 그룹부터 하나씩 더 채워짐.
  • 동일한 데이터가 있는 경우 NTILE(2)일 때 각각 1그룹과 2그룹으로 할당
  • 똑같은 ㅎ애 수로 할당되지 않는 경우 1그룹부터 다시 할당이 시작됨

06) Top-N 쿼리

• ROWNUM  (↔ ROW_NUMBER)
  • Pseudo Column 
  • 실제로 존재하지 않는 가짜 컬럼
  • 항상 < 조건이나 <= 조건으로 사용
  • ex. 엑셀을 작성하다 보면 순번이 필요한 상황이 종종 생김. 그럴 땐 보통 맨 앞에 별도의 열을 만들어서 엑셀 자동번호를 매기는 경우가 있는데 Oracle에서는 그런 경우 SELECT절에 ROWNUM 컬럼을 하나 추가하면 됨.

07) 셀프 조인 (Self Join)

• 나 자신과의 조인
• FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에 혼란을 막고자 ALIAS를 반드시 표기해야함.
• 서로 연관 관계가 있는 컬럼이 하나의 테이블 내에 존재할 때 수행

A.CATEGORY_NAME = B.PARENT_CATEGORY
# 셀프 조인으로 나타내면 위와 같다.

 

08) 계층 쿼리

• 테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리 이용하여 데이터 출력
• 별도의 JOIN 없이도 계층 구조가 출력됨.

LEVEL
현재의 DEPTH 반환. 루트 노드는 1.

SYS_CONNECT_BY_PATH
루트 노드부터 현재 노드까지의 경로를 출력해주는 함수

START WITH
경로가 시작되는 루트 노드를 생성해주는 절

CONNECT BY
루트로부터 자식 노드를 생성해주는 절.
조건에 만족하는 데이터가 없을 때까지 노드 생성.

PRIOR
바로 앞에 있는 부모 노드의 값을 반환.

 

• START WITH PARENT_CATEGORY IS NULL
• CONNVECT BY PRIOR CATEGORY_NAME = PARENT_CATEGORY
• CONNECT BY PRIOR CATEGORY_NAEM = PARENT_CATEGORY

CONNECT_BY_ROOT 컬럼
루트 노드의 주어진 컬럼 값 반환

CONNECT_BY_ISLEAF
가장 하위 노드인 경우 1을 반환하고 그 외에는 0 반환

• 계층 쿼리에서 ORDER BY 절을 사용하면 계층구조와는 전혀 상관 없이 정렬이 되버림.
• ORDER SIBLINGS BY 절을 사용하여 같은 레벨끼리 정렬되도록 할 수 있음.
  • SIBLINGS, 즉 형제들끼리 정렬한다는 의미.

순방향 (Top-Down) : PRIOR이 왼쪽(부모 컬럼) → 부모 → 자식
역방향 (Bottom-Up) : PRIOR이 오른쪽(자식 컬럼) → 자식 → 부모