如何通过SQL计算有无WiFi的AirBnB房源平均价格差？

咱们先拆解你原来的SQL为什么跑不通，再给你更简洁的解决方案～

首先说核心问题：

• 错误的关联逻辑：你把无WiFi的房源集合（子查询A）和有WiFi的房源集合（子查询B）用A.id = B.id关联，这相当于在找同一个房源同时既没有WiFi又有WiFi——但每个房源的id是主键，对应唯一的wifi状态（0或1），根本不存在这样的房源，所以查询结果必然是空的。
• 错误的计算逻辑：就算有匹配的记录，avg(B.price - A.price)计算的是“每一对有/无WiFi房源价格差的平均值”，但你实际需要的是“有WiFi房源的平均价格”减去“无WiFi房源的平均价格”，这完全是两个概念。

更简便的正确写法 #

我们可以用条件聚合一步完成计算，同时还能直接得到价格差的绝对值：

SELECT 
    -- 有WiFi vs 无WiFi的平均价格差
    (AVG(CASE WHEN a.wifi = 1 THEN b.price END) - 
     AVG(CASE WHEN a.wifi = 0 THEN b.price END)) AS average_price_difference,
    -- 价格差的绝对值
    ABS(AVG(CASE WHEN a.wifi = 1 THEN b.price END) - 
        AVG(CASE WHEN a.wifi = 0 THEN b.price END)) AS absolute_price_difference
FROM Billing b
INNER JOIN Amenities a ON b.id = a.id;

逻辑解释：

1. CASE WHEN a.wifi = 1 THEN b.price END会筛选出所有有WiFi房源的价格，AVG()会自动忽略NULL值，得到有WiFi房源的平均价格；同理得到无WiFi房源的平均价格。
2. 直接对两个平均值做减法，就是你要的平均价格差；用ABS()包裹就能得到绝对值。

另一种直观写法（子查询分别计算平均值） #

如果你觉得子查询更易读，也可以先分别算出两个平均值，再做差：

SELECT 
    (wifi_avg - no_wifi_avg) AS average_price_difference,
    ABS(wifi_avg - no_wifi_avg) AS absolute_price_difference
FROM
    (SELECT AVG(price) AS wifi_avg 
     FROM Billing 
     JOIN Amenities ON Billing.id = Amenities.id 
     WHERE wifi = 1) AS wifi_stats,
    (SELECT AVG(price) AS no_wifi_avg 
     FROM Billing 
     JOIN Amenities ON Billing.id = Amenities.id 
     WHERE wifi = 0) AS no_wifi_stats;

这个写法是先分别计算两个分组的平均价格，再将两个单行结果做笛卡尔积（因为都是单行，所以没问题），最后计算差值和绝对值。

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