【Oracle】查历史表里最近七天有多少人留下过登录记录

有一张登录历史表：

create table AccessHistory(
    id int,
    userid int,
    loginDate timestamp,
    primary key(id)
);

测试数据如下：

insert into AccessHistory values(1,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(2,2,to_date('2021.10.12 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(3,3,to_date('2021.10.11 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(4,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(5,2,to_date('2021.10.10 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(6,3,to_date('2021.10.09 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(7,4,to_date('2021.10.08 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(8,5,to_date('2021.10.10 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(9,3,to_date('2021.10.12 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(10,1,to_date('2021.10.11 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(11,3,to_date('2021.10.06 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(12,4,to_date('2021.10.07 08:00:00','yyyy.MM.dd hh24:mi:ss'));

求七天内的登陆记录不难，sql如下：

select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6) order by vdate

SQL> select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6) order by vdate;

VDATE          USERID
---------- ----------
10.07               4
10.08               4
10.09               3
10.10               2
10.10               5
10.11               1
10.11               3
10.12               2
10.12               3
10.13               1
10.13               1

已选择 11 行。

在此基础上，我们可以得出最近七天有多少人次登录：

（中间SQL）

select a.vdate,count(*) from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
group by a.vdate order by vdate

SQL> select a.vdate,count(*) from
  2  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
  3  group by a.vdate order by vdate;

VDATE        COUNT(*)
---------- ----------
10.07               1
10.08               1
10.09               1
10.10               2
10.11               2
10.12               2
10.13               2

已选择 7 行。

现在是否达成需求了？没有! 因为测试数据每天的都在，所以七天都是满的，如果有一天的数据是缺失的，那就变成了六天，所以我们还要准备一个七天的连续序列，要达成此目的我们可以用connect by语句帮忙（连续序列生成请参考：https://www.cnblogs.com/heyang78/p/15239372.html）。

select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8

SQL> select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8;

VDATE            VCNT
---------- ----------
10.07               0
10.08               0
10.09               0
10.10               0
10.11               0
10.12               0
10.13               0

已选择 7 行。

然后以连续序列为左表，七天数据为右表，即可呈现完美的登录记录。

select b.vdate,nvl(c.cnt,b.vcnt) from
(select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8) b
left join
( select a.vdate,count(*) as cnt from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
group by a.vdate ) c
on b.vdate=c.vdate
order by b.vdate

SQL> select b.vdate,nvl(c.cnt,b.vcnt) from
  2  (select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8) b
  3  left join
  4  ( select a.vdate,count(*) as cnt from
  5  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
  6  group by a.vdate ) c
  7  on b.vdate=c.vdate
  8  order by b.vdate;

VDATE      NVL(C.CNT,B.VCNT)
---------- -----------------
10.07                      1
10.08                      1
10.09                      1
10.10                      2
10.11                      2
10.12                      2
10.13                      2

已选择 7 行。

 

为了验证生成连续序列的必要性，我们可以删除表中数据，然后插入以下行：

insert into AccessHistory values(1,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(2,2,to_date('2021.10.12 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(3,3,to_date('2021.10.11 08:00:00','yyyy.MM.dd hh24:mi:ss'));
insert into AccessHistory values(4,1,to_date('2021.10.13 08:00:00','yyyy.MM.dd hh24:mi:ss'));

现在七天数据不是慢的了，看看执行中间sql的效果：

select a.vdate,count(*) from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
group by a.vdate order by vdate

SQL> select a.vdate,count(*) from
  2  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
  3  group by a.vdate order by vdate;

VDATE        COUNT(*)
---------- ----------
10.11               1
10.12               1
10.13               2

看吧，只有三天，前四天为零的记录全不在了。

而执行最终sql：

select b.vdate,nvl(c.cnt,b.vcnt) from
(select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8) b
left join 
( select a.vdate,count(*) as cnt from
(select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
group by a.vdate ) c
on b.vdate=c.vdate
order by b.vdate

SQL> select b.vdate,nvl(c.cnt,b.vcnt) from
  2  (select to_char((trunc(sysdate-6)+(level-1)),'mm.dd') as vdate,0 as vcnt from dual connect by level<8) b
  3  left join
  4  ( select a.vdate,count(*) as cnt from
  5  (select to_char(logindate,'mm.dd') as vdate,userid from AccessHistory where logindate>trunc(sysdate-6)) a
  6  group by a.vdate ) c
  7  on b.vdate=c.vdate
  8  order by b.vdate;

VDATE      NVL(C.CNT,B.VCNT)
---------- -----------------
10.07                      0
10.08                      0
10.09                      0
10.10                      0
10.11                      1
10.12                      1
10.13                      2

已选择 7 行。

七天数据全在，没有遗漏。

 

有些人觉得中间sql就够了，然后将数据拿到服务器端，用Java生成7天的连续序列，再把数据挨个往里填，这样多了一次IO，不划算，还是尽量在DB端把数据整理完最好。

END