Oracle Database 23ai新特性：增加聚合函数对INTERVAL数据类型支持

在Oracle早期的版本，聚合函数和分析函数MIN/MAX 就支持了INTERVAL 数据类型。但是，当我们使用SUM或AVG函数对INTERVAL 数据类型进行运算时，就会报错。

随着Oracle Database 23ai 的发布，增加了 AVG 以及 SUM 函数对INTERVAL 数据类型的支持，它们可以作为聚合函数或者分析函数使用。

创建测试表以及测试数据

create table TEST1 (

  id          integer,

  start_time  timestamp,

  end_time    timestamp,

  duration    interval day to second generated always as (end_time - start_time) virtual

);

insert into TEST1 (id, start_time, end_time) values (1, timestamp '2024-07-01 08:00:00.0', timestamp '2024-07-01 14:05:00.0');

insert into TEST1 (id, start_time, end_time) values (2, timestamp '2024-07-02 09:00:00.0', timestamp '2024-07-02 15:00:00.0');

insert into TEST1 (id, start_time, end_time) values (3, timestamp '2024-07-03 08:30:00.0', timestamp '2024-07-03 16:45:00.0');

insert into TEST1 (id, start_time, end_time) values (4, timestamp '2024-07-04 07:00:00.0', timestamp '2024-07-04 17:30:00.0');

commit;

查询表中的数据时可以看到基于 START_TIME 和 END_TIME 计算出的时间间隔

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

SET LINESIZE 200

COL END_TIME FOR A25

COL START_TIME FOR A25

COL DURATION FOR A25

select * from TEST1 ;

Oracle早期版本（12.1.0.2）对INTERVAL 数据类型的 MIN/MAX 聚合函数和分析函数的支持

COL MIN_DURATION FOR A25

COL MAX_DURATION FOR A25

select min(duration) as min_duration,

       max(duration) as max_duration

from TEST1;

select id,

       start_time,

       end_time,

       duration,

       min(duration) over () as min_duration,

       max(duration) over () as max_duration

from TEST1;

在此版本中，使用 SUM 或者 AVG 函数，将会产生一个错误信息，错误信息如下：

select sum(duration) from TEST1;

select avg(duration) from TEST1;

Oracle 23ai 新增了 INTERVAL 数据类型的 SUM 和 AVG 函数支持

select sum(duration) from TEST1;

select avg(duration) from TEST1;

作为分析函数使用：

select id,

       start_time,

       end_time,

       duration,

       sum(duration) over () as sum_duration

from TEST1;

select id,

       start_time,

       end_time,

       duration,

       avg(duration) over () as avg_duration

from TEST1;

由此可以看到，在Oracle 23ai中，支持了SUM 和 AVG 函数作为分析函数使用。