SQL 마스터하기: 조인과 윈도우 함수 가이드

Source: Dev.to

데이터 분석 세계에서 SQL은 관계형 데이터베이스에서 인사이트를 추출하기 위한 필수 언어입니다. 기본 쿼리(SELECT, WHERE)는 직관적이지만, SQL의 진정한 힘은 복잡한 데이터 관계를 조작하고 분석하는 데 있습니다. 바로 여기서 Joins와 Window Functions가 없어서는 안 될 도구가 됩니다.

이 두 개념을 이해하면 초보자와 고급 SQL 사용자 사이를 구분할 수 있습니다. Joins는 서로 다른 데이터셋을 연결하는 데 도움을 주고, Window Functions는 세부 정보를 잃지 않으면서 해당 데이터셋 내에서 데이터를 분석할 수 있게 해줍니다.

SQL Joins: Combining Data Across Tables

관계형 데이터베이스는 데이터 중복을 방지하기 위해 여러 개의 작은 테이블로 구조화됩니다(정규화). 예를 들어 customers 테이블과 orders 테이블을 별도로 유지합니다. 조인은 일반적으로 customers.customer_id = orders.customer_id와 같은 Primary‑Key – Foreign‑Key 관계를 기반으로 이러한 테이블을 결합할 수 있게 해줍니다.

핵심 조인 유형

INNER JOIN

두 테이블 모두에서 일치하는 행만 반환합니다.
사용 사례: 유효하고 연결된 데이터 찾기(예: 특정 고객에 속한 모든 주문).

SELECT
    customers.name,
    orders.order_id,
    orders.amount
FROM orders
INNER JOIN customers
    ON orders.customer_id = customers.customer_id;

LEFT JOIN (LEFT OUTER JOIN)

왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환합니다.
일치하는 행이 없으면 오른쪽 쪽에 NULL 값이 들어갑니다.
사용 사례: 메인 목록을 그대로 유지하기(예: 주문을 하지 않은 고객을 포함한 모든 고객).

SELECT
    customers.name,
    orders.order_id
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

RIGHT JOIN

LEFT JOIN의 반대이며, 오른쪽 테이블의 모든 행을 유지합니다.

SELECT
    customers.name,
    orders.order_id
FROM customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN

두 테이블 중 어느 한쪽에 일치하는 행이 있으면 모두 반환합니다. LEFT JOIN과 RIGHT JOIN의 결과를 결합한 형태입니다.

SELECT
    customers.name,
    orders.order_id
FROM customers
FULL OUTER JOIN orders
    ON customers.customer_id = orders.customer_id;

Source:

Window Functions: Context‑Aware Analytics

윈도우 함수는 현재 행과 관련된 테이블 행 집합에 대해 계산을 수행합니다. GROUP BY가 행들을 하나의 요약 행으로 압축하는 것과 달리, 윈도우 함수는 개별 행을 그대로 유지하면서 계산된 열을 추가합니다.

The Syntax: OVER()

OVER() 절은 함수가 작동하는 윈도우 (데이터의 부분 집합)를 정의합니다.

• PARTITION BY – 행을 그룹으로 나눕니다 (예: PARTITION BY customer_id).
• ORDER BY – 각 파티션 내에서 행을 정렬합니다 (예: ORDER BY amount DESC).

Common Window Functions

Ranking Functions

예시: 금액이 높은 순서대로 주문을 순위 매기기.

SELECT
    order_id,
    customer_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM orders;

Aggregation Functions

SUM(), AVG(), COUNT(), MAX(), MIN()

예시: 시간에 따라 주문 금액의 누적 합계.

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Value Functions

예시: 각 주문을 이전 주문과 비교.

SELECT
    order_id,
    amount,
    LAG(amount) OVER (ORDER BY order_id) AS previous_amount
FROM orders;

조인과 윈도우 함수 결합

분석 작업에서는 먼저 테이블을 조인하여 전체적인 모습을 파악한 뒤, 윈도우 함수를 적용해 추세를 분석하는 경우가 많습니다.

시나리오: 모든 주문을 고객 이름과 함께 표시하고, 각 고객별로 금액 기준으로 주문 순위를 매깁니다.

SELECT
    customers.name,
    orders.order_id,
    orders.amount,
    RANK() OVER (
        PARTITION BY orders.customer_id
        ORDER BY orders.amount DESC
    ) AS order_rank
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id;

조인과 윈도우 함수의 차이점

• 조인은 두 개 이상의 테이블을 연결하여 관련 데이터를 함께 가져옵니다. 조인 유형에 따라 결과 집합의 행 수가 늘어나거나 줄어들 수 있습니다.
• 윈도우 함수는 단일 결과 집합 내의 행 집합에 대해 계산을 수행하지만, 행을 축소하지는 않습니다. 원래 행 수를 유지하면서 누적 합계, 순위, 이동 평균 등과 같은 계산된 값을 추가합니다. OVER() 절을 사용하여 정의하며, 종종 PARTITION BY를 통해 분석 대상 행을 그룹화합니다.

결론

조인과 윈도우 함수를 모두 마스터하면 흩어져 있는 원시 데이터를 의미 있고 실행 가능한 인사이트로 변환할 수 있습니다—대개 최소한의 코드와 최대의 유연성으로.

정리된 마크다운 세그먼트

…하지만 복잡하고 다단계 서브쿼리가 필요합니다. 이러한 도구들은 데이터 분석, 백엔드 개발, 혹은 데이터베이스 관리에 종사하는 모든 사람에게 기본이 됩니다.

목표가 중급에서 고급 SQL로 나아가는 것이라면, 이것은 배울 수 있는 가장 가치 있는 기술 조합 중 하나입니다.