Unir tablas con problema SUM en MYSQL

Resuelto joanb asked hace 8 años • 0 respuestas

Siempre he tenido problemas para obtener SUM en tablas de unión, siempre hay un problema, puedo obtener los resultados que necesito ejecutando dos consultas, me pregunto si estas dos consultas se pueden combinar para hacer una consulta de unión, aquí están las consultas que tengo y mi intento de unirme a la consulta

Consulta 1

SELECT last_name, first_name, DATE_FORMAT( (mil_date),  '%m/%d/%y' ) AS dates, 
SUM( drive_time ) MINUTES FROM bhds_mileage LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_mileage.ds_id 
WHERE mil_date BETWEEN  '2016-04-11' AND  '2016-04-30'
AND bhds_mileage.ds_id =5
GROUP BY CONCAT( YEAR( mil_date ) ,  '/', WEEK( mil_date ) ) ,    
bhds_mileage.ds_id
ORDER BY last_name ASC , dates ASC

la salida en minutos es 271, 281, 279

Consulta 2

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,   
SUM(tm_hours) total FROM bhds_timecard LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_timecard.ds_id 
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 
ORDER BY last_name ASC, dates ASC

La salida aquí es 33.00, 36.00, 26.75

Ahora mi intento de unirme a la consulta.

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,  
SUM(tm_hours) total,  SUM( drive_time ) MINUTES FROM bhds_timecard 
LEFT JOIN bhds_teachers i ON i.ds_id = bhds_timecard.ds_id 
LEFT JOIN bhds_mileage ON DATE_FORMAT((bhds_timecard.tm_date), '%m/%d/%y') = 
DATE_FORMAT((bhds_mileage.mil_date), '%m/%d/%y') AND bhds_timecard.ds_id = bhds_mileage.ds_id
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id

paréntesis es lo que se espera

esto genera 1044 (271), 1086 (281), 1215 (279)

joanb avatar Jun 23 '16 04:06 joanb
Aceptado

Cuando usas múltiples combinaciones en la consulta principal, terminas con un producto cruzado de todas las tablas, por lo que las sumas se multiplican por el número de filas que coinciden en otra tabla. Debe mover las sumas a subconsultas.

SELECT last_name, first_name, DATE_FORMAT(LEAST(mil_date, tm_date),  '%m/%d/%y' ) AS dates, 
        total, minutes
FROM bhds_teachers AS i
LEFT JOIN (
    SELECT ds_id, YEARWEEK(mil_date) AS week, MIN(mil_date) AS mil_date, SUM(drive_time) AS minutes
    FROM bhds_mileage
    WHERE mil_date BETWEEN '2016-04-11' AND  '2016-04-30'
    AND bhds_mileage.ds_id = 5
    GROUP BY ds_id, week) AS m 
ON m.ds_id = i.ds_id
LEFT JOIN (
    SELECT ds_id, YEARWEEK(tm_date) AS week, MIN(tm_date) AS tm_date, SUM(tm_hours) AS total
    WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
    GROUP BY ds_id, week) AS t 
ON t.ds_id = i.ds_id AND t.week = m.week
Barmar avatar Jun 22 '2016 22:06 Barmar

Hay un par de problemas... producto cartesiano parcial (producto cruzado) entre bhds_mileagey bhds_timecard, porque cada fila de detalles (dentro de un grupo) de una tabla estará "unida en cruz" con las filas de detalles de la otra tabla. Y eso sucede antes de que la operación GROUP BY colapse las filas y calcule la SUMA. Eso explica por qué estás viendo valores "inflados".

La solución para esto es calcular al menos uno de los agregados SUM() en una vista en línea... hacer SUM()/GROUP BY() como lo hace una de sus primeras consultas. Para mayor claridad, puede hacer lo mismo para ambas consultas originales y luego unir los resultados de las vistas en línea.

