PostgreSQL聚合查询报错：字段需在GROUP BY或聚合函数中使用

表结构与数据 #

需求 #

找到与指定地址（示例：Gompelbaan 121, Mol）最近的10条记录，计算这些记录中h1和h2列的平均值。

尝试过程与问题 #

能正常获取10条最近邻记录的h1、h2值：

SELECT h1, h2
FROM table1
WHERE municipality = 'Mol' and street = 'Gompelbaan'
ORDER BY @(SELECT substring(SPLIT_PART(street_number, '-', 1) from '^[0-9]+')::DECIMAL - 121)
limit 10

但直接计算平均值时触发报错：

SELECT AVG(h1)
FROM table1
WHERE municipality = 'Mol' and street = 'Gompelbaan'
ORDER BY @(SELECT substring(SPLIT_PART(street_number, '-', 1) from '^[0-9]+')::DECIMAL - 121)
limit 10

报错信息：

column "table1.street_number" must appear in the GROUP BY clause or be used in an aggregate function

改用FILTER改写后依然报错：

SELECT AVG(h1) FILTER(WHERE municipality = 'Mol' and street = 'Gompelbaan') FROM table1 ORDER BY @(SELECT substring(SPLIT_PART(street_number, '-', 1) from '^[0-9]+') :: DECIMAL - 121) limit 10

报错信息：

subquery uses ungrouped column "table1.street_number" from outer query

解决方案 #

问题根源是聚合函数和排序逻辑的执行顺序冲突：聚合会先对全表符合条件的数据计算平均值，而排序用到的street_number既没被分组也没被聚合，导致语法错误。正确思路是先筛选出最近的10条记录，再对这个子集计算平均值，可以用子查询或CTE实现。

方法1：子查询实现 #

SELECT AVG(h1) AS avg_h1, AVG(h2) AS avg_h2
FROM (
    SELECT h1, h2
    FROM table1
    WHERE municipality = 'Mol' AND street = 'Gompelbaan'
    ORDER BY @(substring(SPLIT_PART(street_number, '-', 1) FROM '^[0-9]+')::DECIMAL - 121)
    LIMIT 10
) AS nearest_records;

方法2：CTE（公共表表达式）实现 #

WITH nearest_records AS (
    SELECT h1, h2
    FROM table1
    WHERE municipality = 'Mol' AND street = 'Gompelbaan'
    ORDER BY @(substring(SPLIT_PART(street_number, '-', 1) FROM '^[0-9]+')::DECIMAL - 121)
    LIMIT 10
)
SELECT AVG(h1) AS avg_h1, AVG(h2) AS avg_h2
FROM nearest_records;

说明 #

• 子查询/CTE先完成筛选、排序、取前10的操作，得到目标数据集
• 外层查询针对这个子集计算平均值，此时street_number只在子查询中用于排序，无需处理聚合或分组问题
• 可以同时计算h1和h2的平均值，一次查询完成需求

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