oracle登录_ORACLE数据库SQL实现连续登录天数

with a as (

select 'A' as uid1, date '2019-05-01' as time from dual union

select 'A' as uid1, date '2019-05-02' as time from dual union

select 'A' as uid1, date '2019-05-03' as time from dual union

select 'A' as uid1, date '2019-05-05' as time from dual union

select 'A' as uid1, date '2019-05-07' as time from dual union

select 'B' as uid1, date '2019-05-01' as time from dual union

select 'B' as uid1, date '2019-05-06' as time from dual union

select 'B' as uid1, date '2019-05-08' as time from dual /*union*/

/*select 'A' as uid1, date '2019-05-16' as time from dual union

select 'A' as uid1, date '2019-05-17' as time from dual */

)

select uid1, min(time) 起始时间, max(time) 结束时间, count(*) 累计登录天数

from (select uid1,

time - row_number() over(PARTITION BY uid1 order by time) gn,

time

from a)

group by uid1, gn ORDER BY uid1