时间戳

首页 > 解决方案 > 在 Postgres 中执行 sql 查询的问题

sql - 在 Postgres 中执行 sql 查询的问题

问题描述

我想执行此查询，但无法在 where 条件中使用 max()，请帮助我执行此查询：

SELECT AVG(pushing_force) 
from drawing 
where pushing_force > max(drawing_force) 
group by coil_id 
order by coil_id

ERROR: ERROR: Aggregate functions are not allowed in WHERE<br>
LINE 1: ...pushing_force) from drawing where pushing_force > max(drawin...
                                                             ^

我想计算每个线圈的 push_force 高于最大drawing_force 的平均值。它不应该从 0 开始计算。例如，如果在线圈 push_force = 80 和 drawing_force =60 中，它应该计算从 60 到 80 的平均推力

标签： sqlpostgresqlgroup-by

解决方案

尝试这个

SELECT AVG(pushing_force) 
  FROM drawing d1 
 WHERE pushing_force > (
       SELECT max(drawing_force)
         FROM drawing d2 
        WHERE d1.coil_id = d2.coil_id
        )
GROUP BY coil_id
ORDER BY coil_id

类似的方法，但可能更快，看起来像

SELECT AVG(d1.pushing_force) 
  FROM drawing d1, (
       SELECT coil_id, max(drawing_force) as mdrawing_force
         FROM drawing
        GROUP BY coil_id
       ) d2 
 WHERE d1.pushing_force > d2.mdrawing_force
   AND d1.coil_id = d2.coil_id
GROUP BY d1.coil_id
ORDER BY d1.coil_id

推荐阅读