데이터베이스 질문과 답변
by 안떠니 [SQL Query] INNER JOIN NOT IN GROUP BY SUM [2021.04.07 12:53:17]
하려는 일을 최대한 간단히 설명하면,
상품 하나가 세일 가격을 시작할 때, 이 상품을 과거에 정상가로 구매한 적이 있는 고객 목록을 추출하고,
혹시 똑같은 내용 (상품코드 + 세일만료일 + 고객ID) 로 이멜을 보낸 적이 있으면 목록에서 제거하는 것입니다.
receipt - 주문서 (master table)
receipt_item - 주문서의 상품 내역 (slave table)
다른 쇼핑몰과 살짝 다른 부분은 receipt_item 에 상품이 할인 중인 상품이라면,
할인 가격이 컬럼으로 존재하지 않고, 동일한 정보를 추가 record 로 입력하고,
대신 할인 가격을 음수로 대입합니다. 그러면, 마지막 합산에 자동으로 할인 가격이 적용되는 구조이죠.
그래서, 아래 코드에 GROUP BY member_id, item_id, uploaded_dt 를 사용했고, sum 으로
정상 가격 (할인 이전 가격) 으로 구매했는지 확인하고 있습니다.
여기서 질문은 마지막 NOT IN (SELECT...) 입니다.
똑같은 할인 정보로 이멜을 보낸 적이 있다면, 해당 고객을 목록에서 제거하려고 NOT IN (SELECT)
를 사용했습니다. 하지만, 이 방법 말고 JOIN 으로 할 수 있는 방법도 있을까 해서 질문드려 봅니다.
2,3 년전에 마농님께 조인을 배워서, 아래와 같은 문장을 완성하기는 했지만,
아직도 반신반의 하고 있습니다. 문장이 잘못 되었거나, 다른 조인 방법으로 개선될 부분이 있다면,
아낌없는 조언 부탁드립니다.
감사합니다.
WITH receipt(member_id, uploaded_dt, file_name) AS
(
SELECT '111880574', '03-24-2021', 'a.jpg' FROM dual
UNION ALL SELECT '111880575', '03-25-2021', 'b.jpg' FROM dual
UNION ALL SELECT '111880576', '03-26-2021', 'c.jpg' FROM dual
UNION ALL SELECT '111880577', '03-27-2021', 'd.jpg' FROM dual
)
,receipt_items(member_id, uploaded_dt, item_id, price) AS
(
SELECT '111880574', '03-24-2021', '638854', 9.79 FROM dual
UNION ALL SELECT '111880574', '03-24-2021', '638854',-2.00 FROM dual
UNION ALL SELECT '111880575', '03-25-2021', '638854', 9.79 FROM dual
UNION ALL SELECT '111880576', '03-26-2021', '638854', 9.79 FROM dual
UNION ALL SELECT '111880576', '03-26-2021', '638854',-2.00 FROM dual
)
,receipt_email(member_id, item_id, expire_dt) AS
(
SELECT '111880576', '638854', '04-25-2021' FROM dual
UNION ALL SELECT '111880577', '638854', '04-25-2021' FROM dual
)
, members(member_id, email, wh1) AS
(
SELECT '111880574', 'a@msn.com', 116 FROM dual
UNION ALL SELECT '111880575', 'b@msn.com', 116 FROM dual
UNION ALL SELECT '111880576', 'c@msn.com', 116 FROM dual
UNION ALL SELECT '111880577', 'd@msn.com', 116 FROM dual
)
SELECT b.email, c.file_name, d.member_id, d.item_id, d.price
FROM receipt c
INNER JOIN
(
SELECT member_id, item_id, sum(price) as price
FROM receipt_items
WHERE item_id = '638854'
GROUP BY member_id, item_id, uploaded_dt
) d
ON c.member_id = d.member_id
INNER JOIN
members b
ON b.member_id = c.member_id
WHERE (d.price = 9.79) AND
(b.wh1 = 116) AND
d.member_id NOT IN (SELECT member_id
FROM receipt_email
WHERE item_id = '638854' AND
expire_dt = '04-25-2021')
1. 테이블 설계가 애매하네요.
- PK 나 FK 설정이 member_id 하나 만으로는 부족한 듯 하네요.
- 가격 조건이나 만료일 조건이 상수로 들어가는 부분도 유연성이 없어 보이구요.
2. NOT IN 은 대체 구문은
- NOT EXISTS
- OUTER JOIN 후 IS NULL 체크
쉬는 시간 중에 짬을 내서 댓글 주셔서 정말 감사합니다.
receipt 테이블에 member_id, uploaded_dt 이 PK 이고,
receipt_items 테이블에 member_id, uploaded_dt 이 FK 입니다.
with 문에 uploaded_dt 컬럼에 날짜만 적고 시간을 잊어버리고 추가하지 않아서, 오해를 일으킨 것 같습니다.
가격 조건과 만료일은 SQL 을 테스트하기 위해 상수로 대입한 것입니다.
실제에는 세일 상품이 승인되어 하나씩 요청될 때마다 위의 문장을 실행하게 되는 구조입니다.
다른 테이블에서 불러와서 묶는 것이 아니라요.
OUTER JOIN 후에 IS NULL 체크 방식을 사용하는 이유가 있을까요?
다시 한번 감사합니다.
조인 조건에 uploaded_dt 가 추가되어야 하구요.
OUTER JOIN 후에 IS NULL 체크 방식을 사용하는 이유가 따로 있다기 보다는
조인 방식을 물으셔서 그렇게 답변 드린 것도 있고요.
일반적으로 서브쿼리 방식보다는 조인 방식이 더 빠른 경우가 많습니다.
다양한 방식을 익혀 두고 그때 그때 상황에 맞게 선택하여 사용하시면 됩니다.
그런데,
위 쿼리는 동일 제품을 여러번 구매했을 경우에 대한 고려가 없네요.
예를 들면 한번은 정상가 구매, 또 한번은 할인가 구매 한 회원은
- 정상가 구매내역이 있으니 결과에 포함되어야 할까요?
- 할인가 구매내역이 있으니 결과에 제외되어야 할까요?
조인 조건에 uploaded_dt 추가되어야 하는 지적, 정말 감사드립니다.
미처 발견하지 못했었네요. 이 조건이 없으면, 마스터 테이블 receipt 에서 정확한 레코드를 가져오지 못하겠죠.
지적해 주신 부분은 , 저도 고려한 부분입니다.
WHERE (d.price = 9.79) // 예시를 위해 상수로 표기했으며, 세일 상품이 결정될 때, 이와 같은 정상가 금액을 받아오게 됩니다.
따라서, 이 조건문으로 정상가 구매 내역만을 결과에 포함시킬 수 있게 되며,
동일한 고객이 할인가 구매를 한 기록이 있는 것은 이 조건에 맞지 않기에 결과에 포함되지 않게 됩니다.
혹시 제가 잘못 생각하고 있다면, 또 한번 조언 부탁드립니다.
감사합니다.
한 고객이 동일 제품을 여러번 구매했는데
구매내역중에는 정상가 구매도 있고 할인가 구매도 있을 경우
이 고객은 메일 대상자에 포함시켜야 할까요? 제외시켜야 할까요?
- 정상가 구매내역이 있으니 결과에 포함되어야 할까요?
- 할인가 구매내역이 있으니 결과에 제외되어야 할까요?
몬가 제가 잘못한게 있는 것 같은데, 잘 모르겠네요.
제 생각에는 정상가로 구매한 기록이 있으면, 포함 시켜야 하죠.
단 한번이라도, 즉 10번 구매한게 할인가였고, 딱 1번이 정상가였으면,
이 사람에게도 메일이 발송되게 해야죠.
제가 잘못한게 있나 싶어서, 테스트 데이터를 더 추가해서,
동일 고객이 한번은 정상가, 한번은 할인가로 설정하고,
쿼리를 실행했더니, 정상가 구매 기록 때문에, 쿼리 결과 (이멜 보낼 대상 고객) 에 포함되어 나왔습니다.
이렇게 되면, 쿼리 실행이 맞는거 아닐까요?
그리고, 말씀해 주신, OUTER JOIN 과 IS NULL 이라는걸루 구글링을 해보니, 좋은 글이 있어서,
한번 링크를 가져와 봤습니다. 이 주제로 꽤 많은 글들이 있어서 좀 놀랐습니다. 이런걸 여태 모르고 있었구나 싶어서요. ㅠㅠ
https://explainextended.com/2009/09/17/not-in-vs-not-exists-vs-left-join-is-null-oracle/
쿼리 조건 자체가 틀렸다는게 아니라
기준을 정확하게 세운게 맞는지를 물은 것입니다.
기준을 어떻게 정했는지에 따라 쿼리는 달라져야 합니다.
기준을 정확하게 세우고 그 기준에 맞게 조건을 줬다면. 맞는거죠.
SELECT d.email
, c.file_name
, d.member_id
, d.uploaded_dt
, d.item_id
, d.price
FROM (SELECT b.email
, a.member_id, a.uploaded_dt, a.item_id
, SUM(a.price) price
FROM receipt_items a
INNER JOIN members b
ON a.member_id = b.member_id
WHERE a.item_id = '638854'
AND b.wh1 = 116
GROUP BY b.email
, a.member_id, a.uploaded_dt, a.item_id
HAVING SUM(a.price) = 9.79
) d
INNER JOIN receipt c
ON d.member_id = c.member_id
AND d.uploaded_dt = c.uploaded_dt
LEFT OUTER JOIN receipt_email e
ON d.member_id = e.member_id
AND d.item_id = e.item_id
AND e.expire_dt = '04-25-2021'
WHERE e.member_id IS NULL
;
아, 저는 제가 몰 잘못 생각하고 있다는 것을 지적하신 줄 알았습니다.
기준은 10번 구매 중에서 한번이라도 정상가 구매 기록이 있으면, 메일 발송을 하는 것이였습니다.
쉬는 시간에도 이렇게 댓글을 달아주신 것 진심으로 감사합니다.
일반 어플 개발에서 로직 구현이랑 쿼리 작성은 정말 다른 세계라는걸 또 한번 느끼고 가네요.
) 버튼을 클릭하여 작성 하시면 됩니다.