Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

INNER JOIN con tres tablas y función SUM

Estas en el tema de INNER JOIN con tres tablas y función SUM en el foro de Mysql en Foros del Web. Bueno, siendo la 01:59 AM me rendí del infalibre método de prueba y error (agotado ya el método de leer el manual de MySQL) y ...
  #1 (permalink)  
Antiguo 17/01/2011, 23:19
Avatar de ecarlevaro  
Fecha de Ingreso: octubre-2010
Ubicación: Paraná, Argentina
Mensajes: 80
Antigüedad: 9 años, 1 mes
Puntos: 13
Pregunta INNER JOIN con tres tablas y función SUM

Bueno, siendo la 01:59 AM me rendí del infalibre método de prueba y error (agotado ya el método de leer el manual de MySQL) y decidí consultar.

Tengo tres tablas, ARTICULOS, IMAGENES, ESTADISTICAS.

Las tres tablas comparten la columna idarticulo.

Por cada registro de artículos puede haber cero o muchos registros en imágenes (las imágenes del artículo).

Por cada registro de articulos puede haber cero o muchos registros en estadísticas (básicamente cada visita de cada fecha a cada artículo se registra en ESTADÍSTICAS).

Yo necesito saber el idarticulo, la primera imagen guardada, y el total de visitas (para todas las fechas) por cada articulo.

Es decir, el resultado que necesito sería:

IDARTICULO IMAGEN VISITAS
1 hola.jpg 38
2 gaylord.jpg 12
Donde visitas es la suma de todas las visitas a cada artículo para todas las fechas.

En ESTADISTICAS los registros basicamente son así:

IDARTICULO FECHA VISITAS
1 18/01 1
1 19/01 2
2 20/01 5

Entonces para obtener el total de visitas por artículo hago un SUM(estadisticas.visitas) y un GROUP BY articulos.idarticulo.

Ahora bien, para conseguir la primer imagen de cada artículo, me aprovecho de la columna idimagen de la tabla IMAGENES, y lo consigo con MIN(imagenes.idimg) y imagenes.imagen, también con un GROUP BY articulos.idarticulo que me devuelve una imagen por cada artículo.

El problema es que cuando junto todo en una consulta, todo anda bien, excepto el SUM(estadisticas.visitas) que devuelve la cantidad de imágenes para cada artículo, no suma las visitas, sino devuelve la cantidad de imágenes (de registros en la tabla IMAGENES para cada artículo) en vez de sumar las visitas suma la cantidad de registros de imagen para cada artículo.

La consulta que hago es:

Código MySQL:
Ver original
  1. SELECT articulos.idarticulo,SUM(estadisticas.visitas) as visitas, imagenes.imagen,MIN(imagenes.idimg) FROM (articulos LEFT JOIN estadisticas ON articulos.idarticulo = estadisticas.idarticulo) INNER JOIN imagenes ON articulos.idarticulo = imagenes.idarticulo GROUP BY articulos.idarticulo ORDER BY idarticulo ASC

Uso LEFT JOIN pues no todos los artículos tienen un registro en ESTADÍSTICAS pues algunos no tienen visitas todavía.

Y me devuelve algo así:

IDARTICULO IMAGEN VISITAS
1 hola.jpg 5
2 gaylord.jpg 3

donde 5 y 3 ¡no son las sumas de las visitas para las distintas fechas del artículo 1 y 2! son la cantidad de imágenes (registros en IMAGENES) del artículo 1 y 2.

Si alguien anda con suficientes ganas de meterse en este problema le agredeceré, mientras seguiré probando...
  #2 (permalink)  
Antiguo 18/01/2011, 04:24
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Cáceres
Mensajes: 3.735
Antigüedad: 11 años, 8 meses
Puntos: 300
Respuesta: INNER JOIN con tres tablas y función SUM

Prueba con algo así:
SELECT a.idarticulo, a.articulo, ifnull(i.imagen, 'sin imagen'), IFNULL(SUM(e.visitas),0) totalvisitas FROM articulos a LEFT JOIN imagenes i ON a.idarticulo = i.idarticulo LEFT JOIN estadisticas e ON a.idarticulo = e.idarticulo GROUP BY a.idarticulo
  #3 (permalink)  
Antiguo 18/01/2011, 04:27
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Sabadell
Mensajes: 4.897
Antigüedad: 11 años, 8 meses
Puntos: 574
Respuesta: INNER JOIN con tres tablas y función SUM

