顧客と商品ごとに購入日時が最新のデータを取得したい

質問

お世話になります。
以下のテーブル1からサンプル1のようなデータを抽出したいのですが、SQLはどのように書いたらよいでしょうか。

・顧客と商品ごとに購入日時が最新のデータを取得
・商品は左側の番号が同じ場合は購入日が最新のデータのみ抽出したい

データベースは　MySQL5　です。
よろしくお願いいたします。

●テーブル1
顧客　　商品　　購入日時
---------------------------------
　A　　2-2　　2014-04-23 08-00-00
　A　　2-1　　2014-04-21 08-00-00
　B　　2-3　　2014-04-20 09-00-00
　B　　2-2　　2014-04-18 06-00-00
　B　　2-1　　2014-04-15 05-00-00
　A　　1-3　　2014-04-12 08-00-00
　A　　1-2　　2014-04-10 04-00-00
　C　　2-2　　2014-04-09 05-00-00
　C　　2-1　　2014-04-08 06-00-00
　C　　1-2　　2014-04-07 08-00-00
　C　　1-1　　2014-04-06 05-00-00
　A　　1-1　　2014-04-05 02-00-00
　B　　1-2　　2014-04-04 02-00-00
　B　　1-1　　2014-04-03 01-00-00

●サンプル1
顧客　　商品　　購入日時
---------------------------------
　A　　2-2　　2014-04-23 08-00-00
　B　　2-3　　2014-04-20 09-00-00
　A　　1-3　　2014-04-12 08-00-00
　C　　2-2　　2014-04-09 05-00-00
　C　　1-2　　2014-04-07 08-00-00
　B　　1-2　　2014-04-04 02-00-00

回答No.1

yambejp · ベストアンサー

たぶんこういうことをしたいのだと思うけど・・・・

select 顧客,商品,max(購入日時) as 購入日時
from tbl
group by 顧客,left(商品,1)
order by 購入日時 desc;

商品をx-xのような形式でもつとデータの持ち方が冗長で効率的な集計ができない
素直に枝番で分けている分を別カラムに分けたほうがよいでしょう
また、各レコードを認識するためのidを振ってprimary keyを設定するのがスマートです

日時が一緒だった場合どうするとか考えるべき問題はほかにもあります

お礼

本当に度々すみません、上に二度もおかしなデータを貼り付けてしまいました。
頭がまわっていないようです、書き込める欄がもうないので出直してまいります。
本当にありがとうございます。m(_ _)m

補足

回答ありがとうございます。
上記教えていただいたSQL文はサンプルに書いた質問のテーブルだとうまくいっているのですが、実際のテーブルで使うと、なぜか商品だけデータが古いものを取得しています。

>素直に枝番で分けている分を別カラムに分けたほうがよいでしょう

やはりそうですよね。
わかってはいてもなんとか出来たらなぁと思うとつい。

>また、各レコードを認識するためのidを振ってprimary keyを設定するのがスマートです

すみません、素人眼鏡で質問に使ったテーブルにid欄を書き忘れました。実際のテーブルには存在します。
また、日時が一緒のものは基本的に存在しないものと思っていただいて大丈夫です。

いろいろとありがとうございます、もう少し悩んでみます。

回答No.3

yambejp

枝番管理も難点は桁がふえたときの問題です
仮に11-30とか出てきたときに文字列でのみ判断すると
11-30には1-3が含まれていますので1-3を抽出しようとして11-30まで
ヒットしてしまうことになりかねません

ざっと、枝番を別カラムに分けた例をあげておきます。

//テーブル作成
create table tbl (id int not null primary key,顧客 varchar(10),商品a int,商品b int,購入日時 datetime);
insert into tbl values
(1,'A','2','2','2014-04-23 08:00:00')
,(2,'A','2','1','2014-04-21 08:00:00')
,(3,'B','2','3','2014-04-20 09:00:00')
,(4,'B','2','2','2014-04-18 06:00:00')
,(5,'B','2','1','2014-04-15 05:00:00')
,(6,'A','1','3','2014-04-12 08:00:00')
,(7,'A','1','2','2014-04-10 04:00:00')
,(8,'C','2','2','2014-04-09 05:00:00')
,(9,'C','2','1','2014-04-08 06:00:00')
,(10,'C','1','2','2014-04-07 08:00:00')
,(11,'C','1','1','2014-04-06 05:00:00')
,(12,'A','1','1','2014-04-05 02:00:00')
,(13,'B','1','2','2014-04-04 02:00:00')
,(14,'B','1','1','2014-04-03 01:00:00');
※実際の運用時にはインデックスなど適宜設定する

//集計
select id,顧客,商品a,商品b,購入日時 from tbl
where (顧客,商品a,購入日時) in
(select 顧客,商品a,max(購入日時) from tblgroup by 顧客,商品a)
order by 購入日時 desc;

