[SQLD] SQL 활용

01. 서브쿼리

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

(1) 스킬라 서브쿼리

• SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
• 컬럼 대신 사용되므로 반드시 하나의 값만 반환해야 하며 그렇지 않은 경우 에러를 발생

(2) 인라인 뷰

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

(3) 중첩 서브쿼리

• WHERE 절과 HAVING 절에 사용할 수 있음.
• 중첩 서브쿼리는 메인쿼리와의 관계에 따라 다음과 같이 나눌 수 있음.
  • 비연관 서브쿼리 - 서브쿼리 내에 메인쿼리의 컬럼이 존재하지 않음.
  • 연관 서브쿼리 - 서브쿼리 내에 메인쿼리의 컬럼이 존재함.
• 중첩 서브쿼리는 반환하는 데이터 형태에 따라 다음과 같이 나눌 수 있음.
  • 단일 행 서브쿼리 - 서브쿼리가 1건 이하의 데이터를 반환, 단일 행 비교 연산자와 함께 사용
  • 다중 행 서브쿼리 - 서브쿼리가 여러 건의 데이터를 반환, 다중 행 비교 연산자와 함께 사용
  • 다중 컬럼 - 서브쿼리가 여러 컬럼의 데이터를 반환

 

02. 뷰

• 특정 SELECT 문에 이름 붙여서 재사용이 가능하도록 저장해놓은 오브젝트.
• 혼돈하지 말아야 할 점은 가상 테이블이라는 것.
• 실제 데이터를 저장하지 않고 해당 데이터를 조회해오는 SELECT 문만 가짐.

 

03. 집합 연산자

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

(1) UNION ALL / UNION

• 각 쿼리의 결과 집합의 합집합,
  • UNION ALL - 중복된 행도 그대로 출력
  • UNION - 중복된 행은 한 줄로 출력
  • 데이터베이스 내부적으로 중복되는 행을 제거하는 과정을 거치는 게 성능상 불리할 수 있다는 점을 염두

(2) INTERSECT

• 쿼리1의 결과와 쿼리2의 결과에서 공통된 부분만 중복을 제거하여 출력

(3) MINUS / EXCEPT

• 쿼리 1의 결과에서 쿼리 2의 결과를 제거하고 출력

 

04. 그룹 함수

• 데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수
• 역할에 따라 구분해 보면 집계 함수와 소계 함수로 나눌 수 있다.

(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는 조합할 수 있는 모든 그룹에 대한 소계를 집계

| CUBE (A) | - A로 그룹핑

• 총합계 | | --- | --- | | CUBE (A, B) | - A, B로 그룹핑
• A로 그룹핑
• B로 그룹핑
• 총합계 | | CUBE (A, B, C) | - A, B, C로 그룹핑
• A,B로 그룹핑
• A, 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

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

 

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) 순위 함수

1. RANK

• 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다.

2. DENSE_RANK

• 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다.

3. ROW_NUMBER

• 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.

(2) 집계 함수

1. SUM

• 데이터의 합계를 구하는 함수
• 인자값으로는 숫자형만

2. MAX

• 데이터의 최댓값을 구하는 함수

3. MIN

• 데이터의 최솟값을 구하는 함수

4. AVG

• 데이터의 평균값을 구하는 함수

5. COUNT

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

(3) 행 순서 함수

1. FIRST_VALUE

• 파티션별 가장 선두에 위치한 데이터를 구하는 함수
• SQL Server에서는 지원하지 않음

2. LAST_VALUE

• 파티션별 가장 끝에 위치한 데이터를 구하는 함수

3. LAG

• 파티션별로 특정 수만큼 앞선 데이터를 구하는 함수

4. LEAD

• 파티션별 특정 수만큼 뒤에 있는 데이터를 구하는 함수

(4) 비율 함수

1. RATIO_TO_REPORT

• 파티션별 합계에서 차지하는 비율을 구하는 함수
• SQL Server에서는 지원하지 않음

2. PERCENT_RANK

• 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수

3. CUME_DIST

• 해당 파티션에서의 누적 백분율을 구하는 함수
• 결과값은 0보다 크고 1보다 작거나 같은 값을 가짐

4, NTILE

• 주어진 수만큼 행들을 n등분 한 후 현재 행에 해당하는 등급을 구하는 함수