Oracle 23c的INTERVAL数据类型的聚合

DBA巫师 2024-04-26

177

点个蓝字关注我们

加入我们的微信群，你将获得不仅仅是知识，还能享受免费GPT4-tubro微信机器人+Oracle MOS免费查询+数据库大佬交流，很多志同道合的小伙伴，欢迎加群一起探讨、一起学习、一起进步！

扫描下方二维码添加作者微信，回复“DBA理想”即可开启你的数据库学习之旅。

Oracle 23c 允许DBAers使用SUM
和AVG
聚合和分析函数处理INTERVAL
数据类型。

前置准备：

本文中的示例需要以下表格。

--创建测试表
create table t1 (
  id          number,
  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 '2023-04-10 08:45:00.0', timestamp '2023-04-10 18:01:00.0');
insert into t1 (id, start_time, end_time) values (2, timestamp '2023-04-11 09:00:00.0', timestamp '2023-04-11 17:00:00.0');
insert into t1 (id, start_time, end_time) values (3, timestamp '2023-04-12 08:00:00.0', timestamp '2023-04-12 17:45:00.0');
insert into t1 (id, start_time, end_time) values (4, timestamp '2023-04-13 07:00:00.0', timestamp '2023-04-13 16:00:00.0');
commit;

当我们展示数据时，可以看到虚拟列DURATION显示了START_TIME和END_TIME值之间的INTERVAL。

应用场景：

我们已经可以在INTERVAL
数据类型列上使用MIN
和MAX
聚合和分析函数。



column min_duration format a20
column max_duration format a20


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


SQL>


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 2023-04-10 08:45:00  2023-04-10 18:01:00  +00 09:16:00.000000  +00 08:00:00.000000  +00 09:45:00.000000
         2 2023-04-11 09:00:00  2023-04-11 17:00:00  +00 08:00:00.000000  +00 08:00:00.000000  +00 09:45:00.000000
         3 2023-04-12 08:00:00  2023-04-12 17:45:00  +00 09:45:00.000000  +00 08:00:00.000000  +00 09:45:00.000000
         4 2023-04-13 07:00:00  2023-04-13 16:00:00  +00 09:00:00.000000  +00 08:00:00.000000  +00 09:45:00.0000

如果我们尝试在INTERVAL数据类型上使用SUM或AVG聚合函数，在数据库的早期版本中会得到一个错误。

select sum(duration) from t1;
           *
ERROR at line 1:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND


SQL>


select avg(duration) from t1
           *
ERROR at line 1:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND

解决方案：

Oracle 23c允许我们使用SUM
和AVG
聚合函数处理INTERVAL
数据类型。

select sum(duration) from t1;


SUM(DURATION)
---------------------------------------------------------------------------
+000000001 12:01:00.000000000


SQL>


select avg(duration) from t1;


AVG(DURATION)
---------------------------------------------------------------------------
+000000000 09:00:15.000000000


SQL>

我们也可以使用SUM和AVG作为分析函数，来展示原始数据和聚合值。

set linesize 120
column sum_duration format a30


select id,
       start_time,
       end_time,
       duration,
       sum(duration) over () as sum_duration
from t1;


        ID START_TIME           END_TIME             DURATION             SUM_DURATION
---------- -------------------- -------------------- -------------------- ------------------------------
         1 2023-04-10 08:45:00  2023-04-10 18:01:00  +00 09:16:00.000000  +000000001 12:01:00.000000000
         2 2023-04-11 09:00:00  2023-04-11 17:00:00  +00 08:00:00.000000  +000000001 12:01:00.000000000
         3 2023-04-12 08:00:00  2023-04-12 17:45:00  +00 09:45:00.000000  +000000001 12:01:00.000000000
         4 2023-04-13 07:00:00  2023-04-13 16:00:00  +00 09:00:00.000000  +000000001 12:01:00.000000000


SQL>


column avg_duration format a30


select id,
       start_time,
       end_time,
       duration,
       avg(duration) over () as avg_duration
from t1;


        ID START_TIME           END_TIME             DURATION             AVG_DURATION
---------- -------------------- -------------------- -------------------- ------------------------------
         1 2023-04-10 08:45:00  2023-04-10 18:01:00  +00 09:16:00.000000  +000000000 09:00:15.000000000
         2 2023-04-11 09:00:00  2023-04-11 17:00:00  +00 08:00:00.000000  +000000000 09:00:15.000000000
         3 2023-04-12 08:00:00  2023-04-12 17:45:00  +00 09:45:00.000000  +000000000 09:00:15.000000000
         4 2023-04-13 07:00:00  2023-04-13 16:00:00  +00 09:00:00.000000  +000000000 09:00:15.000000000

往期推荐

Oracle oswbb工具安装与介绍

SQL*Plus工具的继任者--Oracle sqlcl

Oracle的SQL调化健康检查脚本介绍

项目管理实战一：Oracle到达梦的迁移经验分享

摆脱Oracle 错误码困扰，免费公益查询MOS

文章转载自DBA巫师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 23c的INTERVAL数据类型的聚合

前置准备：

应用场景：

解决方案：

评论