MySQLでの差額取得方法を教えてください。

Question

MySQL5.5の商品データベース（ここでは product_jans ）から、各商品名コード（product_name_id）の最新の価格情報を取得して、そこから最安値と最高値、さらにそのproduct_name_id、その価格差を取得したいです。
また、新品価格・中古価格・コレクター価格それぞれで出力したいです。

下記のSQLによって結果01までは取得できたのですが、ここから結果02にするにはこのSQLをどのように変えればいいでしょうか。できれば外部結合で行いたいです。

それとも、そもそも不可能なのでしょうか。

SELECT data.* FROM product_jans AS data LEFT OUTER JOIN product_jans AS jan ON data.product_name_id = jan.product_name_id AND data.modified < jan.modified  WHERE jan.modified IS NULL ORDER BY jan;

《結果01》
+--------+-----------------+---------------+-----------------------+------+------------------+---------------------+---------------------+
| id | product_name_id | jan | head | data | shop_id | created | modified |
+--------+-----------------+---------------+-----------------------+------+------------------+---------------------+---------------------+
| 209210 | 13039 | 4541993022113 | 中古価格 | 8888 | 1 | 2016-03-30 22:51:59 | 2016-03-30 22:51:59 |
| 368052 | 10787 | 9780091912697 | 新品価格 | 1530 | 1 | 2016-04-05 07:11:04 | 2016-04-05 07:11:04 |
| 368053 | 10787 | 9780091912697 | 中古価格 | 765 | 1 | 2016-04-05 07:11:04 | 2016-04-05 07:11:04 |
| 458270 | 16694 | 9781594024603 | 中古価格 | 700 | 1 | 2016-04-05 12:21:42 | 2016-04-05 12:21:42 |
| 355781 | 18547 | 9784000056793 | 中古価格 | 198 | 15 | 2016-04-05 01:44:43 | 2016-04-05 01:44:43 |
| 426540 | 20493 | 9784000056793 | 新品価格 | 2052 | 1 | 2016-04-05 07:38:02 | 2016-04-05 07:38:02 |
| 426541 | 20493 | 9784000056793 | 中古価格 | 88 | 1 | 2016-04-05 07:38:02 | 2016-04-05 07:38:02 |
| 426542 | 20493 | 9784000056793 | コレクター価格 | 643 | 1 | 2016-04-05 07:38:02 | 2016-04-05 07:38:02 |
| 454038 | 8556 | 9784000225786 | 中古価格 | 748 | 22 | 2016-04-05 10:50:43 | 2016-04-05 10:50:43 |
| 458684 | 16846 | 9784000225786 | 新品価格 | 2484 | 1 | 2016-04-05 12:22:34 | 2016-04-05 12:22:34 |
| 458685 | 16846 | 9784000225786 | 中古価格 | 997 | 1 | 2016-04-05 12:22:34 | 2016-04-05 12:22:34 |
| 350238 | 18096 | 9784000236874 | 中古価格 | 1150 | 15 | 2016-04-05 01:42:55 | 2016-04-05 01:42:55 |
| 411784 | 20025 | 9784000236874 | 新品価格 | 2808 | 1 | 2016-04-05 07:36:35 | 2016-04-05 07:36:35 |
| 411785 | 20025 | 9784000236874 | 中古価格 | 1502 | 1 | 2016-04-05 07:36:35 | 2016-04-05 07:36:35 |
| 450664 | 7188 | 9784000240055 | 中古価格 | 498 | 22 | 2016-04-05 10:46:23 | 2016-04-05 10:46:23 |
| 454883 | 8902 | 9784000240055 | 新品価格 | 2052 | 1 | 2016-04-05 12:14:47 | 2016-04-05 12:14:47 |
| 454884 | 8902 | 9784000240055 | 中古価格 | 584 | 1 | 2016-04-05 12:14:47 | 2016-04-05 12:14:47 |
| 454885 | 8902 | 9784000240055 | コレクター価格 | 1000 | 1 | 2016-04-05 12:14:47 | 2016-04-05 12:14:47 |
| 451346 | 7418 | 9784000252959 | 中古価格 | 498 | 22 | 2016-04-05 10:47:08 | 2016-04-05 10:47:08 |
| 455628 | 15694 | 9784000252959 | 中古価格 | 499 | 1 | 2016-04-05 12:16:09 | 2016-04-05 12:16:09 |

↓↓↓

