含SUM与DECODE的GROUP BY查询报ORA-00934错误求助

错误根源 #

ORA-00934错误的核心原因是：GROUP BY子句中不允许使用窗口函数（如MAX() OVER ()）。窗口函数的执行时机在分组聚合（GROUP BY）之后，数据库无法在分组阶段计算窗口函数的结果，因此直接在GROUP BY中包含带OVER()子句的函数会触发该错误。

修正方案 #

需要先通过子查询或公共表表达式（CTE）计算出MELO字段的窗口函数结果，再在外层执行分组聚合操作。

方案1：使用CTE（推荐） #

WITH facturas_preprocessed AS (
    SELECT 
        CLT_NOMBRE1,
        -- 若zpk为空则取同组最大zpk，否则保留zpk（优化原DECODE逻辑，避免非空时返回NULL）
        COALESCE(zpk, MAX(zpk) OVER (PARTITION BY factura_ha, facturanumero, locacion)) MELO,
        fct_neto
    FROM tbl_facturas
    WHERE NETOZ != 0 
      AND lugar_lectura = '902'
      AND anio = '2023'
)
SELECT 
    CLT_NOMBRE1,
    MELO,
    SUM(fct_neto) AS fct_neto
FROM facturas_preprocessed
GROUP BY CLT_NOMBRE1, MELO;

方案2：使用子查询 #

SELECT 
    CLT_NOMBRE1,
    MELO,
    SUM(fct_neto) AS fct_neto
FROM (
    SELECT 
        CLT_NOMBRE1,
        DECODE(zpk, NULL, MAX(zpk) OVER (PARTITION BY factura_ha, facturanumero, locacion)) MELO,
        fct_neto
    FROM tbl_facturas
    WHERE NETOZ != 0 
      AND lugar_lectura = '902'
      AND anio = '2023'
) sub_query
GROUP BY CLT_NOMBRE1, MELO;

额外优化提示 #

原DECODE(zpk, NULL, MAX(...))的逻辑会在zpk非空时返回NULL，如果业务需求是保留非空的zpk值，建议改用COALESCE(zpk, MAX(...))，这样逻辑更清晰且符合常规业务场景。

内容的提问来源于stack exchange，提问作者Andrea Alfo