Oracle Database 23ai 开始 AVG 以及 SUM 函数支持 INTERVAL 数据类型，它们可以作为聚合函数或者分析函数使用。
示例表
本文将会使用以下示例表：
create table t1 (id integer,start_time timestamp,end_time timestamp,duration interval day to second generated always as (end_time - start_time) virtual
);insert into t1 (id, start_time, end_time) values (1, timestamp '2024-06-10 08:45:00.0', timestamp '2024-06-10 18:01:00.0');
insert into t1 (id, start_time, end_time) values (2, timestamp '2024-06-11 09:00:00.0', timestamp '2024-06-11 17:00:00.0');
insert into t1 (id, start_time, end_time) values (3, timestamp '2024-06-12 08:00:00.0', timestamp '2024-06-12 17:45:00.0');
insert into t1 (id, start_time, end_time) values (4, timestamp '2024-06-13 07:00:00.0', timestamp '2024-06-13 16:00:00.0');
commit;
查询表中的数据时可以看到基于 START_TIME 和 END_TIME 计算出的时间间隔：
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';select * from t1;ID START_TIME END_TIME DURATION
---------- -------------------- -------------------- --------------------1 2024-06-10 08:45:00 2024-06-10 18:01:00 +00 09:16:00.0000002 2024-06-11 09:00:00 2024-06-11 17:00:00 +00 08:00:00.0000003 2024-06-12 08:00:00 2024-06-12 17:45:00 +00 09:45:00.0000004 2024-06-13 07:00:00 2024-06-13 16:00:00 +00 09:00:00.000000
已知问题
Oracle 数据库很早就支持了 INTERVAL 数据类型的 MIN/MAX 聚合函数和分析函数，例如：
select min(duration) as min_duration,max(duration) as max_duration
from t1;MIN_DURATION MAX_DURATION
-------------------- --------------------
+00 08:00:00.000000 +00 09:45:00.000000select id,start_time,end_time,duration,min(duration) over () as min_duration,max(duration) over () as max_duration
from t1;ID START_TIME END_TIME DURATION MIN_DURATION MAX_DURATION
---------- -------------------- -------------------- -------------------- -------------------- --------------------1 2024-06-10 08:45:00 2024-06-10 18:01:00 +00 09:16:00.000000 +00 08:00:00.000000 +00 09:45:00.0000002 2024-06-11 09:00:00 2024-06-11 17:00:00 +00 08:00:00.000000 +00 08:00:00.000000 +00 09:45:00.0000003 2024-06-12 08:00:00 2024-06-12 17:45:00 +00 09:45:00.000000 +00 08:00:00.000000 +00 09:45:00.0000004 2024-06-13 07:00:00 2024-06-13 16:00:00 +00 09:00:00.000000 +00 08:00:00.000000 +00 09:45:00.000000
但是，如果我们尝试在以上示例中使用 SUM 或者 AVG 函数，将会产生一个错误信息（Oracle 23ai 之前的版本）：
select sum(duration) from t1;*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECONDselect avg(duration) from t1*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
解决方案
Oracle 23ai 新增了 INTERVAL 数据类型的 SUM 和 AVG 函数支持。
select sum(duration) from t1;SUM(DURATION)
---------------------------------------------------------------------------
+000000001 12:01:00.000000000select avg(duration) from t1;AVG(DURATION)
---------------------------------------------------------------------------
+000000000 09:00:15.000000000
这种情况下，SUM 和 AVG 函数也可以作为分析函数使用：
select id,start_time,end_time,duration,sum(duration) over () as sum_duration
from t1;ID START_TIME END_TIME DURATION SUM_DURATION
---------- -------------------- -------------------- -------------------- ------------------------------1 2024-06-10 08:45:00 2024-06-10 18:01:00 +00 09:16:00.000000 +000000001 12:01:00.0000000002 2024-06-11 09:00:00 2024-06-11 17:00:00 +00 08:00:00.000000 +000000001 12:01:00.0000000003 2024-06-12 08:00:00 2024-06-12 17:45:00 +00 09:45:00.000000 +000000001 12:01:00.0000000004 2024-06-13 07:00:00 2024-06-13 16:00:00 +00 09:00:00.000000 +000000001 12:01:00.000000000select id,start_time,end_time,duration,avg(duration) over () as avg_duration
from t1;ID START_TIME END_TIME DURATION AVG_DURATION
---------- -------------------- -------------------- -------------------- ------------------------------1 2024-06-10 08:45:00 2024-06-10 18:01:00 +00 09:16:00.000000 +000000000 09:00:15.0000000002 2024-06-11 09:00:00 2024-06-11 17:00:00 +00 08:00:00.000000 +000000000 09:00:15.0000000003 2024-06-12 08:00:00 2024-06-12 17:45:00 +00 09:45:00.000000 +000000000 09:00:15.0000000004 2024-06-13 07:00:00 2024-06-13 16:00:00 +00 09:00:00.000000 +000000000 09:00:15.000000000
