[MySQL] 셀프조인(Feat: 재구매율)

본 내용은 [멀티캠퍼스] 데이터 분석&데이터 엔지니어링 취업캠프 28회차에서 실시한 수업 내용 중 일부입니다.

 

교재 홍보 (본 과정의 SQL 교재)

교재 구매: https://www.yes24.com/Product/Goods/86544423

SQL로 맛보는 데이터 전처리 분석 - 예스24

 

셀프조인이란?

셀프 조인(Self-Join)은 동일한 테이블 내에서 발생하는 조인 작업을 의미한다. 다시 말해, 하나의 테이블을 여러 번 사용하여 자체적으로 조인하는 것이다. 셀프 조인은 주로 데이터베이스 테이블 내에서 계층 구조나 관계를 나타내는 데 사용된다.

 

셀프조인이 유용하게 사용되는 상황:

1. 계층 구조 표현: 셀프 조인을 사용하여 테이블 내의 각 레코드가 다른 레코드와의 부모-자식 또는 상위-하위 관계를 나타낼 수 있다. 이를 통해 조직도, 카테고리 구조, 트리 구조 등을 모델링할 수 있다.

2. 자기 참조 테이블: 테이블 내의 엔터티가 자기 자신과 관련된 정보를 가질 때(예: 조직 내에서 각 직원이 직장 상사를 가리킬 때), 셀프 조인을 사용하여 이러한 관계를 표현한다.

 

셀프조인의 수행 단계:

1. 테이블에 대해 서로 다른 별칭(테이블 별칭)을 지정한다. 이렇게 하면 쿼리에서 동일한 테이블을 여러 번 참조할 수 있다.

2. 별칭을 사용하여 서로 다른 테이블(실제로는 동일한 테이블) 간의 조인 조건을 정의한다. 이 조인 조건은 테이블 내의 열 값을 기반으로 행을 연결한다.

 

교재에 있는 예제를 설명하기 전에 다른 예제를 먼저 살펴보자.

SELECT
    e1.employee_name AS employee,
    e2.employee_name AS manager 		-- 파트1
FROM employees e1						
LEFT 
JOIN employees e2				-- 파트2
ON
    e1.manager_id = e2.employee_id;		-- 파트3

위 쿼리는 셀프 조인(Self-Join)을 사용하여 employees 테이블 내에서 각 직원과 그 직원의 상사를 조회하는 예제이다.

 

• 파트1: 이 부분은 결과 집합의 컬럼 명칭을 설정한다. 결과 집합에는 "employee"와 "manager"라는 두 개의 컬럼이 포함된다. "employee" 컬럼은 직원의 이름을, "manager" 컬럼은 해당 직원의 상사의 이름을 나타낸다.
• 파트2: 각각 employees 테이블에 대한 두 개의 테이블 별칭이다. 이 별칭은 동일한 테이블을 나타낸다.
• 파트3: 이 부분은 셀프 조인을 정의한다.
  • e1 테이블과 e2 테이블을 manager_id 열과 employee_id 열을 기준으로 조인한다. 이것은 각 직원의 manager_id가 다른 직원의 employee_id와 일치하는 경우 두 테이블을 연결한다.
  • LEFT JOIN은 왼쪽 테이블(e1)의 모든 행을 포함하고, 오른쪽 테이블(e2)의 일치하는 행을 가져온다. 이것은 모든 직원에 대한 정보와 그 직원의 상사 정보를 반환하게 된다.

조회 결과 예시:

• MIke Johnson를 제외한 모든 직원은 자신의 상사가 있다.
• MIke Johnson는 상사가 없어 Null값이 표시된다.

 

교재에 있는 예제:

ERD

 

'ordes', 'orderdetails', 'customers'테이블의 컬럼과 관계에 주목

ERD와 관련한 내용은 아래 링크 참고:

https://hsyhrae.tistory.com/28

[MySQL] ERD 확인 및 추출하기

 

조회 목적: 국가별 재구매율 조회

교재에서 정의한 재구매율: 특정기간 1(ex. 2003년) 구매자 중 특정기간 2 (ex. 2004년)에 연달아 구매한 구매자 비중이다.

수식으로 표현하면 '2004년 구매자수 /  2003년 구매자수'

 

예제 코드:

SELECT 
    C.country
    , SUBSTR(A.orderdate, 1, 4) YY
    , COUNT(DISTINCT A.customernumber) BU_1
    , COUNT(DISTINCT B.customernumber) BU_2)
    , COUNT(DISTINCT B.customernumber) / COUNT(DISTINCT A.customernumber) AS 재구매율
FROM orders A
LEFT
JOIN orders B
ON A.customernumber = B.customernumber
    AND SUBSTR(A.orderdate, 1, 4) = SUBSTR(B.orderdate, 1, 4) -1
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1,2
;

1. SELECT 절:

• C.country: 국가 정보를 선택한다.
• SUBSTR(A.orderdate, 1, 4) YY`: 주문 날짜에서 연도 정보를 추출하여 `YY` 컬럼으로 선택한다.
• COUNT(DISTINCT A.customernumber) BU_1`: 2003년 고유 구매자 수를 계산하고 별칭은 `BU_1`이다.
• COUNT(DISTINCT B.customernumber) BU_2`: 2004년 고유 구매자 수를 계산하여 별칭은 `BU_2`이다.
• COUNT(DISTINCT B.customernumber) / COUNT(DISTINCT A.customernumber) AS 재구매율`: 2004년 구매자 수를 2003년 구매자 수로 나누어 재구매율을 계산한다. 이 컬럼의 별칭은 `재구매율` 컬럼이다.

2. FROM 절:

• 'orders A' 및 'orders B'는 'orders' 테이블의 별칭이다. 이 별칭은 셀프 조인을 수행하는 데 사용된다.
• 'customers C'는 고객 정보를 포함하는 'customers' 테이블이다.

3. LEFT JOIN:

• 첫 번째 'LEFT JOIN' 절에서는 2003년과 2004년에 대한 주문 정보를 조인한다.
• 'A.customernumber = B.customernumber'는 고객 번호가 동일한 주문을 연결하고,
• 'SUBSTR(A.orderdate, 1, 4) = SUBSTR(B.orderdate, 1, 4) - 1'은 주문 연도가 연속되는 주문만 조인한다.
• 두 번째 `LEFT JOIN` 절에서는 고객 번호를 기준으로 주문 정보(`orders`)와 고객 정보(`customers`)를 조인한다.

4. GROUP BY:
   - 결과 집합을 'C.country', 'YY' 칼럼으로 그룹화한다. 이렇게 하면 결과를 국가 및 연도별로 그룹화하여 각 조합에 대해 계산을 수행할 수 있다.

조회화면:

• '2003', '2004'년의 재구매율을 확인할 수 있다.
• 하지만 해당 데이터에는 '2006'년의 데이터가 없어 '2005'년 기준의 재구매율은 확인할 수 없다.