oracle 连续15天,求教：统计用户连续活跃天数的sql

本帖最后由 cs4680ok 于 2015-9-24 17:37 编辑

思路比较乱，但是可以实现：

create table test1 as

with t1 as

(select '1' as u_id,

to_date('2015-6-1 8:20:00', 'yyyy-mm-dd hh24:mi:ss') as login_time

from dual

union all

select '1', to_date('2015-6-2 7:20:05', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '1', to_date('2015-6-3 21:20:30', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '2', to_date('2015-6-1 8:10:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '2', to_date('2015-6-3 8:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '2', to_date('2015-6-4 18:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '1', to_date('2015-6-5 9:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '1', to_date('2015-6-6 16:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '3', to_date('2015-6-1 8:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '1', to_date('2015-6-7 12:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '1', to_date('2015-6-8 23:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '3', to_date('2015-6-2 8:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual

union all

select '3', to_date('2015-6-3 2:20:00', 'yyyy-mm-dd hh24:mi:ss')

from dual)

select * from t1;

create view t1 as

select t1.u_id,

to_char(t1.login_time, 'yyyymmdd') begin_day,

to_char(t2.login_time, 'yyyymmdd') next_day

from test1 t1, test1 t2

where t1.u_id = t2.u_id

and to_char(t1.login_time, 'yyyymmdd') =

to_char(t2.login_time - 1, 'yyyymmdd')

select u_id, max(next_day - begin_day)+1

from (select u_id,

min(begin_day) begin_day,

max(next_day) next_day

from (select u_id,

begin_day,

next_day,

sum(flag) over(partition by u_id order by begin_day) gr

from (select u_id,

begin_day,

next_day,

lag(next_day) over(partition by u_id order by next_day),

case

when lag(next_day)

over(partition by u_id order by

next_day) = begin_day then

0

else

1

end flag

from t1))

group by u_id, gr)

group by u_id