如何在WHERE子句中用聚合函数做过滤？解决PostgreSQL报错

错误原因 #

PostgreSQL不允许在WHERE子句中使用聚合函数（如MAX()），因为WHERE的作用是筛选原始行数据，而聚合函数是基于一组行计算得出的结果，逻辑上需要先完成分组/聚合，再对聚合结果进行筛选。

解决方案 #

方案一：使用GROUP BY + HAVING #

将行级过滤条件（fu.id_fleet <> 10）保留在WHERE中，聚合过滤条件移到HAVING子句，同时对SELECT中所有非聚合列进行分组：

SELECT  lf.m_id_unit,
        lf.CAN_freq,
        lf.CAN_blackout,
        lf.GPS_freq,
        lf.GPS_blackout,
        lf.chargeloss
FROM tlm.main_dash_tele_freq_blackout(m_id_unit, p_utz_begin, p_utz_end) lf
JOIN var.vreadings vr ON vr.id_unit = lf.m_id_unit
JOIN dat.fleet_units fu ON fu.id_unit = lf.m_id_unit
WHERE fu.id_fleet <> 10
GROUP BY lf.m_id_unit, lf.CAN_freq, lf.CAN_blackout, lf.GPS_freq, lf.GPS_blackout, lf.chargeloss
HAVING MAX(vr.utz) > p_utz_begin

方案二：使用子查询预计算聚合值 #

先通过子查询算出每个id_unit对应的最大utz，再将子查询结果与主表关联，直接在WHERE中使用预计算的聚合值：

SELECT  lf.m_id_unit,
        lf.CAN_freq,
        lf.CAN_blackout,
        lf.GPS_freq,
        lf.GPS_blackout,
        lf.chargeloss
FROM tlm.main_dash_tele_freq_blackout(m_id_unit, p_utz_begin, p_utz_end) lf
JOIN (
    SELECT id_unit, MAX(utz) AS max_utz
    FROM var.vreadings
    GROUP BY id_unit
) vr_max ON vr_max.id_unit = lf.m_id_unit
JOIN dat.fleet_units fu ON fu.id_unit = lf.m_id_unit
WHERE vr_max.max_utz > p_utz_begin AND fu.id_fleet <> 10

方案选择 #

• 方案一适合主查询需要同时使用其他聚合函数的场景；
• 方案二逻辑更清晰，性能更优（提前完成聚合计算），适合仅用聚合值做过滤的场景。

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