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

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

ただいまの
回答率

90.76%

  • MySQL

    5529questions

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

直前の情報を合せて取得したい(クエリ)

解決済

回答 2

投稿 編集

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

tkda

score 13

現在、下記のテーブルのように、日々の商品の状態をitem_statusテーブルで保存しています。

CREATE TABALE `items(商品)` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
);

CREATE TABALE `status(状態)` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
);

CREATE TABALE `items_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(1) NOT NULL,
  `status_id` int(2) NOT NULL,
  `output_date` date NOT NULL,
  PRIMARY KEY (`id`),
);


今回、例えばoutput_dateが今日のものを全件取得する際に、直前のstatusを合せて取得したいと考えております。

ただし、単純に昨日のstatusを取得するというものではなく、今日のstatusと異なる、もっとも直近のstatusを直前のstatusと定義しております(存在しない場合はNULL)。

追記:
サンプルデータと取得したい情報は以下のとおりです。

items(商品)

id name
1 サーモン
2 昆布

status(状態)

id name
1 good
2 poor
3 bad

items_status

id item_id status_id output_date
1 1 1 2017-09-01
2 2 1 2017-09-01
3 1 2 2017-09-02
4 2 1 2017-09-02
5 1 3 2017-09-03
6 2 1 2017-09-03
7 1 3 2017-09-04
8 2 1 2017-09-04

取得したい情報(output_date: 2017-09-04を指定)

items.name status.name status.name(直前) output_date
サーモン bad poor 2017-09-04
昆布 good NULL 2017-09-04
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • yambejp

    2017/10/02 12:09

    具体的なsampleデータをつけて、どういった結果をほしいか提示下さい。いまのテーブル構成ですと、status_idがユニークでなく、id毎のoutput_dateもユニークではありません。そうなると別のなんらかの条件がない場合直前のstatus_idをユニークに抜き出すことは理論上できません

    キャンセル

  • tkda

    2017/10/02 13:18

    ご指摘ありがとうございます。できるだけシンプルにと思って記載したつもりがまったく情報が足りておりませんでした。一から質問内容を書き直しております。

    キャンセル

回答 2

checkベストアンサー

+1

itemsを駆動表として、「指定日付のstatusと異なる、過去直近の日付を求めたitems_status」とitems_statusをそれぞれ、item_idと求めた日付で結合

select  itm.name
      ,(select name from status where id=sts.status_id) 
      ,(select name from status where id=lag_sts.status_id)
      , sts.output_date
from    items as itm 
        left join (
            select * 
                 ,(select max(output_date) from items_status 
                   where item_id=st.item_id 
                     and output_date < st.output_date
                     and status_id <> st.status_id
                   ) as lag_output_date
            from items_status as st
            where output_date='2017-09-14'
        ) sts
        on  itm.id=sts.item_id
        left join items_status as lag_sts
        on   sts.item_id=lag_sts.item_id
         and sts.lag_output_date=lag_sts.output_date


※状態名はサブクエリーにしていますが、joinして取得の方が高速かもしれません。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/10/04 10:25

    ありがとうございます。無事に私の環境でも再現ができました!

    キャンセル

0

items_statusのitem_id、output_dateはuniqueではないのでしょうか?
仮にitems_statusが以下のようなデータだったとき

CREATE TABLE `items_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(1) NOT NULL,
  `status_id` int(2) NOT NULL,
  `output_date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE(item_id,output_date)
);

insert into items_status values
(1,1,1,'2017-09-01'),
(2,2,1,'2017-09-01'),
(3,1,2,'2017-09-02'),
(4,2,1,'2017-09-02'),
(5,1,3,'2017-09-03'),
(6,2,1,'2017-09-03'),
(7,1,3,'2017-09-04'),
(8,2,1,'2017-09-04'),
(9,3,1,'2017-09-04');

 考え方

抽出するデータは’2017-09-04'をベースに
id=1は2017-09-03のstatus_id=3なのでNG、遡って2017-09-02のstatus_id=2
id=2は遡ってもstatus_id=1以外のデータがないのでnull
id=3は遡ってもそもそもデータがないのでnull

という計算根拠ですね?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/10/02 16:51

    確かに、item_id, output_dateはuniqueですね。
    また、計算根拠につきましても「考え方」に記載の通りで間違いありません。
    たびたびのご指摘ありがとうございます。

    キャンセル

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

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

関連した質問

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

  • MySQL

    5529questions

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