MySQL自连接搭配聚合函数筛选同区域高于均价房源方案优化

问题背景 #

现有listings表结构如下：

字段含义见名知意。需求为：查询与指定id的房源同state、同county、面积为指定房源±50%、status取值为1或2，且price_per_acre高于符合上述条件的所有房源平均单价的条目。

原报错原因 #

最初的写法报错invalid use of group function，是因为聚合函数AVG()不能直接放在WHERE子句中使用，WHERE是在分组前过滤行，而聚合函数的计算依赖分组后的结果集，本次需求取的是全量符合条件结果的平均值，不需要分组，所以不能直接在WHERE里写和AVG的比较。

优化实现方案 #

方案1：MySQL 8.0+ 窗口函数（最高效、可读性最好） #

使用窗口函数计算全局平均值，只需要扫描一次符合条件的结果集，不需要重复写两次关联逻辑：

WITH qualified_listings AS (
    SELECT
        t1.id,
        t1.price_per_acre,
        AVG(t1.price_per_acre) OVER() AS avg_price
    FROM listings t1
    JOIN listings t2 
        ON t1.area >= t2.area * 0.5 
        AND t1.area <= t2.area * 1.5 
        AND t1.status IN (1, 2) 
        AND t1.state = t2.state 
        AND t1.county = t2.county
    WHERE t2.id = 1731961
)
SELECT id, price_per_acre, avg_price
FROM qualified_listings
WHERE price_per_acre > avg_price;

用CTE把符合基础条件的房源和全局平均价查出来，外层直接过滤即可，逻辑清晰，只执行一次基础关联逻辑，性能比重复写两次关联的版本高很多。

方案2：兼容MySQL 5.x的写法 #

如果需要兼容低版本MySQL，可以用子查询先查平均值：

SELECT 
    t1.id,
    t1.price_per_acre,
    t.avg_price
FROM listings t1
JOIN listings t2 
    ON t1.area >= t2.area * 0.5 
    AND t1.area <= t2.area * 1.5 
    AND t1.status IN (1, 2) 
    AND t1.state = t2.state 
    AND t1.county = t2.county
JOIN (
    SELECT AVG(t1.price_per_acre) AS avg_price
    FROM listings t1
    JOIN listings t2 
        ON t1.area >= t2.area * 0.5 
        AND t1.area <= t2.area * 1.5 
        AND t1.status IN (1, 2) 
        AND t1.state = t2.state 
        AND t1.county = t2.county
    WHERE t2.id = 1731961
) t ON t1.price_per_acre > t.avg_price
WHERE t2.id = 1731961;

内容的提问来源于stack exchange，提问作者Arman P.