阿里云
发表主题 回复主题
  • 354阅读
  • 9回复

oracle 离线时间计算

级别: 论坛粉丝
发帖
640
云币
1286



有一张这样子的表。
oracle  离线时间计算-oracle 时间计算

这个on_off_flag表示的是0为离线,1为在线。

用create_time作为标识,每天统计昨天一整天的离线时间。

统计昨天的时间可以这样写。

  1. where create_time

  2. between

  3.                                   gintime(trunc(sysdate - 1) +

  4.                                           (1) / (24 * 60))  and

  5.                                   gintime(trunc(sysdate) + (1) / (24 * 60))


复制代码gintime是自己定义的函数。目的是把create_time转换成数字的形式。

但是其他的不知道要怎么写了。

离线时间是这样算的。当on_off_flag=1时表示他在线,那么用他的的 at_time时间减去上一条记录的时间(lag函数)。就能算出离线的时间。因为如果on_off_flag是1 那么上一条肯定是0.但是有可能碰到隔天的情况。比如我在前天的23:00离线。在昨天的19:41上线。那么我只统计昨天的00:01到19:41上线的时间。

如果他的上一条没有记录了。就用at_time减去trunc(sysdate - 1) + (1) / (24 * 60)(就是每天的最早时间的00:01)来计算离线的时间。

还有一种就是当on_off_flag=0时,开始算离线。那如果他的下一条找不到on_off_flag=1的。那就说明他是到了今天才上线的。

这种时候就要用trunc(sysdate) + (1) / (24 * 60)-这条记录的at_time了。

最后把结果sum一下。

举个例子上面那张表的离线时间就是2015-07-30 19:42:23 - 2015-07-30 19:41:00 得出的时间加上 2015-07-31 00:01 - 2015-07-30 23:03:17的时间。

当然不只一个终端那么简单。

还有一张终端表。像这样
oracle  离线时间计算-oracle计算时间差函数

这里面ter_id有的 全部都要统计离线时间。

我怕你们不理解什么意思。如果不理解我会及时解释  到底是什么情况。请大家帮帮忙。oracle  离线时间计算-oracle 计算时间差值oracle  离线时间计算-oracle 计算时间差oracle  离线时间计算-oracle如何计算时间差oracle  离线时间计算-oracle 时间计算




级别: *
发帖
*
云币
*




这样的判断 用if then可行吗?
级别: *
发帖
*
云币
*




我懂为什么了。

36号终端只有一条记录,照理来说它应该减去00:01的时间 得到28532的结果。

但是用in做批量查询时。 36号终端的上一条就变得有数据了。他就会减上一条记录。不会去减00:01了。

我看看写个循环。一次只查询一个ID试试看。
级别: *
发帖
*
云币
*




不需要循环,使用分析函数时,带上partition by 子句。

例如:

lead(on_off_flag) over (partition by ter_id order by at_time)


意为:按ter_id列的取值分组,各分组内按at_time升序排序。
级别: *
发帖
*
云币
*



西瓜哥 语句我写出来了。

  1. select ter_id, case   when on_off_flag=1 then at_time- lag(at_time,1, gintime(trunc(sysdate - 1) + (1) / (24 * 60))) over (order by at_time)

  2.   when  on_off_flag=0 and lead(on_off_flag) over (order by at_time)  is null then

  3. gintime(trunc(sysdate ) + (1) / (24 * 60))-at_time  

  4.    end as qwe  from tbl_s_terminal_online where ter_id in  (select ter_id from tbl_s_car_following_tmp )and

  5. create_time between

  6. gintime(trunc(sysdate - 1) + (1) / (24 * 60)) and

  7.                           gintime(trunc(sysdate) + (1) / (24 * 60))


复制代码这个再做一个嵌套查询就可以了。

但是现在有一个问题。我查询出来的结果不对。当使用ter_id in这张终端表的时候
oracle  离线时间计算-oracle计算时间差函数

36号的结果是11114
oracle  离线时间计算-oracle 计算时间差值



而单独查询时
oracle  离线时间计算-oracle 计算时间差

结果是28532

oracle  离线时间计算-oracle如何计算时间差


