[SQL 기본] 예제_프로모션 페이백 대상자 선정

🚨요청사항
이번 설 연휴를 마자 고객 대상 구매금액의 일정 부분 포인트로 돌려주는 페이백 프로모션을 진행했습니다. 정산기간인 설 연휴 동안 구매금액이 20만 원 이상인 고객을 대상으로 결제수단에 따라 카드 5%, 현금 10%에 해당하는 금액이 페이백 대상입니다. 구매금액 정산에는 '의류', '잡화' 항목만 해당되며 지급을 위해 대상이 되는 고객번호, 결제수단, 구매가격, 환급금액이 필요합니다.

구매 테이블 명세서

이번 예제는 지난 예제보다 난이도가 낮기 때문에 충분히 아래 설명 없이 해결하실 수 있을 겁니다. 아래 설명 없이 스스로 문제를 풀어본 뒤에 맞춰보는 것도 좋습니다.

• SELECT 절에는 우리가 얻고자 하는 값을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 다음과 같습니다.
  
  • 값: 고객번호, 결제수단, 구매가격, 환급금액

• FROM 절에는 해당 데이터가 담긴 테이블을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 테이블 명세서에 명시되어 있습니다.
  • 테이블: member_order
• WHERE와 HAVING절에는 우리가 원하는 데이터를 추출하기 위한 필터링 조건을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 다음과 같습니다.
  • 조건: 연휴기간동안 구매, 연휴기간 총 구매금액이 20만 원(집계 후 조건), 구매항목이 '의류'이거나 '잡화'

여기서 우리가 원하는 데이터를 얻기위해 필요한 구문들과 함수들이 어떤 것인지 생각할 수 있어야 합니다. 구매금액을 출력하기 위해선 집계함수를 활용해야 하고, 환금금액을 출력하기 집계함수로 도출한 구매금액으로 산술연산을 해야 합니다.

 

간단하게 위에 정리한 정보들을 가지고 쿼리를 작성해 보겠습니다.

SELECT member_no, payment_type, SUM(order_price), SUM(order_price) * 5%? 10%(?) 
FROM member_order 
WHERE date BETWEEN '2024-02-09' AND '2024-02-12' 
	AND goods_category =  '의류' AND goods_category = '잡화'(?) 
GROUP BY member_no, payment_type
HAVING SUM(order_price) > 200000

 

이제 이 정도는 우리가 배운 것들을 바탕으로 쉽게 작성해 나갈 수 있습니다. 다만 위와 같이 작성한다면 우리가 원하는 결과를 얻지 못할 수 있습니다. 먼저 WHERE 절부터 확인해 봅시다. 구매기간에 대한 필터링은 BETWEEN 함수를 통해 멋지게 해결했지만 구매 카테고리를 필터링하는 과정에서 수정이 필요해 보입니다. 

 

우리가 쿼리를 작성하는 과정은 데이터를 추출하기 위해 집합을 만들어 나가는 과정이라고 생각하시면 됩니다. 우리가 학창 시절 배웠던 집합을 생각해 보면 집합에는 합집합, 교집합, 차집합 여집합과 같이 여러 가지가 존재하는데 WHERE 절이나 HAVING 절에서는 바로 그 집합의 종류를 설계한다고 생각하시면 됩니다.

 

만약 위의 쿼리처럼 WHERE절에서 AND조건을 계속해서 나열한다면 그것은 조건에 해당하는 집합들의 교집합을 계속해서 만드는 것이라고 보시면 됩니다. 다만, 요청사항을 잘 살펴보면 우리가 원하는 바는 교집합을 만드는 것이 아니라는 것을 확인할 수 있습니다. 합집합이 필요한 조건이 있는데 그것이 바로 구매 카테고리 조건입니다. 만약 교집합을 구하는 AND 연산자로 '의류'와 '잡화' 조건을 나열한다면 상품 중에는 의류이면서 잡화인 상품은 없기 때문에 쿼리결과에 아무것도 나오지 않게 됩니다. 그럴 때 활용하는 것이 합집합을 만들 수 있는 OR 연산자가 있습니다. 쉽게 생각해 AND는 ~이면서, OR는 ~이거나로 생각하시면 편합니다. 그렇기 때문에 카테고리 조건은 OR로 나열되어야 하며 아래와 같이 수정할 수 있습니다. 

 

SELECT member_no, payment_type, SUM(order_price), SUM(order_price) * 5%? 10%(?) 
FROM member_order 
WHERE date BETWEEN '2024-02-09' AND '2024-02-12' 
	AND (goods_category =  '의류' OR goods_category = '잡화')
GROUP BY member_no, payment_type
HAVING SUM(order_price) > 200000

 

자, 그렇지만 이렇게 같은 컬럼을 여러번 쓰게 되는 것은 귀찮기도하고 미관상 깔끔해보이지도 않은 것 같아요. 그렇기 때문에 같은 컬럼을 활용해 OR 조건을 나열할 때 쓸 수 있는 좀 더 편한 친구를 소개해드립니다. 그것은 바로 IN입니다. IN이라는 이름이 주는 느낌처럼 우리가 정의한 항목들 안에 하나라도 포함되어 있다면 반환하는 기능을 가지고 있습니다. 

SELECT member_no, payment_type, SUM(order_price), SUM(order_price) * 5%? 10%(?) 
FROM member_order 
WHERE date BETWEEN '2024-02-09' AND '2024-02-12' 
	AND goods_category IN ('의류', '잡화')
GROUP BY member_no, payment_type
HAVING SUM(order_price) > 200000

 

좀 더 편하고 깔끔하죠? 자 그럼 이어서, 환급금액을 도출하는 산술연산을 해결해 봅시다. 카드결제는 5%, 현금결제는 10%이기 때문에 때에 따라 다른 값으로 산술연산을 해야 합니다. 뭔가 어려워 보이지만 잠깐 생각해 보면 우리는 엑셀, 스프레드시트 등에서도 많이 활용한 기능이라는 걸 알 수 있습니다. 그것은 바로 조건문 IF입니다. 조건에 따라 다른 값을 출력하는 IF를 활용한다면 때에 따라 다른 산술연산이 가능합니다. IF는 IF(조건, 참일 때 반환할 것, 거짓일 때 반환할 것) 이런 형태로 활용할 수 있는데 많이 익숙한 함수이기 때문에 쉽게 적응하실 수 있습니다. 그럼 조건문인 IF를 활용해 우리의 쿼리를 완성하러 가봅시다. 

 

SELECT 
	member_no, 
    payment_type, 
    SUM(order_price), 
    SUM(order_price) * IF(payment_type = '카드', 5%, 10%)
FROM member_order 
WHERE date BETWEEN '2024-02-09' AND '2024-02-12' 
	AND goods_category IN ('의류', '잡화')
GROUP BY member_no, payment_type
HAVING SUM(order_price) > 200000

 

이제는 이 정도 요청사항은 쉽게 처리할 수 있는 수준이 되었습니다. 이제 기본에 대한 내용은 이쯤에서 마무리하고 다음부터는 좀 더 심화된 SQL을 배워보도록 하겠습니다. 

 

함께 성장해요.