[문제 조건] - Person_Person 테이블을 사용하여 다음 조건을 만족하는 고객의 수를 구하세요 - 이메일 프로모션에 “동의”한 고객 해당 고객들 중 "개인(소매)" 고객의 수 - 출력 컬럼 : customer_counts → WHERE 절로 필터링 하기
[문제 해결]
테이블 확인
emailpromotion 컬럼에 고유 값 확인 : 0 (프로모션 x) 1,2 (프로모션 o)
emailpromotion 컬럼에 null 값 여부 확인 : 없음 `왜?` 0이 아닌경우로 필터링 할경우 null값도 출력 되기 때문에 같이 확인함
조건
emailpromotion 컬럼에서 프로모션에 동의한 고객이면서
PersonType 컬럼 값이 'IN' 일때
고객수 집계 : COUNT(DISTINCT 고객ID)
SELECT COUNT(DISTINCT BusinessEntityID) AS customer_count
FROM Person_Person pp
WHERE EmailPromotion <> 0
AND PersonType ='IN'
[오류사항 & 피드백]
오답 `왜?` : 조건 값 잘못 기재 함 . 개인(소매) 고객 값을 'SC'로 잘못 봄.
문제와 조건 값을 잘 읽자..!
TIP! 고유 값 집계할 때, PK 이여도 DISTINCT 를 꼭 적어줘야 함.
☑️ 문제2. 10월 한달간 구매한 고객에게 그룹별 특별 할인 쿠폰 제공 하기
[문제 조건] - 주문 날짜가 “2011-10-01 부터 2011-10-31" 사이에 해당 - 고객의 총 주문 수량에 따라 5가지 등급으로 분류 VIP : 100 개 이상 GOLD : 70 개 이상 100 개 미만 SILVER : 40 개 이상 70 개 미만 BRONZE : 20 개 이상 40 개 미만 BASIC : 20 개 미만 - 해당 고객들의 기본 정보를 포함, 총 주문 수량 기준 내림차순 정렬하여 출력 - 출력 컬럼 : customer_id , first_name , last_name , total_quantity (총 주문 수량) , customer_class (고객 등급) → 여러개 테이블 조인하기
[문제 해결]
테이블 확인
출력 컬럼 기준 테이블 erd & 조인키 확인 `왜?` 어떤 테이블 어떻게 연결해야하는지 확인 필요
Sales_SalesOrderHeader 와 Sales_SalesOrderDetail (조인키 : SalesOrderID )
Sales_SalesOrderHeader 와 Sales_Customer (조인키 CustomerID)
Sales_Customer 와 Person_Person (조인키 : PersonID , BusinessEntityID )
주문날짜 (orderdate) 타입 확인 : 시/분/초 까지 있음. str 타입
`왜?` 날짜 필터링을 위해 시/분/초 유무 & 컬럼 타입 확인하여 조건 함수 파악
조건1 : 주문 날짜가 “2011-10-01 부터 2011-10-31" 사이 (1달간)
주문날짜가 str 타입이므로 date_format 으로 변경 후 날짜 조건 필터링
DATE_FORMAT(so.orderDate,'%Y-%m-%d') BETWEEN '2011-10-01' AND '2011-10-31'
조건2 : 고객 별 총 주문 수량
group by(고객 id) 후 셀렉절에서 sum(주문 수량) 합계로 집계
조건 3 : 고객의 총 주문 수량 기준 고객 등급 나누기
case when 으로 조건별 타입 기재 한 컬럼 생성
조건 4 : 조건1 & 조건2 서브쿼리로 만든 후 아우터 쿼리에 출력컬럼 조인 & 조건 3 작성
조건5 : 정렬 조건 만족
WITH sub AS( -- 날짜 필터링 & 총 주문 수량 집계
SELECT so.CustomerID
,SUM(sd.OrderQty) AS total_quantity
FROM Sales_SalesOrderHeader so
INNER JOIN Sales_SalesOrderDetail sd ON so.SalesOrderID = sd.SalesOrderID
WHERE DATE_FORMAT(so.orderDate,'%Y-%m-%d') BETWEEN '2011-10-01'AND '2011-10-31'
GROUP BY so.CustomerID
)
SELECT s.CustomerID AS customer_id
,p.FirstName AS first_name
,p.LastName AS last_name
,s.total_quantity AS total_quantity
,CASE WHEN s.total_quantity <20 THEN 'BASIC'
WHEN s.total_quantity <40 THEN 'BRONZE'
WHEN s.total_quantity <70 THEN 'SILVER'
WHEN s.total_quantity <100 THEN 'GOLD'
ELSE 'VIP'
END AS customer_class
FROM sub s
INNER JOIN Sales_Customer c ON s.CustomerID =c.CustomerID
INNER JOIN Person_Person p ON c.PersonID =p.BusinessEntityID
ORDER BY total_quantity DESC;
TIP! join으로 연결하여 풀수있는 문제는 선 join 해준 후 필터링 하는게 쿼리 성능 면에서 빠름
TIP! DATE_FORMAT 사용시 VARCHAR로 반환 BUT DATE_SUB / DATE_ADD 등 날짜 연산 함수 사용시 자동으로 날짜 타입으로 변환되어 사용됨. (단, 데이트포맷한 값이 %Y-%m-%d 의 날짜 형이여야 함)
▼ 다양하게 날짜 필터링 하기 (date, date_format, 부등호, % )
문제 ) 주문 날짜가 “2011-10-01 부터 2011-10-31" 까지 1달간 (2022-10-01 00:00:00 ~2022-01-31 23:59:59) 필터링 시
다양한 해결법)
-- 1. date_format 함수
DATE_FORMAT(Date,'%Y-%m-%d') BETWEEN '2011-10-01' AND '2011-10-31'
DATE_FORMAT(Date,'%Y-%m') = '2011-10'
-- 2. date 함수
DATE(Date,'%Y-%m-%d') BETWEEN '2011-10-01' AND '2011-10-31'
-- 3.와일드 카드 & like
Date LIKE '2011-10%'
-- #4.부등호 사용
Date >= '2011-10-01' AND Date < '2011-11-01'
# 모두 동일한 값 출력
