250307 TIL

-- 개인 (소매) 고객
-- 이메일 프로모션 수락
SELECT COUNT(DISTINCT business_entity_id) AS customer_count
FROM person
WHERE person_type = 'IN'
AND (email_promotion = 1 OR email_promotion = 2)

해설

SELECT COUNT(DISTINCT business_entity_id) as customer_count
FROM qcc.person
WHERE email_promotion > 0 -- 0 = 이메일 프로모션을 받지 않음
AND person_type = 'IN' -- IN = 개인(소매) 고객

여기서 business_entity_id는 PK값으로 DISTINCT를 하지 않아도 답이 나오지만, 실무에서 불필요한 실수를 방지하기 위해 (ex. 원본 데이터 담당자가 실수, 내가 PK값을 잘못 알고 있을 경우) DISTINCT를 대부분의 상황에서 사용하신다고 했다. 나도 헷갈려서 DISTINCT를 넣었다 뺐다 돌려봤는데 넣는 게 더 좋을 거 같아서 넣었다. 굿굿
WHERE email_promotion <> 0
혹은 WHERE email_promotion IN(1,2)로도 작성 가능
나는 왜 자꾸 IN을 안 쓰는지 모르겠다 반성하렴
쉬운 길을 놔두고 굳이.. 또 저렇게 써버렸네 .... 🥲
주석을 다는 건 좋은 습관이라고 한다. 하도 문제 읽고 빠트리거나 헷갈리는 게 많아서 요즘 벨로그에도 조건을 정리해놓고 풀어서 QCC할때도 적용해봤더니 좋았다!

문제 2) VIP 고객 산출

풀이

-- 2011-10 회사 제품 수량 총 70개이상 고객 o
-- 취소 주문 제외 o
-- 고객 정보 포함 / ID, 이름, 성, 총 주문 수량 출력
-- 고객 ID 기준 오름차순 정렬
WITH filter AS 
(
  SELECT sales_order_id,
       customer_id,
       order_date
FROM sales_order_header
WHERE (order_date BETWEEN '2011-10-01' AND '2011-10-31')
      AND status != 6
  ),

WITH calculate AS 
(
  SELECT sales_order_id,
       SUM(order_qty) AS total_qty
FROM sales_order_detail
GROUP BY sales_order_id
HAVING total_qty >= 70
  )

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       ca.total_qty
FROM filter f
INNER JOIN calculate ca 
ON f.sales_order_id = ca.sales_order_id
INNER JOIN sales_customer c 
ON f.customer_id = c.customer_id
INNER JOIN person p 
ON f.customer_id = p.business_entity_id

일단 틀린 코드인데 . . . .테이블 설명 읽는 것부터 좀 고난이었고 시간이 모자라서 촉박하게 제출하느라 마지막 컬럼을 제대로 못 썼다

다시 돌아와서 동진님이 머리 싸매고 도와주셨다
~~외쳐 갓동진~~

수정코드1

WITH filter AS 
(
  SELECT sales_order_id,
       customer_id,
       order_date
FROM sales_order_header
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2011-10'
      AND status != 6
  ),

calculate AS 
(
  SELECT sales_order_id,
       SUM(order_qty) AS total_qty
FROM sales_order_detail
GROUP BY sales_order_id
HAVING total_qty >= 70
  )

SELECT c.customer_id,
       p.first_name,
       p.last_name,
       ca.total_qty
FROM filter f
INNER JOIN calculate ca 
ON f.sales_order_id = ca.sales_order_id
INNER JOIN sales_customer c 
ON f.customer_id = c.customer_id
INNER JOIN person p 
ON c.person_id = p.business_entity_id
ORDER BY c.customer_id

그런데 이 쿼리의 문제점이 또 동진님에 의해 밝혀졌는데..!

고객이 2011-10에 한 번의 주문에서 70개 이상 구매했는지 < 를 계산하는 쿼리라는 것
내가 쓴 BETWEEN 2011-10-01 AND 2011-10-31은 11/10/31 00:00:00까지만 계산한다는 것

그런데 왜 정답으로 출력되는지 궁금해서 준수 튜터님한테 가서 여쭤봤다.

굳이 WITH를 쓸 거면 위에서 JOIN으로 한번 묶어주고 calculate절이 굳이 필요가 없다.
이 문제 테이블에서 각 고객이 주문한 건수가 1건밖에 되지 않아서 합친 값이 똑같이 나온다
-> 문제의 목적은 고객이 2011-10에 총 70개 이상 구매했는지를 구하는 것이어서 수정을 하는 게 맞다

그래서 또 다시 수정..

수정코드2

WITH filter AS
(SELECT h.customer_id,
	   SUM(d.order_qty) AS sum_qty
FROM sales_order_header h
INNER JOIN sales_order_detail d 
ON h.sales_order_id = h.sales_order_id
WHERE DATE_FORMAT(h.order_date, '%Y-%m') = '2011-10'
	  AND h.status != 6
HAVING sum_qty >= 70)

SELECT c.customer_id,
       p.first_name,
       p.last_name,
       f.total_qty
FROM `filter` f
INNER JOIN sales_customer c
ON f.customer_id = c.customer_id
INNER JOIN person p
ON c.person_id = p.business_entity_id
ORDER BY c.customer_id

훨씬 깔끔하게 정리가 되었다
근데 사실상 정답 코드가 훨 명확하고 간단하기 때문에 WITH구문은 굳이 사용할 필요가 없다.

~오늘의 결론~
쉬운 길 놔두고 어려운 길로 돌아가지 말자

해설

필요한 정보 정리해두기
c.customer_id, p.first_name, p.last_name, h.order_qty
날짜먼저 필터

SELECT min(order_date), max(order_date) -- 이걸로 먼저 확인할 수 잇음 날짜 잘 불러 왔는지 
FROM sales_order_header 
WHERE DATE_FORMAT(order_date, '%Y-%m) = '2011-10'

qty 정보 필요하므로 JOIN / status 조건추가 / qty 조건추가

SELECT soh.customer_id, 
       sum(sod.order_qty) AS order_qty 
FROM sales_order_header soh
JOIN sales_order_detail sod 
ON soh.sales_order_id = sod.sales_order_id
WHERE DATE_FORMAT(order_date, '%Y-%m) = '2011-10'
AND soh.status <> 6
GROUP BY 1
HAVING order_qty >= 70

이름 정보 불러오기

JOIN 2개 추가
JOIN 순서가 중요하긴 함
정렬까지하면 끝

SELECT soh.customer_id,
	   p.first_name, -- 
       p.last_name, 
       sum(sod.order_qty) AS order_qty 
FROM sales_order_header soh
JOIN sales_order_detail sod 
ON soh.sales_order_id = sod.sales_order_id

JOIN sales_customer sc -- 조인 추가 1
ON soh.customer_id = sc.customer_id

JOIN person p -- 조인 추가 2
ON sc.person_id = p.business_entity_id

WHERE DATE_FORMAT(order_date, '%Y-%m) = '2011-10'
AND soh.status <> 6
GROUP BY 1
HAVING order_qty >= 70
ORDER BY soh.customer_id