[Average Selling Price] 문제 테스트케이스 실패

종훈님 안녕하세요:)

예전에는 위 풀이로 통과가 되었는데, 이번에 새로운 테스트 케이스가 추가되어서 통과가 안되었네요. 새로운 테스트 케이스 product_id=3을 포함하기 위해서는, 말씀 주신대로 left outer join으로 진행해야 됩니다.

제보 감사합니다. 업데이트된 코드 및 설명은 강의의 수업자료에 업데이트하였습니다. 새롭게 다운받아주시기 바랍니다. 상세한 설명은 아래와 같습니다.

결과적으로는 아래의 쿼리로 진행해야 됩니다.

select p.product_id,
ifnull(round(sum(u.units*p.price)/sum(u.units), 2),0) average_price
from Prices as p
left join UnitsSold as u
on p.product_id = u.product_id
and u.purchase_date between p.start_date and p.end_date
group by p.product_id;

left join 이후에, Prices의 product_id=3은 UnitsSold와 조인할 부분이 없기 때문에, UnitsSold 부분에서는 null로 나올 것입니다.

그 뒤에 select문에서 product_id=3을 처리하기 위해서 ifnull을 사용하여 ifnull(round(sum(u.units*p.price)/sum(u.units), 2),0)로 작성합니다.

위 정답을 떠나서 중요하게 집고 넘어가야 하는 부분은,

u.purchase_date between p.start_date and p.end_date를 where조건이 아닌, on조건에 추가해야 됩니다.

join의 on조건과 where조건의 실행 순서는 다음과 같습니다.

1. on조건을 기준으로 '조인'한 후에, 2. where조건을 기준으로 '필터링' 합니다.

만약 u.purchase_date between p.start_date and p.end_date조건을 on조건에 안쓰고, where조건에 쓰면 다음과 같이 결과가 나옵니다. (틀린 결과)

1. on조건을 기준으로 '조인'한 후에

   
   -> on p.product_id=u.product_id만 쓰는 과정에서

   product_id=1은 2x2=4개로 뻥튀기 되고, product_id=2도 2x2=4개로 뻥튀기 됩니다.

2. where조건을 기준으로 '필터링' 합니다.

-> where조건으로 필터링 되는 과정에서, 우리가 원했던 product_id=3도 필터링이 됩니다. 그러다보니 product_id=3을 포함시키려고 사용한 left outer join의 의미가 사라지게 됩니다.

결과적으로 위 쿼리는 우리가 원했던 결과가 아닌, 틀린 결과입니다.

left outer join에서 on조건과 where조건을 혼용할 때 많이 발생하는 실수입니다.

1. on조건을 기준으로 '조인'한 후에, 2. where조건을 기준으로 '필터링' 하기 때문에

left outer join에서 on조건과 where조건을 잘 구분해서 작성해야 됩니다.

대신, inner join에서는 2개의 조건을 혼용해도 결과가 똑같기 때문에 신경을 덜 쓰셔도 됩니다.

혹시 위 글 읽어보시고 이해가 안가는 부분이 있으면, 다시 한번 말씀 부탁드립니다:)

친절한 답변 감사합니다🙇‍♂

다만, 궁금한게 있습니다. 저는 지금까지 on 조건에는 join을 하기 위한 두 테이블 간에 서로 같은 컬럼만 연결해주는 느낌으로 알고 있었습니다.
지금처럼 범위 관련된 조건을 on에 넣는 케이스를 본 적이 없어서 개념이 헷갈립니다.

on 조건절을 어떻게 이해하는게 좋을까요?!

안녕하세요, 인프런 AI 인턴입니다.

이러한 유형의 문제에 대해서는 익혀야 할 중요한 SQL 개념이 두 가지 있습니다: INNER JOIN과 LEFT OUTER JOIN. INNER JOIN은 두 테이블의 교집합, 즉 두 테이블 모두에 존재하는 데이터만을 반환합니다. 반면에 LEFT OUTER JOIN은 왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에 해당하는 데이터가 없다면 NULL 값을 사용하게 됩니다.

질문하신 내용과 유사한 상황을 다루는 답변이 있어서 첨부해 드립니다. 다음 답변 링크에서는 SQL 문제를 해결하기 위한 다양한 조인 방법과 이를 적용하는 방법에 대해 설명하고 있습니다.

• EASY INNER JOIN 관련 average-selling-price 연습문제
  

해당 답변에서는 Prices 테이블과 UnitsSold 테이블을 조인할 때 사용되는 조건인 AND p.start_date <= u.purchase_date와 AND u.purchase_date <= p.end_date 부분이 왜 필요한지에 대한 설명이 있습니다. 두 테이블의 특정 조건을 충족하는 데이터만을 이용해야 한다는 것이 핵심입니다.

질문자님의 경우에는, INNER JOIN으로는 product_id가 3인 row가 UnitsSold 테이블에 없을 때 결과로 나타나지 않는 문제가 발생합니다. 이를 해결하기 위해서는 LEFT OUTER JOIN을 사용하면 됩니다.

다음은 LEFT OUTER JOIN을 사용한 예제 쿼리입니다:

SELECT p.product_id, IFNULL(round((sum(u.units*p.price) / sum(u.units)),2), 0) as average_price
FROM Prices as p
LEFT OUTER JOIN UnitsSold as u
ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

이 쿼리는 Prices 테이블의 모든 product_id를 가져오며, UnitsSold 테이블에 해당 product_id에 대한 데이터가 없을 경우 평균 가격을 0으로 처리합니다 (IFNULL 함수 사용).

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.