oracle查询连续n天登录的用户
-- 查询连续3天登录的用户
1 先创建一个表,如下:
1 create table USER_DATA 2 ( 3 USER_ID NUMBER, 4 LOGIN_TIME DATE 5 );
2 插入用户登录数据:
1 insert into user_data (USER_ID, LOGIN_TIME) values ('10002', to_date('21-07-2014', 'dd-mm-yyyy')); 2 insert into user_data (USER_ID, LOGIN_TIME) values ('10002', to_date('22-07-2014', 'dd-mm-yyyy')); 3 insert into user_data (USER_ID, LOGIN_TIME) values ('10002', to_date('24-07-2014', 'dd-mm-yyyy')); 4 insert into user_data (USER_ID, LOGIN_TIME) values ('10002', to_date('25-07-2014', 'dd-mm-yyyy')); 5 insert into user_data (USER_ID, LOGIN_TIME) values ('10003', to_date('21-07-2014', 'dd-mm-yyyy')); 6 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('28-07-2014', 'dd-mm-yyyy')); 7 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('29-07-2014', 'dd-mm-yyyy')); 8 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('30-07-2014', 'dd-mm-yyyy')); 9 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('21-07-2014', 'dd-mm-yyyy')); 10 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('22-07-2014', 'dd-mm-yyyy')); 11 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('23-07-2014', 'dd-mm-yyyy')); 12 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('24-07-2014', 'dd-mm-yyyy')); 13 insert into user_data (USER_ID, LOGIN_TIME) values ('10001', to_date('26-07-2014', 'dd-mm-yyyy'));
3 查询连续3天登录的用户
思路:现有的数据里面已经是经过处理的,每个用户每天只有一条数据,并且登录时间是每天的零点,如果是真实的数据则进行trunc后就行,然后选择的时候,需要依据user_id和login_time进行排序,这样在外层选择时,rownm在每个用户按日志排序时是连续的,然后查询时用日志减去rownum,这样如果是连续的日期,其相减的结果就是一样的,然后根据此结果来判断是否是连续登录就行了,查询连续3天登录的用户id的具体代码如下:
1 select distinct user_id 2 from 3 ( 4 select b.user_id, b.d_temp, count(*) 5 from 6 ( 7 select a.user_id, a.login_time - rownum d_temp 8 from 9 ( 10 select t.* 11 from user_data t 12 order by t.user_id, t.login_time 13 )a 14 )b 15 group by b.user_id, b.d_temp 16 having count(*) >= 3 17 );
以上查询出来的结果就是至少连续登录3天的用户id了.
