SQL GROUP BY与PARTITION OVER问题：指定邮编内按物业和房间数求均价

需求：在指定邮编（0001）范围内，将同一邮编下不同郊区的同物业类型、同房间数的房源价格求平均，得到按物业类型（property）和房间数（rooms）分组的均价表。

示例原始数据 #

期望结果 #

尝试的SQL语句 #

SELECT `property`, `rooms`, ROUND(AVG(`price`) 
OVER (PARTITION BY `property`,`rooms`),0) 
AS avg_price FROM `suburbs` WHERE `postcode` = '0001' GROUP BY `postcode`

不加GROUP BY时返回重复行，添加GROUP BY后仅返回1行，调整逻辑未成功，需指导正确写法。

你的核心问题是窗口函数和GROUP BY的错误混用：窗口函数AVG() OVER()会为每一行数据计算对应分组的平均值，所以不加GROUP BY会返回所有原始行，每行都带上同组的均价；而按postcode分组只能得到一行结果，完全不符合需求。

推荐写法：聚合函数+GROUP BY分组 #

直接用聚合函数对分组后的价格求平均，这是最直观且高效的方案：

SELECT 
    `property`,
    `rooms`,
    ROUND(AVG(`price`), 0) AS avg_price
FROM `suburbs`
WHERE `postcode` = '0001'
GROUP BY `property`, `rooms`
ORDER BY `property`, `rooms`;

逻辑说明

1. WHERE postcode = '0001'：先筛选出指定邮编的所有房源；
2. GROUP BY property, rooms：将物业类型和房间数完全相同的房源归为一组；
3. AVG(price)：计算每个分组的价格平均值，ROUND()用来将结果保留为整数；
4. ORDER BY：让输出结果和期望格式的排序一致，保证输出顺序稳定（可选，但建议添加）。

备选写法：窗口函数+DISTINCT去重 #

如果一定要用窗口函数实现，可以配合DISTINCT去掉重复的均值行：

SELECT DISTINCT
    `property`,
    `rooms`,
    ROUND(AVG(`price`) OVER (PARTITION BY `property`, `rooms`), 0) AS avg_price
FROM `suburbs`
WHERE `postcode` = '0001'
ORDER BY `property`, `rooms`;

这种写法能得到正确结果，但效率不如直接GROUP BY，仅作为场景扩展参考。

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