[항해99 - 미니프로젝트] 최근에 판매 등록된 상품 15개 가져오기

항해 99 미니 프로젝트 중에서 어질 어질한 경우를 만났다.

바로 복잡한 쿼리의 등장이다.

우선 상황은 아래와 같다.

erd

1. 우리는 가장 최근에 판매 등록된 상품 15개를 반환해야한다.
2. 최근에 판매 등록된 상품은 Price 테이블의 최근 id로 검색해야한다.(이거부터 이상하지만 date 컬럼이 없기 때문에 어쩔 수 없다)
3. 상품 반환할 때, thumbnail URL 한 개를 함께 반환해야하며 thumbnail과 product는 다 대 일 관계다.
4. 이때 반환될 URL은 thumbnail 중 가장 높은 id를 가진 url을 반환한다.

---

1. 최근 등록한 price 15개 가져오기

가장 최근에 등록된 Price 15개가 아래와 같다고 하자,

id	price	size	product_id	seller
69608	2100000	280	1321	admin
69607	2100000	270	1320	admin
69606	2100000	260	1319	admin
69605	2100000	250	1318	admin
69604	2100000	230	1317	admin
69603	2100000	260	1316	admin
69602	2100000	270	1315	admin
69601	2100000	280	1314	admin
69600	2100000	230	1313	admin
69599	2100000	250	1312	admin
69598	2100000	240	1321	admin
69597	2100000	250	1321	admin
69596	2100000	270	1313	admin
69595	2100000	240	1321	admin
69594	2100000	280	1321	admin

단순히 15개만 뽑아오면 같은 품목이 나오게 된다. 따라서 product_id로 group by 한 다음에 15개를 가져와야 한다.

그리고 id 순으로 역정렬을 해야하기 때문에 Query가 아래와 같이 된다.

SELECT product_id 
FROM kream.price as price 
GROUP BY product_id
ORDER BY price.id DESC
LIMIT 15

결과

---

2. 상품별 가장 높은 thumbnail_id를 가진 url 하나만 가져오기

현재 상품은 여러 thumbnail url을 가지고 있다.(1대다 관계)

하지만 상세 페이지를 제외하고는 단 하나의 thumbnail url만 있으면 된다.

따라서 상품별로 가장 높은 thumbnail id를 가진 url을 추출하여야 한다.

	SELECT th.id, url, th.product_id
    FROM kream.thumbnail as th
	WHERE(th.product_id, th.id)
    in(
		select sub_th.product_id, max(id) as id 
        	from kream.thumbnail as sub_th
            group by sub_th.product_id
    )

---

3. 이제 이 url 과 product를 조인해줘야 한다.

SELECT product.name, product.brand, product.id, c.url
FROM (
	SELECT th.id, url, th.product_id
    FROM kream.thumbnail as th
	WHERE(th.product_id, th.id)
    in(
		select sub_th.product_id, max(id) as id 
        	from kream.thumbnail as sub_th
            group by sub_th.product_id
    )
)c
JOIN kream.product as product
WHERE product.id = c.product_id

---

4. url + product 와 최근 등록된 15개의 price와 조인하기

Mysql에서는 JOIN 내부의 Subquery를 지원하지 않는다.

따라서 위 최근 등록된 15개의 price를 구하는 subquery를 사용하기 위해 WHERE IN을 사용한다.

이때 주의할 점은 IN 내부의 LIMIT를 지원하지 않기 때문에 alias를 적절히 사용해줘야 한다.

SELECT product.name, product.brand, product.id, c.url
FROM (
	SELECT th.id, url, th.product_id
    FROM kream.thumbnail as th
	WHERE(th.product_id, th.id)
    in(
		select sub_th.product_id, max(id) as id 
        	from kream.thumbnail as sub_th
            group by sub_th.product_id
    )
)c
JOIN kream.product as product
WHERE product.id = c.product_id and product.id 
IN (
	select * from(
		SELECT product_id 
		FROM kream.price as price 
		GROUP BY product_id
		ORDER BY price.id DESC
		LIMIT 15
    ) as tmp
);

---

5. 결론 및 포스팅 이유

쿼리문이 다소 복잡하지만 원하는 결과를 만들기에는 충분하다.

하지만 위 쿼리문이 최적인지는 모르겠다.

WHERE IN 보다는 JOIN이 나으나, Subquery를 사용할 수 없다는 점, IN 내부에 LIMIT을 사용할 수 없어서 Alias를 사용한 점, 등등 뭔가 개선할 여지가 더 있을 거 같다.

지금은 잘 모르겠지만 기록을 남겨놓고 차후에 Query에 대해 더 학습한 후, 이 SQL문을 개선해보고 싶다.

• 추가적으로 위 Query를 QueryDSL로 바꿔야 한다.