在R中使用SQL查询符合高度条件的山脉时遇分组函数使用错误求助

错误原因分析 #

你的SQL语句存在两个核心问题：

• WHERE子句不能直接使用聚合函数：AVG(Height)是聚合函数，用于计算多行数据的平均值，而WHERE是在聚合计算前逐行过滤数据，此时聚合结果尚未生成，因此会触发Invalid use of group function [1111]错误。
• 条件逻辑矛盾：AVG(Height) > 100 AND AVG(Height) < 100没有任何值能同时满足，这会导致即使语法正确也查不到结果。

修正方案 #

要实现“高度与所有山脉平均高度差值小于100米”的需求，需要先计算整体平均高度，再用该值过滤数据，以下是两种可行的写法：

方案1：子查询计算整体平均 #

通过子查询先获取所有山脉的平均高度，再在WHERE子句中比较每个山体高度与该平均值的差值：

sql6 = "SELECT Name, Height, (SELECT AVG(Height) FROM mountain) AS AvgHeight
        FROM mountain
        WHERE ABS(Height - (SELECT AVG(Height) FROM mountain)) < 100" 

result6 = dbSendQuery(con, sql6)
df6 = dbFetch(result6)
head(df6)

方案2：窗口函数（适用于支持窗口函数的数据库，如MySQL 8+、PostgreSQL等） #

使用AVG(Height) OVER()窗口函数为每行数据附加整体平均高度，再通过子查询过滤差值符合条件的记录：

sql6 = "SELECT Name, Height, AvgHeight
        FROM (
            SELECT Name, Height, AVG(Height) OVER() AS AvgHeight
            FROM mountain
        ) AS sub_query
        WHERE ABS(Height - AvgHeight) < 100" 

result6 = dbSendQuery(con, sql6)
df6 = dbFetch(result6)
head(df6)

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