SQL 중급 완전 정복 – JOIN, 집계 함수, 서브쿼리로 데이터 분석하기

머신러닝과 데이터 분석에서 SQL은 필수적인 도구이다.
이번 포스트에서는 JOIN, 집계 함수, 서브쿼리를 사용하여 더욱 복잡한 데이터 분석을 수행하는 방법을 데이터 사이언스 실무에서 활용되는 SQL 쿼리 예제를 중심으로 알아보자.

Table of Contents　

SQL 중급 개념이 필요한 이유

SQL을 잘 활용하면 머신러닝 모델 학습용 데이터를 효과적으로 추출하고, AI 분석을 위한 고급 데이터 조작이 가능하다.

실제 머신러닝 프로젝트에서 SQL이 필요한 순간

AI 추천 시스템을 위한 사용자 행동 데이터 분석
이상 탐지를 위한 로그 데이터 분석
예측 모델 학습을 위한 대규모 데이터 전처리
비즈니스 인사이트 도출을 위한 고객 세그먼트 분석

예제 시나리오:
온라인 쇼핑몰에서 고객별 구매 내역을 분석하여 재구매 가능성이 높은 고객을 찾아낸다.
이때, JOIN, 집계 함수, 서브쿼리가 필수적으로 사용된다.

JOIN을 활용한 관계형 데이터 분석

INNER JOIN – 기본적인 두 테이블 결합

INNER JOIN 문법

설명: 테이블 A(별칭 A)과 테이블 B(별칭B)에서 특정 컬럼을 선택하여 조회하고, 테이블 A, B 사이에 공통된 키 값(A.공통컬럼, B.공통컬럼)을 가진 행들로 결합하여 반환한다.

SELECT A.컬럼명, B.컬럼명
FROM 테이블A AS A
INNER JOIN 테이블B AS B
ON A.공통컬럼 = B.공통컬럼;

예제

고객 정보(customers)와 주문 내역(orders)을 JOIN하여, 고객별 총 주문 금액을 계산

SELECT c.customer_id, c.name, SUM(o.total_price) AS total_spent
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

LEFT JOIN – 일부 데이터만 있는 경우 처리

LEFT JOIN 문법

설명: 테이블 A(별칭 A)과 테이블 B(별칭B)에서 특정 컬럼을 선택하여 조회하고, 테이블 A의 모든 행을 가져오고, 테이블 A와 B 사이에 공통된 키 값(A.공통컬럼, B.공통컬럼)으로 테이블 B가 가진 행들로 결합하여 반환한다.

특징:

왼쪽 테이블(테이블 A)의 모든 데이터를 가져온다.
오른쪽 테이블(테이블 B)에 매칭되는 데이터를 결합하고, 매칭되는 데이터가 없으면 NULL이 반환된다.

SELECT A.컬럼명, B.컬럼명
FROM 테이블A AS A
LEFT JOIN 테이블B AS B
ON A.공통컬럼 = B.공통컬럼;

예제

주문을 한 적이 없는 고객도 포함하여 고객별 주문 데이터를 조회

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

SELF JOIN – 자기 자신을 조인

SELF JOIN 문법

SELF JOIN은 같은 테이블을 두 번 사용하여 조인하는 기법이다.
이를 통해 하나의 테이블 내에서 관련된 데이터를 연결할 수 있다.

설명: 같은 테이블을 JOIN 하며, 별칭을 각각 A, B로 지정하고 공통된 키값으로 JOIN을 수행하여 반환한다.

SELECT A.컬럼명, B.컬럼명
FROM 테이블명 AS A
JOIN 테이블명 AS B
ON A.조건컬럼 = B.조건컬럼;

예제

직원의 멘토-멘티 관계 분석

SELECT e1.name AS mentor, e2.name AS mentee
FROM employees e1
JOIN employees e2
ON e1.employee_id = e2.mentor_id;

집계 함수와 그룹화 (GROUP BY)

그룹별 데이터 집계

SQL의 집계 함수는 머신러닝 데이터 전처리에서 필수적으로 사용되며, 쿼리(Query)되는 그룹들을 대상으로 계산하게 된다.

