Tables
erDiagram
ProductPurchases {
user_id int PK
product_id int PK
quantity int
}
ProductInfo {
product_id int PK
category varchar
price decimal
}
ProductPurchases }o -- || ProductInfo : orders
Mermaid
복사
DataFlow
구매 상품의 pair를 만드는 일에 리소스 소모가 크고,
상품 정보를 매번 붙여야해서 이 부분에서도 소모가 크다.
Answer
WITH p_pair AS (
SELECT
p1.user_id,
p1.product_id AS p_one,
p2.product_id AS p_two
FROM ProductPurchases p1
INNER JOIN ProductInfo p2
ON p1.product_id != p2.product_id
AND p1.product_id < p2.product_id
),
but_p AS (
SELECT
pp.user_id,
pp.p_one,
pp.p_two
FROM p_pair pp
INNER JOIN ProductPurchases pc1
ON pp.user_id = pc1.user_id
AND pp.p_one = pc1.product_id
INNER JOIN ProductPurchases pc2
ON pp.user_id = pc2.user_id
AND pp.p_two = pc2.product_id
)
SELECT
p_one AS product1_id,
p_two AS product2_id,
p_info1.category AS product1_category,
p_info2.category AS product2_category,
COUNT(user_id) AS customer_count
FROM but_p
INNER JOIN ProductInfo p_info1
ON but_p.p_one = p_info1.product_id
INNER JOIN ProductInfo p_info2
ON but_p.p_two = p_info2.product_id
GROUP BY p_one, p_two
HAVING COUNT(user_id) >= 3
ORDER BY COUNT(user_id) DESC, p_one ASC, p_two ASC;
SQL
복사
비효율의 끝이 었다. -> 구매기록에 카테고리를 붙여서 조합으로 count하는 방법이 더 효율적이다.