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

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

ただいまの
回答率

89.99%

同一のテーブル内で、異なるデータを抽出したい

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 1,127

ky_46

score 89

前提・実現したいこと

商品管理データベースのようなものを検討しています。

例として、ガンプラ屋さんのデータを作成してみました。
テーブルganpura
ID,name,price,stock,date
MS-06,ザク,2000,3,2017/03/06
MS-09,ドム,2500,3,2017/03/06
MSM-07,ズゴック,1700,3,2017/03/06

MS-06,ザク,2000,0,2017/03/07
MS-09,ドム,2500,3,2017/03/07
MSM-07,ズゴック,1700,3,2017/03/07
MSM-04,アッガイ,2000,3,2017/03/07

MS-09,ドム,2000,3,2017/03/08
MSM-07,ズゴック,1700,3,2017/03/08
MSM-04,ガンダム,2000,3,2017/03/08

このように、毎日の在庫や価格を取り込んで、データの点検をしたいと思います。

この場合、例えば2017/03/06と、2017/03/07の日付で比較し、ザクが3つ減って、在庫が0である事、アッガイが新しく入荷した事を検出したいです。
また、2017/03/07と、2017/03/08 の比較で、ドムの値段を500円下げた事、MSM-04の名前が間違ってガンダムに変わってしまった事、ザクがデータ一覧に無い事なども検出したいです。

該当のソースコード

SELECT `ID` FROM (
  SELECT `ID` FROM `ganpura` WHERE `date` = '2017/03/07'
) AS t1
WHERE `ID` NOT IN (
  SELECT t2.`ID` FROM (
    SELECT `ID` FROM `ganpura` WHERE `date` = '2017/03/06'
  ) AS t2
)
;

試したこと

MySQLのデーブル同士の比較や、IN句でのサブクエリなどの情報をミックスして、上のようなSQLを組んだところ、MSM-04を得られましたので、新しく増えたアッガイを検出することは出来ると思います。
また、これを逆転させれば、3/8にザクが無い事も検出は可能と思います。
しかし、その他の同じIDの価格や名前の違いを検出する方法を見つけ出せずにおります。

一度のSQLで検出できなくても大丈夫ですので、このようなデータから、日付毎の項目内容の違いを抽出するSQLがありましたら、ご教授ください。

なお、記載はいたしませんでしたが、ganpuraテーブルにはオートインクリメントのPrimarykeyのフィールドはあります。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+1

stokを元に入出庫を想像することは不可能です。
3/7のアッガイは3個入荷したのか、5個入荷して2個売れたのかわかりません。

この手の在庫管理は月初在庫を確定しておき、そこから入出庫データを
たしたり引いたりして特定日のstokを計算してください

 追記

create table ganpura(id varchar(20),name varchar(20),price int,stock int,d date,unique(id,d));
insert into ganpura values
('MS-06','ザク',2000,3,'2017-03-06'),
('MS-09','ドム',2500,3,'2017-03-06'),
('MSM-07','ズゴック',1700,3,'2017-03-06'),
('MS-06','ザク',2000,0,'2017-03-07'),
('MS-09','ドム',2500,3,'2017-03-07'),
('MSM-07','ズゴック',1700,3,'2017-03-07'),
('MSM-04','アッガイ',2000,3,'2017-03-07'),
('MS-09','ドム',2000,3,'2017-03-08'),
('MSM-07','ズゴック',1700,3,'2017-03-08'),
('MSM-04','ガンダム',2000,3,'2017-03-08');


※条件:日付が連続していること
それが担保されないなら、前日の日付を探す処理が必要

3/6→3/7への変化

select id,name
,-sum(stock*(d=(@d:='2017-03-07')- interval 1 day))+sum(stock*(d=@d)) as diff_stock
,-sum(price*(d=@d- interval 1 day))+sum(price*(d=@d)) as diff_price 
,case when sum(d=(@d-interval 1 day))=0 then '追加' when sum(d=@d)=0 then '削除' else '' end as status
from ganpura where d between @d- interval 1 day and @d
group by id
having diff_stock<>0 or diff_price<>0;
id name diff_stock diff_price status
MS-06 ザク -3 0
MSM-04 アッガイ 3 2000 追加

@d:='2017-03-07'の箇所を'2017-03-08'変更すれば

id name diff_stock diff_price status
MS-06 ザク 0 -2000 削除
MS-09 ドム 0 -500

調整しました。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/03/09 14:20

    まったく気がつきませんでしたが、MSM-04の名前が途中でかわっていたのですね・・、こうやるとよいですよ

    SELECT id,count(DISTINCT name),group_concat(DISTINCT name) as names FROM ganpura where d between '2017-03-07' and '2017-03-08' GROUP BY id;

    キャンセル

  • 2017/03/09 14:23

    ちなみに日付を2つ指定するなら変数を2つ用意すればいいでしょう

    select id,name
    ,-sum(stock*(d=(@d1:='2017-03-07')))+sum(stock*(d=(@d2:='2017-03-08'))) as diff_stock
    ,-sum(price*(d=@d1))+sum(price*(d=@d2)) as diff_price
    ,case when sum(d=@d1)=0 then '追加' when sum(d=@d2)=0 then '削除' else '' end as status
    from ganpura where d between @d1 and @d2
    group by id
    having diff_stock<>0 or diff_price<>0

    キャンセル

  • 2017/03/09 14:47

    できました!ありがとうございます。大変助かりました。

    キャンセル

0

価格の変更を確認する
SELECT * FROM ganpura GROUP BY ID, price ORDER BY date;

個数の変更を確認する
SELECT * FROM ganpura GROUP BY ID, stock ORDER BY date;

などでは要件を満たせないでしょうか?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/03/09 13:37

    せっかくご回答頂いたのに申し訳ありません。
    確かにこの方法だと、得られたリストを注意して見れば、発見は可能だと思います。ただ要望としては、「異常」(商品名や値段が変わった、idが増えた減った)があるのを見つけて、一覧にしてほしいとの内容ですので、この方法では要件を満たせないでしょう…申し訳ないです。

    キャンセル

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

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

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