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