Oracle 嵌套分组无法返回唯一值

askTom 2018-01-17

115

问题描述

嗨，汤姆
我正在使用以下环境 (我认为这是相关的，因为从11迁移到12c后类似的查询中断了):

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production                                      
CORE 12.1.0.2.0 Production                                                  
TNS for Linux: Version 12.1.0.2.0 - Production                              
NLSRTL Version 12.1.0.2.0 - Production复制

以下查询

select trunc(SEC), SUM(SEC_COUNT) 
from (
       select CREATED as SEC, COUNT(* ) as SEC_COUNT 
       from ALL_OBJECTS group by CREATED
     ) 
group by trunc(SEC) 
order by 1 asc
;

returns this result

TRUNC(SECOND)   SUM(SECOND_COUNT)
8-Feb-2012 9 
8-Feb-2012 1 
8-Feb-2012 55
8-Feb-2012 64
8-Feb-2012 58
...复制

分组列显示了许多重复的值，我期望只有一个 (每天分组)。
真正的查询还处理每秒的最大计数，这不允许用单个聚合粒度重写。

感谢您指出我对此的误解。

问候，
斯特凡

专家解答

我看到的是一样的:

select trunc( sec ) dt,
       sum( sec_count )
from (
  select created as sec,
         count(*) as sec_count
  from   all_objects
  group by created
)
group by trunc( sec )
order by 1;

DT                     SUM(SEC_COUNT)   
07-JUL-2014 00:00:00                 70 
07-JUL-2014 00:00:00                 30 
07-JUL-2014 00:00:00                 80 
07-JUL-2014 00:00:00                 87 
07-JUL-2014 00:00:00                 64 
07-JUL-2014 00:00:00                 49 
07-JUL-2014 00:00:00                 50 
07-JUL-2014 00:00:00                 74 
07-JUL-2014 00:00:00                 95 
07-JUL-2014 00:00:00                 56 
07-JUL-2014 00:00:00                 40 
07-JUL-2014 00:00:00                 77 
07-JUL-2014 00:00:00                 58 
...复制

这看起来像是与错误26588069有关 (目前仅报告12.2)。如果需要补丁，请与支持人员联系。

同时，MOS note 26588069.8中建议的以下解决方法可以解决问题:

ALTER SESSION SET "_optimizer_aggr_groupby_elim" = FALSE;

select trunc( sec ) dt,
       sum( sec_count )
from (
  select created as sec,
         count(*) as sec_count
  from   all_objects
  group by created
)
group by trunc( sec )
order by 1;

DT                     SUM(SEC_COUNT)   
07-JUL-2014 00:00:00              89359 
08-AUG-2016 00:00:00                  4 
09-AUG-2016 00:00:00                 55 
10-AUG-2016 00:00:00                  2 
11-AUG-2016 00:00:00                  4 
12-AUG-2016 00:00:00                  2 
15-AUG-2016 00:00:00                  3 
17-AUG-2016 00:00:00                  1 
18-AUG-2016 00:00:00                  2
...复制

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 嵌套分组无法返回唯一值

问题描述

专家解答

评论

相关阅读