Decode a long query someone else wrote

1. 한 문장 요약

이 쿼리는 2026-01-01 이후 주문이 있는 고객별로, 주문·주문상품·리뷰 조인으로 만들어진 행들을 기준으로 COUNT(o.id)와 상품 금액 합계를 계산하므로, 고객의 리뷰 수와 주문상품 수에 따라 주문 건수와 총액이 부풀 수 있습니다.

2. 안쪽에서 바깥쪽으로

명시적인 CTE나 서브쿼리는 없습니다. FROM과 JOIN이 만드는 결과를 순서대로 보면 다음과 같습니다.

users u JOIN orders o

JOIN orders o ON o.user_id = u.id

• 생산 결과: 사용자와 주문의 조합
• grain: 고객 1명 × 주문 1건
• orders에 연결된 사용자가 없으면 제외됩니다.
• 이후 WHERE o.created_at >= '2026-01-01' 때문에 2026년 이전 주문은 제외됩니다.

JOIN order_items oi

JOIN order_items oi ON oi.order_id = o.id

• 생산 결과: 주문별 주문상품 행
• grain: 고객 1명 × 주문 1건 × 주문상품 1행
• 한 주문에 상품 행이 3개면 해당 주문은 3행이 됩니다.
• 주문상품이 없는 주문은 제외됩니다.

LEFT JOIN reviews r

LEFT JOIN reviews r ON r.user_id = u.id

• 생산 결과: 각 주문상품 행에 해당 고객의 리뷰를 붙인 결과
• grain: 고객 1명 × 주문 1건 × 주문상품 1행 × 해당 고객의 리뷰 1행
• 고객에게 리뷰가 없으면 r 컬럼이 NULL인 한 행은 유지됩니다.
• 고객에게 리뷰가 4개 있으면, 그 고객의 모든 주문상품 행이 리뷰 4개와 각각 결합됩니다.
• 리뷰가 상품별로 저장되어 있어도 조인 조건에는 product_id가 없으므로, 고객의 모든 리뷰가 모든 주문상품에 붙습니다.

GROUP BY

GROUP BY u.id, u.name

• 최종 grain: 고객 1명당 1행
• 각 고객에 대해 조인 결과의 모든 행을 집계합니다.

3. Grain 변화와 행 수 증감

주요 변화는 다음과 같습니다.

1. users → orders

   • 고객 한 명에게 주문이 여러 개면 행이 늘어납니다.
   • 정상적인 일대다 관계입니다.

2. orders → order_items

   • 주문 한 건에 주문상품이 여러 개면 행이 늘어납니다.
   • 따라서 이 시점부터 주문 하나가 상품 행 수만큼 표현됩니다.

3. order_items → reviews

   • 고객에게 리뷰가 여러 개면 각 주문상품 행이 리뷰 수만큼 복제됩니다.
   • 주문상품이 n개이고 리뷰가 m개인 고객은 대략 n × m개의 행을 만들 수 있습니다.
   • 리뷰가 없는 고객은 LEFT JOIN 때문에 리뷰 NULL인 한 행만 유지됩니다.

4. GROUP BY

   • 여러 조인 행이 고객별 한 행으로 collapse됩니다.
   • 하지만 이미 복제된 뒤 집계하므로, 중복은 사라지지 않고 집계값에 반영됩니다.

order_count의 추가 문제

COUNT(o.id)

이 값은 주문의 개수가 아니라 최종 조인 결과에서 o.id가 NULL이 아닌 행의 개수입니다.

따라서 리뷰가 전혀 없더라도:

• 주문 1건
• 주문상품 3개

이면 COUNT(o.id)는 3입니다.

리뷰가 2개 있으면 같은 주문상품 행이 리뷰별로 복제되어 6이 될 수 있습니다.

즉, 현재 쿼리의 order_count는 실제 주문 건수가 아니라 대략 다음의 영향을 받습니다.

주문상품 행 수 × 고객 리뷰 행 수

리뷰가 없는 경우에도 주문상품 행 수만큼 셉니다.

total_spent의 추가 문제

SUM(oi.quantity * oi.price)

주문상품별 금액은 정상적으로 합산되기 전에 리뷰 조인으로 복제됩니다.

