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

Question

現在、下記のテーブルのように、日々の商品の状態を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|

Comments

[yambejp]

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

[tkda]

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

Answer 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して取得の方が高速かもしれません。

Comments

[tkda]

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

Answer 2

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

という計算根拠ですね？

Comments

[tkda]

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