sql, join 시 on 절로 인한 중복 레코드 문제

join과 데이터의 뻥튀기

• 아래에는 두 개의 테이블이 있다. 과일과 주문이다.
  • 과일에는 상품고유번호(goods_id)와 농장고유번호(farm_id)로 있다.
  • 주문에는 주문고유번호(order_id)가 추가된다.

create table fruit (
	idx int primary key auto_increment,
	goods_id int,
	farm_id int
);
		
create table orders (
	idx int primary key auto_increment,
	order_id int,
	goods_id int,
	farm_id int
);

insert into fruit (goods_id, farm_id) values 
(1,2),
(1,3),
(2,2);

insert into orders (order_id, goods_id, farm_id) values
(1,1,2),
(2,2,2);

• 데이터를 join으로 합성할 경우 그것이 일치하는 모든 경우의 수를 리턴한다.
• 두 테이블을 합성하기 위해서 아래와 같이 작성하였다.
• 그러자 비정상적으로 동작하였다.

select 
	o.order_id
	, o.goods_id
	, o.farm_id
from orders o
join fruit f
on f.goods_id = o.goods_id;

order_id	goods_id	farm_id
1	1	2
1	1	2
2	2	2

• 위의 결과를 보면 중복 레코드 두 개가 있는 것으로 확인된다. 그럼 group by 혹은 distinct를 사용해야할까?
• 사실 on절 자체가 잘못되었다. fruit과 orders의 연관관계는 goods_id와 함께 farm_id를 포함한다.
• 아래와 같이 on절을 수정하였고 정상동작한다.

select 
	o.order_id
	, o.goods_id
	, o.farm_id
from orders o
join fruit f
on f.goods_id = o.goods_id and f.farm_id = o.farm_id ;

두 번째로 판매한 상품이, 선호하는 상품인가?

• 위와 같은 문제로 쉽게 풀지 못한 문제였다.
• https://leetcode.com/problems/market-analysis-ii/
• orders에는 해당 상품을 판매한 판매자의 seller_id가 있다.
• users에는 판매자와 관련한 데이터가 있다. 판매자가 선호하는 브랜드를 칼럼으로 가지고 있다.
• 요구사항은 다음과 같다. 판매자가 판매한 상품 중 두 번째로 판매한 것이 셀러가 선호하는 브랜드인지를 판별하는 것이다.

Input: 
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2019-01-01 | Lenovo         |
| 2       | 2019-02-09 | Samsung        |
| 3       | 2019-01-19 | LG             |
| 4       | 2019-05-21 | HP             |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2019-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2019-08-04 | 1       | 4        | 2         |
| 5        | 2019-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
Output: 
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1         | no                 |
| 2         | yes                |
| 3         | yes                |
| 4         | no                 |
+-----------+--------------------+
Explanation: 
The answer for the user with id 1 is no because they sold nothing.
The answer for the users with id 2 and 3 is yes because the brands of their second sold items are their favorite brands.
The answer for the user with id 4 is no because the brand of their second sold item is not their favorite brand.

해소

• users 테이블을 보면 판매자 당 선호하는 상품이 하나다. 겉보기에는 user_id가 unique처럼 보인다.
• 하지만 위의 내용 중 어디에도 user_id가 uk임을 명시한 곳이 없다.
• 처음 문제를 풀 때, 나는 user_id를 uk로 이해하고 풀었다. 몇 개의 테스트는 성공했지만 몇 개의 테이트는 실패하였다.
• 아래와 같이 쿼리를 작성하였고 성공할 수 있었다.

-- 1) 유일함을 보장하는 것 : 판매자가 두 번째로 판매한 상품, 두 번 이상 판매한 적이 없는 판매자의 목록
-- 2) 유일함을 보장하지 않는 것 : 선호 상품

-- 1)과 2)를 join 하면 뻥튀기가 발생할 수 있음

-- 모든 유저를 두 번째를 판매한 유저와 하지 아니한 유저를 분류한다.
-- 판매한 유저의 경우 판매한 상품과 선호상품의 일치여부를 비교한다. 

with fav as(
    select 
        u.user_id
        , i.item_id
    from users u
    join items i
    on u.favorite_brand = i.item_brand
)
, 2ndOrd as(
    select 
        seller_id
        , item_id
    from (
        select 
            seller_id
            , item_id
            , row_number() over (partition by seller_id order by order_date asc) ranks
        from orders 
    )tb
    where ranks = 2
)
select 
    u.user_id seller_id
    , case when tb.isPresent is not null then 'yes'
        else 'no'
        end '2nd_item_fav_brand'
from users u
left join (
    select 
        user_id
        , 'yes' isPresent
    from 2ndOrd s
    join fav f
    on s.seller_id = f.user_id and s.item_id = f.item_id
) tb
on u.user_id = tb.user_id

• on s.seller_id = f.user_id and s.item_id = f.item_id 의 코드를 통해 문제를 해소할 수 있었다.