《結果02》
+-----------------+---------------+-----------------------+---------------------+---------------------+-----------+-----------+------------+---------------------+---------------------+
| id | jan | price_type | max_product_name_id | min_product_name_id | price_max | price_min | price_diff |
+-----------------+---------------+-----------------------+---------------------+---------------------+-----------+-----------+------------+---------------------+---------------------+
| 454199302211302 | 4541993022113 | 中古価格 | 13039 | 0 | 8888 | 0 | 0 |
| 978009191269701 | 9780091912697 | 新品価格 | 10787 | 0 | 1530 | 0 | 0 |
| 978009191269702 | 9780091912697 | 中古価格 | 10787 | 0 | 765 | 0 | 0 |
| 978159402460302 | 9781594024603 | 中古価格 | 16694 | 0 | 700 | 0 | 0 |
| 978400005679301 | 9784000056793 | 新品価格 | 20493 | 0 | 2052 | 0 | 0 |
| 978400005679302 | 9784000056793 | 中古価格 | 18547 | 20493 | 198 | 88 | 110 |
| 978400005679303 | 9784000056793 | コレクター価格 | 20493 | 0 | 643 | 0 | 0 |
| 978400022578601 | 9784000225786 | 新品価格 | 16846 | 0 | 2484 | 0 | 0 |
| 978400022578602 | 9784000225786 | 中古価格 | 16846 | 8556 | 997 | 748 | 249 |
| 978400023687401 | 9784000236874 | 新品価格 | 20025 | 0 | 2808 | 0 | 0 |
| 978400023687402 | 9784000236874 | 中古価格 | 20025 | 18096 | 1502 | 1150 | 352 |
| 978400024005501 | 9784000240055 | 新品価格 | 8902 | 0 | 2052 | 0 | 0 |
| 978400024005502 | 9784000240055 | 中古価格 | 8902 | 7188 | 584 | 498 | 86 |
| 978400024005503 | 9784000240055 | コレクター価格 | 8902 | 0 | 1000 | 0 | 0 |
| 978400025295902 | 9784000252959 | 中古価格 | 15694 | 7418 | 499 | 498 | 1 |

Comments

[KiyoshiMotoki]

ご提示のSQLで《結果01》 の結果を得ることはできません。なぜなら、ご提示のSQLではproduct_codeが重複することがあり得ないからです。ご質問の内容に記載間違いなどないか、もう一度確認してください。

[noripi]

すみません。確かに主キーが抜けてますね。

[KiyoshiMotoki]

そういう問題ではありません。ご提示のSQLは"t1.product_code = t2.product_code"部分がproduct_codeでGROUP BYしているようなものなので、これを実行した結果、複数行に渡って同じproduct_codeが現れることがあり得ない、と申し上げているのです。ご自身で手を動かした上で、ご質問されていますか？

[noripi]

理解がたりず申し訳ありません。実際のコードと結果を記載してみました。

[KiyoshiMotoki]

情報の更新、ありがとうございます。しかし、理解できた点と新たに不明な点の両方が出てきました。改行できないこの欄ではうまくかけないので、回答欄の方に追記させていただきます。

[noripi]

回答欄にコメントしましたので、よろしくお願いします。

Answer 1

※これは回答ではありません。"情報の追加・修正の依頼をする"欄ではうまくかけないので、こちらに記載させていただいております。

ようやく理解しました。
実際のデータではproduct_name_idとmodifiedがともに同じレコードがあるため、《結果01》 にproduct_name_idが重複して現れるわけですね。

しかし、新たに不明な点があります。

以下を、再度ご確認願います。

• ご質問文にある"商品コード"とは、どのカラムのことですか？
  最初はproduct_codeだと思っておりましたが、新たにjanというカラムが出てきました。
• 商品コードごとに最高値と最安値を求めたいにも関わらず、
  《結果02》 にも複数行に渡って同じjan、product_name_idが現れています。
  （例えば《結果02》の2行目と3行目）
  実際に求めたいのは、商品コードとprice_typeの組み合わせごとの最新の最高値と最安値ではありませんか？
• modifiedカラムはproduct_name_idごとに全く同じタイミングで更新される、
  という認識で問題ありませんか？
  例えばproduct_name_id = 20493 は、新品価格・中古価格・コレクター価格のmodifiedが
  いずれも"2016-04-05 07:38:02"になっていますが、
  これが1秒でもズレることはないという認識で問題ないか、という意味です。
  この前提が誤っていると、そもそも《結果01》を取得するSQLから見直す必要があるかもしれません。

---

