相互積の問題を回避するために、MySQL で結合テーブルの集計値を正しく合計する方法

SUM 集計を使用して MySQL のテーブルを結合する: 製品間の問題を解決する

SUM 集計関数を使用して 2 つのクエリを結合すると、MySQL で問題が発生することがよくあります。テーブル間の外積により、SUM 値が不正確になる可能性があります。この問題を解決するには、SUM 関数をサブクエリにカプセル化する必要があります。

2 つのクエリで、特定の日付と教師 ID 5 の SUM(drive_time) と SUM(tm_hours) をそれぞれ取得する次の例を考えてみましょう。

クエリ 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

クエリ 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

単純な接続の試行:

これらのクエリを組み合わせる簡単な方法は、次のように連結することです:

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 

ただし、このアプローチではテーブル間に外積が作成され、SUM 値が不正確になります。

解決策:

正しい SUM 値を取得するには、SUM 集計関数をサブクエリに移動します。これにより、積間の問題が回避され、SUM 値が関連する行のみで計算されるようになります:

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 
    FROM bhds_timecard
    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 

以上が相互積の問題を回避するために、MySQL で結合テーブルの集計値を正しく合計する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。