SQL 마스터하기: JOIN 및 윈도우 함수 심층 탐구

Source: Dev.to

소개

SQL은 데이터 조작 및 분석의 핵심입니다. 가장 강력한 기능 두 가지는 JOINs와 Window Functions입니다. JOINs은 여러 테이블의 데이터를 결합할 수 있게 해주며, Window Functions는 행 집합 전체에 걸친 고급 계산을 가능하게 합니다. 이 기사에서는 명확한 설명, 시각 자료, 실용적인 예제를 통해 이러한 개념을 쉽게 풀어냅니다.

JOIN 이해하기

JOIN이란?

JOIN은 관련된 열을 기준으로 두 개 이상의 테이블에서 행을 결합합니다. 데이터가 여러 테이블에 분산되어 있는 관계형 데이터베이스를 다룰 때 필수적인 기능입니다.

JOIN 종류

• INNER JOIN: 두 테이블 모두에서 일치하는 값이 있는 행만 반환합니다.
• LEFT JOIN (또는 LEFT OUTER JOIN): 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환합니다. 일치하는 행이 없을 경우 오른쪽 테이블에 대해 NULL 값이 반환됩니다.
• RIGHT JOIN (또는 RIGHT OUTER JOIN): 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환합니다. 일치하는 행이 없을 경우 왼쪽 테이블에 대해 NULL 값이 반환됩니다.
• FULL JOIN (또는 FULL OUTER JOIN): 왼쪽 또는 오른쪽 테이블 중 하나라도 일치하는 경우 모든 행을 반환합니다. 일치하는 행이 없을 경우 해당 측에 NULL 값이 반환됩니다.

Inner Join 예시

SELECT employees.name,
       departments.name AS department
FROM employees
INNER JOIN departments
  ON employees.dept_id = departments.dept_id;

Left Join 예시

SELECT employees.name,
       departments.name AS department
FROM employees
LEFT JOIN departments
  ON employees.dept_id = departments.dept_id;

윈도우 함수 탐색

윈도우 함수란?

윈도우 함수는 현재 행과 관련된 테이블 행 집합 전체에 걸쳐 계산을 수행합니다. 집계 함수(SUM, AVG 등)와 달리 단일 출력 행으로 행을 그룹화하지 않습니다.

주요 윈도우 함수

• ROW_NUMBER(): 파티션 내 행에 고유한 순차 정수를 할당합니다.
• RANK(): 파티션 내 각 행에 순위를 할당하며, 동점이 있을 경우 순위 사이에 공백이 생깁니다.
• DENSE_RANK(): 파티션 내 각 행에 순위를 할당하지만, 동점이 있어도 순위 사이에 공백이 없습니다.
• LEAD() 및 LAG(): 동일 결과 집합에서 이후 행 또는 이전 행의 데이터를 가져옵니다.
• SUM() OVER(): 윈도우를 기준으로 누적 합계 또는 러닝 토탈을 계산합니다.

예제 1: ROW_NUMBER()

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

결과: 급여 순으로 직원들을 정렬하고, 가장 높은 급여에 1위를 부여합니다.

예제 2: SUM() OVER()를 이용한 누적 합계

SELECT date,
       revenue,
       SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales;

결과: 시간에 따라 누적된 매출을 보여줍니다.

예제 3: 이전 행과 비교하기 위한 LAG()

SELECT date,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY date) AS previous_day_revenue
FROM sales;

결과: 각 날짜의 매출과 전날 매출을 함께 표시합니다.

JOIN과 윈도우 함수 결합

실제 시나리오

부서별로 직원 성과를 분석하고, 각 부서 내에서 급여 순위에 따라 직원을 순위 매깁니다.

SELECT e.name,
       d.name AS department,
       e.salary,
       RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_rank
FROM employees e
JOIN departments d
  ON e.dept_id = d.dept_id;

결론

JOIN과 윈도우 함수는 SQL을 다루는 모든 사람에게 필수적인 도구입니다. JOIN은 여러 테이블의 데이터를 결합할 수 있게 해 주며, 윈도우 함수는 행을 축소하지 않고도 고급 분석을 가능하게 합니다. 이러한 개념을 마스터하면 데이터 분석 및 보고를 위한 SQL의 전체 잠재력을 활용할 수 있습니다.