database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 30333 게시물 읽기
No. 30333
연속 로그인(3일 연속) 한 회원수 뽑아내는 쿼리 문의드립니다.
작성자
원도재
작성일
2012-10-05 16:50:17
조회수
5,038

연속 로그인(3일 연속) 한 회원수 뽑아내는 쿼리 문의드립니다.

우선 테이블 구조 및 데이터는 아래와 같습니다.

회원ID       로그인 시간          로그아웃시간

20    2012-5-29 16:59:28     2012-5-29 17:41:50
24    2012-5-29 17:14:54     2012-5-29 18:30:08
20    2012-5-29 17:43:44     2012-5-29 18:47:14
16    2012-5-29 09:49:29     2012-5-29 18:59:24
18    2012-5-29 16:47:00     2012-5-29 19:00:13
11    2012-5-29 16:45:55     2012-5-29 19:09:05
16    2012-5-29 20:31:02     2012-5-29 21:37:33
23    2012-5-30 11:08:46    2012-5-30 11:11:28
13    2012-5-30 09:36:25    2012-5-30 11:12:58
16    2012-5-30 11:01:22    2012-5-30 11:30:49
12    2012-5-30 10:25:50    2012-5-30 11:38:49
16    2012-5-30 21:45:21    2012-5-30 21:48:31
20    2012-5-30 02:56:47    2012-5-30 03:10:36
27    2012-5-31 06:01:16    2012-5-31 06:18:04
15    2012-5-31 11:10:17    2012-5-31 11:17:01
13    2012-5-31 09:37:05    2012-5-31 11:30:50
20    2012-5-31 11:43:00    2012-5-31 11:54:34



위와 같이 회원이 로그인을 하면 로그인 시간을 로그아웃하면 로그아웃 시간을 담고있습니다.
질문의 요지는 3일 연속 로그인 한 회원의 수를 구하는 것입니다.
문제는 로그인,로그아웃한 데이터가 여러번 들어갈수 있게끔 처리를 했기때문에 될듯 될듯 하면서 영 감이 오질 않네요 ㅠㅠ

부탁드립니다.

이 글에 대한 댓글이 총 3건 있습니다.

일단 쿼리는 링크를 참조하세요 : http://sqlfiddle.com/#!2/63a90/11

 

원리는 다음과 같습니다.

1. 회원 아이디, 로그인 시간으로 정렬합니다.

2. 회원 아이디가 연속이고,

3. 날짜가 1일 이하로 차이가 나면,

4. 날짜의 차이 값 만큼 serial 을 + 시킵니다.

5. serial 값이 2보다 크면 3일 이상의 연속을 의미합니다.

 

박현우(lqez)님이 2012-10-08 22:37:25에 작성한 댓글입니다.

약간 다른 형태로 접근해 보았습니다.

성능에 대해선 그다지 고려 하지 않았고, 그냥 힌트 정도로 생각 하시면 될것 같습니다.

위 데이타 기준으로 볼때, 

mysql> select user_id,  max(date(login))  login from log group by user_id, date(login) order by user_id, login          -> ;

+---------+------------+

| user_id | login      |

+---------+------------+

|      11 | 2012-05-29 |

|      12 | 2012-05-30 |

|      13 | 2012-05-30 |

|      13 | 2012-05-31 |

|      15 | 2012-05-31 |

|      16 | 2012-05-29 |

|      16 | 2012-05-30 |

|      18 | 2012-05-29 |

|      20 | 2012-05-29 |

|      20 | 2012-05-30 |

|      20 | 2012-05-31 |

|      23 | 2012-05-30 |

|      24 | 2012-05-29 |

|      27 | 2012-05-31 |

+---------+------------+

14 rows in set (0.00 sec)

 

user_id = 20 만 3일 연속으로 로긴 한것 같네요, 그럼 20만 결과로 나오면 제대로 돈게 맞을것 같습니다 :)

mysql> set @seq=0; -- 초기 값 설정. 

select user_id, count(*) from

    (select user_id,  max(date(login))  login

     from log group by user_id, date(login) order by user_id, login) as t1

group by user_id,  login -@seq := @seq+1  having count(*) >2; -- seq가 1씩 증가하고 날짜 값도 1씩 증가하므로, 동일 한 값으로 증가 했을 경우 date가 가지는 값은 같아 지므로, 하나의 그룹이 될수 있습니다. 따라서, 이걸 다시 user_id + 날짜차의 그룹으로 그룹핑해서  3일 이상인 사람을 구합니다.

Query OK, 0 rows affected (0.00 sec)

 

+---------+----------+

| user_id | count(*) |

+---------+----------+

|      20 |        3 |

+---------+----------+

1 row in set (0.00 sec)

 

 

 

 

 

lnx4u님이 2012-11-07 20:36:58에 작성한 댓글입니다. Edit

count 가 해당 유저의 연속 방문일수 입니다. 

lnx4u님이 2012-11-07 20:40:31에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
30336쿼리 재질문드립니다.(박현우님)^^; [3]
다중이
2012-10-12
3859
30335federated engine 테이블에 트리거 가능한가요? [1]
233
2012-10-09
3170
30334한달중에 화요일의 날자와 몇주차인지 알고싶어요! [1]
권순환
2012-10-08
3839
30333연속 로그인(3일 연속) 한 회원수 뽑아내는 쿼리 문의드립니다. [3]
원도재
2012-10-05
5038
30332innodb 테이블당 개별 TableSpace 생성할 경우 질문입니다. [2]
나그네
2012-10-05
4936
30330My.ini 설정에 관하여 질문 드리겠습니다 ^^ [2]
최병길
2012-09-25
4222
30329조인 알고리즘 관련 질문 [3]
정호영
2012-09-24
3411
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2017 DSN, All rights reserved.
작업시간: 0.071초, 이곳 서비스는
	PostgreSQL v9.6.3으로 자료를 관리합니다