查询连续5天登录的用户

来源：互联网发布：下载购物淘宝网编辑：程序博客网时间：2024/06/06 04:04

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；