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