データの間隔が知りたい時

質問

１０面体のサイコロを２個を振ってデータを入力しています。
結果が以下のようになったとします。
(kai=回数,dice1=サイコロ1,dice2=サイコロ2)

kai　dice1　dice2
--+------+------
5回　　1　　5 ←最新回
4回　　3　　8
3回　　5　　9
2回　　1　　7
1回　　10　　5
----------------
とあった時、dice1の1は３回ぶりに出ていますので、ここで３回と
間隔を表示したいのですが間隔を出すSQL文の立て方に悩んでいます。
またdice2の5は1回目に出ていますが、3回目にdice1で5が出ていますので
この場合は2回と表示したいのですが。
間隔をつかみつつ、なおかつ2つの要素を比較しながらその間隔を導く
というSQL文はどういった構造になるのでしょうか？
お詳しい方、参考サイトなどでも結構ですのでご教授宜しくお願いい致します。

回答No.3

moon_night

とりあえず間隔だけ。
テストデータ
create table d (
kai tinyint,
dice1 tinyint,
dice2 tinyint
);

insert into d values(1,10,5);
insert into d values(2,1,8);
insert into d values(3,5,9);
insert into d values(4,3,7);
insert into d values(5,1,5);
insert into d values(6,2,5);
insert into d values(7,3,1);
insert into d values(8,4,2);
insert into d values(9,5,3);
insert into d values(10,1,4);

SQL文
select a.kai,a.dice1,a.kai-max(b.kai) from d as a,d as b
where a.kai > b.kai
and a.dice1 = b.dice1
group by a.kai
;

未出現のデータは出てきませんが。
出現回数は回数を数えるだけなのでそれほど難しくないでしょう。
一緒に結果を出すことは考えないほうがいいと思います。（やろうと思えば出来ないことは無いと思いますが）

回答No.2

chukenkenkou

MySQL 4.0系だと、サブクエリ等も使えないので、1回のクエリでは無理ですが、それでもいいですか？

最新が第5回で、1と5の場合は、1の間隔としては3を返す。
最新が第10回で、1が出て、第6～9回に1が出ていない場合は、1の間隔として5を返す。

つまり、最新の回から見て、もっとも近い同じ目が出た回数までの間隔を見つければいいのですよね？
それとも、第10回の場合、第5回までで間隔が5、さらに遡って第2回までの間隔が3と、すべて出したいと言ってますか？

回答No.1

chukenkenkou

MySQLのバージョンは、何ですか？

最新の回で出た目と、直近の同じ目が出た回を見つけるだけでいいのですね？

お礼

ありがとうございます。
MySQLのバージョンは4.0.2でございます。

補足

すみません、補足させて頂きます。
直近だけでなく、例えばこの時点で第5回のdice1の1に対して3回と
なりますが、第10回でdice1に1が出た場合、その時は5回となります。
そうして延々と間隔のみを捉えていきたいという事でございます。サイコロは最終的に10個ほど、回数は数千回となると思います。