※まだ未完成です。うまく動きません。

SELECT
    h.jan,
    h.product_name_id AS max_product_name_id,
    l.product_name_id AS min_product_name_id,
    h.data AS price_max,
    l.data AS price_min,
    (h.data - l.data) AS price_diff
FROM (

    # ②
    SELECT main.*
    FROM product_jans AS main
    LEFT OUTER JOIN (

        # ①
        SELECT t1.*
        FROM product_jans AS t1
        LEFT JOIN product_jans AS t2 ON
            t1.shop_id = t2.shop_id
            AND t1.product_name_id = t2.product_name_id
            AND t1.head = t2.head
            AND t1.modified < t2.modified
        WHERE t2.modified IS NULL

    ) AS sub ON
        main.shop_id = sub.shop_id
        AND main.head = sub.head
        AND main.data < sub.data
    WHERE sub.data IS NULL

) AS h
LEFT OUTER JOIN (

    # ③
    SELECT main.* 
    FROM product_jans AS main
    LEFT OUTER JOIN (

        # ①
        SELECT t1.product_name_id, t1.jan, t1.head, t1.data, t1.shop_id
        FROM product_jans AS t1
        LEFT JOIN product_jans AS t2 ON
            t1.shop_id = t2.shop_id
            AND t1.product_name_id = t2.product_name_id
            AND t1.head = t2.head
            AND t1.modified < t2.modified
        WHERE t2.modified IS NULL

    ) AS sub ON
        main.shop_id = sub.shop_id
        AND main.head = sub.head
        AND main.data > sub.data
    WHERE sub.data IS NULL

) AS l ON h.shop_id = l.shop_id AND h.head = l.head;

---

いろいろ想像で補った部分がありますが、《結果01》を経由して《結果02》を得るためのSQLは、以下のようになります。

# ④
SELECT
    h.jan,
    h.head AS price_type, 
    h.product_name_id AS max_product_name_id,

    # ⑤
    CASE l.product_name_id 
        WHEN h.product_name_id THEN 0 
        ELSE l.product_name_id 
    END AS min_product_name_id,

    h.data AS price_max,

    # ⑤
    CASE l.data 
        WHEN h.data THEN 0 
        ELSE l.data 
    END AS price_min,

    (h.data - l.data) AS price_diff
FROM (

    # ②
    SELECT main.* 
    FROM (

        # ①
        SELECT t1.*
        FROM product_jans AS t1
        LEFT JOIN product_jans AS t2 ON
            t1.shop_id = t2.shop_id
            AND t1.jan = t2.jan
            AND t1.head = t2.head
            AND t1.modified < t2.modified
        WHERE t2.modified IS NULL

    ) AS main
    LEFT OUTER JOIN (

        # ①
        SELECT t1.*
        FROM product_jans AS t1
        LEFT JOIN product_jans AS t2 ON
            t1.shop_id = t2.shop_id
            AND t1.jan = t2.jan
            AND t1.head = t2.head
            AND t1.modified < t2.modified
        WHERE t2.modified IS NULL

    ) AS sub ON
        main.jan = sub.jan
        AND main.head = sub.head
        AND main.data < sub.data
    WHERE sub.data IS NULL

) AS h
LEFT OUTER JOIN (

    # ③
    SELECT main.* 
    FROM (

        # ①
        SELECT t1.*
        FROM product_jans AS t1
        LEFT JOIN product_jans AS t2 ON
            t1.shop_id = t2.shop_id
            AND t1.jan = t2.jan
            AND t1.head = t2.head
            AND t1.modified < t2.modified
        WHERE t2.modified IS NULL

    ) AS main
    LEFT OUTER JOIN (

        # ①
        SELECT t1.*
        FROM product_jans AS t1
        LEFT JOIN product_jans AS t2 ON
            t1.shop_id = t2.shop_id
            AND t1.jan = t2.jan
            AND t1.head = t2.head
            AND t1.modified < t2.modified
        WHERE t2.modified IS NULL

    ) AS sub ON
        main.jan = sub.jan
        AND main.head = sub.head
        AND main.data > sub.data
    WHERE sub.data IS NULL

) AS l ON h.jan = l.jan AND h.head = l.head;

