Oracle Database 23ai新特性之INTERVAL聚合函数增强

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.000000
         2 2024-06-11 09:00:00  2024-06-11 17:00:00  +00 08:00:00.000000
         3 2024-06-12 08:00:00  2024-06-12 17:45:00  +00 09:45:00.000000
         4 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.000000


select 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.000000
         2 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.000000
         3 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.000000
         4 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 SECOND


select 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.000000000


select 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.000000000
         2 2024-06-11 09:00:00  2024-06-11 17:00:00  +00 08:00:00.000000  +000000001 12:01:00.000000000
         3 2024-06-12 08:00:00  2024-06-12 17:45:00  +00 09:45:00.000000  +000000001 12:01:00.000000000
         4 2024-06-13 07:00:00  2024-06-13 16:00:00  +00 09:00:00.000000  +000000001 12:01:00.000000000


select 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.000000000
         2 2024-06-11 09:00:00  2024-06-11 17:00:00  +00 08:00:00.000000  +000000000 09:00:15.000000000
         3 2024-06-12 08:00:00  2024-06-12 17:45:00  +00 09:45:00.000000  +000000000 09:00:15.000000000
         4 2024-06-13 07:00:00  2024-06-13 16:00:00  +00 09:00:00.000000  +000000000 09:00:15.000000000