예를 들어 한 주문의 상품 금액 합계가 100이고 고객 리뷰가 3개면, 리뷰 조인 이후에는 100이 3번 합산되어 300이 될 수 있습니다.

4. NULL 동작

• users, orders, order_items는 모두 일반 JOIN이므로 연결되지 않는 행은 결과에서 제거됩니다.
• reviews는 LEFT JOIN이므로 리뷰가 없어도 고객의 주문상품 행은 유지됩니다. 이 경우 r의 컬럼만 NULL입니다.
• WHERE 조건은 o.created_at에 적용되지만 orders가 이미 inner join이므로, 여기서는 outer join 행을 다시 제거하는 문제는 없습니다.
• COUNT(o.id)는 NULL을 세지 않습니다. 다만 이 쿼리에서는 inner join 때문에 정상적인 경우 o.id가 NULL이 아니므로 주문상품/리뷰 중복 행을 모두 셉니다.
• SUM은 NULL 값을 건너뜁니다.
• oi.quantity 또는 oi.price 중 하나가 NULL이면 oi.quantity * oi.price도 NULL이고, 해당 금액은 SUM에 기여하지 않습니다.
• 모든 금액 표현식이 NULL이면 SUM(...) 결과는 0이 아니라 NULL입니다.
• NOT IN이나 != 조건은 이 쿼리에 없습니다.
• u.name이 NULL이어도 GROUP BY u.id, u.name에는 영향을 주지만, 일반적으로 u.id가 유일하면 고객별 그룹 구분은 유지됩니다.

5. 데이터에 의존하지만 쿼리가 보장하지 않는 가정

다음은 스키마나 쿼리만으로 확인할 수 없습니다.

• users.id가 유일해야 고객별 집계가 의도대로 됩니다.
• orders.id가 유일해야 주문 식별이 정상적입니다.
• order_items.id가 유일해야 주문상품 중복을 구분할 수 있습니다.
• reviews.id가 유일해야 리뷰 행이 중복 저장되지 않았다고 볼 수 있습니다.
• orders.user_id가 실제 존재하는 사용자만 가리킨다는 참조 무결성을 가정합니다.
• order_items.order_id가 실제 존재하는 주문만 가리킨다는 참조 무결성을 가정합니다.
• 한 주문에 동일한 상품이 여러 주문상품 행으로 저장될 수 있는지 알 수 없습니다.
• 리뷰가 고객별 여러 행이라는 것은 테이블 구조상 가능하며, 그 경우 현재 조인은 확실히 fan-out됩니다.
• reviews.product_id와 order_items.product_id가 어떤 관계인지 확인하지 않습니다. 상품별 리뷰를 붙이려는 목적이었다면 현재 조인은 상품을 기준으로 제한하지 않습니다.
• quantity와 price가 NULL이 아니고 유효한 금액이라는 가정이 필요합니다.
• price가 주문 당시 가격인지, 현재 상품 가격인지도 쿼리만으로는 알 수 없습니다.
• status는 전혀 사용되지 않으므로 취소·환불 주문도 날짜 조건만 통과하면 포함됩니다.
• 주문 및 상품 데이터에 중복 행이 없다는 가정은 쿼리가 보장하지 않습니다.

6. 기대 결과와의 비교

기대 결과가 “고객별 순수 주문 건수와 총 구매 금액”이라면 현재 쿼리는 일치하지 않습니다.

구체적으로:

• 리뷰가 여러 개인 고객은 reviews 조인 때문에 주문상품 금액이 리뷰 수만큼 반복되어 total_spent가 부풀 수 있습니다.
• order_count는 리뷰 때문에 부풀 뿐 아니라, 리뷰가 없어도 주문상품 수를 세므로 실제 주문 건수가 아닙니다.
• 주문에 여러 상품 행이 있으면 주문 하나가 여러 번 계산됩니다.
• status를 필터링하지 않으므로 취소나 환불 주문을 제외해야 한다는 요구가 있다면 그것도 불일치입니다.

따라서 사용자가 관찰한 리뷰가 많은 고객의 과대 집계 현상은 쿼리의 LEFT JOIN reviews r ON r.user_id = u.id가 직접적인 원인입니다. 다만 그 문제를 제거해도 COUNT(o.id)는 여전히 주문 건수가 아니라 주문상품 행 수를 셉니다.