Código MySQL:
Ver original
  1. SELECT articulos.idArticulo,
  2.              articulos.nombre,
  3.              IFNULL(sbcimagenes.imagen,"noimagen.jpg") as img,
  4.              IFNULL(SbCVisitas.SuVisitas,0) AS Visitas
  5. FROM (articulos
  6.                LEFT JOIN
  7.                   (SELECT img1.idArticulo,
  8.                                 img1.imagen
  9.                       FROM imagenes AS img1
  10.                    INNER JOIN
  11.                     (SELECT idArticulo,
  12.                                   Min(idImagen) AS MidImagen
  13.                        FROM imagenes
  14.                        GROUP BY idArticulo) AS img2
  15.                     ON img1.idImagen = img2.MidImagen) AS sbcimagenes
  16.                 ON articulos.idArticulo = sbcimagenes.idArticulo)
  17.                 LEFT JOIN (SELECT estadisticas.idarticulo,
  18.                                                SUM(estadisticas.visitas) as SuVisitas
  19.                                       FROM estadisticas
  20.                                       GROUP BY estadisticas.idarticulo) AS SbCVisitas
  21.                  ON articulos.idArticulo = SbCVisitas.idarticulo;

Creo!! Vaya seguro.

Crea una imagen llamada noimagen.jpg para los que no tengan!!!

Nota; jurena eres más rápido que yo!!!
__________________
Quim
--------------------------------------------------
Ayudar a ayudar es una buena práctica!!! Y da buenos resultados.

Última edición por quimfv; 18/01/2011 a las 06:09 Razón: Corregido
  #4 (permalink)  
Antiguo 18/01/2011, 14:39
Avatar de ecarlevaro  
Fecha de Ingreso: octubre-2010
Ubicación: Paraná, Argentina
Mensajes: 80
Antigüedad: 9 años, 1 mes
Puntos: 13
Mensaje Respuesta: INNER JOIN con tres tablas y función SUM

Bueno así parece entonces que jurena es más rápido pero quimfv más efectivo.

Es que probé con ambas consulta luego de hojearlas y la de quimfv en su aparente complejidad funcionó muy bien mientras la de jurena hace lo mismo que la mía. Que por cierto, no devuelve como yo dije al principio la cantidad de registros de imagen de cada idArticulo en la DB sino que devuelve la cantidad de imágenes multiplicada por la cantidad de visitas, así un artículo con 6 imágenes y 335 visitas da 2010 "visitas".

Me acabo de desayunar el tema de las subconsultas en el Manual de MySQL para entender la consulta de quimfv, que si anda con ganas y tiempo le agradecería me explicara al menos a grandes rasgos, como fue razonando él para llegar a la consulta que llegó. Por mi parte entiendo esto:
Código SQL:
Ver original
  1. /*Las columnas que quiero*/
  2. SELECT articulos.idarticulo,
  3.        articulos.titulo,
  4.        sbcimagenes.src AS img,
  5.        SbCVisitas.SuVisitas AS Visitas
  6. FROM (articulos
  7.                LEFT JOIN
  8.                   /*Consigo todos los articulos y src de la imagen*/
  9.                   (SELECT img1.idArticulo,img1.src FROM imagenes AS img1
  10.                    INNER JOIN
  11.                     /*El menor idimg por cada articulo*/
  12.                     (SELECT idarticulo,MIN(idimg) AS MidImagen FROM imagenes GROUP BY idarticulo) AS img2
  13.                     /*Las uno por idimg y consigo todos los articulos con sus imagenes*/
  14.                     ON img1.idimg = img2.MidImagen) AS sbcimagenes
  15.                  /*Uno por idarticulo con articulos*/
  16.                 ON articulos.idarticulo = sbcimagenes.idarticulo)
  17.                 /*Combino las imagenes y los idarticulos con las estadisticas */
  18.                 LEFT JOIN (SELECT estadisticas.idarticulo,SUM(estadisticas.visitas) AS SuVisitas
  19.                                       FROM estadisticas
  20.                                       GROUP BY estadisticas.idarticulo) AS SbCVisitas
  21.                                       /*A través de idarticulo*/
  22.                  ON articulos.idarticulo = SbCVisitas.idarticulo;

Lo que también me gustaría entender es por qué falla la consulta mía o la de jurena, ¿por qué cuando le digo que SUM(estadisticas.visitas) multiplica la cantidad de registros de imagen por la cantidad de visitas?.

Bueno ando preguntón y disculpas si molesta, por mi parte las enormes gracias a quimfv porque creo que jamás hubiera llegado a tal consulta.
  #5 (permalink)  
Antiguo 19/01/2011, 02:59
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Sabadell
Mensajes: 4.897
Antigüedad: 11 años, 8 meses
Puntos: 574
Respuesta: INNER JOIN con tres tablas y función SUM

Veo que has entendido perfectamente la consulta. Parece compleja pero no lo es.

