如何在不折叠其他列的情况下聚合数据（使用GROUP BY子句）

现有数据表items：

需求是计算全局平均价格（2.5），将其作为新列添加到每一行，最终筛选出价格高于平均价的商品。直接使用AVG(Prices)会将结果压缩为一行，以下是几种可行的实现方式：

1. 窗口函数（推荐方案） #

利用窗口函数AVG(Prices) OVER ()可以在保留原表所有行的前提下，计算全局平均值并广播到每一行：

先查看所有行带平均价的结果 #

SELECT 
    Item,
    Prices,
    AVG(Prices) OVER () AS avg_price
FROM items;

执行后得到的结果符合预期：

直接筛选价格高于平均价的商品 #

SELECT 
    Item,
    Prices,
    AVG(Prices) OVER () AS avg_price
FROM items
WHERE Prices > (SELECT AVG(Prices) FROM items);

或者用CTE先计算再筛选，逻辑更清晰：

WITH item_with_avg AS (
    SELECT 
        Item,
        Prices,
        AVG(Prices) OVER () AS avg_price
    FROM items
)
SELECT * FROM item_with_avg WHERE Prices > avg_price;

2. 交叉连接子查询 #

将平均价作为单行子查询，与原表做交叉连接，让平均价匹配到每一行：

SELECT 
    i.Item,
    i.Prices,
    a.avg_price
FROM items i
CROSS JOIN (SELECT AVG(Prices) AS avg_price FROM items) a
WHERE i.Prices > a.avg_price;

3. GROUP BY 配合窗口函数（不推荐） #

如果数据库支持分组所有非聚合列，可以通过GROUP BY保留原行，再用窗口函数计算平均值：

SELECT 
    Item,
    Prices,
    AVG(Prices) OVER () AS avg_price
FROM items
GROUP BY Item, Prices
HAVING Prices > AVG(Prices) OVER ();

核心原理说明 #

直接使用AVG(Prices)这类聚合函数时，默认会对整个数据集做聚合，仅返回一行结果。而窗口函数通过OVER ()指定窗口范围为整个数据集，既完成了全局聚合，又保留了原表的所有行结构，从而实现聚合结果的广播。

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