oracle之在 Oracle 中计算求和时间

虾米哥 阅读:15 2025-01-19 22:14:33 评论:0

我在 Oracle 中有这个表,电子邮件和时间是 varchar。

Email               Time 
----------------    ----------- 
paolo@gmail.com     00:10:40 
paolo@gmail.com     00:40:10 
paolo@gmail.com     01:10:20 
paolo@gmail.com     00:43:40 
paolo@gmail.com     00:42:40 
chiara@gmail.com    00:30:40 
chiara@gmail.com    00:54:10 
chiara@gmail.com    00:47:40 
simo@gmail.com      00:50:40 
simo@gmail.com      01:05:40 
simo@gmail.com      00:45:40 
simo@gmail.com      00:51:40 
simo@gmail.com      00:36:40 

我想通过电子邮件获取时间和组的总和。在 Oracle 中可以吗?

更新

时间就是持续时间。

请您参考如下方法:

使用这个查询

SQL> WITH table_(Email, Time) as ( 
  2      select 'paolo@gmail.com', '00:10:40' from dual union all 
  3      select 'paolo@gmail.com', '00:40:10' from dual union all 
  4      select 'paolo@gmail.com', '01:10:20' from dual union all 
  5      select 'paolo@gmail.com', '00:43:40' from dual union all 
  6      select 'paolo@gmail.com', '00:42:40' from dual union all 
  7      select 'chiara@gmail.com', '00:30:40' from dual union all 
  8      select 'chiara@gmail.com', '00:54:10' from dual union all 
  9      select 'chiara@gmail.com', '00:47:40' from dual union all 
 10      select 'simo@gmail.com', '00:50:40' from dual union all 
 11      select 'simo@gmail.com', '01:05:40' from dual union all 
 12      select 'simo@gmail.com', '00:45:40' from dual union all 
 13      select 'simo@gmail.com', '00:51:40' from dual union all 
 14      select 'simo@gmail.com', '00:36:40' from dual ) 
 15  --------------------- 
 16  -- End if sample data 
 17  --------------------- 
 18  SELECT email, numtodsinterval(sum(SUBSTR(TIME, 1, 2)*3600 + SUBSTR(TIME, 4, 2)*60 + SUBSTR(TIME, 7, 2)), 'SECOND') total_duration 
 19    FROM table_ 
 20   GROUP BY email; 

输出:

EMAIL            TOTAL_DURATION 
---------------- -------------------------------------------------------------------------------- 
chiara@gmail.com +000000000 02:12:30.000000000 
simo@gmail.com   +000000000 04:10:20.000000000 
paolo@gmail.com  +000000000 03:27:30.000000000 

所以,你的查询是

SELECT email, numtodsinterval(sum(SUBSTR(TIME, 1, 2)*3600 + SUBSTR(TIME, 4, 2)*60 + SUBSTR(TIME, 7, 2)), 'SECOND') total_duration 
  FROM table_ 
 GROUP BY email; 


标签:oracle
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号