SQL JOINの注意点について

初回購入商品ごとに顧客を出力して顧客たちの初回購入月の総売上を出力するSQLを作っていたのですが 問題点があったので記載いたします。

まず今回このようなデータが入っているものとします。

customersテーブル
     顧客名   
+---+-------+
|id |name   |
+---+-------+
|1  |tanaka |
|2  |yamada |
+---+-------+

customer_historiesテーブル
      顧客id         注文日          購入回数         注文id
+---+------------+----------------+------------------+---------+
|id |customer_id |date_of_purchase|number_of_purchase|order_id |
+---+------------+----------------+------------------+---------+
|1  | 1          |2017-05-01      | 1                |1        |
|2  | 1          |2017-05-15      | 2                |2        |
|3  | 1          |2017-05-20      | 3                |3        |
|4  | 2          |2017-05-02      | 1                |4        |
+---+------------+----------------+------------------+---------+

ordersテーブル
      顧客id      注文日          金額
+---+------------+--------------+--------------+
|id |customer_id |order_date    | total_amount |
+---+------------+--------------+--------------+
|1  | 1          |2017-05-01    | 100          |
|2  | 1          |2017-05-15    | 500          |
|3  | 1          |2017-05-20    | 300          |
|4  | 2          |2017-05-02    | 100          |
+---+------------+--------------+--------------+

order_detailsテーブル
      注文id    商品id
+---+---------+---------+
|id |order_id |item_id  |
+---+---------+---------+
|1  | 1       |1        |
|2  | 2       |2        |
|3  | 2       |3        |
|4  | 3       |3        |
|5  | 4       |1        |
+---+---------+---------+

itemsテーブル(商品テーブル)
     　商品名　　値段
+---+----------+-----+
|id |name      |price|
+---+----------+-----+
|1  |Pepper    |100  |
|2  |salt      |200  |
|3  |mayonnaise|300  |
|4  |ketchup   |400  |
+---+----------+-----+

まず初めに以下のようなSQLを作りました サブクエリやDATE_FORMATやFORMATの使い方に関しては以前の記事を参考にしてください。

MySQLで特定期間のデータを抽出する - 情弱エンジニアのなかのblog

MySQLで日付に関する処理 - 情弱エンジニアのなかのblog

SQL小数点の表示とtimestamp - 情弱エンジニアのなかのblog

SELECT
    ch.name AS '商品名',
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.total_amount ),0) AS '1月間の総売上'
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id,
        customer_histories.date_of_purchase,
        items.id,
        items.name
    FROM
        customer_histories
    WHERE
        date_of_purchase between '2017-05-01' and '2017-05-31'
        AND
        number_of_purchase = 1
    ) AS ch
ON
    orders.customer_id = ch.customer_id
INNER JOIN
    order_details
ON
    orders.id = order_details.order_id
INNER JOIN
    items
ON
    order_details.item_id = items.id
WHERE
    DATE_FORMAT(orders.order_date , '%Y%m') <= DATE_FORMAT(ch.date_of_purchase , '%Y%m')
GROUP BY
    items.id

こちらですと問題点として まず初回購入以外の商品データもGROUP BYの集計に入ってしまいます。

あとordersテーブルとorder_detailsテーブルを見るとわかるのですが、 order.id = 2で[tanaka]さんが同時に2つの商品を購入しているのでordersとorder_detailsの結合時に order.id = 2が2行分作られます。その状態で

select
    sum(orders.charged_amount)

をするとorder.id = 2 の total_amount = 500が2回集計されて間違ったデータになってしまします。

以下が正しい修正バージョンです。

SELECT
    ch.name AS '商品名',
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.total_amount),0) AS '1月間の総売上'
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id,
        customer_histories.date_of_purchase,
        items.id,
        items.name
    FROM
        customer_histories
    INNER JOIN
        order_details
    ON
        customer_histories.order.id = order_details.order_id
    INNER JOIN
        items
    ON
        order_details.item_id = items.id
    WHERE
        date_of_purchase between '2017-05-01' and '2017-05-31'
        AND
        number_of_purchase = 1
    ) AS ch
ON
    orders.customer_id = ch.customer_id
WHERE
    DATE_FORMAT(orders.order_date , '%Y%m') <= DATE_FORMAT(ch.date_of_purchase , '%Y%m')
GROUP BY
    ch.id

今回の問題点は無駄な結合を行っていたことと、集計の軸を間違えていたことです。

それでは