Oracle 连续登陆问题

最新推荐文章于 2023-04-20 14:56:59 发布

aperfels

最新推荐文章于 2023-04-20 14:56:59 发布

阅读量947

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/u012132482/article/details/53128024

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

– // 连续登陆问题 ORACLE SQL

-- 数据表结构
CREATE TABLE TMP_DT(
  MID  VARCHAR2(10),            -- 用户ID
  DT   date                     -- 用户登陆时间
);

-- 初始化数据
insert into TMP_DT (MID, DT) values ('U1', to_date('01-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('01-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('01-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('02-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('02-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('03-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('03-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('05-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U1', to_date('05-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('05-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('05-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('06-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('06-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('06-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('07-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('07-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('10-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('10-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U2', to_date('10-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('05-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('05-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('06-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('06-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('06-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('07-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('08-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('09-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('10-08-2016', 'dd-mm-yyyy'));
insert into TMP_DT (MID, DT) values ('U3', to_date('10-08-2016', 'dd-mm-yyyy'));

---------------------------------------------------------------------
-- oracle sql语句
-- 查询出符合条件的用户
-- 查询结果：连续登陆3天及以上,且每天登陆次数大于等于2
SELECT DISTINCT T.MID
FROM(
  SELECT MID,
         ROW_NUMBER() OVER(PARTITION BY B.MID ORDER BY B.DTC ASC) ROWNO,   -- 每个用户的登陆日期编号
         B.DTC - MIN(B.DTC) OVER(PARTITION BY B.MID)  DIF   -- 日期当前与用户最小日期的差值
    FROM ( --// 符合条件的日期
           SELECT A.MID,
                  TRUNC(A.DT,'DD') DTC
             FROM TMP_DT A
             GROUP BY A.MID, TRUNC(A.DT,'DD')
            HAVING COUNT(1) > 1    -- 每天登陆大于1次
             ORDER BY A.MID, DTC ASC
        ) B
) T
GROUP BY T.MID, T.ROWNO-T.DIF  --对用户和 日期编号-日期差值 分组统计数量
HAVING COUNT(1) >= 3;   -- 连续登陆大于2天