以下の条件で、データを抽出・整形しています。
斜体字の部分が、私が想像で補った箇所です。
① jan、shop_id、"新品・中古・コレクター"価格の組み合わせごとにmodifiedが最新の行を抽出
② ①の中から、jan、"新品・中古・コレクター"価格の組み合わせごとに最高値（dataが最大）の行を抽出
③ ①の中から、jan、"新品・中古・コレクター"価格の組み合わせごとに最安値（dataが最小）の行を抽出
④ ②、③を、jan、"新品・中古・コレクター"価格の組み合わせごとに１行に結合し、最高値と最安値、さらにそのそれぞれの商品名コード、価格差を取得
⑤ その際、最高値と最安値が一致している行は最安値の商品名コードと価格を"0"と表示

なお、どこから出てきたか不明なため、《結果02》のidカラムは無視しています。

Comments

[noripi]

再度コメント頂き感謝します。
文章力がなく上手くイメージを伝えられず申し訳ありません。
・商品コード -> 商品名コード（product_name_id）です。名が抜けてました。
・複数行に渡って同じjan、product_name_idが現れているのは、price_type毎にデータがほしいためです。
・新品価格・中古価格・コレクター価格の登録はほぼ同時ですが、それぞれの登録はループ処理のため、厳密にはズレると思います。結果02の created modified は編集ミスです。出力されなくて大丈夫です。
度々、申し訳ありません。

[KiyoshiMotoki]

情報の追記、ありがとうございます。
今更気づいたのですが、当初のご質問は
> 各shop_id の最新の価格情報を取得して、そこから最安値と最高値のショップIDと、その価格差

だったのが、いつのまにか
> 各商品名コード（product_name_id）の最新の価格情報を取得して、そこから最安値と最高値、さらにそのproduct_name_id、その価格差

になっております。

しかし、《結果02》を見る限り
各shop_id の最新の価格情報を取得して、そこから最安値と最高値の商品名コードと、その価格差
を求めたいように見受けます。

五月雨の確認依頼で申し訳ないのですが、再度、どのような結果を得たいのか確認願います。

現状、私は以下ではないか？と想定しています。
"__"で囲んだ部分が、私の想像で補完した条件です。
① __shop_id__、商品名コード、"新品・中古・コレクター"価格の組み合わせごとに`modified`が最新の行を抽出
② ①の中から、__shop_id__、"新品・中古・コレクター"価格の組み合わせごとに最高値（`data`が最大）の行を抽出
③ ①の中から、__shop_id__、"新品・中古・コレクター"価格の組み合わせごとに最安値（`data`が最小）の行を抽出
④ ②、③を、__shop_id__、"新品・中古・コレクター"価格の組み合わせごとに１行に結合し、最高値と最安値、さらにそのそれぞれの商品名コード、価格差を取得

また、回答欄に考え中のSQLを追記させていただきます。
ただし、【まだ何かおかしいため、うまく動きません。】

[KiyoshiMotoki]

最終的な回答を追記させていただきました。

【本当に】知りたい内容と一致しているか、よくご確認ください。

Answer 2

結果01を得るSQLが正しくて、かつ、結果01を得るSQLを絶対に使わなければならないということであれば、下記のSQLで得られるはずです。

SELECT
  MIN_TBL.product_code
  , max_shop_id
  , min_shop_id
  , (max_price - min_price) as price_diff 
FROM
  ( 
    SELECT
      product_code
      , shop_id as min_shop_id
      , MIN(price) as min_price 
    FROM
      (/* ここに結果01を得るSQL */ ) 
    GROUP BY
      product_code
      , shop_id
  ) as MIN_TBL 
  INNER JOIN ( 
    SELECT
      product_code
      , shop_id as max_shop_id
      , MAX(price) as max_price 
    FROM
      (/* ここに結果01を得るSQL */ ) 
    GROUP BY
      product_code
      , shop_id
  ) as MAX_TBL 
    ON MIN_TBL.product_code = MAX_TBL.product_code

Comments

[KiyoshiMotoki]

これだと、MIN_TBLとMAX_TBLの同じproduct_codeごとのクロスジョインになってしまいますね。

《結果01》のデータを適当なテーブルに投入し、"/* ここに結果01を得るSQL */"部分を
そのテーブル名に置き換えて実行してみるとわかるかと思います。

また、" (/* ここに結果01を得るSQL */ ) "部分にサブクエリを入れる場合、
" (/* ここに結果01を得るSQL */ ) AS tmp"
などのようにエイリアスをつけてやる必要があります。
（さもないとシンタックスエラーとなります）

これは恐らくMySQL特有の制約で、リファレンスにも
「The [AS] name clause is mandatory, because every table in a FROM clause must have a name.」
と記載があります。
https://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html