MySQL no admite de forma nativa la unión externa COMPLETA. Una de las mesas deberá ser la mesa de conducción. Por ejemplo, podríamos usarla _timecardcomo tabla de conducción, pero eso significaría que tenemos que devolver una fila para una semana determinada _timecardpara poder devolver una fila correspondiente de _mileage. Es decir, sin una fila en _timecard, no podríamos obtener una fila de _mileage.

Notamos que la unión a bhds_teacheres una unión externa. Si tenemos una restricción de clave externa entre ds_idin two _mileagey _timecard, haciendo referencia a _teacher, entonces no necesariamente tendría que ser una unión externa, podríamos usar una unión interna y usarla _teachercomo tabla conductora para dos uniones externas.

Otro problema son los no agregados en la lista SELECT... por ejemploDATE_FORMAT((tm_date), '%m/%d/%y')

GROUP BY es por año y semana, por lo que el valor de DATE_FORMAT es indeterminado... podría ser de cualquiera tm_date dentro del grupo. No hay garantía de que obtenga el primer día de la semana, la fecha más temprana de la semana o lo que sea.

Además, el segundo parámetro de WEEKla función se omite, por lo que de forma predeterminada será la default_week_formatvariable del sistema. Personalmente, evitaría las funciones YEAR, WEEKy CONCATy elegiría una más simple DATE_FORMAT, usando una cadena de formato de fecha que incluya explícitamente el parámetro de modo para la semana.

Si desea unirse en "semana", entonces el predicado de unión debe estar en el valor "semana", no en una fecha indeterminada dentro de la semana.

(Puede haber algunas restricciones específicas en los datos que no conocemos... si hay filas en _millaje para una semana determinada, un lunes, entonces tenemos la garantía de tener una _tarjeta de tiempo para ese mismo lunes. En el En un caso más general, no tendríamos esa garantía).

Incluso si tenemos esa garantía, no tenemos garantía de que el no agregado en la lista SELECT no devolverá la fecha de una tarjeta de tiempo del martes y de un kilometraje del jueves... (a menos que haya algún tipo de garantía de que los datos incluirá solo filas con fechas "lunes" en _timecard y _mileage). Sin eso, la expresión no agregada no es una expresión confiable para un predicado de unión.

Suponiendo ds_idque es único en _teachery que hace referencia a claves externas ds_idde ambos _mileagey _timecard, entonces algo como esto:

SELECT i.last_name
     , i.first_name
     , tm.dates
     , tm.total_hours
     , mm.total_minutes
  FROM bhds_teacher i 
  LEFT
  JOIN ( SELECT t.ds_id
              , DATE_FORMAT( t.tm_date,'%Y/%U')          AS week_
              , DATE_FORMAT( MIN(t.tm_date) ,'%m/%d/%y') AS dates
              , SUM(t.tm_hours)                          AS total_hours
           FROM bhds_timecard t
          WHERE t.tm_date BETWEEN '2016-04-11' AND '2016-04-30'   -- <
            AND t.ds_id = 5                                       -- <
          GROUP
             BY t.ds_id
              , DATE_FORMAT( t.tm_date,'%Y/%U')                   -- week
       ) tm
    ON tm.ds_id = i.ds_id
  LEFT
  JOIN ( SELECT m.ds_id
              , DATE_FORMAT( m.mil_date,'%Y/%U')           AS week_
              , DATE_FORMAT( MIN(m.mil_date), '%m/%d/%y' ) AS dates
              , SUM( m.drive_time )                        AS total_minutes 
           FROM bhds_mileage m
          WHERE m.mil_date BETWEEN '2016-04-11' AND '2016-04-30'  -- <
            AND m.ds_id = 5                                       -- <
          GROUP
             BY m.ds_id
              , DATE_FORMAT( m.mil_date,'%Y/%U')                  -- week
       ) mm
    ON mm.ds_id = i.ds_id
   AND mm.week_ = tm.week_
 WHERE i.ds_id = 5                                                -- <
 ORDER
    BY i.last_name ASC, tm.dates ASC
spencer7593 avatar Jun 22 '2016 22:06 spencer7593