＞商品に"3"という文字を含むもののみ取得

select id,顧客,商品a,商品b,購入日時 from tbl
where (顧客,商品a,購入日時) in
(select 顧客,商品a,max(購入日時) from tbl
group by 顧客,商品a)
and (商品a=3 or 商品b=3)
order by 購入日時 desc

※3を含むというのが「13」や「30」まで含むかどうかで
いろいろやりかたに工夫が必要になります

お礼

度々すみません！
カンマがおかしな位置に！
修正版です。

CREATE TABLE IF NOT EXISTS `TABLE1` (
`SEQ_ID` mediumint(8) NOT NULL AUTO_INCREMENT COMMENT 'シーケンスID',
`TO_USER_NM` varchar(128) DEFAULT NULL COMMENT '顧客',
`MAIL_TITLE` varchar(256) DEFAULT NULL COMMENT '商品',
`SEND_TIME` datetime NOT NULL COMMENT '購入日時',
PRIMARY KEY (`SEQ_ID`)
);

INSERT INTO `TABLE1` (`SEQ_ID`, `TO_USER_NM`, `MAIL_TITLE`, `SEND_TIME`) VALUES
(1 , '顧客A', '『商品1』仮申込', '2014-03-01 13:07:43'),
(2 , '顧客C', '『商品1』仮申込 / 変更1', '2014-03-02 10:06:54'),
(3 , '顧客E', '『商品1』本申込', '2014-03-03 10:06:54'),
(4 , '顧客E', '『商品1』仮申込', '2014-03-04 13:33:51'),
(5 , '顧客B', '『商品1』本申込', '2014-03-05 14:58:41'),
(6 , '顧客A', '『商品1』本申込 / 変更1', '2014-03-06 16:42:56'),
(7 , '顧客D', '『商品1』本申込 / 変更2', '2014-03-07 17:27:33'),
(8 , '顧客C', '『商品1』本申込 / 変更1', '2014-03-08 13:56:29'),
(9 , '顧客A', '『商品2』仮申込', '2014-03-09 18:44:05'),
(10, '顧客A', '『商品2』仮申込 / 変更1', '2014-03-10 10:43:14'),
(11, '顧客C', '『商品1』本申込 / 変更2', '2014-03-11 08:51:02'),
(12, '顧客C', '『商品1』本申込 / 変更3', '2014-03-12 20:33:39'),
(13, '顧客A', '『商品2』本申込', '2014-03-14 18:52:44'),
(14, '顧客B', '『商品1』仮申込', '2014-03-15 16:13:47'),
(15, '顧客B', '『商品1』本申込 / 変更3', '2014-03-16 21:07:35'),
(16, '顧客B', '『商品1』本申込 / 変更4', '2014-03-18 21:07:34'),
(17, '顧客B', '『商品3』仮申込', '2014-03-19 21:42:46'),
(18, '顧客B', '『商品3』本申込', '2014-03-20 22:14:10'),
(19, '顧客A', '『商品2』本申込 / 変更1', '2014-03-21 23:44:06'),
(20, '顧客A', '『商品1』本申込 / 変更5', '2014-03-22 08:22:59'),
(21, '顧客A', '『商品2』仮申込', '2014-04-01 13:07:43'),
(22, '顧客A', '『商品2』本申込 / 変更2', '2014-04-02 10:06:54'),
(23, '顧客B', '『商品2』仮申込', '2014-04-03 10:06:54'),
(24, '顧客B', '『商品2』仮申込 / 変更1', '2014-04-04 13:33:51'),
(25, '顧客B', '『商品2』本申込', '2014-04-05 14:58:41'),
(26, '顧客F', '『商品1』仮申込', '2014-04-06 16:42:56'),
(27, '顧客F', '『商品1』本申込', '2014-04-07 17:27:33'),
(28, '顧客G', '『商品3』仮申込', '2014-04-08 13:56:29'),
(29, '顧客F', '『商品1』本申込 / 変更1', '2014-04-09 18:44:05'),
(30, '顧客G', '『商品3』本申込', '2014-04-10 10:43:14'),
(31, '顧客A', '『商品1』本申込 / 変更6', '2014-04-11 08:51:02'),
(32, '顧客A', '『商品2』本申込 / 変更3', '2014-04-12 20:33:39'),
(33, '顧客F', '『商品1』本申込 / 変更2', '2014-04-14 18:52:44'),
(34, '顧客C', '『商品3』仮申込', '2014-04-15 16:13:47'),
(35, '顧客C', '『商品3』本申込', '2014-04-16 21:07:35'),
(36, '顧客H', '『商品1』仮申込', '2014-04-18 21:07:34'),
(37, '顧客H', '『商品1』本申込', '2014-04-19 21:42:46'),
(38, '顧客H', '『商品1』本申込 / 変更1', '2014-04-20 22:14:10'),
(39, '顧客E', '『商品2』仮申込', '2014-04-21 23:44:06'),
(40, '顧客E', '『商品2』本申込', '2014-04-22 08:22:59');

