お世話になっております。
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日連続 1名
2日連続 2名
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
回りくどい気がしますが、こんな感じでどうでしょうか。
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
- 回答日時:
#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
- 回答日時:
大量データを多重join すると途方もない時間がかかったりするし、usernameとlogin_dateで並べたのを順に見ていけば、一巡で、集計値は出てきそうなのにって思うから、mysql の procedure で実行してみました。
やってることは、php側でやるならこうやるだろうことをプログラムしています。phpとmysqlで大量データのやりとりが減るのが利点。
offset をカウンタ変数で指定しながら1行ずつデータ取得のところ、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
- 回答日時:
見直したら一部非効率なところがあったのでちょっと改善するとともに
ちょっとだけ解説します
//元データ
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
で、命題の解を得ることができます
なお、処理的には第一段階ではインデックスがきいていますが
第二、第三段階は結果にたいする集計のためインデックスが利用されません。
ただ、第一段階でほぼデータ抽出が完了しているため、
さほど負荷が問題になることはないと思います
早速の回答ありがとうございます。
ただ私自身が風邪をひいてしまい、まだ会社に行けておりません。
週明けには行くつもりですので実験させていただきます。
結果出ましたら再度お礼させていただきます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- SQL Server [SQLServer] テーブル名からカラム名を取得する 1 2022/08/23 21:20
- システム 帳票出力を行う単体テストのテストデータが作成できません 2 2023/08/26 21:26
- IT・エンジニアリング 帳票出力を行う単体テストのテストデータが作成できません 2 2023/08/26 21:25
- PostgreSQL PostgreSQL レコードからアイテム種類数を取得したい 2 2022/11/23 22:31
- PHP PHP MySql ページング 2 2022/09/20 06:38
- PHP コメント機能に返信欄を矢印で追加したい 1 2022/05/09 21:17
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- Access(アクセス) Accessテーブルの結合で別々のテーブルのフィールドを組み合わせて値を出す方法について 2 2022/07/20 19:43
- JavaScript Typescript が必要な理由 1 2023/01/07 11:45
- MySQL 書籍の内容はまともでしょうか? 1 2023/01/22 03:07
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SQLだけでselect結果に定数を加...
-
1の行を固定した上でVBAを用い...
-
ORMについて
-
複数のテーブルから値を合計出...
-
これをSQL文で出来るでしょうか?
-
2回実行のSQL文を1回にしたい
-
count関数の値をwhere句で使用...
-
テーブルの最後(最新)のレコー...
-
ファイルの漢数字の順番につい...
-
エクセル、並び替え正しくソー...
-
AccessのSQL文で1件のみヒット...
-
固定長データのテキストファイ...
-
レコードの登録順がおかしい
-
SQL*Loader Append
-
ACCESSのクエリで空白以降を別...
-
アクセスのレポートでレコード...
-
excel:一番上の行がソートでき...
-
コンボボックスのソートについて
-
Excelで作成した円グラフのデー...
-
ファイルメーカーでソート後の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
select文の実行結果に空白行を...
-
SQL文の入れ子について
-
割合(パーセント)を求めるには?
-
24時間以内に更新されたデータ...
-
Mysqlで変数を使ったSELECT文の...
-
時間帯テーブルから直近空き時...
-
集計されたテーブルの結合
-
php mysqlにて年月日で登録され...
-
SQLだけでselect結果に定数を加...
-
時間帯テーブルから直近空き時...
-
SQLにて順列の抽出
-
mysql5でGROUP BYごとにLIMIT??
-
LIMIT句で少なくとも1行は選択...
-
条件付けで集計したものをUNION...
-
SQLローダーCSV取込で、囲み文...
-
単一グループのグループ関数で...
-
テーブルの最後(最新)のレコー...
-
count関数の値をwhere句で使用...
-
【PL/SQL】FROM区に変数を使う方法
-
SELECT FOR UPDATE で該当レコ...
おすすめ情報