大家好，欢迎来到IT知识分享网。
有一张登录历史表：
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语句帮忙（连续序列生成请参考：【Oracle】查历史表里最近七天有多少人留下过登录记录）。
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
免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://yundeesoft.com/34281.html
