SQL 조인 및 SQL 함수, CTE와 서브쿼리 이해

Source: Dev.to

My journey to becoming a competent data analyst is still in progress, and my SQL knowledge keeps deepening. I like to share the things I pick up along the way.

몇 주 전, SQL fundamentals(DDL, DML, 그리고 기본 데이터 조작)에 관한 글을 발표했습니다. 여기에서 읽어보실 수 있습니다:
Understanding SQL Fundamentals – DDL, DML, and Data Manipulation

그 기본을 바탕으로, 저는 SQL joins와 SQL functions를 사용하여 여러 테이블을 동시에 다루는 방법을 배웠습니다.

JOIN이란 무엇인가?

A JOIN은 두 개(또는 그 이상) 테이블의 행을 관련된 열을 기준으로 결합합니다—보통 한 테이블의 primary key와 다른 테이블의 foreign key가 그 열이 됩니다.

예시: 한 테이블은 학생을 저장하고, 다른 테이블은 점수를 저장합니다. JOIN을 사용하면 어떤 학생이 어떤 점수를 받았는지 확인할 수 있습니다.

조인 유형

LEFT JOIN (LEFT OUTER JOIN)

왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환합니다. 일치하는 행이 없으면 오른쪽 컬럼은 NULL이 됩니다.

SELECT s.name, c.course_name
FROM students AS s
LEFT JOIN courses AS c
  ON s.course_id = c.id;

결과: 모든 학생이 표시되며, 코스가 할당되지 않은 학생도 포함됩니다.

INNER JOIN

두 테이블 모두에서 값이 일치하는 행만 반환합니다.

SELECT s.name, c.course_name
FROM students AS s
INNER JOIN courses AS c
  ON s.course_id = c.id;

RIGHT JOIN (RIGHT OUTER JOIN)

오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환합니다. 일치하지 않는 왼쪽 컬럼은 NULL이 됩니다.

SELECT s.name, c.course_name
FROM students AS s
RIGHT JOIN courses AS c
  ON s.course_id = c.id;

결과: 모든 코스가 표시되며, 해당 코스에 등록된 학생이 없더라도 포함됩니다.

FULL JOIN (FULL OUTER JOIN)

두 테이블 중 하나라도 일치하는 경우 모든 행을 반환합니다. 일치하지 않는 행은 NULL 자리표시자로 포함됩니다.

SELECT s.name, c.course_name
FROM students AS s
FULL JOIN courses AS c
  ON s.course_id = c.id;

핵심 요점

• 조인은 테이블 간의 관계를 정의합니다.
• 조인이 없었다면 관계형 데이터베이스는 훨씬 덜 강력했을 것입니다.

SQL 윈도우 함수

윈도우 함수란?

윈도우(또는 분석) 함수는 현재 행과 관련된 행 집합에 대해 계산을 수행하지만 결과 집합을 축소하지 않습니다.

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

Result: 모든 직원이 출력에 남으며, 각 행은 해당 직원이 속한 부서의 평균 급여도 표시합니다.

초보자가 알아야 할 SQL 함수들

이 함수들은 데이터 분석가, 엔지니어, 과학자 등 누구든 일상적인 데이터 분석 작업을 수행할 때 기본이 되는 요소입니다.

SQL 서브쿼리

서브쿼리란?

A subquery (or inner query) is a query nested inside another query. It runs first, and its result is used by the outer query.

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

설명: 내부 쿼리는 평균 급여를 계산하고, 외부 쿼리는 그 평균보다 높은 급여를 받는 직원을 반환합니다.

서브쿼리 유형

Common Table Expression (CTE)

What Is a CTE?

A **Common Table Expression (CTE)**는 WITH 절을 사용하여 정의된 임시의 명명된 결과 집합입니다. 동일한 쿼리 내에서 여러 번 참조할 수 있어 가독성을 높이고 재귀 쿼리를 가능하게 합니다.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d
  ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Result: CTE dept_avg는 부서별 평균 급여를 한 번 계산하고, 메인 쿼리는 그 결과를 사용하여 부서 평균보다 높은 급여를 받는 직원을 나열합니다.

최종 생각

조인, 함수, 윈도우 함수, 서브쿼리 및 CTE를 이해하면 데이터 탐색 및 변환을 위한 강력한 도구 상자를 갖추게 됩니다. 계속 실험해 보세요, 그리고 즐거운 쿼리 작성 되세요!

CTE가 쿼리를 더 쉽게 조직하고 읽을 수 있게 함

예시: CTE를 사용한 동일한 쿼리

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
)

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT avg_sal
    FROM avg_salary
);

CTE 사용 이유

• 가독성 향상
• 복잡한 쿼리 단순화
• 구조의 체계화
• 중간 결과 재사용 가능

여러 단계 CTE

CTE의 장점 중 하나는 논리를 체인처럼 연결할 수 있다는 점입니다.

예시: 월별 판매 분석

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY month
),

ranked_sales AS (
    SELECT *,
           RANK() OVER (ORDER BY revenue DESC) AS sales_rank
    FROM monthly_sales
)

SELECT *
FROM ranked_sales;

이 쿼리는 월별 매출을 계산하고, 매출 기준으로 월을 순위 매겨 최종 결과를 반환합니다.

초보자를 위한 SQL 학습 로드맵

SQL 기본

• JOIN
• GROUP BY
• 윈도우 함수
• SQL 함수
• 서브쿼리
• CTE

이 핵심 개념들은 다음을 위해 필요합니다:

• SQL 기술 면접 준비
• 품질 높은 SQL 스크립트 작성
• Microsoft Power BI와 같은 도구용 대시보드 구축
• 데이터 분석 수행

결론

데이터 과학자로서 SQL Functions, Window Functions, 그리고 Joins을 배우고 이해하게 되면서 데이터 조작 및 분석이 훨씬 쉬워졌습니다.

CTEs와 Subqueries를 사용하면 SQL 쿼리를 더 읽기 쉽고 체계적으로 구성할 수 있습니다.

참고: 서브쿼리가 읽기 어려워지면 CTE로 변환하는 것을 고려하세요.