[My SQL] 서비스 분석 | JOIN 활용 - 서비스/제품 교차구매 여부 확인하기

오늘은 JOIN 구문을 활용해서 교차 구매자 데이터를 확인하는 쿼리문을 연습했다.
 

• Table 1:  Service1
• Table 2: Service2

우리 회사에 Service 1과 2가 있다고 가정했을 때,
Service 1을 구매한 고객이 Service 2도 구매했을 때의 고객 수를 알아보자!
 
 

#1 |  LEFT JOIN을 통해 각 서비스 교차 사용 유저 id를 리스트업

SELECT A.customer_id AS service1_user
      ,B.customer_id AS service2_user
FROM Service1 A
LEFT JOIN Service2 B
ON A.customer_id = B.customer_id
GROUP BY 1
ORDER BY 2 DESC ;

 
 
먼저 JOIN 구문을 활용해서 service1 유저의 id를 기준으로 service2 유저의 id를 연결한다.
service1 유저는 GROUP BY를 통해 그룹핑하여 중복을 제거했다.
 
 

 
 
위의 결과값과 같이 service1 을 구매한 유저가 service2 를 구매했다면 동일한 id가 연결되고, 
구매하지 않았다면 NULL값을 띄게 된다.
 
작성한 쿼리문에 중첩 쿼리문(서브쿼리)을 작성하여, 살을 더 붙여보겠다.
 
위에 작성한 쿼리문을 FROM 절에 묶어 인라인뷰를 생성한다.
 
그리고 SELECT 문에는 CASE WHEN 함수를 활용해서 교차구매를 한 유저는 'Y' 값으로, 교차구매를 하지 않은 유저는 'N'으로 변경하여 교차 구매여부를 카운팅 해주었다.
 

 
#2 |  교차 유저 리스트를 CASE WHEN 구문을 활용해 교차 구매 여부 지정 후 그룹핑

SELECT  CASE   -- #2 | 교차 유저 리스트를 CASE WHEN 구문을 활용해 교차 구매 여부 지정 후 그룹핑
        WHEN service2_user IS NULL THEN 'N' 
        ELSE 'Y' END AS '교차구매'
       ,COUNT(*) cnt
FROM (                         
       SELECT A.customer_id AS service1_user
             ,B.customer_id AS service2_user
       FROM Service1 A
       LEFT JOIN Service2 B
       ON A.customer_id = B.customer_id
       GROUP BY 1
       ORDER BY 2 DESC
      ) C      -- #1 | LEFT JOIN을 통해 각 서비스 교차 사용 유저 id를 리스트업
GROUP BY 1
ORDER BY 2 ;

 
 
위의 쿼리문을 실행한 결과,
 
service1과  service2를 교차 구매한 유저 수는 5,000명 정도이고 service1만 이용한 유저는 50,000명 정도로,
약 9% 유저가 서비스 2가지를 모두 이용하는 셈이다.
 
 

---

🔋 My SQL 서비스 분석 포스팅  더 보기 ⇩
 

[My SQL] 서비스 분석 | 재구매 / 구매간격 / 구매주기