我试图找出 oracle 中每分钟的登录次数。我有如下表格。登录和注销是时间戳(6)数据类型
login | logout
--------------------------------+--------------------------------
14-JAN-21 05.10.38.769000000 AM | 14-JAN-21 05.14.55.046000000 AM
15-JAN-21 10.14.24.304000000 AM | 15-JAN-21 10.17.32.591000000 AM
对于每条记录,我都在寻找像下面这样的会议记录
login | logout | minutesBetween
--------------------------------+---------------------------------+---------------
14-JAN-21 05.10.38.769000000 AM | 14-JAN-21 05.14.55.046000000 AM | 4
15-JAN-21 10.14.24.304000000 AM | 15-JAN-21 10.17.32.591000000 AM | 3
现在我正在尝试使用 with 子句每分钟生成登录记录,如下所示
with outer as (
select
login,
logout,
extract(minute from ((logout - login) )) AS minutesBetween
from logins_test
),
inner as (
select
login + numtodsinterval(rownum,'MINUTE'),
rownum
from outer
connect by level <= minutesBetween
)
select *
from inner;
结果 I',得到的是
login + numtodsinterval(rownum,'MINUTE') | rownum
-----------------------------------------+--------
14-JAN-21 05.11.38.769000000 AM | 1
14-JAN-21 05.12.38.769000000 AM | 2
14-JAN-21 05.13.38.769000000 AM | 3
14-JAN-21 05.14.38.769000000 AM | 4
15-JAN-21 10.19.24.304000000 AM | 5
14-JAN-21 05.16.38.769000000 AM | 6
15-JAN-21 10.21.24.304000000 AM | 7
14-JAN-21 05.18.38.769000000 AM | 8
14-JAN-21 05.19.38.769000000 AM | 9
15-JAN-21 10.24.24.304000000 AM | 10
14-JAN-21 05.21.38.769000000 AM | 11
15-JAN-21 10.26.24.304000000 AM | 12
14-JAN-21 05.23.38.769000000 AM | 13
14-JAN-21 05.24.38.769000000 AM | 14
14-JAN-21 05.25.38.769000000 AM | 15
15-JAN-21 10.30.24.304000000 AM | 16
14-JAN-21 05.27.38.769000000 AM | 17
15-JAN-21 10.32.24.304000000 AM | 18
14-JAN-21 05.29.38.769000000 AM | 19
14-JAN-21 05.30.38.769000000 AM | 20
15-JAN-21 10.35.24.304000000 AM | 21
14-JAN-21 05.32.38.769000000 AM | 22
我期待每分钟有 1 条记录,所以总共有 7 条记录
但我得到的记录比我预期的要多。有人可以帮忙吗?
它可以通过递归 CTE 轻松实现,因为connect by
将上一步中的行相乘。
并且ROWNUM
是在整个数据集上计算的,因此对于多个输入行,您永远不会有连续的时间。
with logins_test as ( select 1 as id, current_timestamp as login, current_timestamp + NUMTODSINTERVAL(10, 'MINUTE') as logout from dual union all select 2 as id, current_timestamp + NUMTODSINTERVAL(2, 'MINUTE') as login, current_timestamp + NUMTODSINTERVAL(6, 'MINUTE') as logout from dual ) , outer as ( select id, login, logout, extract(minute from ((logout - login) )) AS minutesBetween from logins_test ) , inner (id, login, newtime, minutesbetween, lvl) as ( select id, login, login, minutesbetween, 0 as lvl from outer union all /*Increment time by LEVEL minutes*/ select id, login, login + NUMTODSINTERVAL(lvl + 1, 'MINUTE'), minutesbetween, lvl + 1 from inner where lvl < minutesbetween ) select * from inner order by id, lvl;
身份证 | 登录 | 新时代 | 分钟之间 | LVL -: | :------------------------------------- | :------------------------------------- | -------------: | --: 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 10 | 0 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.28.32.109571000 上午 +00:00 | 10 | 1 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 10 | 2 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.30.32.109571000 上午 +00:00 | 10 | 3 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.31.32.109571000 上午 +00:00 | 10 | 4 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.32.32.109571000 上午 +00:00 | 10 | 5 2 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 4 | 0 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.33.32.109571000 上午 +00:00 | 10 | 6 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.34.32.109571000 上午 +00:00 | 10 | 7 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.35.32.109571000 上午 +00:00 | 10 | 8 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.36.32.109571000 上午 +00:00 | 10 | 9 1 | 14-JAN-21 11.27.32.109571000 上午 +00:00 | 14-JAN-21 11.37.32.109571000 上午 +00:00 | 10 | 10 2 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 14-JAN-21 11.30.32.109571000 上午 +00:00 | 4 | 1 2 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 14-JAN-21 11.31.32.109571000 上午 +00:00 | 4 | 2 2 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 14-JAN-21 11.32.32.109571000 上午 +00:00 | 4 | 3 2 | 14-JAN-21 11.29.32.109571000 上午 +00:00 | 14-JAN-21 11.33.32.109571000 上午 +00:00 | 4 | 4
db<>在这里摆弄
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句