Oracle查询新增关联列遇‘非分组表达式’错误求助

错误原因 #

你遇到的not in group expression错误，是因为Oracle的GROUP BY规则要求：SELECT列表中所有非聚合函数、非相关子查询的列，必须全部出现在GROUP BY子句中。你的SQL里substr(ncp.host,1,4)和substr(ncp.device,6)这两个派生列没有加入GROUP BY，导致语法报错。

同时，针对200万行的大表，原SQL的相关子查询写法性能会很差，每行都要单独执行一次子查询，建议改用窗口函数优化。

方案一：修正GROUP BY解决语法错误（兼容原逻辑） #

直接将SELECT中的所有非聚合列加入GROUP BY即可解决报错：

SELECT  ncp.host AS Host,
        substr(ncp.host,1,4) AS IP,
        ncp.node_name AS Node_Name,
        substr(ncp.device,6) AS technology,
        ncp.grouping,
        (SELECT listagg(ncp1.node_name,',') within group(ORDER BY ncp1.node_name) 
         FROM node.plan ncp1 
         WHERE ncp1.host_name = ncp.host_name
           AND ncp1.serving_group = ncp.serving_group
           AND ncp1.node_name != ncp.node_name) AS Sibling_Nodes
FROM node.plan ncp
LEFT JOIN node_sum.sum ncs ON ncp.site_id = ncs.site_id
WHERE ncp.flag = 'N'
GROUP BY ncp.host,
         substr(ncp.host,1,4),  -- 新增：将派生列加入GROUP BY
         ncp.node_name,
         substr(ncp.device,6),  -- 新增：将派生列加入GROUP BY
         ncp.grouping

方案二：窗口函数优化（大表推荐） #

针对200万行的海量数据，窗口函数只需扫描一次数据完成聚合，性能远优于相关子查询：

WITH group_agg AS (
    SELECT 
        ncp.host,
        substr(ncp.host,1,4) AS IP,
        ncp.node_name AS Node_Name,
        substr(ncp.device,6) AS technology,
        ncp.grouping,
        -- 聚合当前host_name+serving_group组内所有节点（含自身）
        listagg(ncp.node_name, ',') WITHIN GROUP (ORDER BY ncp.node_name) 
            OVER (PARTITION BY ncp.host_name, ncp.serving_group) AS all_siblings
    FROM node.plan ncp
    LEFT JOIN node_sum.sum ncs ON ncp.site_id = ncs.site_id
    WHERE ncp.flag = 'N'
)
SELECT 
    host AS Host,
    IP,
    Node_Name,
    technology,
    grouping,
    -- 移除自身节点名，处理空值和多余逗号
    CASE
        WHEN all_siblings = Node_Name THEN NULL
        ELSE TRIM(BOTH ',' FROM REPLACE(REPLACE(all_siblings, ','||Node_Name, ''), Node_Name||',', ''))
    END AS Sibling_Nodes
FROM group_agg
-- 如果关联node_sum.sum后无重复行，可去掉GROUP BY提升性能
GROUP BY host, IP, Node_Name, technology, grouping, all_siblings;

额外优化建议 #

1. 给node.plan表的host_name和serving_group字段创建联合索引，大幅提升聚合查询速度。
2. 如果node_sum.sum与node.plan关联后不会产生重复行，可以删除GROUP BY子句，进一步降低查询耗时。

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