如何使用sql从oracle中的时间戳生成登录记录?

雪松花瓶

我试图找出 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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

如何使用相同的时间戳将多个记录插入Oracle

如何使用sql中的时间戳列从表中获取过去30天的记录

如何使用联接在SQL Server中基于时间戳获取最新记录

如何使用ORACLE对时间戳按时间戳排序的前一个非空记录进行求和?

Oracle SQL中的时间戳转换

Oracle SQL中的时间戳+数字小时

如何在 Spark Structured Streaming 中为每个微批次的书面记录生成时间戳?

如何在oracle中两个时间戳之间选择记录

如何基于日期从Oracle中的当前日期的时间戳值将记录分组

如何从oracle的时间戳列中获取基于当前月份和年份的记录

如何使用Java生成此时间戳

Oracle SQL 中的时间戳转换为 EPOCH 时间

如何选择时间戳之间状态的记录?T-SQL

如何使用给定的时间戳在mongodb中查找每天的最后一条记录?

如何从Oracle时间戳值中仅提取时间分量?

如何使用时间戳作为参考合并 SQL Server 2014 中同一个表中的记录

如何查看bash历史记录中的时间戳?

如何添加时间戳以使用cron记录输出条目?

如何使用 unix 时间戳获取最近 24 小时的记录

如何从SQL中的日期/时间字段中删除时间戳?

如何从时间戳生成哈希?

如何生成Unix时间戳?

如何正确生成时间戳

SQL获取登录和注销时间戳

如何在Oracle中修剪时间戳字段的毫秒数

2条记录时间戳sql的区别

如何使用时间戳作为MongoDB和Node.js中的字段按天对记录进行分组

如何根据时间在sql中合并记录

SparkR。SQL。使用时间戳在滚动时间窗口内计数满足条件的记录