mysqlのデータから連続する日数の最大を出す方法

いろいろと調べたのですが分からなかったので質問させて下さい。

あるMysqlのデータベースにこのようなデータがあるとき、

ID date point
------------
1 2011-06-12 10
1 2011-06-13 5
1 2011-06-14 10
2 2011-06-09 5
2 2011-06-10 10
2 2011-06-15 5
3 2011-06-09 10
3 2011-06-10 10
3 2011-06-14 5
3 2011-06-15 10
3 2011-06-16 5

ここから各IDごとの最大の連続日数数を抜き出す方法はありますでしょうか？
結果として

ID 連続日数
-----------
1 3
2 2
3 3

といったものを求めています。

No.2 回答者： nora1962 回答日時： 2011/07/15 22:28

idごとの連続日付の最大回数を求めるファンクションをつくってみました。

テーブル名じゃ便宜的に「tbl」にしてあります。

delimiter //
create function consecutive_date( inp_id int ) returns int
begin
declare done int;
declare dt date;
declare sv_dt date;
declare i int;
declare j int;
DECLARE myCursor CURSOR
FOR select `date` from TBL where id = inp_id
order by `date`;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

set done = 0;
set i = 0;
set j = 0;
set sv_dt = null;
set dt = null;

open myCursor;

repeat
fetch myCursor into dt;
if sv_dt + 1 = dt then
set i = i + 1;
elseif sv_dt is null then
set sv_dt = dt;
set i = 1;
set j = 1;
else
if i > j then
set j = i;
end if;
set sv_dt = dt;
set i = 1;
end if;
until done = 1 end repeat;

close myCursor;

return j;
end;
//
delimiter ;

select distinct id, consecutive_date(id) from tbl;

No.1 回答者： yambejp 回答日時： 2011/07/14 16:51

なんか効率的なやりかたがありそうな気がしますが・・・

冗長にロジックを考えてみました。

連続したデータということは・・・
(1)開始日と終了日がある→その差＋１が連続した日付
(2)開始日と終了日の間におさまる日付の数が連続した日付
(3)(1)と(2)が合致するのが連続した日付であり、その最大値がIDごとの最大連続日

ただし命題に条件の付加が必要・・・IDとdateの組合せがユニークであること。

以下SQL
CREATE TABLE `hoge`(`ID` int,`date` date, `point` int ,unique key(`ID`,`date`));
INSERT INTO `hoge` VALUES(
1,'2011-06-12',10),(
1,'2011-06-13',5 ),(
1,'2011-06-14',10),(
2,'2011-06-09',5 ),(
2,'2011-06-10',10),(
2,'2011-06-15',5 ),(
3,'2011-06-09',10),(
3,'2011-06-10',10),(
3,'2011-06-14',5 ),(
3,'2011-06-15',10),(
3,'2011-06-16',5 );

-- ここまでが初期設定

SELECT ID,MAX(DATEDIFF(d2,d1)+1) AS renzoku
FROM (
SELECT T1.ID, T1.date as d1, T2.date as d2
FROM hoge AS T1
INNER JOIN hoge AS T2
ON T1.ID=T2.ID
AND T1.date<T2.date
) AS SUB
WHERE (SELECT COUNT(*)
FROM hoge
WHERE date BETWEEN d1 AND d2
and ID=SUB.ID
)
=DATEDIFF(d2,d1)+1
GROUP BY ID

この回答へのお礼

ありがとうございます。

なるほど1日違いの部分で最初と最後の日があるので、
その日数差の最大値が連続日数最大という考え方ですね。

自分のデータで試してみたいと思います。

やはり結構、複雑な形になるのですね。
冗長かもしれないということですので、解答欄は他の方法もあるのか少し空けておきます。

お礼日時：2011/07/15 10:19