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;