Nivel 4 · Líneas por pedido sin contar doble

Cuando unes una tabla con otra donde cada fila coincide con muchas filas, las filas de la primera tabla se repiten una vez por coincidencia. Esto se llama fan-out, y es el origen de la mayoría de los números inflados en analítica.

Grano

Toda tabla tiene un grano: lo que representa una fila. orders es una fila por pedido. order_items es una fila por línea de pedido. Tras

FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id

el resultado está al grano de línea de pedido. Un pedido con tres líneas aparece tres veces, y cada columna de orders, incluida shipping_fee, se copia en las tres filas.

Qué se rompe

• COUNT(*) ahora cuenta líneas, no pedidos. La consulta del código inicial reporta aproximadamente de dos a tres veces el número real de pedidos.
• SUM(o.shipping_fee) suma el costo de envío de cada pedido una vez por línea. Un cargo de $6.95 en un pedido de tres líneas se cuenta como $20.85. Finanzas lo notará antes que tú.
• AVG(o.shipping_fee) queda ponderado hacia los pedidos con muchas líneas.

Nada da error. La consulta se ejecuta, los números parecen plausibles, y están mal.

Soluciones

Cuenta claves distintas para los conteos del lado “uno”:

COUNT(DISTINCT o.order_id) AS orders,
COUNT(*) AS order_lines

Agrega antes de unir para las sumas del lado “uno”. Resume primero las líneas por pedido, para que el join vuelva a ser uno a uno:

SELECT o.channel,
       SUM(o.shipping_fee) AS shipping,
       SUM(l.lines) AS order_lines
FROM orders AS o
JOIN (
  SELECT order_id, COUNT(*) AS lines
  FROM order_items
  GROUP BY order_id
) AS l ON l.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.channel;

Lo harás de forma limpia con CTE en el capítulo 7. No uses SUM(DISTINCT o.shipping_fee): dos pedidos distintos con el mismo cargo de $6.95 se colapsarían en uno.

Razones tras un join

lines_per_order son las líneas de pedido divididas entre los pedidos. Ambos son enteros, así que multiplica por 1.0 antes de dividir. En Postgres, COUNT(*) / COUNT(DISTINCT o.order_id) truncaría 2.6 a 2. DuckDB y MySQL devuelven un decimal, el / de BigQuery siempre devuelve un float, pero * 1.0 mantiene la consulta portable.

Cómo detectar el fan-out

Antes de agregar, ejecuta el join con COUNT(*) y compáralo con el conteo de filas de la tabla cuyo grano quieres. Si esperabas una fila por pedido y obtuviste más, tienes fan-out. Los analistas senior hacen esta comprobación por reflejo cada vez que añaden un join.

Nota de entrevista

“Nuestro panel de ingresos se duplicó después de que alguien añadió un join con la tabla de promociones. ¿Qué pasó?” es una pregunta de escenario común. Nombra el fan-out, explica el grano y ofrece ambas soluciones: COUNT(DISTINCT ...) para los conteos y preagregación para las sumas.

Errores comunes

• Contar pedidos con COUNT(*) tras el join. Cuenta líneas de pedido. Usa COUNT(DISTINCT o.order_id) para los pedidos.
• Sumar una columna a nivel de pedido tras el join. SUM(o.shipping_fee) suma cada cargo una vez por línea. Agrega primero las líneas por pedido y luego haz el join.
• Usar SUM(DISTINCT o.shipping_fee) como solución. Dos pedidos distintos con el mismo cargo de $6.95 se colapsan en uno, así que el total sale demasiado bajo en lugar de demasiado alto.
• Dividir dos conteos sin * 1.0. En Postgres y SQL Server, COUNT(*) / COUNT(DISTINCT o.order_id) trunca 2.6 a 2.

En SQL Server

Aquí la división entera muerde: COUNT(*) / COUNT(DISTINCT o.order_id) divide dos valores int y devuelve un número entero (2 en lugar de 2.5). Mantén el * 1.0, o escribe CAST(COUNT(*) AS decimal(10, 2)) / COUNT(DISTINCT o.order_id).