gooID利用規約 改定のお知らせ

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

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名

このQ&Aに関連する最新のQ&A

A 回答 (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

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

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

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

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

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

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

大量データを多重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;
    • good
    • 0

#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
    • good
    • 0

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



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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q2つのテーブルから条件に一致しないデータ抽出

2つのテーブルから条件に一致しない行のみ抜き出しCSV
か他のテーブルに抜き出す処理を作ろうと思っています。

そこで質問なのですが、以下の2つのテーブルから
条件に一致しないコードのみ抜き出すSQL文などありますか?

-----------------------------------------
マスターテーブル
コード、ネーム1、ネーム2、店コード
1、あ、い、6
2、う、え、8
3、お、か、4
4、き、く、2


店コードテーブル
店コード、店区分
1、スーパー
2、コンビニ
3、デパート
4、ホームセンター
------------------------------------------

条件は、マスターテーブルの店コードが店コードテーブルに存在
しないデータのみ抽出です。

抽出結果は、以下の様にしたいです。
1、あ、い、6
2、う、え、8

このような考え大丈夫でしょうか?
SELECT *
FROM マスターテーブル AS M INNER JOIN 店コードテーブル AS S
ON M.店コード = S.店コード

わかりずらい質問ですがよろしくお願いします。

2つのテーブルから条件に一致しない行のみ抜き出しCSV
か他のテーブルに抜き出す処理を作ろうと思っています。

そこで質問なのですが、以下の2つのテーブルから
条件に一致しないコードのみ抜き出すSQL文などありますか?

-----------------------------------------
マスターテーブル
コード、ネーム1、ネーム2、店コード
1、あ、い、6
2、う、え、8
3、お、か、4
4、き、く、2


店コードテーブル
店コード、店区分
1、スーパー
2、コンビニ
3、デパート
4、ホームセンタ...続きを読む

Aベストアンサー

このような考え大丈夫でしょうか?
SELECT *
FROM マスターテーブル AS M INNER JOIN 店コードテーブル AS S
ON M.店コード = S.店コード

店コードが一致するレコードを抽出しているのですね。
はい、大丈夫です。
もっとも単純に、分かりやすい式を書くのであれば

この条件否定なので
left outer join で結合して、結合できなかったデータ、
つまり、店がNullのデータを取れば抽出できます。

SELECT *
FROM マスターテーブル AS M left outer join 店コードテーブル AS S
ON M.店コード = S.店コード
where S.店コード is null

または、
SELECT *
FROM マスターテーブル as M
where M.店コード not in ( select 店コード from 店コードテーブル)

等でいけるでしょう。

ただ、参考URLにもありますように、
is null とか not in は遅いです。(Indexで見れなくなるので)
大量データを扱うような場合や、速度が求められるような場合は
別途クエリを検討しましょう。

参考URL:http://www.geocities.jp/mickindex/database/db_optimize.html

このような考え大丈夫でしょうか?
SELECT *
FROM マスターテーブル AS M INNER JOIN 店コードテーブル AS S
ON M.店コード = S.店コード

店コードが一致するレコードを抽出しているのですね。
はい、大丈夫です。
もっとも単純に、分かりやすい式を書くのであれば

この条件否定なので
left outer join で結合して、結合できなかったデータ、
つまり、店がNullのデータを取れば抽出できます。

SELECT *
FROM マスターテーブル AS M left outer join 店コードテーブル AS S
ON M.店コード = S....続きを読む

QSQLで特定の項目の重複のみを排除した全項目を取得する方法

私は仕事上でデータベースを扱っていて、タイトルのような処理を行う必要があるのですが、いかんせん方法がわからずネット上を検索しても同様だったためここで質問させていただきます。

質問点を簡単に説明いたしますと、
たとえばAというテーブルがあって、

項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 A    い    ア    以
 A    う    ア    宇
 B    え    イ    江
 B    お    イ    尾

上のような構造になっている場合に「項目名1」について重複している項目を排除し、結果として


項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 B    え    イ    江

上のようなデータを取得したいのです。
この時に、Aの重複を排除して取得するレコードは1~3行目のどれでもよいです。
また、データを取得する際には必ずそのレコードの「全項目」を取得したいのでDistinctはうまく使えませんでした。

どなたか詳しい方、方法を教えてくださると幸いです。回答お待ちしております。

私は仕事上でデータベースを扱っていて、タイトルのような処理を行う必要があるのですが、いかんせん方法がわからずネット上を検索しても同様だったためここで質問させていただきます。

質問点を簡単に説明いたしますと、
たとえばAというテーブルがあって、

項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 A    い    ア    以
 A    う    ア    宇
 B    え    イ    江
 B    お    イ    尾

上のよ...続きを読む

Aベストアンサー

比較可能で一意性のある値をもてる項目6をテーブルに追加して、

select T.* from T, (select Item1,min(Item6) as Item6 from T group by item1) W where T.item6=W.item6;

――ってやるのが、一番手っ取り早いと思います。
他のところに影響がでないのであればですが。
oracleならrowidを使うとか、レコードの更新時刻を突っ込むとか。

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

QInner join と Left joinの明確な違いは?

Inner join と Left joinの違いがよくわかりません。
教えてください。

Aベストアンサー

出てくる結果が違います。

テーブル1のフィールド1に、






が、

テーブル2のフィールド1に、






が入力されている場合、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 LEFT JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3               3
4               NULL
5               NULL
6               NULL
の6レコードが出力されますが、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 INNER JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3               3
の3レコードしか出力されません。

出てくる結果が違います。

テーブル1のフィールド1に、






が、

テーブル2のフィールド1に、






が入力されている場合、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 LEFT JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3           ...続きを読む

Q2つの項目が重複するレコードを抽出する方法はありますか?

MySQL4.1で既存データに対し複合キーを新しく設定したいのですが、

ALTER TABLE `test` ADD PRIMARY KEY (`a`,`b`)

としても重複データが存在する為、作成できませんでした。

10万件あるテーブルから重複するレコードを手動で削除したいのですが、aとbが重複しているレコードだけ抽出するSQL文はありますでしょうか?

Aベストアンサー

select * from `test`
where (a,b) in(
select a,b from `test`
group by a,b
having count(*)>1)

Q連続値を取得するクエリ

T-SQLで、テーブルを参照せず、1から30までの雨に連続する値のをもつ30行を取得するには、どのようなクエリになりますでしょうか?

やりたいことは、今日から過去30日間の集計データを表にするということで、そのために、今日から過去30日の日付の行をもつ日付列を用意して、Left Joinしようとしています。

よろしくお願いします。

Aベストアンサー

参考までに
WITH T( [DATE] ) AS ( SELECT GETDATE() AS [DATE]
UNION ALL SELECT [DATE] - 1 FROM T WHERE [DATE] >= GETDATE() - 30 )
SELECT * FROM T;

Qテーブルの最後(最新)のレコードを抽出したい

宜しくお願いします。
PHP MYSQL の組み合わせで使っています

以下のようにして、最後のレコードを取り出したいのですが
まったく違う事をしているのかもしれません。

$sql =" select * from テーブル where フィールド='max' " ;

フィールドはauto_incrementで番号を振っています。
これで最大のつもりなのですが・・・。

他にも、レコードを入れた時間も記録したフィールドがあるのですが
どうしてよいか?判りません。

テーブルの最新のレコードを出したいのです。
**その中の一つのフィールドを取り出すのですが、
  それはうまくいっているみたいです 
  (max の所に数字を入れると表示します)

お手数かけますが、どなたかご教授お願いいたします。

Aベストアンサー

#2回答者です。

MySQL 4.1以前(サブクエリを使えない)なら、以下のような方法が考えられます。

select * from 表名
order by 列名 desc limit 1

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

Q割合(パーセント)を求めるにはどのようにSQLを書けばよいのでしょうか?

SQLで グループ毎にカウントした数でなくて割合を求めたいのです。

select name, count(*) from データベース名 group by name order by 2 desc;

とすると、以下のように200件あるデータで
グループ(名前)毎に個数が表示されますが、

田中 70
上村 120
橘川 10

下のように割合(パーセント)を求めるにはどのようにSQLを書けばよいのでしょうか?

田中 70 35%
上村 120 60%
橘川 10 5%

Aベストアンサー

MySQL 4.1以降なら、インラインビューを使うと簡単です。

-- MySQL 4.1-
select name,cnt,cast(cnt/ttl*100 as signed) as pct
from
(select name,count(*) as cnt
from t0
group by name) as x,
(select count(*) as ttl
from t0) as y
;

MySQL 4.0までの場合は、1回のSQLでは結果を得られません。
temporary tableまたはユーザ変数を利用して、2回のSQLで得る必要があります。ユーザ変数を利用する例を示します。

-- MySQL -4.0
select count(*) into @ttl from t0;
select name,count(*),cast(count(*)/@ttl*100 as signed) as pct
from t0
group by name
;

QSQL内でループさせるような検索文

以下の(1)のようなデータがあった場合、一つのSQL文で(3)の表が
取得できる方法はございますでしょうか?

(1)table T_SAMPLE
|UID| FLAG|CREATE_DATE|
------------------------------------------------
|000|A|2009-01-26 00:00:00|★
|000|A|2009-01-26 00:00:10|
|000|B|2009-01-26 00:00:20|

|002|A|2009-01-26 00:00:05|★
|002|B|2009-01-26 00:00:13|
|002|A|2009-01-26 00:01:00|
|002|B|2009-01-26 00:02:02|

|003|B|2009-01-26 00:05:00|★

|004|B|2009-01-26 00:00:00|★
|004|B|2009-01-26 00:00:15|
|004|A|2009-01-26 01:00:00|

|005|B|2009-01-26 00:00:11|★
|005|B|2009-01-26 00:04:05|


(2)同じUIDがあった場合、CREATE_DATEが早い情報(上表★)を使用
|UID| FLAG|CREATE_DATE|
---------------------------------------------------
|000|A|2009-01-26 00:00:00|★
|002|A|2009-01-26 00:00:05|★
|003|B|2009-01-26 00:05:00|★
|004|B|2009-01-26 00:00:00|★
|005|B|2009-01-26 00:00:11|★

(3)FLAGでカウント<= 取得したい結果
|COUNT(FLAG)| FLAG|
--------------------------------
|2| A|
|3| B|


イメージとしては、

[処理a]
select FLAG from T_SAMPLE where UID = '000' order by CREATE_DATE limit 0,1
×それぞれUID

とすると、上記(1)→(2)で

select UID,[処理aの結果においてUIDに対応したFLAG] from T_SAMPLE group by UID

という状態の結果が一度のSQLで取得できればよいと考えているのですが。
実際にこの処理を実施する際は、自由に指定されるCREATE_DATEの範囲で絞り込むため、別テーブルを作って、、、等の処理は避けたいのです。

最適な方法がございましたら、なにとぞご教示願います。

以下の(1)のようなデータがあった場合、一つのSQL文で(3)の表が
取得できる方法はございますでしょうか?

(1)table T_SAMPLE
|UID| FLAG|CREATE_DATE|
------------------------------------------------
|000|A|2009-01-26 00:00:00|★
|000|A|2009-01-26 00:00:10|
|000|B|2009-01-26 00:00:20|

|002|A|2009-01-26 00:00:05|★
|002|B|2009-01-26 00:00:13|
|002|A|2009-01-26 00:01:00|
|002|B|2009-01-26 00:02:02|

|003|B|2009-01-26 00:05:00|★

|004|B|2009-01-26 00:00:00|★
|004|B...続きを読む

Aベストアンサー

今MySQLの環境がないのでSQL Serverでしか試してませんが、これでどうでしょう?
4.1でサブクエリが実装されてるということなので、動くと思うのですが・・・

SELECT
  COUNT(*)
 , flag
FROM
  T_Sample A
WHERE
  NOT EXISTS(
   SELECT * FROM T_Sample B
   WHERE A.uid = B.uid AND
      B.create_date < A.create_date
  )
GROUP BY
  flag
;

SELECT
  COUNT(*)
 , flag
FROM
  T_Sample A
WHERE
  A.create_date =
   (SELECT MIN(B.create_date) FROM T_Sample B
    WHERE A.uid = B.uid)
GROUP BY
  flag
;


人気Q&Aランキング

おすすめ情報