Oracle分析视图中使用SUM聚合函数报错ORA-00934求助

问题描述 #

尝试创建Oracle分析视图，用SUM聚合函数汇总指定层级的数量，代码如下：

CREATE OR REPLACE ANALYTIC VIEW inventory_av
USING INVENTORY_FACT
DIMENSION BY (
    inventory_attribute_dim KEY (LOCATION_ID)
    REFERENCES (LOCATION_ID)
    HIERARCHIES (inventory_hier DEFAULT)
)
MEASURES (
   QUANTITY FACT QUANTITY ,
   QUANTITY_SUM as (SUM(QUANTITY))
);

执行后报错：

Error report -
ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:

错误原因 #

Oracle分析视图的MEASURES子句不支持直接使用SUM这类组函数定义度量，必须使用分析视图专属的聚合语法声明聚合逻辑，否则会触发ORA-00934错误。

修正后的代码 #

CREATE OR REPLACE ANALYTIC VIEW inventory_av
USING INVENTORY_FACT
DIMENSION BY (
    inventory_attribute_dim KEY (LOCATION_ID)
    REFERENCES (LOCATION_ID)
    HIERARCHIES (inventory_hier DEFAULT)
)
MEASURES (
   QUANTITY FACT QUANTITY,
   QUANTITY_SUM AS AGGREGATE(QUANTITY) USING SUM
);

说明 #

• 采用AGGREGATE(基础度量) USING 聚合函数的语法替代直接调用SUM函数，这是Oracle分析视图定义聚合度量的标准方式。
• 定义完成后，查询分析视图时，Oracle会自动根据指定的层级（inventory_hier）对QUANTITY_SUM执行相应的汇总计算。

内容的提问来源于stack exchange，提问作者Neeraj Behera