주요 집계 함수

함수	설명
`COUNT()`	행 개수
`SUM()`	합계
`AVG()`	평균
`MAX()`	최대값
`MIN()`	최소값

예제
고객의 평균 구매 금액 분석

SELECT customer_id, AVG(total_price) AS avg_spent
FROM orders
GROUP BY customer_id;

HAVING – 그룹화된 데이터 필터링

HAVING 문법

HAVING 문법은 GROUP BY로 그룹화된 데이터에 대한 필터링을 수행할 때 사용된다.

비슷한 기능으로 WHERE 문법이 있으나 WHERE는 개별 행을 필터링하고, HAVING은 집계 함수 결과를 필터링하는 차이가 있다.

SELECT 컬럼, 집계함수
FROM 테이블
GROUP BY 컬럼
HAVING 집계함수 조건;

예제
총 구매 금액이 $500 이상인 고객만 조회

SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 500;

서브쿼리_(Subquery)와 공통 테이블 표현식_(CTE)

서브쿼리 – 중첩된 SQL 쿼리

서브쿼리 문법

서브 쿼리_(Subquery)란 다른 SQL 쿼리 내부에 포함된 쿼리를 위미한다.
일반적으로 WHERE, FROM, SELECT 내부에서 사용한다.
복잡한 데이터의 필터링, 특정 값 계산 후 활용하고자 할 때 유용하다.

SELECT 컬럼
FROM (SELECT 컬럼 FROM 테이블 WHERE 조건);

안쪽 쿼리(SELECT 컬럼 FROM 테이블 WHERE 조건)는 특정 데이터를 필터링 한다.
바깥쪽 쿼리(SELECT 컬럼 FROM (...))는 필터링 된 데이터를 최종적으로 활용한다.

예제
가장 많이 구매한 고객 찾기

SELECT customer_id, total_spent
FROM (SELECT customer_id, SUM(total_price) AS total_spent
      FROM orders
      GROUP BY customer_id)
WHERE total_spent = (SELECT MAX(total_spent) FROM orders);

공통 테이블 표현식_(CTE)

WITH 문법

공통 테이블 표현식_{(CTE, Common Table Expression)} WITH를 활용해 복잡한 SQL 쿼리를 임시 테이블처럼 만들어 코드의 가독성을 높이는 기법이다. 이는 서브쿼리보다 더 읽기 쉽고 유지보수하기 용이하다.

WITH cte AS (
    SELECT customer_id, SUM(total_price) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM cte WHERE total_spent > 500;

With cte AS (...) , cte라는 가상 테이블을 먼저 생성하고, cte 테이블에서 총 구매 금액이 500 이상인 고객만 조회

실전 프로젝트

머신러닝에서 사용할 데이터 준비해 보자.

목표: AI 기반 고객 세분화를 위해 고객의 구매 패턴 분석
사용 테이블: customers, orders
수행 작업:
- 최근 6개월간 구매한 고객만 필터링
- 고객별 총 구매 금액 계산
- 특정 구매 기준(500 이상)을 만족하는 고객 조회

최종 SQL 코드

WITH recent_orders AS (
    SELECT customer_id, SUM(total_price) AS total_spent
    FROM orders
    WHERE order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
    GROUP BY customer_id
)
SELECT c.customer_id, c.name, r.total_spent
FROM customers c
JOIN recent_orders r ON c.customer_id = r.customer_id
WHERE r.total_spent > 500;

orders 테이블에서 최근 6개월간 고객별 총 구매 금액을 계산하고, customer 테이블과 customer_id를 기준으로 JOIN 수행한 뒤 총 구매 금액이 500이상인 고객을 필터링

이번 포스트에서는 JOIN, 집계 함수, 서브쿼리, CTE를 활용하여 머신러닝에서 SQL을 어떻게 활용할 수 있는지 알아보았다. 대부분의 데이터는 데이터베이스로 나눠져서 저장되어 있고, 널리 퍼져있는 데이터를 종합하고 이를 기반으로 머신러닝 학습을 진행하고자 한다면, 이번 포스트에서 사용한 기능들을 손쉽게 생각하고 사용할 수 있어야 한다.