如何在SQL查询中对同一列计算多组平均值并求差值

问题分析 #

你需要按街区分组，计算私人房间（roomType_id=1）和整套公寓（roomType_id=2）的平均价格差，并显示街区名称。之前的尝试存在以下问题：

• 版本1、2仅单独查询单种房型的均价，无法在同一结果集中对比计算差值；
• 联合查询使用Listings l INNER JOIN Listings l2 on l.id = l2.id逻辑错误——单个房源不可能同时属于两种房型，导致无有效结果。

方案1：条件聚合（推荐） #

通过CASE WHEN在同一查询中分别计算两种房型的均价，直接得到差值，效率更高：

SELECT
    n.name AS "Neighborhood Name",
    AVG(CASE WHEN l.roomType_id = 1 THEN l.price END) AS "Private Room Avg Price",
    AVG(CASE WHEN l.roomType_id = 2 THEN l.price END) AS "Entire Home/Apt Avg Price",
    -- 可根据需求调整差值顺序（比如私人房间减整套公寓）
    AVG(CASE WHEN l.roomType_id = 2 THEN l.price END) - AVG(CASE WHEN l.roomType_id = 1 THEN l.price END) AS "Price Difference"
FROM Listings l
INNER JOIN Neighborhoods n ON l.neighborhood_id = n.id
WHERE l.roomType_id IN (1, 2) -- 仅筛选目标房型，排除无关数据
GROUP BY n.id, n.name
-- 可选：过滤掉缺少任一房型数据的街区
HAVING AVG(CASE WHEN l.roomType_id = 1 THEN l.price END) IS NOT NULL 
   AND AVG(CASE WHEN l.roomType_id = 2 THEN l.price END) IS NOT NULL;

方案2：子查询关联 #

先分别统计两种房型的街区均价，再通过街区ID关联计算差值：

SELECT
    n.name AS "Neighborhood Name",
    p.private_avg AS "Private Room Avg Price",
    e.entire_avg AS "Entire Home/Apt Avg Price",
    e.entire_avg - p.private_avg AS "Price Difference"
FROM Neighborhoods n
LEFT JOIN (
    SELECT neighborhood_id, AVG(price) AS private_avg
    FROM Listings
    WHERE roomType_id = 1
    GROUP BY neighborhood_id
) p ON n.id = p.neighborhood_id
LEFT JOIN (
    SELECT neighborhood_id, AVG(price) AS entire_avg
    FROM Listings
    WHERE roomType_id = 2
    GROUP BY neighborhood_id
) e ON n.id = e.neighborhood_id
-- 可选：仅保留两种房型都存在的街区
WHERE p.private_avg IS NOT NULL AND e.entire_avg IS NOT NULL;

补充说明 #

• 两种方案都不需要关联RoomTypes表，因为你已经明确知道roomType_id的对应值（1=私人房间，2=整套公寓），直接用ID过滤更高效；如果后续房型ID可能变动，再关联RoomTypes用roomType字段过滤即可。
• 若需要保留只有一种房型的街区，去掉HAVING或WHERE中的过滤条件即可。

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