reno에서 ship ratio가 0이고 pct도 이상해요, SQLite

업데이트: join 때문인 것 같아요. 주문이 line마다 한 번씩 반복돼서 COUNT(*)가 주문이 아니라 line을 세고 있어요. COUNT(DISTINCT o.id)로 고쳐질까요? 그리고 ratio에는 1.0 *을 쓰면...

혹시 COUNT(DISTINCT o.id)로 고쳐질까요? 그리고 비율에는 1.0 *을 쓰고요...

네, 둘 다 맞는데 분자도 같이 고쳐야 해요. reno의 66.7은 4/6이에요. 일부 주문은 라인이 두 개라서 join하면 6행이 나오고, COUNT(o.shipped_at)가 그 주문들을 두 번 세요. 분모만 distinct로 세면 발송 4 / 주문 4 = 반대 방향으로 틀려요. 양쪽 다 중복 없는 주문이어야 해요.

ratio가 0인 건 두 피연산자가 둘 다 정수라서 SQLite가 정수 나눗셈을 하기 때문이에요. 3/4는 0이에요. 먼저 1.0을 곱하면 real이 돼요. https://www.sqlite.org/lang_expr.html 의 "Operators" 섹션을 보세요.

SELECT o.warehouse,
       COUNT(DISTINCT o.id) AS orders,
       COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END) AS shipped,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END)
             / COUNT(DISTINCT o.id), 1) AS ship_pct,
       SUM(l.qty) AS units
FROM orders o
JOIN order_lines l ON l.order_id = o.id
GROUP BY o.warehouse;

SUM(l.qty)는 라인 단위로 합산하니까 그대로 괜찮아요. 그리고 inner JOIN은 라인이 하나도 없는 주문을 빼버려서, reno의 4가 그냥 센 값과 여전히 다를 수 있어요. 다르면 LEFT JOIN으로 바꾸세요. 돌려 보고 직접 센 값이랑 비교해 보세요.

양쪽 다 중복 없는 주문이어야 해요.

join을 먼저 할 때만 그래요. 저라면 서브쿼리에서 주문별로 qty를 합산한 다음에 join해요. 그러면 그냥 COUNT(*)로 되고 DISTINCT는 필요 없어요.

그건 되는데, "DISTINCT 필요 없다"는 서브쿼리가 정확히 주문당 한 행이고 join이 모든 주문을 유지할 때만 맞아요. inner join이면 line이 없는 주문은 서브쿼리 쪽에서 사라져서 COUNT(*)가 조용히 덜 세요. #3에서 나온 reno의 4에 대한 질문이 바로 그건데, 아직 답이 없네요.

SELECT o.warehouse,
       COUNT(*) AS orders,
       COUNT(o.shipped_at) AS shipped,
       ROUND(100.0 * COUNT(o.shipped_at) / COUNT(*), 1) AS ship_pct,
       COALESCE(SUM(t.units), 0) AS units
FROM orders o
LEFT JOIN (SELECT order_id, SUM(qty) AS units
           FROM order_lines
           GROUP BY order_id) t ON t.order_id = o.id
GROUP BY o.warehouse;

세부 사항이 두 가지 있어요. 반드시 LEFT JOIN이어야 하고, line이 없는 주문은 units가 NULL이에요(그래서 COALESCE를 써요). 100.0 *도 여전히 필요해요. 원래 쿼리의 정수 나눗셈 문제는 그대로 남아 있으니까요.

(솔직히 저라면 쿼리를 둘로 나눠서 주문 수를 비교해 볼 것 같아요. 싸게 확인할 수 있어요.)