SQLで連続したカラムが何個あるかを求める方法

お世話になっております。

PHPとmysqlでサイトを作っておりまして、そのログを分析しようとしています。
あるテーブルにユーザー名とログイン日付が入ったテーブルが有ったとして、その中で連続○日ログインしていた人は○人という結果を取りたいと考えているのですがこれをSQLで処理する方法はありますでしょうか？
今のところ、PHPで全データを取った後にPHP側で処理するしかないかと思っているのですが、件数が多いため可能であればmysql側で処理したいと思っています。

日付はUNIXTIMEに直すなどすればINTEGERに変換出来るとは思っているのですが、日付型（難しいようであればINT型の連続値でも問題ありません）でSQLで処理出来る方法はあるのでしょうか？
○○結合をすれば良いなど手がかりなど教えていただけると助かります。

■テーブル
username　　　login_date
ほげ　　　　　　2014/5/1
ほげ　　　　　　2014/5/2
ほげ　　　　　　2014/5/3
ほげ　　　　　　2014/5/4
ふーばー　　　2014/5/2
ふーばー　　　2014/5/3
あああ　　　　　2014/5/1
あああ　　　　　2014/5/3
あああ　　　　　2014/5/4

■ほしい結果
4日連続　　　１名
２日連続　　　２名

No.1 回答者： yamada_g 回答日時： 2014/05/13 09:41

回りくどい気がしますが、こんな感じでどうでしょうか。

select consecutive, count(*)
from (
　select username, count(*) + 1 consecutive
　from (
　　select username, login_date
　　from テーブル t1
　　where exists (
　　　select *
　　　from テーブル t2
　　　where t2.username = t1.username
　　　and t2.login_date = t1.login_date + interval 1 day
　　)
　) t3
　group by username
) t4
group by consecutive;

MySQLの環境がないため動作確認をしていないので、文法的な修正が必要かもしれません。
また、全角スペースでインデントしているので実行時は削除してください。

No.2 回答者： yambejp 回答日時： 2014/05/13 11:39

#1は要件に合致していないような・・・

かなり効率悪いですがこんな感じでどうでしょう？

//準備
create table tbl(username varchar(20),login_date date);
insert into tbl values('ほげ','2014-5-1'),('ほげ','2014-5-2'),('ほげ','2014-5-3'),('ほげ','2014-5-4'),('ふーばー','2014-5-2'),('ふーばー','2014-5-3'),('あああ','2014-5-1'),('あああ','2014-5-3'),('あああ','2014-5-4'),('いいい','2014-5-1'),('ううう','2014-5-10'),('ううう','2014-5-11'),('ううう','2014-5-13'),('ううう','2014-5-14');

//集計
select renzoku,count(*) as count
from (select username,max(diff) as renzoku
from (select username,d1,max(diff) as diff
from(
select t1.username,d1,d2,diff
from (select t1.username
,t1.login_date as d1
,t2.login_date as d2
,datediff(t2.login_date,t1.login_date)+1 as diff
from tbl as t1
left join tbl as t2
on t1.username=t2.username
and t1.login_date <= t2.login_date) as v1
inner join tbl as t1
on login_date between d1 and d2
and v1.username=t1.username
group by username,d1,d2
having count(*)=v1.diff
) as t2
group by username,d1
) as t3
group by username
) as t4
group by renzoku

No.3 回答者： mpro-gram 回答日時： 2014/05/13 23:21

大量データを多重join すると途方もない時間がかかったりするし、usernameとlogin_dateで並べたのを順に見ていけば、一巡で、集計値は出てきそうなのにって思うから、mysql の procedure で実行してみました。

やってることは、php側でやるならこうやるだろうことをプログラムしています。phpとmysqlで大量データのやりとりが減るのが利点。
offset をカウンタ変数で指定しながら１行ずつデータ取得のところ、prepared文が必要なのが苦労しました。prepared文には、@変数でないと、外からデータを入れたり、取り出したり出来ないというのもあって、局所変数と入り交じってます。
実行時間的には、どうかな、それほど大量データでは試してないので、行数と同じ回数select発行するのは、かなり時間掛かる気がしないでもないけど、unique indexがあればなんとかというところ。

-- 元データ、 unique index があること
create table `loglog` (
username varchar(100)
, login_date date
, unique ( username, login_date )
);
-- 集計用　一時テーブル procedure 内で create や truncate すると、権限設定がややこしいので、先に作成しておく
create table logseq (
username varchar(10)
, seqstart date
, sequence int
);
-- delimiter 変更
\d #
-- 全角空白で字下げしています。一応集計区間の開始だけ指定してみました。
create procedure logsequence(in start date)
begin
DECLARE seqday, maxline INT DEFAULT 0;
DECLARE u1 varchar(10) default '';

select count(*) into maxline from loglog where login_date>=start ;
if (maxline >1 ) then