補足

大変親切にありがとうございます。
いろいろとご指摘恐れ入ります。
カラム分けについても今後の参考にさせていただきます。

ある程度ヒントだけでもいただけたら自分で出来るだろうと思っていたので、最初の質問に書いたデータはかなりおおまかになっていたため、回答者様への情報提供が後出し状態で本当に申し訳ありません。
以下、実際のデータに近いサンプルを用意させていただきました。

■テーブルデータ
CREATE TABLE IF NOT EXISTS `TABLE1` (
`SEQ_ID` mediumint(8) NOT NULL AUTO_INCREMENT COMMENT 'シーケンスID',
`TO_USER_NM` varchar(128) DEFAULT NULL COMMENT '顧客',
`MAIL_TITLE` varchar(256) DEFAULT NULL COMMENT '商品',
`SEND_TIME` datetime NOT NULL COMMENT '購入日時',
PRIMARY KEY (`SEQ_ID`)
);

INSERT INTO `TABLE1` (`SEQ_ID`, `TO_USER_NM`, `MAIL_TITLE`, `SEND_TIME`) VALUES
(1 , '顧客A', '『商品1』仮申込', , '2014-03-01 13:07:43'),
(2 , '顧客C', '『商品1』仮申込 / 変更1', '2014-03-02 10:06:54'),
(3 , '顧客E', '『商品1』仮申込', '2014-03-03 10:06:54'),
(4 , '顧客E', '『商品1』本申込', '2014-03-04 13:33:51'),
(5 , '顧客B', '『商品1』本申込', , '2014-03-05 14:58:41'),
(6 , '顧客A', '『商品1』本申込 / 変更1', '2014-03-06 16:42:56'),
(7 , '顧客D', '『商品1』本申込 / 変更2' '2014-03-07 17:27:33'),
(8 , '顧客C', '『商品1』本申込 / 変更1' '2014-03-08 13:56:29'),
(9 , '顧客A', '『商品2』仮申込', , '2014-03-09 18:44:05'),
(10, '顧客A', '『商品2』仮申込 / 変更1', '2014-03-10 10:43:14'),
(11, '顧客C', '『商品1』本申込 / 変更2', '2014-03-11 08:51:02'),
(12, '顧客C', '『商品1』本申込 / 変更3' '2014-03-12 20:33:39'),
(13, '顧客A', '『商品2』本申込', , '2014-03-14 18:52:44'),
(14, '顧客B', '『商品1』仮申込', , '2014-03-15 16:13:47'),
(15, '顧客B', '『商品1』本申込 / 変更3', '2014-03-16 21:07:35'),
(16, '顧客B', '『商品1』本申込 / 変更4' '2014-03-18 21:07:34'),
(17, '顧客B', '『商品3』仮申込', '2014-03-19 21:42:46'),
(18, '顧客B', '『商品3』本申込', '2014-03-20 22:14:10'),
(19, '顧客A', '『商品2』本申込 / 変更1', '2014-03-21 23:44:06'),
(20, '顧客A', '『商品1』本申込 / 変更5' '2014-03-22 08:22:59'),
(21, '顧客A', '『商品2』仮申込', '2014-04-01 13:07:43'),
(22, '顧客A', '『商品2』本申込 / 変更2', '2014-04-02 10:06:54'),
(23, '顧客B', '『商品2』仮申込', '2014-04-03 10:06:54'),
(24, '顧客B', '『商品2』仮申込 / 変更1', '2014-04-04 13:33:51'),
(25, '顧客B', '『商品2』本申込', '2014-04-05 14:58:41'),
(26, '顧客F', '『商品1』仮申込', '2014-04-06 16:42:56'),
(27, '顧客F', '『商品1』本申込', '2014-04-07 17:27:33'),
(28, '顧客G', '『商品3』仮申込', '2014-04-08 13:56:29'),
(29, '顧客F', '『商品1』本申込 / 変更1', '2014-04-09 18:44:05'),
(30, '顧客G', '『商品3』本申込', '2014-04-10 10:43:14'),
(31, '顧客A', '『商品1』本申込 / 変更6', '2014-04-11 08:51:02'),
(32, '顧客A', '『商品2』本申込 / 変更3', '2014-04-12 20:33:39'),
(33, '顧客F', '『商品1』本申込 / 変更2', '2014-04-14 18:52:44'),
(34, '顧客C', '『商品3』仮申込', '2014-04-15 16:13:47'),
(35, '顧客C', '『商品3』本申込', '2014-04-16 21:07:35'),
(36, '顧客H', '『商品1』仮申込', '2014-04-18 21:07:34'),
(37, '顧客H', '『商品1』本申込', '2014-04-19 21:42:46'),
(38, '顧客H', '『商品1』本申込 / 変更1', '2014-04-20 22:14:10'),
(39, '顧客E', '『商品2』仮申込', '2014-04-21 23:44:06'),
(40, '顧客E', '『商品2』仮申込 / 変更1', '2014-04-22 08:22:59');

