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