이번 포스팅에서는 이전 포스팅에 이어서 SQL 자격검정 실전문제 책을 보며 익힌 내용들을 정리하는 시간을 가져보도록 하겠습니다.
Window 함수
Window 함수는 SQL에서 데이터 분석 및 집계 작업을 수행할 때 매우 유용한 기능입니다. 기본적인 집계 함수와 달리, Window 함수는 결과 집합의 각 행에 대해 집계된 값을 계산하지만, 이 결과를 원래의 여러 행들과 함께 반환합니다. 이를 통해 데이터의 특정 범위나 그룹에 대해 다양한 계산을 수행하면서도 원본 데이터의 상세 정보를 잃지 않게 됩니다.
PARTITION BY
데이터를 특정 기준으로 그룹화합니다. 각 그룹 내에서 Window 함수가 적용됩니다.
ORDER BY
각 그룹 내에서 데이터의 순서를 지정합니다.
OVER()
Window 함수가 작동할 범위를 지정합니다.
주요 Window 함수
ROW_NUMBER()
각 행에 대해 고유한 번호를 부여합니다.
PERCENT_RANK
파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구하는 함수입니다.
CUME_DIST
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구하는 함수입니다.
RANK()
순위를 매기되, 동일한 값에는 동일한 순위를 부여합니다.
RANK() 함수와 같은 윈도우 함수가 동일한 순위를 가지는 행들에 대해 어떤 특정 순서를 보장하지 않습니다.
DENSE_RANK()
순위를 매기되, 동일한 값에 동일한 순위를 부여하고, 다음 순위는 연속적으로 부여합니다.
NTILE(n)
파티션별 전체 건수를 주어진 인자로 N 등분한 결과를 반환합니다.
예를들어, 지정된 그룹이 총 100개의 결과 셋을 반환하는데 NTILE(4)를 사용하면, 각 로우를 순서대로 25%(100/4)씩 나누어 1부터 4까지 반환한다.
LAG() / LEAD()
이전 또는 다음 행의 값을 반환합니다.
SUM() / AVG() / MIN() / MAX()
특정 범위의 합계, 평균, 최소값, 최대값을 계산합니다.
SUM()을 사용하는 경우, ORDER BY 절이 포함되면 누적 합계가 계산됩니다. 만약 부서별 총합계를 계산하려는 의도라면, PARTITION BY만 사용하고 ORDER BY 절은 제외해야 합니다.
예제 코드
SELECT
sales_person,
sale_amount,
SUM(sale_amount) OVER (PARTITION BY sales_person ORDER BY sale_date) AS running_total,
RANK() OVER (PARTITION BY sales_person ORDER BY sale_date) AS rank
FROM
sales
ORDER BY
sales_person, sale_date;SUM(sale_amount) OVER (PARTITION BY sales_person ORDER BY sale_date) 부분은 각 판매원의 판매 날짜별로 누적 판매량을 계산합니다.
RANK() OVER (PARTITION BY sales_person ORDER BY sale_date) 부분은 각 판매원의 판매 날짜별 순위를 매깁니다.
RATIO_TO_REPORT
RATIO_TO_REPORT는 SQL에서 Window 함수의 하나로, 특정 값이 전체 값에서 차지하는 비율을 계산하는 데 사용됩니다. 이 함수는 전체 집합이나 지정된 그룹 내에서 각 값의 비율을 쉽게 계산할 수 있도록 도와줍니다.
구문 형식
RATIO_TO_REPORT(expression) OVER (PARTITION BY partition_clause)expression은 비율을 계산할 대상 값입니다.
PARTITION BY partition_clause은 (선택 사항) 비율을 계산할 그룹을 지정합니다.
예제 코드
SELECT
sales_person,
sale_amount,
RATIO_TO_REPORT(sale_amount) OVER (PARTITION BY sales_person) AS sales_ratio
FROM
sales;
해당 쿼리는 각 판매원의 판매액이 전체 판매액에서 차지하는 비율을 계산합니다.
계층형 쿼리
- 노드(Node): 하나의 레코드(행)를 의미합니다.
- 루트 노드(Root Node): 계층 구조의 시작점입니다.
- 부모-자식 관계(Parent-Child Relationship): 부모 노드는 자식 노드에 연결됩니다.
예제 코드
SELECT C3
FROM TAB1
START WITH C2 IS NULL
CONNECT BY PRIOR C1 = C2
ORDER SIBLINGS BY C3 DESC;가상의 테이블 TAB1
| C1 | C2 | C3 |
|---|---|---|
| 1 | NULL | A |
| 2 | 1 | B |
| 3 | 1 | C |
| 4 | 2 | D |
| 5 | 2 | E |
| 6 | 3 | F |
| 7 | 3 | G |
실행 단계
1. 루트 노드 찾기 (START WITH C2 IS NULL)
C2 IS NULL 조건에 맞는 루트 노드를 찾습니다. 여기서는 C1 = 1인 행이 루트 노드가 됩니다.
| C1 | C2 | C3 |
|-----|------|-----|
| 1 | NULL | A |2. 계층 구조 확장 (CONNECT BY PRIOR C1 = C2)
루트 노드의 자식 노드를 찾습니다. C1 = 1의 자식 노드는 C2 = 1인 노드들입니다.
| C1 | C2 | C3 |
|-----|------|-----|
| 2 | 1 | B |
| 3 | 1 | C |3. 형제 노드 정렬 (ORDER SIBLINGS BY C3 DESC)
같은 부모를 가진 형제 노드들을 C3 기준으로 내림차순 정렬합니다.
| C1 | C2 | C3 |
|-----|------|-----|
| 3 | 1 | C |
| 2 | 1 | B |4. 자식 노드들에 대해 동일한 과정 반복
각 자식 노드에 대해 동일한 과정을 반복하여 계층 구조를 확장하고 정렬합니다.
| C1 | C2 | C3 |
|-----|------|-----|
| 4 | 2 | D |
| 5 | 2 | E |
| 6 | 3 | F |
| 7 | 3 | G |PRIOR
CONNECT BY절에 사용되며, 현재 읽은 컬럼을 지정합니다.
PRIOR A = B 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터 방향으로 전개하는 순방향 전개를 합니다.
A = PRIOR B 형태를 사용하면 반대로 자식 데이터에서 부모 데이터 방향으로 전개하는 역방향 전개를 합니다.
CONNECT BY
CONNECT BY 절에 작성된 조건절은 WHERE 절에 작성된 조건절과는 다릅니다.
START WITH 절에서 필터링된 시작데이터는 결과목록에 포함되며, 이후 CONNECT BY절에 의해 필터링됩니다.
부모 - 자식 관계에서 부모 정렬하고 그다음 자식들은 해당 부모들 바로 밑 행으로 들어가게 됩니다.
셀프 조인
셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 뜻합니다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타납니다.
동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 합니다.
구문 형식
SELECT
ALIAS명1.컬럼명,
ALIAS명2.컬럼명, ...
FROM
테이블 ALIAS명1,
테이블 ALIAS명2,
WHERE
ALIAS명1.컬럼명 = ALIAS명2.컬럼명;서브 쿼리 사용 시 주의사항
-
서브쿼리를 괄호로 감싸서 사용합니다.
-
서브쿼리는 단일행 또는 복수 행 비교 연산자와 함께 사용가능하며 단일 행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브 쿼리의 결과 건수와 상관없습니다.
-
서브쿼리에서는
ORDER BY를 사용하지 못합니다.ORDER BY절은SELECT절에서 오직 한 개만 올 수 있기 때문에ORDER BY절은 메인 쿼리의 마지막 문장에 위치해야합니다.
연관 쿼리, 비연관 쿼리
연관 서브쿼리(Correlated Subquery)와 비연관 서브쿼리(Uncorrelated Subquery)는 SQL에서 두 가지 주요 유형의 서브쿼리입니다. 이 두 가지 서브쿼리는 주 쿼리(Main Query)와 서브쿼리(Subquery) 간의 관계에 따라 다릅니다.
연관 서브쿼리는 서브쿼리가 주 쿼리의 각 행에 의존하는 형태입니다. 즉, 서브쿼리가 주 쿼리의 각 행에 대해 한 번씩 실행됩니다. 연관 서브쿼리는 주 쿼리의 열을 참조하여 그 값을 기준으로 조건을 만족하는 행을 찾습니다.
연관 서브 쿼리 예시
SELECT e1.employee_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);비연관 서브쿼리는 주 쿼리와 독립적으로 실행되는 서브쿼리입니다. 비연관 서브쿼리는 한 번만 실행되며, 그 결과는 주 쿼리의 모든 행에 대해 사용됩니다.
비연관 서브 쿼리 예시
SELECT department_id, department_name
FROM departments
WHERE department_id IN (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 10
);인라인 뷰(Inline View)
FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 합니다. 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있어 동적 뷰(Dynamic View)라고도 합니다.
인라인 뷰는 SQL 문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않습니다.
뷰(View)
뷰(View)는 데이터베이스에서 하나 이상의 테이블의 결과를 저장하는 가상의 테이블입니다. 실제 데이터가 저장되지 않으며, 쿼리를 실행할 때마다 정의된 쿼리를 기반으로 데이터를 동적으로 생성합니다. 뷰는 복잡한 쿼리를 단순화하고, 데이터 접근을 제어하며, 재사용 가능한 쿼리 논리를 제공하는 데 유용합니다.
- 독립성
테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됩니다.
- 편리성
복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작성할 수 있습니다. 또한 해당 형태의 SQL을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있습니다.
- 보안성
직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 해 해당 컬럼을 빼고 생성하여 사용자에게 정보를 감출 수 있습니다.
