在 Oracle 中计算求和时间 [英] Calculate sum time in Oracle
本文介绍了在 Oracle 中计算求和时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在 Oracle 中有这个表，email 和 time 是 varchar.
I have this table in Oracle, email and time are 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 中可以吗?
I want to get sum of the time and group by email. Is it possibile in 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 中计算求和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
