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

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

ただいまの
回答率

90.54%

  • MySQL

    6833questions

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

  • SQL

    2965questions

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

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

解決済

回答 2

投稿

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

ky_46

score 82

前提・実現したいこと

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

例として、ガンプラ屋さんのデータを作成してみました。
テーブル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/07 18:13

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

    キャンセル

  • 2017/03/07 18:27

    ん~・・・ちょっと運用方法がわからないのですが、
    品目テーブルをつくって正規化するのがDBの基本ですが、
    そういう考え方ではない?
    また前回も書きましたが、入荷と出荷が同日に発生することはない?

    それと在庫が0の品目は削除してよいのか残しておくのか
    ロジックが決められてないので、結局全部在庫0のまま残しておくしかないですよ

    キャンセル

  • 2017/03/07 18:41

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

    キャンセル

  • 2017/03/07 18:44

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

    キャンセル

  • 2017/03/07 22:17

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

    キャンセル

  • 2017/03/09 13: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
    ;

    ほぼ完全に希望どおりです! これを見せれば、「昨日と今日の比較」として異常箇所がすぐに判るので、納得して貰えると思います。

    ただ、もう一点については、申し訳ありませんが、よいお知恵がありましたら拝借させてください。

    キャンセル

  • 2017/03/09 13: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を送信するのは問題ないと思いますので、名前の変更をキャッチできる方法があればご教授願えればと思います。

    よろしくお願いいたします。

    キャンセル

  • 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が増えた減った)があるのを見つけて、一覧にしてほしいとの内容ですので、この方法では要件を満たせないでしょう…申し訳ないです。

    キャンセル

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

  • MySQL

    6833questions

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

  • SQL

    2965questions

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