查询连续5天登录的用户

1.关注1: 要求连续5天，不是总数超过5天

2.关注2:一天可以登录多次。

 

测试数据

 

CREATE TABLE t(user_id varchar2(50), access_time date);

insert into t values('a',sysdate-10);
insert into t values('a',sysdate-9);
insert into t values('a',sysdate-8);
insert into t values('a',sysdate-7);
insert into t values('a',sysdate-6);
insert into t values('b',sysdate-11);
insert into t values('b',sysdate-9);
insert into t values('b',sysdate-8);
insert into t values('c',sysdate-10);
insert into t values('c',sysdate-9);
insert into t values('c',sysdate-8);
insert into t values('c',sysdate-7);
insert into t values('c',sysdate-6);
insert into t values('c',sysdate-1);

解决思路一:使用分析函数

SELECT distinct UID
  FROM (SELECT UID,
               LAG (access_dt, 4) OVER (PARTITION BY UID ORDER BY access_dt)
                  prev_access_dt， access_dt
          FROM ( SELECT UID, TRUNC (access_time) access_dt
                    FROM t
                GROUP BY UID, TRUNC (access_time)))
WHERE prev_access_dt IS NOT NULL AND (access_dt - prev_access_dt) = 4；

解决思路二：使用rownum

SELECT DISTINCT user_name
    FROM ( SELECT user_name, TRUNC (access_time) access_dt
              FROM t
          GROUP BY user_name, TRUNC (access_time)
          ORDER BY user_name, TRUNC (access_time))
GROUP BY user_name, TO_CHAR (access_dt, 'J') - rownum
  HAVING COUNT (*) > 4；