Para resolver una consulta de este tipo debes aplicar el principio de "divide y vencerás".

Tu pedías

1. Una lista de todos los artículos --> artículos LEFT JOIN ...
2. Una imagen por articulo, concretamente, la imagen con el id mas pequeño de cada articulo. Aquí hay que hacer una doble subconsulta, primero obtener el id de imagen mas pequeño para cada articulo y a partir de este el nombre de la imagen. --> idarticulo,imagen FROM imagenes INNER JOIN (MIN(idArticulo) From imagenes GROUP BY idarticulo) ON idimagen=MIN(idimagen)
Con el INNER JOIN consigo que solo me de el idArticulo y nombreimagen de los idMinimos, si un articulo no tiene imagen no sale en esta subconsulta
3.Un número de visitas por artículo. Estadísticas por articulo --> idarticulo,SUM(visitas) FROM estadistica GROUP BY idarticulo, si un articulo no tiene visitas no sale en esta subconsulta

Como en los tres puntos tengo idArticulo es fácil armar la consulta final jugando con los alias y con LEFT JOIN para que aparezcan todos los artículos de la tabla artículos independientemente de si tienen o no imagen y/o visitas.

Finalmente los IFNULL() tienen una función estética.

Cada una de las partes se pueden ejecutar independientemente, luego es fácil comprobar si dan lo que buscas


Por que falla la vuestra

La cardinalidad de las tablas no es la misma

TablaArticulos tiene n registros (los queremos todos)
LEF JOIN (correcto)
TablaImagenes tiene x imágenes * m artículos (no coincide ni el numero de artículos, además pueden estar repetidos) digamos que tiene i registros
LEFT JOIN
TablaEstadisticas tiene y fechas * m artículos (lo mismo) digamos que tiene e registros

luego la consulta nos dará potencialmente i*e registros todos los de artículos combinados por idart con TODOS los de imágenes (i) y estos combinados con TODOS los de estadísticas (e). Si ademas n fuera mayor que el mayor de i o e tendriamos que sumar el numero de articulos restante.

Como hacéis el group by se reduce a n registros, el MIN(idimagen) funciona puesto que el mínimo es el mínimo pero el SUM(visitas) suma todas las repeticiones que haya (i*e).

Por otro lado intentáis obtener el nombre de la imagen directamente, eso es imposible ya que sql nos puede dar el mínimo de un grupo dado pero no los campos que lo acompañan...(MsAccess (no se si MsSQLserver) implementa FIRST() y LAST() que podían haber servido pero no es SQL estándar).

Fijate que la cardinalidad de mis subconsultas tiene como máximo n registros y sin repeticiones.


FINALMENTE Y EN MAYÚSCULAS ESTA NO ES LA ÚNICA SOLUCIÓN Y NO SE SI DE TODAS LAS POSIBLES ES LA MAS EFICIENTE
__________________
Quim
--------------------------------------------------
Ayudar a ayudar es una buena práctica!!! Y da buenos resultados.

Última edición por quimfv; 19/01/2011 a las 04:53
  #6 (permalink)  
Antiguo 19/01/2011, 18:31
Avatar de ecarlevaro  
Fecha de Ingreso: octubre-2010
Ubicación: Paraná, Argentina
Mensajes: 80
Antigüedad: 9 años, 1 mes
Puntos: 13
Sonrisa Respuesta: INNER JOIN con tres tablas y función SUM

¡Sí! ¡Estoy entendiendo!

Todavía me falta para aprehender este método de divide y vencerás y las subconsultas, pero con tu ayuda y lo que estuve leyendo estoy aprehendiendo la esencia de consultas a mútliples tablas y combinar las tablas.

Y ese concepto de la cardinalidad lo tenía bastante olvidado de cuando alguna vez una profesora de matemática me habrá explicado teoría de conjuntos, pero ya me lo repasé.

¡Muchas gracias quim!

Ahora jugaré con EXPLAIN para ir viendo si puedo optimizar esta consulta.

Nuevamente, gracias.
  #7 (permalink)  
Antiguo 20/01/2011, 03:16
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Sabadell
Mensajes: 4.897
Antigüedad: 11 años, 8 meses
Puntos: 574
Respuesta: INNER JOIN con tres tablas y función SUM

Si en la teoria de conjuntos esta la base de todo.

Cita:
método de divide y vencerás
mas que un método es un principio aplicable a la programación en general.

Quim
__________________
Quim
--------------------------------------------------
Ayudar a ayudar es una buena práctica!!! Y da buenos resultados.

Etiquetas: innerjoin, join, left
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Tema Cerrado

SíEste tema le ha gustado a 1 personas




La zona horaria es GMT -6. Ahora son las 08:49.