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

SQL 코딩테스트를 위한 첫 걸음

inner join, left join

해결된 질문

작성

173

코드

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

현재 inner join으로 코드를 작성해봤습니다. submit을 하고나니 아래와 같이 테스트케이스에 통과하지 못했습니다!

[ 문제 상황 ]

테스트케이스를 확인해보니 Prices 테이블에 product_id가 3인 row가 있음
inner join을 했으므로 3번에 대한 결과가 나오지 않음
실제 결과값에는 3번도 포함되어야한다

[ 궁금한 사항 ]

결과를 만족하려면 left outter join을 사용해야할 것 같습니다. 하지만 어떻게 해야지 잘 적용할 수 있는지 감이 잘 안옵니다.
left outer join을 활용한 풀이와 설명 부탁드립니다!

sql mysql 코딩-테스트

답변 3

Gemma

지식공유자

종훈님 안녕하세요:)

예전에는 위 풀이로 통과가 되었는데, 이번에 새로운 테스트 케이스가 추가되어서 통과가 안되었네요. 새로운 테스트 케이스 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조건에 쓰면 다음과 같이 결과가 나옵니다. (틀린 결과)

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 조건절을 어떻게 이해하는게 좋을까요?!

Gemma

지식공유자

종훈님 안녕하세요:)

대부분의 경우, on조건에 join을 하기 위해 두 테이블 간의 서로 같은 컬럼을 연결하는 용도로 사용합니다. 그런데 간혹 이렇게 예외적인 경우로 on조건에 작성할 필요가 있습니다. (꼭 ‘범위’ 관련된 조건이 아니라 where 조건을 on조건에 넣어야 되는 경우)

그래서 on 조건은 종훈님께서 알고 계시는 것처럼 두 테이블을 잇기 위해 사용한다라고만 이해해도 괜찮습니다. 다만 left outer join을 할때만 추가적으로 on조건을 좀더 신경 써주면 됩니다. (inner join은 추가적으로 신경 안쓰셔도 됩니다)

위의 예제에서 where 조건에 썼을 때, product_id=3가 사라지니까 left outer join을 쓰는 의미가 사라졌는데요. 그래서 left outer join을 할 때는 데이터 중간 결과를 확인하면서, 내 의도대로 결과가 나오는지 혹시라도 where 조건 때문에 원했던 데이터가 필터링이 되지는 않았는지 추가적인 확인만 하시면 됩니다.

문제가 되는 경우는 left outer join의 right에 해당 하는 테이블 (예: UnitsSold)의 조건을 where조건에 쓸 때 입니다.

인프런 AI 인턴

안녕하세요, 인프런 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 함수 사용).

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

인프런 커뮤니티 질문&답변

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

코드