SQL에서 조인과 윈도우 함수 이해하기

Source: Dev.to

위에 있는 소스 링크 아래에 번역하고 싶은 텍스트를 제공해 주시면, 해당 내용을 한국어로 번역해 드리겠습니다. (코드 블록이나 URL은 그대로 유지됩니다.)

소개

이 기사에서는 SQL에서 가장 강력하고 널리 사용되는 두 가지 기능인 JOINs와 Window Functions를 살펴봅니다.
각 개념을 정의한 뒤, 실제 시나리오에서 언제, 어디서, 왜 사용되는지를 보여주는 실용적인 예제를 진행합니다.

1️⃣ JOINs

JOIN은 관련된 열을 기준으로 두 개 이상의 테이블에서 행을 결합하는 절입니다.
JOIN의 목적은 여러 테이블에 퍼져 있는 데이터를 가져와 하나의 통합된 결과 집합으로 표시하는 것입니다.

예시 시나리오:
orders 테이블과 customers 테이블을 조인하면 “각 주문을 어떤 고객이 했는가?”와 같은 질문에 답할 수 있습니다.

Types of Joins

1.1 INNER JOIN

-- INNER JOIN example
SELECT  c.first_name,
        c.last_name
FROM    article.customers   AS c
INNER JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: 두 customers와 orders 테이블에 customer_id가 일치하는 레코드만 반환합니다.

1.2 LEFT JOIN

-- LEFT JOIN example
SELECT  c.first_name,
        c.last_name,
        o.order_date
FROM    article.customers   AS c
LEFT JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: 모든 고객을 반환합니다. 고객이 주문을 한 번도 하지 않은 경우 order_date(및 다른 주문 열)는 NULL이 됩니다.
왼쪽 조인은 left outer join이라고도 합니다.

1.3 RIGHT JOIN

-- RIGHT JOIN example
SELECT  c.first_name,
        c.last_name,
        o.order_date
FROM    article.customers   AS c
RIGHT JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: 모든 주문을 반환합니다. 주문에 일치하는 고객이 없을 경우 고객 열은 NULL이 됩니다.
오른쪽 조인은 right outer join이라고도 합니다.

1.4 FULL OUTER JOIN

-- FULL OUTER JOIN example
SELECT  c.first_name,
        c.last_name,
        c.email,
        c.phone_number,
        o.order_id,
        o.order_date,
        o.book_id
FROM    article.customers   AS c
FULL OUTER JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: 두 테이블의 모든 행을 반환합니다. 일치하는 행이 없는 경우 해당 쪽 열은 NULL로 채워집니다.

2️⃣ 윈도우 함수

윈도우 함수는 현재 행과 관련된 행 집합 전체에 대해 계산을 수행하지만, 해당 행들을 하나의 출력 값으로 축소하지 않습니다.
많은 행을 하나로 축소하는 SUM()이나 COUNT()와 같은 집계 함수와 달리, 윈도우 함수는 원본 결과 집합의 각 행마다 값을 반환합니다.

구문은 OVER() 절을 중심으로 구성됩니다:

SELECT  <columns>,
        <window_function>() OVER (
            PARTITION BY <partition_expression>
            ORDER BY <order_expression>
            ROWS BETWEEN <frame_start> AND <frame_end>
        ) AS <alias>
FROM    <table>;

주요 구성 요소

윈도우 함수의 종류

1. 집계 윈도우 함수 – 각 행을 유지하면서 윈도우에 대해 집계(SUM(), AVG() 등)를 계산합니다.
2. 순위 윈도우 함수 – 파티션 내에서 순위 또는 행 번호를 할당합니다(예: RANK(), DENSE_RANK(), ROW_NUMBER()).
3. 값 윈도우 함수 – 다른 행의 값을 반환합니다(예: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()).

3️⃣ 예시: 부서별 급여 순위 매기기

Assume we have an employees table:

3.1 RANK() 사용

SELECT  employee_id,
        first_name,
        last_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rank_by_salary
FROM    article.employees;

무엇을 하는가

• department 별로 행을 파티션합니다.
• 각 부서 내에서 salary 를 내림차순으로 정렬합니다.
• 순위(1 = 가장 높은 급여)를 부여합니다.
• 두 명 이상의 직원이 같은 급여를 받으면 동일한 순위를 받고, 그 다음 순위는 건너뛰게 됩니다(표준 RANK() 동작).

결과 (발췌)

각 부서마다 순위가 다시 시작됩니다.

4️⃣ 예시: 부서별 평균 급여 (Aggregate Window Function)

SELECT  employee_id,
        first_name,
        last_name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM    article.employees;

동작 설명

• 각 부서별 평균 급여를 계산합니다.
• 평균값이 모든 행에 표시되어, 개별 급여를 부서 평균과 바로 비교할 수 있습니다.

결과 (발췌)

5️⃣ Further Reading

• Window Functions – Comprehensive guide:
• SQL JOIN Types – Detailed comparison:

이제부터는 다른 윈도우 함수(예: LEAD(), LAG(), FIRST_VALUE())를 실험하여 중첩 서브쿼리 없이도 고급 분석을 수행할 수 있습니다.

조인과 윈도우 함수가 무엇인지, 사용 가능한 다양한 유형, 작동 방식, 그리고 원하는 결과를 얻기 위한 깔끔하고 효율적인 SQL 쿼리 작성 방법을 이제 보셨습니다. 이러한 도구들을 마스터하면 데이터를 정밀하게 분석·변환·조회하는 능력이 크게 향상됩니다. 오늘부터 직접 프로젝트에 적용해 보세요; 연습할수록 자연스럽게 익숙해질 것입니다.

Happy querying!