Joindre des tables avec un problème de SOMME dans MYSQL

Question

J'ai toujours eu des difficultés à obtenir des SOMMES sur des tables jointes, il y a toujours un problème, je peux obtenir les résultats dont j'ai besoin en exécutant deux requêtes, je me demande si ces deux requêtes peuvent être combinées pour faire une requête jointe, voici les requêtes que j'ai et ma tentative de joindre la requête.

Requête 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 

le résultat en minutes est 271, 281, 279

Requête 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 sortie ici est 33.00, 36.00, 26.75.

Maintenant ma tentative de joindre la requête

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 

la parenthèse est ce qui est attendu

ces sorties 1044 (271), 1086 (281), 1215 (279)

Answer 1

Lorsque vous utilisez des jointures multiples dans la requête principale, vous obtenez un produit croisé de toutes les tables, de sorte que les sommes sont multipliées par le nombre de lignes correspondant dans une autre table. Vous devez déplacer les sommes dans des sous-requêtes.

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

Answer 2

Il y a quelques problèmes... un produit cartésien partiel (produit croisé) entre bhds_mileage y bhds_timecard En effet, chaque ligne de détail (au sein d'un groupe) d'une table sera "croisée" avec les lignes de détail de l'autre table. Et cela se produit avant que l'opération GROUP BY ne réduise les lignes et ne calcule la somme. Cela explique pourquoi vous voyez des valeurs "gonflées".

La solution consiste à calculer au moins un des agrégats SUM() dans une vue en ligne... faire le SUM() / GROUP BY() comme le fait une de vos premières requêtes. Pour plus de clarté, vous pourriez faire la même chose pour les deux requêtes originales, et ensuite joindre les résultats des vues en ligne.

MySQL ne supporte pas nativement la jointure FULL outer. L'une des tables devra être la table de pilotage. Par exemple, nous pourrions utiliser _timecard comme table de conduite, mais cela signifierait que nous devons retourner une ligne pour une semaine donnée à partir de _timecard afin de retourner une ligne correspondante de _mileage. C'est-à-dire que sans une ligne dans _timecard nous n'avons pas pu obtenir une rangée de _mileage .

Nous remarquons que la jointure à bhds_teacher est une jointure externe. Si nous avons une contrainte de clé étrangère entre ds_id dans les deux _mileage y _timecard référencement _teacher alors il n'y aurait pas nécessairement besoin d'une jointure externe, nous pourrions utiliser une jointure interne, et utiliser _teacher comme table de pilotage pour deux jointures externes.

Un autre problème est celui des non-agrégats dans la liste SELECT... ex. DATE_FORMAT((tm_date), '%m/%d/%y')

Le GROUP BY est sur l'année et la semaine, donc la valeur du DATE_FORMAT est indéterminée... elle pourrait être de tout tm_date au sein du groupe. Il n'y a aucune garantie que vous obtiendrez le premier jour de la semaine, la date la plus proche dans la semaine ou autre.

De même, le deuxième paramètre pour WEEK est omise, de sorte qu'elle sera remplacée par défaut par la fonction default_week_format variable du système. Personnellement, j'éviterais le YEAR , WEEK y CONCAT fonctions, et opter pour un système plus simple DATE_FORMAT en utilisant une chaîne de format de date qui inclut explicitement le paramètre de mode pour la semaine.

Si vous voulez faire une jointure sur "semaine", alors le prédicat de jointure doit être sur la valeur "semaine", et non sur une date indéterminée dans la semaine.

(Il peut y avoir des contraintes spécifiques sur les données dont nous ne sommes pas conscients... s'il y a des lignes dans _mileage pour une semaine donnée, un lundi, alors nous sommes assurés d'avoir une _timecard pour ce même lundi. Dans le cas plus général, nous n'aurions pas cette garantie).

Même si nous avons cette garantie, nous n'avons pas la garantie que le non-agrégat dans la liste SELECT ne retournera pas la date d'un mardi _timecard, et d'un jeudi _mileage... (à moins qu'il n'y ait une sorte de garantie que les données ne comprendront que des lignes avec des dates "lundi" sur _timecard et _mileage). Sans cela, l'expression non agrégée n'est pas une expression fiable pour un prédicat de jointure.

En supposant que ds_id est unique sur _teacher et est référencé par des clés étrangères ds_id des deux _mileage y _timecard puis quelque chose comme ça :

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