質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.48%

  • MySQL

    6038questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

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

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 975

noripi

score 22

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 |

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • noripi

    2016/04/05 15:47

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

    キャンセル

  • KiyoshiMotoki

    2016/04/05 18:53

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

    キャンセル

  • noripi

    2016/04/05 19:51

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

    キャンセル

回答 2

0

結果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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/04/03 03:53

    これだと、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

    キャンセル

0

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

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

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

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

  • ご質問文にある"商品コード"とは、どのカラムのことですか?
      最初はproduct_codeだと思っておりましたが、新たにjanというカラムが出てきました。
  • 商品コードごとに最高値と最安値を求めたいにも関わらず、
      《結果02》 にも複数行に渡って同じjanproduct_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、"新品・中古・コレクター"価格の組み合わせごとに1行に結合し、最高値と最安値、さらにそのそれぞれの商品名コード、価格差を取得
  ⑤ その際、最高値と最安値が一致している行は最安値の商品名コードと価格を"0"と表示

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/04/05 19:43

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

    キャンセル

  • 2016/04/06 17:17

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

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

    になっております。

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

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

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

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

    キャンセル

  • 2016/04/07 00:31

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.48%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • MySQL

    6038questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。