■取得したいデータ

※顧客＆商品ごとの本申込という文字列が含まれる最新データを取得したい
※商品名の長さは変動します（商品名には実際は番号はありません）

38, '顧客H', '『商品1』本申込 / 変更1', '2014-04-20 22:14:10'
35, '顧客C', '『商品3』本申込', '2014-04-16 21:07:35'
33, '顧客F', '『商品1』本申込 / 変更2', '2014-04-14 18:52:44'
32, '顧客A', '『商品2』本申込 / 変更3', '2014-04-12 20:33:39'
31, '顧客A', '『商品1』本申込 / 変更6', '2014-04-11 08:51:02'
30, '顧客G', '『商品3』本申込', '2014-04-10 10:43:14'
25, '顧客B', '『商品2』本申込', '2014-04-05 14:58:41'
18, '顧客B', '『商品3』本申込', '2014-03-20 22:14:10'
16, '顧客B', '『商品1』本申込 / 変更4' '2014-03-18 21:07:34'
12, '顧客C', '『商品1』本申込 / 変更3' '2014-03-12 20:33:39'
7 , '顧客D', '『商品1』本申込 / 変更2' '2014-03-07 17:27:33'
4 , '顧客E', '『商品1』本申込', '2014-03-04 13:33:51'

■現状
商品名（長さは変動）が同一のものを指定する必要があったため、前回教えていただいたSQLは一部（substr～のあたり）改変して使ってみました。
購入日時は最新のものを取得するのですが、他のデータは古いもの（IDの若いもの）を取得してしまいます。
最初に質問で書いたサンプルデータの書き方がそもそも時系列とは逆順になっていなかったため、教えていただいたSQLでは使えなかったようです。こちらの不手際で申し訳ありません。

SELECT SEQ_ID, TO_USER_NM, MAIL_TITLE, max( SEND_TIME ) AS SEND_TIME
FROM TABLE1
GROUP BY TO_USER_NM, substr( MAIL_TITLE, 1, instr( MAIL_TITLE, '』' ) -1 )
ORDER BY SEND_TIME DESC

まだまだ悩んでみます。。。

回答No.2

yamada_g

No.1様の回答で期待の結果が取れるのではないかと思いますが、select句にない項目をgroup by句に含めるのはMySQLでの拡張仕様ですので、集計以外の1案を。

select 顧客,商品, 購入日時
from テーブル1 T1
where not exists (
select *
from テーブル1 T2
where T1.顧客 = T2.顧客
and left(T1.商品, 1) = left(T2.商品, 1)
and T1.購入日時 < T2.購入日時
)
order by 購入日時 desc;

not existsを使って、顧客・商品の1文字目が一致する、購入日時がより新しいレコードが存在しないレコードを取得します。

お礼

すみません、出来るだけ本番に近いサンプルデータで取得漏れの原因究明をしたかったのですが、完全に目的のデータが取得できてしまい、さらに困惑中です。
本番データベースでは取れたり取れなかったりという状態のため、なんともかんとも。。。
説明すら出来ず申しわけありません。
とりあえずこちらの質問は、補足欄も使い切ってしまったので出直して立て直そうと思います。
ありがとうございました。m(_ _)m

SELECT SEQ_ID, TO_USER_NM, MAIL_TITLE, SEND_TIME
FROM TABLE1 T1
WHERE NOT
EXISTS (

SELECT *
FROM TABLE1 T2
WHERE T1.TO_USER_NM = T2.TO_USER_NM
AND substr( T1.MAIL_TITLE, 1, instr( T1.MAIL_TITLE, '』' ) -1 ) = substr( T2.MAIL_TITLE, 1, instr( T2.MAIL_TITLE, '』' ) -1 )
AND T1.SEND_TIME < T2.SEND_TIME)

AND MAIL_TITLE like '%本申込%'
ORDER BY SEND_TIME DESC

補足

回答ありがとうございます。
教えていただいたSQL文はこちらの質問に使ったテーブルではうまくいくのですが、実際のテーブルでは取得漏れが発生している？？ようでうまくいきませんでした。
こちらの情報提示不足かもしれません。申し訳ありません。

ちなみにですが、この文に、さらに

・商品に"3"という文字を含むもののみ取得

という条件を追加するとしたら、order by の前あたりへ
AND　商品 like '%3%'
の追記でいいのでしょうか。