sumar - sum if distinct mysql

MYSQL sum() para filas distintas

Question

Estoy buscando ayuda para usar sum () en mi consulta SQL:

SELECT links.id, 
       count(DISTINCT stats.id) as clicks, 
       count(DISTINCT conversions.id) as conversions, 
       sum(conversions.value) as conversion_value 
FROM links 
LEFT OUTER JOIN stats ON links.id = stats.parent_id 
LEFT OUTER JOIN conversions ON links.id = conversions.link_id 
GROUP BY links.id 
ORDER BY links.created desc;

Utilizo DISTINCT porque hago "agrupar por" y esto garantiza que la misma fila no se cuente más de una vez.

El problema es que SUM (conversions.value) cuenta el "valor" para cada fila más de una vez (debido al grupo por)

Básicamente quiero hacer SUM(conversions.value) para cada conversiones DISTINCT.id.

¿Es eso posible?

Answer 1

¡La solución de Jeromes está realmente equivocada y puede producir resultados incorrectos!

sum(conversions.value)*count(DISTINCT conversions.id)/count(*) as conversion_value

asumamos la siguiente tabla

conversions
id value
1 5
1 5
1 5
2 2
3 1

la suma correcta de valor para los distintos identificadores sería 8. La fórmula de Jerome produce:

sum(conversions.value) = 18
count(distinct conversions.id) = 3
count(*) = 5
18*3/5 = 9.6 != 8

Answer 2

Esto hará el truco, simplemente divida la suma con el número de ID de conversación que están duplicados.

SELECT a.id,
       a.clicks,
       SUM(a.conversion_value/a.conversions) AS conversion_value,
       a.conversions
FROM (SELECT links.id, 
       COUNT(DISTINCT stats.id) AS clicks, 
       COUNT(conversions.id) AS conversions, 
       SUM(conversions.value) AS conversion_value 
      FROM links 
      LEFT OUTER JOIN stats ON links.id = stats.parent_id 
      LEFT OUTER JOIN conversions ON links.id = conversions.link_id 
      GROUP BY conversions.id,links.id
      ORDER BY links.created DESC) AS a
GROUP BY a.id

Answer 3

Para obtener una explicación de por qué estaba viendo números incorrectos, lea esto .

Creo que Jerome sabe qué está causando tu error. La consulta de Bryson funcionaría, aunque tener esa subconsulta en SELECT podría ser ineficiente.

Answer 4

Puedo estar equivocado pero por lo que entiendo

• conversions.id es la clave principal de sus conversiones de tabla
• stats.id es la clave principal de las estadísticas de su tabla

Por lo tanto, para cada conversions.id usted tiene como máximo un enlace impactado.

Tu solicitud es un poco como hacer el producto cartesiano de 2 sets:

[clicks]
SELECT *
FROM links 
LEFT OUTER JOIN stats ON links.id = stats.parent_id 

[conversions]
SELECT *
FROM links 
LEFT OUTER JOIN conversions ON links.id = conversions.link_id 

y para cada enlace, obtiene tamaño de líneas ([clics]) x tamaño de ([conversiones])

Como anotó, el número de conversiones únicas en su solicitud se puede obtener a través de un

count(distinct conversions.id) = sizeof([conversions])

Este distintivo logra eliminar todas las líneas [clics] en el producto cartesiano.

pero claramente

sum(conversions.value) = sum([conversions].value) * sizeof([clicks])

En tu caso, desde

count(*) = sizeof([clicks]) x sizeof([conversions])
count(*) = sizeof([clicks]) x count(distinct conversions.id)

tienes

sizeof([clicks]) = count(*)/count(distinct conversions.id)

así que pondría a prueba tu solicitud con

SELECT links.id, 
   count(DISTINCT stats.id) as clicks, 
   count(DISTINCT conversions.id) as conversions, 
   sum(conversions.value)*count(DISTINCT conversions.id)/count(*) as conversion_value 
FROM links 
LEFT OUTER JOIN stats ON links.id = stats.parent_id 
LEFT OUTER JOIN conversions ON links.id = conversions.link_id 
GROUP BY links.id 
ORDER BY links.created desc;

Mantenme informado ! Jerome

Answer 5

Qué tal algo como esto:

select l.id, count(s.id) clicks, count(c.id) clicks, sum(c.value) conversion_value
from    (SELECT l.id id, l.created created,
               s.id clicks,  
               c.id conversions,  
               max(c.value) conversion_value                    
        FROM links l LEFT
        JOIN stats s ON l.id = s.parent_id LEFT
        JOIN conversions c ON l.id = c.link_id  
        GROUP BY l.id, l.created, s.id, c.id) t
order by t.created  

Answer 6

Utilice la siguiente consulta:

SELECT links.id
  , (
    SELECT COUNT(*)
    FROM stats
    WHERE links.id = stats.parent_id
  ) AS clicks
  , conversions.conversions
  , conversions.conversion_value
FROM links
LEFT JOIN (
  SELECT link_id
    , COUNT(id) AS conversions
    , SUM(conversions.value) AS conversion_value
  FROM conversions
  GROUP BY link_id
) AS conversions ON links.id = conversions.link_id
ORDER BY links.created DESC

Answer 7

Yo uso una subconsulta para hacer esto. Elimina los problemas de agrupación. Entonces la consulta sería algo como:

SELECT COUNT(DISTINCT conversions.id)
...
     (SELECT SUM(conversions.value) FROM ....) AS Vals