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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

2514閲覧

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

ky_46

総合スコア92

MySQL

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2017/03/07 08:44

###前提・実現したいこと
商品管理データベースのようなものを検討しています。

例として、ガンプラ屋さんのデータを作成してみました。
テーブル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の名前が間違ってガンダムに変わってしまった事、ザクがデータ一覧に無い事なども検出したいです。

###該当のソースコード

SQL

1SELECT `ID` FROM ( 2 SELECT `ID` FROM `ganpura` WHERE `date` = '2017/03/07' 3) AS t1 4WHERE `ID` NOT IN ( 5 SELECT t2.`ID` FROM ( 6 SELECT `ID` FROM `ganpura` WHERE `date` = '2017/03/06' 7 ) AS t2 8) 9;

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

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

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

追記

SQL

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

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

3/6→3/7への変化

SQL

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

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

idnamediff_stockdiff_pricestatus
MS-06ザク0-2000削除
MS-09ドム0-500

調整しました。

投稿2017/03/07 08:52

編集2017/03/07 13:17
yambejp

総合スコア114583

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ky_46

2017/03/07 09:13

前提条件が足らず、申し訳ありません。ganpuraテーブルにinsertするデータは、IDの存在しているものは在庫が無くても全部載ってくるのを前提にしております。 ですので、前日まで無かったアッガイがいきなり出現したのは、アッガイの新しいIDが作成されて3個入荷したと判り、3/8にザクが無くなっているのは、ザクのIDが「削除」されたという事になります。 問題は、削除がザクの入荷の目処が立たないので廃番になり、削除されたのなら良いのですが、誤ってIDを削除してしまう事があり、その場合は早急に復活させないと、ザクは入荷してもIDがないので、倉庫から店頭に出す作業が出来ないという事になってしまうようです。 そのため、IDの新設や削除、価格の変更、指定の在庫数になった商品を、このテーブルから抽出できないか? と思っております。
yambejp

2017/03/07 09:27

ん~・・・ちょっと運用方法がわからないのですが、 品目テーブルをつくって正規化するのがDBの基本ですが、 そういう考え方ではない? また前回も書きましたが、入荷と出荷が同日に発生することはない? それと在庫が0の品目は削除してよいのか残しておくのか ロジックが決められてないので、結局全部在庫0のまま残しておくしかないですよ
ky_46

2017/03/07 09:41

ありがとうございます。 通常でない方法のようで、申し訳ありません。 所謂、ジャーナルレポートというのでしょうか? 実は、データは提供元があるため、内容を変更できず、定形のデータを取り寄せしてMySQLに取り込んでいます。 ※提供元のデータの段階では日付もありません。これは取り込み時の日付を付与しています。 入出荷は同日に起こります。 しかし、それはこのレポートには載ってこず、また別のレポートデータで、入出荷のレコードを得る必要があります。 今回のレポートはあくまでレポートを要求した時点の商品情報や在庫のデータです。 ただ、何かの理由で商品情報に変更が加えられても、変更結果だけしか来ない為、気付くのが遅れてしまう事がままあるそうで、レポートのデータを過去と比べて、相違点を抽出したいというのが希望です。
yambejp

2017/03/07 09:44

だいたいイメージが付きました。ちょっと書いてみます
yambejp

2017/03/07 13:17

一応それっぽく調整しておきました
ky_46

2017/03/09 04:41

ありがとうございます。 実は日付は必ずしも1日ではないのが泣き所です。土日祝を挟むと、日数がずれてしまいますので… そこで解析しながら、一応、このようなカタチに書き換えさせていただきました。 select id,name ,-sum(stock*(d=('2017-03-07')))+sum(stock*(d=('2017-03-08'))) as diff_stock ,-sum(price*(d=('2017-03-07')))+sum(price*(d=('2017-03-08'))) as diff_price ,case when sum(d=('2017-03-07'))=0 then '追加' when sum(d=('2017-03-08'))=0 then '削除' else '' end as status from ganpura where d between '2017-03-07' and '2017-03-08' group by id having diff_stock<>0 or diff_price<>0 ; ほぼ完全に希望どおりです! これを見せれば、「昨日と今日の比較」として異常箇所がすぐに判るので、納得して貰えると思います。 ただ、もう一点については、申し訳ありませんが、よいお知恵がありましたら拝借させてください。
ky_46

2017/03/09 04:50

SELECT id, name FROM ganpura where d between '2017-03-07' and '2017-03-08' GROUP BY id, name; 簡単なSQLですが、上記を走らせると id name MS-06 ザク MS-09 ドム MSM-04 アッガイ MSM-04 ガンダム MSM-07 ズゴック の結果が得られます。まず、GROUP BY id, nameでグループ化した後に、得られた結果のIDをカウントする方法はありますでしょうか? 得られた上記の結果でカウントすれば、1回しか出て来ないIDを除外すれば、MSM-04が2回でてきて、「名前が変わっている」事を検出できるかと思います。 前述の、新規や削除、価格変更をキャッチできるSQLと別に、もう一度SQLを送信するのは問題ないと思いますので、名前の変更をキャッチできる方法があればご教授願えればと思います。 よろしくお願いいたします。
yambejp

2017/03/09 05: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;
yambejp

2017/03/09 05: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
ky_46

2017/03/09 05:47

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

0

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

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

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

投稿2017/03/07 09:47

mizuiro_makoto

総合スコア40

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ky_46

2017/03/09 04:37

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問