为什么用in  批量查询时  和用= 单独查询 差别那么大?

28532 才是正确结果



oracle  离线时间计算-oracle 时间计算
级别: *
发帖
*
云币
*




感谢西瓜哥。

最终语句出来了。

  1. select ter_id, sum(nvl(qwe, 0))

  2.   from (select ter_id,

  3.                case

  4.                  when on_off_flag = 1 then

  5.                   at_time -

  6.                   lag(at_time,

  7.                       1,

  8.                       gintime(trunc(sysdate - 1) + (1) / (24 * 60)))

  9.                   over(partition by ter_id order by at_time)

  10.                  when on_off_flag = 0 and lead(on_off_flag)

  11.                   over(partition by ter_id order by at_time) is null then

  12.                   gintime(trunc(sysdate) + (1) / (24 * 60)) - at_time

  13.                end as qwe

  14.           from tbl_s_terminal_online

  15.          where ter_id in (select id

  16.                             from tbl_s_terminal

  17.                            where cp_id = 1

  18.                               or cp_id = 3)

  19.            and create_time between

  20.                gintime(trunc(sysdate - 1) + (1) / (24 * 60)) and

  21.                gintime(trunc(sysdate) + (1) / (24 * 60)))

  22. group by ter_id


复制代码目标达成oracle  离线时间计算-oracle计算时间差函数oracle  离线时间计算-oracle 计算时间差值

级别: *
发帖
*
云币
*




if then 是plsql的语法,现在我们用SQL一条语句给写出来,要用case when
级别: *
发帖
*
云币
*



会出什么问题?我没明白
oracle  离线时间计算-oracle 计算时间差

这个时间永远都是取到0:01:00的时间啊。统计前3天就是7月31号 00:01 开始一直到8月3号的 00:01的 刚好3天嘛。

这个函数gintim就是用来转换时间的。

这个转换以后就可以和at_time相减了。 也是精确到秒

  1. create or replace function gIntime(itiem in date) return number is

  2.   Result number;


  3. begin


  4. Result:= (itiem -to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))*86400;

  5.   return(Result);

  6. end gIntime;


复制代码
oracle  离线时间计算-oracle如何计算时间差



相减的结果就是秒

oracle  离线时间计算-oracle 时间计算




要转换成date 用另一个函数转换。





级别: *
发帖
*
云币
*




水平又提升了。


但我觉得如果where 中的条件不是

create_time between

               gintime(trunc(sysdate - 1) + (1) / (24 * 60)) and

               gintime(trunc(sysdate) + (1) / (24 * 60))

而是

create_time between

               gintime(trunc(sysdate - 3) + (1) / (24 * 60)) and

               gintime(trunc(sysdate) + (1) / (24 * 60))


即统计分析近三天的数据时,

你在select 部分直接用sysdate来获取当前时间的方法,就可能会出问题了。而应该用at_time中的值。


其次,从你的样例数据中看,at_time是精确到秒,但你在计算时,使用trunc(sysdate) + (1) / (24 * 60),精度是到分钟。这可能也会造成数据统计上有误差或错误。
级别: *
发帖
*
云币
*


给你提供一个思路:

用分析函数lead,按ter_id分组,

若当前行的on_off_flag=0,且下一行的on_off_flag=1,则把下一行的at_time做为离线结束时间;

若当前行的on_off_flag=0,且下一行的on_off_flag=0,则把本行与at_time同一天的23:59:59做为离线结束时间;

若当前行的on_off_flag=1,则把本行的at_time做为离线结束时间。


各行的离线结束时间-at_time的差值即为离线时长


最后按ter_id汇总
发表主题 回复主题
« 返回列表
«12345678910»
共10页
上一主题下一主题

限100 字节
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
 
验证问题: ECS是阿里云提供的什么服务? 正确答案:云服务器
上一个 下一个

      版权声明

      开发者论坛为你提供“oracle 离线时间计算”的内容,论坛中还有更多关于 oracle中trunc函数 的内容供你使用,该内容是网友上传,与开发者论坛无关,如果需要删除请联系zixun-group@service.aliyun.com,工作人员会在5个工作日内回复您。