　select username, login_date into u1,@d1 from loglog where login_date>=start order by username,login_date limit 1 offset 0;
　set seqday = 1, @idx=1;
　set @sql =concat( 'select username, login_date, datediff(login_date, @d1) into @u2,@d2,@diffcnt from loglog where login_date>="' , start , '" order by username,login_date limit 1 offset ?' );
　prepare tmp_stmt from @sql;
　while @idx < maxline DO
　　execute tmp_stmt using @idx;
　　if(u1 = @u2) then
　　　if(@diffcnt = seqday) then
　　　　set seqday = seqday +1;
　　　else
　　　　if(seqday>1) then
　　　　　insert into logseq values (u1,@d1, seqday );
　　　　end if;
　　　　　set @d1 = @d2 , seqday = 1 ;
　　　end if;
　　else
　　　if(seqday>1) then
　　　　insert into logseq values (u1,@d1, seqday );
　　　end if;
　　　set u1 = @u2 , @d1 = @d2 , seqday = 1 ;
　　end if;

　　set @idx = @idx +1 ;
　end while;
end if;

select maxsequence, count(*) as usercount
from ( select username , max(sequence) as maxsequence from logseq
where seqstart >= start
group by username
) as seq
group by maxsequence;
end #
-- delimiter 変更
\d ;
truncate logseq;
call logsequence('2014-04-01');

-- 一時データのチェック 実連続値の表示
select * from logseq order by username,seqstart;

-- 各連続値ごとの集計（usernameは複数回カウントされる）
select sequence, count(*) as multicount from logseq group by sequence;

No.4 回答者： yambejp 回答日時： 2014/05/14 10:47

見直したら一部非効率なところがあったのでちょっと改善するとともに

ちょっとだけ解説します

//元データ
insert into tbl values('ほげ','2014-5-1'),('ほげ','2014-5-2'),('ほげ','2014-5-3'),('ほげ','2014-5-4'),('ふーばー','2014-5-2'),('ふーばー','2014-5-3'),('あああ','2014-5-1'),('あああ','2014-5-3'),('あああ','2014-5-4'),('いいい','2014-5-1'),('ううう','2014-5-10'),('ううう','2014-5-11'),('ううう','2014-5-13'),('ううう','2014-5-14');
※インデックスを貼ってあります

//第一段階
select t1.username
,t1.login_date as d1
,t2.login_date as d2
,datediff(t2.login_date,t1.login_date)+1 as diff
from tbl as t1
left join tbl as t2
on t1.username=t2.username
and t1.login_date <= t2.login_date
inner join tbl as t3
on t3.login_date between t1.login_date and t2.login_date
and t1.username=t3.username
group by username,d1,d2
having count(*)=diff

これでusername,login_dateごとに、何日連続しているか得られます。
たとえば「あああ」さんは
あああ,2014-05-01,2014-05-01,1
あああ,2014-05-03,2014-05-03,1
あああ,2014-05-03,2014-05-04,2
あああ,2014-05-04,2014-05-04,1

となり、5/1から5/1まで1連続、5/3から5/3まで1連続、5/3から5/4まで2連続、5/4から5/4まで1連続であることがわかります。
考え方次第ですが厳密にいえば1連続データは連続ではないので抽出しないでもいいですが、
これをしておかないと1回しかログインしていない人や、毎日ログインしない人がヒットしません。
すこし無駄なような気がしますが1連続はとっておいて損はないでしょう

今回ほしいのはusernameごとの最大連続日数なので
//第二段階
select username,max(diff) as renzoku
from (
select t1.username
,t1.login_date as d1
,t2.login_date as d2
,datediff(t2.login_date,t1.login_date)+1 as diff
from tbl as t1
left join tbl as t2
on t1.username=t2.username
and t1.login_date <= t2.login_date
inner join tbl as t3
on t3.login_date between t1.login_date and t2.login_date
and t1.username=t3.username
group by username,d1,d2
having count(*)=diff
) as t4
group by username

とすると、結果
username,renzoku
あああ,2
いいい,1
ううう,2
ふーばー,2
ほげ,4

となります。元データをみてもらうとわかりますが
「ううう」さんは2連続ログインが2回ありますが、「最大」は2連続となるので
上記データがただしいとわかると思います。
#1さんの抽出だと2連続が2回は4連続だとカウントされてしまいます


最終的に、連続日数ごとの件数が知りたいので
//第三段階

select renzoku,count(*) as count
from (
select username,max(diff) as renzoku
from (
select t1.username
,t1.login_date as d1
,t2.login_date as d2
,datediff(t2.login_date,t1.login_date)+1 as diff
from tbl as t1
left join tbl as t2
on t1.username=t2.username
and t1.login_date <= t2.login_date
inner join tbl as t3
on t3.login_date between t1.login_date and t2.login_date
and t1.username=t3.username
group by username,d1,d2
having count(*)=diff
) as t4
group by username
) as t5
group by renzoku

で、命題の解を得ることができます

なお、処理的には第一段階ではインデックスがきいていますが
第二、第三段階は結果にたいする集計のためインデックスが利用されません。
ただ、第一段階でほぼデータ抽出が完了しているため、
さほど負荷が問題になることはないと思います

この回答へのお礼

早速の回答ありがとうございます。
ただ私自身が風邪をひいてしまい、まだ会社に行けておりません。

週明けには行くつもりですので実験させていただきます。

結果出ましたら再度お礼させていただきます。

お礼日時：2014/05/18 02:25